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 DRDA server database 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 DRDA server database, but the command fails and you receive an unsuccessful return code. If you understand exactly what information was conveyed to the DRDA server database 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 DRDA server database management system. Data sent to the DRDA server is labeled SEND BUFFER and data received from the DRDA 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 DRDA server system. (For more on mapping, see Chapter 8. "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, see:

• 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 3. Syntax of the ddcstrc Command

                             +-------+
                             V       |
>>-+-------+-ddcstrc-----on----+--r-++--+-----------+----------->
   +-path--+                   +--s-+   +--l=length-+
                               +--c-+
 
>--off--+--------------+--+--------+---------------------------><
        +--t=tracefile-+  +--p=pid-+
 

Trace Parameters

on

Turns on DRDA application requester trace events.

off

Turns off DRDA application requester trace events.

-r

Traces DRDA data streams received from the DRDA server system.

-s

Traces DRDA data streams sent to the DRDA server system.

-c

Traces the SQLCA received from the DRDA server system.

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

ddcstrc 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.

Analyzing the Trace Output File

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

Figure 4 uses DB2 Connect Enterprise Edition Version 5 and DB2 for OS/390 Version 5.1 over an APPC connection.

Figure 5 uses DB2 Connect Enterprise Edition Version 5 and DB2 for OS/390 Version 5.1 over a TCP/IP connection.

Figure 4. Example of Trace Output (APPC connection)

1 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 48732; tid 1; 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 F282976DF3F24040 4040404040404040 ...m..@@@@@@@@@@ 2bp_32 0020 4040F0F0F0F0C2C5 F5C3000C116DA685 @@...........m.. 0000BE5C..._we 0030 81A2859340400013 115AC4C2F240C396 ....@@...Z...@.. asel ...]DB2 Co 0040 95958583A340F54B F000141404140300 .....@.K........ nnect 5.0....... 0050 0414440003240700 05240F0003000D11 ..D..$...$...... ................ 0060 47D8C4C2F261F6F0 F0F000A0D0010002 G....a.......... .QDB2/6000..}... 0070 009A200100162110 E2C1D56DC6D9C1D5 .. ...!....m.... ........SAN_FRAN 0080 C3C9E2C3D6404040 40400006210F2407 .....@@@@@..!.$. CISCO ...... 0090 000D002FD8E3C4E2 D8D3C1E2C3000C11 .../............ ....QTDSQLASC... 00A0 2EE2D8D3F0F5F0F0 F0003C210437E2D8 .......... 2 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: EXCSATRD OBJDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 0056D04300010050 14430010115EC4C2 .V.C...P.C...^.. ..}....&.....;DB 0010 C1C1F0F6C4C3F0F8 C6F8001414041403 ................ AA06DC08F8...... 0020 0004144400032407 0005240F00030008 ...D..$...$..... ................ 0030 1147D8C4C2F20014 116DE2C1D56DC6D9 .G.......m...m.. ..QDB2..._SAN_FR 0040 C1D5C3C9E2C3D640 4040000C115AC4E2 .......@@@...Z.. ANCISCO ...]DS 0050 D5F0F5F0F1F00060 D0020002005A2201 .......`.....Z". N05010.-}....].. 0060 000611490000000D 002FD8E3C4E2D8D3 ...I...../...... ..........QTDSQL 0070 F3F7F0000C112EC4 E2D5F0F5F0F1F000 ................ 370....DSN05010. 0080 0A00350006119C01 F4002D244E000624 ..5.......-$N..$ ........4...+... 0090 4C00010023244D00 06244FFFFF001911 L...#$M..$O..... <.....(...!..... 00A0 E9C3C1C9C2D4D6D4 D34BE2C6D3E44040 .........K....@@ ZCAIBMOML.SFLU 00B0 404007F6C4C2 @@.... .6DB 3 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 48732; tid 1; 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 48732; tid 1; 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 48732; tid 1; 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 5. Example of Trace Output (TCP/IP connection)

1 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 48732; tid 1; 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 F282976DF3F24040 4040404040404040 ...m..@@@@@@@@@@ 2bp_32 0020 4040F0F0F0F0C2C5 F5C3000C116DA685 @@...........m.. 0000BE5C..._we 0030 81A2859340400013 115AC4C2F240C396 ....@@...Z...@.. asel ...]DB2 Co 0040 95958583A340F54B F000181404140300 .....@.K........ nnect 5.0....... 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 2EE2D8D3F0F5F0F0 F0003C210437E2D8 .......... 2 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: EXCSATRD OBJDSS (ASCII) (EBCDIC) 0 1 2 3 4 5 6 7 8 9 A B C D E F 0123456789ABCDEF 0123456789ABCDEF 0000 005AD04300010054 14430010115EC4C2 .Z.C...T.C...^.. .]}..........;DB 0010 C1C1F0F6C4C3F0F8 C6F8001814041403 ................ AA06DC08F8...... 0020 0004147400052407 0005240F00031440 ...t..$...$....@ ............... 0030 000500081147D8C4 C2F20014116DE2C1 .....G.......m.. ......QDB2..._SA 0040 D56DC6D9C1D5C3C9 E2C3D6404040000C .m.........@@@.. N_FRANCISCO .. 0050 115AC4E2D5F0F5F0 F1F00010D0430002 .Z...........C.. .]DSN05010..}... 0060 000A14AC000611A2 00030015D0420003 .............B.. .......s....}... 0070 000F121900061149 0000000511A40000 .......I........ .............u.. 0080 51D0020004004B22 0100061149000000 Q.....K"....I... .}.............. 0090 0D002FD8E3C4E2D8 D3F3F7F0000C112E ../............. ...QTDSQL370.... 00A0 C4E2D5F0F5F0F1F0 000A00350006119C ...........5.... DSN05010........ 00B0 01F4001E244E0006 244C00010014244D ....$N..$L....$M .4...+...<.....( 00C0 0006244FFFFF000A 11E80915155C01BE ..$O.........\.. ...!.....Y...*.. 3 DB2 fnc_data gateway_drda_ar sqljcsend (1.35.10.80) pid 48732; tid 1; 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 48732; tid 1; 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 48732; tid 1; 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 DRDA server CCSID(s)

• The DRDA server database 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 DRDA server database 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 DRDA server database 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 DRDA server 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 (described in "Implementing Chargeback Accounting") has code point X'2104'.

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 EXCSAT and ACCRDBRM

CCSID values are also returned from the DRDA 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 DRDA server system of 500 (X'01F4', SBCS CCSID).

If DB2 Connect does not recognize the code page coming back from the DRDA server, SQLCODE -332 will be returned to the user with the source and target code pages. If the DRDA 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 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 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 DRDA server database management system to commit the current unit of work. The fourth buffer is received from the DRDA 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 6 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 6. 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

[ DB2 List of Books | Search the DB2 Books ]