DB2 Connect User's Guide

Trace Utility (ddcstrc)

The ddcstrc utility provides a record of the data interchanged between the DB2 Connect workstation (on behalf of the database client) and the host or AS/400 database server management system.

As a database administrator (or application developer), you may find it useful to understand how this flow of data works, because this knowledge can help you determine the origin of a particular problem. For example, say you issue a CONNECT TO database statement for a host or AS/400 database server, but the command fails and you receive an unsuccessful return code. If you understand exactly what information was conveyed to the host or AS/400 database server management system, you may be able to determine the cause of the failure even if the return code information is general. Many failures are caused by simple user errors.

Output from ddcstrc lists the data streams exchanged between the DB2 Connect workstation and the host or AS/400 database server management system. Data sent to the host or AS/400 database server is labeled SEND BUFFER and data received from the host or AS/400 database server is labeled RECEIVE BUFFER.

If a receive buffer contains SQLCA information, it will be followed by a formatted interpretation of this data and labeled SQLCA. The SQLCODE field of an SQLCA is the unmapped value as returned by the host or AS/400 database server. (For more on mapping, see SQLCODE Mapping.) The send and receive buffers are arranged from the oldest to the most recent within the file. Each buffer has:

The process ID
A SEND BUFFER, RECEIVE BUFFER, or SQLCA label. The first DDM command or object in a buffer is labeled DSS TYPE.

The remaining data in send and receive buffers is divided into five columns, consisting of:

A byte count.
Columns 2 and 3 represent the DRDA data stream exchanged between the two systems, in ASCII or EBCDIC.
An ASCII representation of columns 2 and 3.
An EBCDIC representation of columns 2 and 3.

For more information about DDM, refer to:

DB2 for OS/390 Reference for Remote DRDA Requesters and Servers
Distributed Relational Database Reference
Distributed Data Management Architecture Level 3: Reference

Trace Syntax

This command is invoked from the operating system command prompt with the following syntax:

Figure 9. Syntax of the ddcstrc Command

Note: The syntax of this command may vary slightly depending on the operating system that you are using. For example, / may be used in place of - for the OS/2 operating system.

Trace Parameters

on

Turns on DB2 Connect tracing of DRDA flows with the host or AS/400 database server.

off

Turns off DB2 Connect tracing of DRDA flows with the host or AS/400 database server.

-i

Timestamps will be included in the trace information.

-r

Traces DRDA data streams received from the host or AS/400 server system.

-s

Traces DRDA data streams sent to the host or AS/400 database server.

-c

Traces the SQLCA received from the host or AS/400 database server.

The default is -r, -s, and -c.

-l=length

Specifies the size of the buffer used to store the trace information. The default is 1M, and the minimum is 64K.

-t=tracefile

Specifies the destination for the trace; tracefile may be the name of a file or a standard device. If a file name is specified without a complete path, the current path is used for the missing parts. The default file name is ddcstrc.dmp.

-p=pid

Traces events only for this process. If -p is not specified, all processes for the user's instance are written to the output file.

Note:

For a remote client, the pid can be found in the Agent ID field returned by the database system monitor.

For more information, see Database System Monitor.

Trace Output

The ddcstrc utility writes the following information to tracefile:

-r
- Type of DRDA reply/object
- Receive buffer
-s
- Type of DRDA request
- Send buffer
-c
- SQLCA
CPI-C error information
- Receive function return code
- Severity
- Protocol used
- API used
- Function
- CPI-C return code
- Error number
- Internal return code.
SNA error information
- Receive function return code
- Severity
- Protocol used
- Function
- Partner LU name
- Error number.
TCP/IP error information
- Receive function return code
- Severity
- Protocol used
- API used
- Function
- Error number.

Notes:

A value of zero for the exit code indicates that the command completed successfully, and a non-zero value indicates that it did not.
The fields returned vary based on the API used. The SNA API is used only for 2PC SPM connections.
The fields returned vary based on the platform on which DB2 Connect is running, even for the same API.
If ddcstrc sends the output to a file that already exists, the old file will be erased unless the permissions on the file do not allow it to be erased.

Analyzing the Trace Output File

The following pages show example output illustrating some DRDA data streams exchanged between DB2 Connect workstations and a host or AS/400 database server. From the user's viewpoint, a CONNECT TO database command has been issued using the command line processor.

Figure 10 uses DB2 Connect Enterprise Edition Version 7 and DB2 Universal Database for OS/390 Version 5.1 over an APPC connection.

Figure 11 uses DB2 Connect Enterprise Edition Version 7 and DB2 Universal Database for OS/390 Version 5.1 over a TCP/IP connection.

Figure 10. Example of Trace Output (APPC connection)

1 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 95212; tid 537115484; node 0; cpid 0; sec 0; nsec 0; tpoint 177 SEND BUFFER: EXCSAT RQSDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 006AD04100010064 10410020115E8482 .j.A...d.A. .^.. .|}..........;db 0010 F282974040404040 4040404040404040 ...@@@@@@@@@@@@@ 2bp 0020 4040F0F0F0F1F7F3 C5C3000C116DA685 @@...........m.. 000173EC..._we 0030 81A2859340400013 115AC4C2F240C396 ....@@...Z...@.. asel ...]DB2 Co 0040 95958583A340F54B F200141404140300 .....@.K........ nnect 5.2....... 0050 0414440003240700 05240F0003000D11 ..D..$...$...... ................ 0060 47D8C4C2F261F6F0 F0F00085D0010002 G....a.......... .QDB2/6000.e}... 0070 007F200100162110 E2C1D56DC6D9C1D5 .. ...!....m.... ."......SAN_FRAN 0080 C3C9E2C3D6404040 40400006210F2407 .....@@@@@..!.$. CISCO ...... 0090 000D002FD8E3C4E2 D8D3C1E2C3000C11 .../............ ....QTDSQLASC... 00A0 2EE2D8D3F0F5F0F2 F0003C210437E2D8 ..........

3 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 95212; tid 537115484; node 0; cpid 0; sec 0; nsec 0; tpoint 177 SEND BUFFER: RDBCMM RQSDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 000AD00100010004 200E ........ . ..}....... 4 DB2 fnc_data gateway_drda_ar sqljcrecv (1.35.10.81) pid 95212; tid 537115484; node 0; cpid 0; sec 0; nsec 0; tpoint 178 RECEIVE BUFFER: ENDUOWRM RPYDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 002BD05200010025 220C000611490004 .+.R...%"....I.. ..}............. 0010 00162110E2C1D56D C6D9C1D5C3C9E2C3 ..!....m........ ....SAN_FRANCISC 0020 D640404040400005 211501000BD00300 .@@@@@..!....... O .......}.. 0030 0100052408FF ...$.. ...... 5 DB2 fnc_data gateway_drda_ar sqljmsca (1.35.10.108) pid 95212; tid 537115484; node 0; cpid 0; sec 0; nsec 0; tpoint 179 SQLCA SQLCAID: SQLCA SQLCABC: 136 SQLCODE: 0 SQLERRML: 0 SQLERRMC: SQLERRP: DSN SQLERRD[0->5]: 00000000, 00000000, 00000000, 00000000, 00000000, 00000000 SQLWARN(0->A): , , , , , , , , , , SQLSTATE: 00000

Figure 11. Example of Trace Output (TCP/IP connection)

1 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 80286; tid 537125164; node 0; cpid 0; sec 0; nsec 0; tpoint 177 SEND BUFFER: EXCSAT RQSDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 006ED04100010068 10410020115E8482 .n.A...h.A. .^.. .>}..........;db 0010 F282974040404040 4040404040404040 ...@@@@@@@@@@@@@ 2bp 0020 4040F0F0F0F1F3F9 F9C5000C116DA685 @@...........m.. 0001399E..._we 0030 81A2859340400013 115AC4C2F240C396 ....@@...Z...@.. asel ...]DB2 Co 0040 95958583A340F54B F200181404140300 .....@.K........ nnect 5.2....... 0050 0514740005240700 05240F0003144000 ..t..$...$....@. .............. . 0060 05000D1147D8C4C2 F261F6F0F0F00010 ....G....a...... .....QDB2/6000.. 0070 D0410002000A106D 000611A20003003C .A.....m.......< }......_...s.... 0080 D04100030036106E 000611A200030016 .A...6.n........ }......>...s.... 0090 2110E2C1D56DC6D9 C1D5C3C9E2C3D640 !....m.........@ ..SAN_FRANCISCO 00A0 40404040000C11A1 9781A2A2A6969984 @@@@............ ....password 00B0 000A11A0A4A28599 8984009CD0010004 ................ ....userid..}... 00C0 0096200100162110 E2C1D56DC6D9C1D5 .. ...!....m.... .o......SAN_FRAN 00D0 C3C9E2C3D6404040 40400006210F2407 .....@@@@@..!.$. CISCO ...... 00E0 000D002FD8E3C4E2 D8D3C1E2C3000C11 .../............ ....QTDSQLASC... 00F0 2EE2D8D3F0F5F0F2 F0003C210437E2D8 ..........

3 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 80286; tid 537125164; node 0; cpid 0; sec 0; nsec 0; tpoint 177 SEND BUFFER: RDBCMM RQSDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 000AD00100010004 200E ........ . ..}....... 4 DB2 fnc_data gateway_drda_ar sqljcrecv (1.35.10.81) pid 80286; tid 537125164; node 0; cpid 0; sec 0; nsec 0; tpoint 178 RECEIVE BUFFER: ENDUOWRM RPYDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 002BD05200010025 220C000611490004 .+.R...%"....I.. ..}............. 0010 00162110E2C1D56D C6D9C1D5C3C9E2C3 ..!....m........ ....SAN_FRANCISC 0020 D640404040400005 211501000BD00300 .@@@@@..!....... O .......}.. 0030 0100052408FF ...$.. ...... 5 DB2 fnc_data gateway_drda_ar sqljmsca (1.35.10.108) pid 80286; tid 537125164; node 0; cpid 0; sec 0; nsec 0; tpoint 179 SQLCA SQLCAID: SQLCA SQLCABC: 136 SQLCODE: 0 SQLERRML: 0 SQLERRMC: SQLERRP: DSN SQLERRD[0->5]: 00000000, 00000000, 00000000, 00000000, 00000000, 00000000 SQLWARN(0->A): , , , , , , , , , , SQLSTATE: 00000

The following information is captured in the traces:

The process ID (PID) of the client application
The RDB_NAME cataloged in the database connection services (DCS) directory
The DB2 Connect CCSID(s)
The host or AS/400 database server CCSID(s)
The host or AS/400 database server management system with which the DB2 Connect system is communicating.

The first buffer contains the Exchange Server Attributes (EXCSAT) and Access RDB (ACCRDB) commands sent to the host or AS/400 database server management system. It sends them as a result of a CONNECT TO database command.

The next buffer contains the reply that DB2 Connect received from the host or AS/400 database server management system. It contains an Exchange Server Attributes Reply Data (EXCSATRD) and an Access RDB Reply Message (ACCRDBRM).

Analyzing EXCSAT and ACCRDB

The EXCSAT command contains the workstation name of the client specified by the Server Name (SRVNAM) object, which is code point X'116D', according to DDM specification. The EXCSAT command is found in the first buffer. Within the EXCSAT command, the values X'116DA68581A28593' (coded in CCSID 500) are translated to weasel once the X'116D' is removed.

The EXCSAT command also contains the EXTNAM (External Name) object, which is often placed in diagnostic information on the host or AS/400 database management system. It consists of a 20-byte application ID followed by an 8-byte process ID (or 4-byte process ID and 4-byte thread ID). It is represented by code point X'115E', and in this example its value is db2bp_32 padded with blanks followed by 0000BE5C. On a UNIX-based database client, this value can be correlated with the ps command, which returns process status information about active processes to standard output.

The ACCRDB command contains the RDB_NAME in the RDBNAM object, which is code point X'2110'. The ACCRDB command follows the EXCSAT command in the first buffer. Within the ACCRDB command, the values X'2110E2C1D56DC6D9C1D5C3C9E2C3D6' are translated to SAN_FRANCISCO once the X'2110' is removed. This corresponds to the target database name field in the DCS directory.

The accounting string has code point X'2104' (see Implementing Charge-Back Accounting on DB2 Universal Database for OS/390).

The code set configured for the DB2 Connect workstation is shown by locating the CCSID object CCSIDSBC (CCSID for single-byte characters) with code point X'119C' in the ACCRDB command. In this example, the CCSIDSBC is X'0352', which is 850.

If the additional objects CCSIDDBC (CCSID for double-byte characters) and CCSIDMBC (CCSID for mixed-byte characters), with code points X'119D' and X'119E' respectively, are present, the DB2 Connect workstation is configured for DBCS code page support. Since the example output file does not include the two additional code points, the workstation is not configured for DBCS.
Note: The TCP/IP flows contains two new commands: ACCSEC used to access the security manager and exchange supported security mechanisms, and SECCHK, which contains the authentication tokens used to authenticate the end user of the connection. ACCSEC and SECCHK only appear for TCP/IP connections, and they do so between EXCSAT and ACCRDB.

Analyzing EXCSATRD and ACCRDBRM

CCSID values are also returned from the host or AS/400 database server in the Access RDB Reply Message (ACCRDBRM) within the second buffer. This buffer contains the EXCSATRD followed by the ACCRDBRM. The example output file contains CCSID values for the host or AS/400 database server system of 500 (X'01F4', SBCS CCSID).

If DB2 Connect does not recognize the code page coming back from the host or AS/400 database server, SQLCODE -332 will be returned to the user with the source and target code pages. If the host or AS/400 database server doesn't recognize the code set sent from DB2 Connect, it will return VALNSPRM (Parameter Value Not Supported, with DDM code point X'1252'), which gets translated into SQLCODE -30073 for the user.

The ACCRDBRM also contains the parameter PRDID (Product-specific Identifier, with code point X'112E'). The value is X'C4E2D5F0F5F0F1F0'. This hex string corresponds to DSN05010 in EBCDIC. According to standards, DSN is DB2 for MVS/ESA or DB2 Universal Database for OS/390. The version, 5.1, is also indicated. ARI is DB2 for VSE & VM, SQL is DB2 Common Server, and QSQ is DB2 Universal Database for AS/400.

Analyzing Subsequent Buffers

You can analyze subsequent send and receive buffers for additional information. The third buffer contains a commit. The commit command instructs the host or AS/400 database server management system to commit the current unit of work. The fourth buffer is received from the host or AS/400 database server database management system as a result of a commit or rollback. It contains the End Unit of Work Reply Message (ENDUOWRM), which indicates that the current unit of work has ended. In this example, it contains a null SQLCA, indicated by DDM code point X'2408' followed by X'FF'. A null SQLCA (X'2408FF') indicates success (SQLCODE 0). When a receive buffer contains an SQLCA (possibly a null SQLCA), ddcstrc will follow this receive buffer with a formatted interpretation of the SQLCA information.

Figure 12 shows an example of a receive buffer containing an error SQLCA, and the formatted display of the SQLCA. This SQLCA is the result of an attempt to delete rows from a nonexistent table.

Figure 12. Receive Buffer Example

1 DB2 fnc_data gateway_drda_ar sqljcrecv (1.35.10.81) pid 48732; tid 1; node 0; cpid 0; sec 0; nsec 0; tpoint 178 RECEIVE BUFFER: SQLCARD OBJDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 0065D0030001005F 240800FFFFFF34F4 .e....._$.....4. ..}....^.......4 0010 F2F7F0F4C4E2D5E7 D6E3D34000E2C1D5 ...........@.... 2704DSNXOTL .SAN 0020 6DC6D9C1D5C3C9E2 C3D64040404040FF m.........@@@@@. _FRANCISCO . 0030 FFFE0C0000000000 000000FFFFFFFF00 ................ ................ 0040 0000000000000040 4040404040404040 .......@@@@@@@@@ ....... 0050 40400000000FC4C4 C3E2E4E2F14BD4E8 @@...........K.. ....DDCSUS1.MY 0060 E3C1C2D3C5 ..... TABLE 2 DB2 fnc_data gateway_drda_ar sqljmsca (1.35.10.108) pid 48732; tid 1; node 0; cpid 0; sec 0; nsec 0; tpoint 179 SQLCA SQLCAID: SQLCA SQLCABC: 136 SQLCODE: -204 SQLERRML: 15 SQLERRMC: DDCSUS1.MYTABLE SQLERRP: DSNXOTL SQLERRD[0->5]: FFFFFE0C, 00000000, 00000000, FFFFFFFF, 00000000, 00000000 SQLWARN(0->A): , , , , , , , , , , SQLSTATE: 42704

[ Top of Page | Previous Page | Next Page ]