gtpo1mfjOperations

ZSTTD-Display the SQL Trace Table Entries

Use this command to display entries from the structured query language (SQL) trace table on the current subsystem in either of two formats: a summary form or a detail form.

Requirements and Restrictions

The TPF Application Requester (TPFAR) feature must be installed on the TPF system.

Format




Notes:

  1. The detail (DET) parameter is not used with the ALL parameter.

All
displays all the entries (or the maximum number of 20 entries) in the SQL trace table. This parameter is not used with the DET parameter.

nbr
is the number of entries to display in the SQL trace table, beginning with the entries at the end of the table.

ERR
displays only the replies to SQL commands that were returned with an error; that is, the SQLSTATE value does not equal 0.

REP
displays only the replies to SQL commands.

REQ
displays only the requests for SQL commands.

DET
displays details about the replies to SQL commands that were returned with an error (an SQLSTATE value not equal to 0). This parameter is not used with the ALL parameter.

PGM-progname
displays only the SQL trace table entries that were processed by the specified application program, where progname is the 4-character alphanumeric name of an application program.

ECB-address
displays only the SQL trace table entries that were processed by the ECB at the specified address, where address is the 1- to 8-digit hexadecimal system virtual memory (SVM) address.

IS-istream
displays entries from the SQL trace table on the specified I-stream, where istream is a decimal number from 1 to 16.

Additional Information

Online help information is available for this command. To display the help information, enter one of the following:

ZSTTD HELP

ZSTTD ?

Examples

The following information is displayed in the examples:

 I O 
is the direction of the message, where:

 O 
indicates the SQL request is outbound to the DBMS.

 I 
indicates the SQL request is being returned to the application program.

 SQL CODE 
is the SQLCODE returned to the application program after the SQL command is processed. The SQLCODE is displayed only for replies to the application program.

 SQL STATE 
is the SQLSTATE returned to the application program after the SQL command is processed. The SQLSTATE is displayed only for replies to the application program.

 SEC NBR 
is the section number assigned to the SQL command by the DB2 precompiler.

 STMT NBR 
is the statement number assigned to the SQL command by the DB2 precompiler.

 STMT TYPE 
is the statement type of the SQL command.

 PGM 
is the name of the application program processing the SQL command.

 ECB ADDR 
is the SVM address of the ECB processing the SQL command.

 TIME 
is the number of seconds between the time the application program processed the SQL command and the time the application program received the results. The time is displayed only for replies to the application program.

The last 4 entries in the SQL trace table on I-stream 2 are displayed in the following example.

+--------------------------------------------------------------------------------+
|User:   ZSTTD 4 IS-2                                                            |
|                                                                                |
|System: CRDW0004I 09.04.31 SQL TRACE TABLE FOR I-STREAM 2                       |
|        I   SQL  SQL   SEC STMT STMT                ECB                         |
|        O   CODE STATE NBR  NBR TYPE           PGM  ADDR      TIME              |
|        O                1   58 SELECT         QXOE 2F9A40                      |
|        I    100 02000   1   58 SELECT         QXOE 2F9A40   0.054              |
|        O                0   47 COMMIT         QXOE 2F9A40                      |
|        I      0 00000   0   47 COMMIT         QXOE 2F9A40   0.175              |
|        END OF TABLE, 4 ENTRIES DISPLAYED                                       |
+--------------------------------------------------------------------------------+

The last 3 replies to SQL commands are displayed in the following example.

+--------------------------------------------------------------------------------+
|User:   ZSTTD 3 REP IS-2                                                        |
|                                                                                |
|System: CRDW0004I 08.02.23 SQL TRACE TABLE FOR I-STREAM 2                       |
|        I   SQL  SQL   SEC STMT STMT                ECB                         |
|        O   CODE STATE NBR  NBR TYPE           PGM  ADDR      TIME              |
|        I      0 00000   0   42 CONNECT TO     QXOE 2F9A40   1.175              |
|        I    100 02000   1   58 SELECT         QXOE 2F9A40   0.154              |
|        I      0 00000   0   47 COMMIT         QXOE 2F9A40   0.075              |
|        END OF TABLE, 3 ENTRIES DISPLAYED                                       |
+--------------------------------------------------------------------------------+

The last 3 requests for SQL commands that were processed by the ECB at the specified address are displayed in the following example. Notice that 6 entries were requested but only 3 exist in the SQL trace table.

+--------------------------------------------------------------------------------+
|User:   ZSTTD 6 REQ ECB-2F9A40                                                  |
|                                                                                |
|System: CRDW0004I 11.22.50 SQL TRACE TABLE FOR I-STREAM 1                       |
|        I   SQL  SQL   SEC STMT STMT                ECB                         |
|        O   CODE STATE NBR  NBR TYPE           PGM  ADDR      TIME              |
|        I                0   42 CONNECT TO     QXOE 2F9A40                      |
|        I                1   58 SELECT         QXOE 2F9A40                      |
|        I                0   47 COMMIT         QXOE 2F9A40                      |
|        END OF TABLE, 3 ENTRIES DISPLAYED                                       |
+--------------------------------------------------------------------------------+

All the replies to SQL commands that were processed with an error by the QXOE application program are displayed in the following example. Notice that only one entry exists.

+--------------------------------------------------------------------------------+
|User:   ZSTTD ALL ERR PGM-QXOE                                                  |
|                                                                                |
|System: CRDW0004I 08.02.31 SQL TRACE TABLE FOR I-STREAM 1                       |
|        I   SQL  SQL   SEC STMT STMT                ECB                         |
|        O   CODE STATE NBR  NBR TYPE           PGM  ADDR      TIME              |
|        I    100 02000   1   58 SELECT         QXOE 2F9A40   0.154              |
|        END OF TABLE, 1 ENTRIES DISPLAYED                                       |
+--------------------------------------------------------------------------------+

The DETAIL parameter provides the most recent entries in the trace table. In the following example program QXOE has returned an SQLCODE of 100.

The information returned is defined by the relational distributed architecture. The previous example gives the definitions for the fields, except for SQLCAID, SQLCABC, SQLWARN, SQLERRD, SQLERRP, SQLERRML, and SQLERRMSG, which provide various kinds of information and warnings as defined by the remote database.

+--------------------------------------------------------------------------------+
|User:   ZSTTD 1,DET                                                             |
|                                                                                |
|System: SQL DETAIL TRACE FOR I-STREAM 1                                         |
|        PROGRAM: QXOE      ECB ADDRESS:   6B1000   RDB: DB23PRD                 |
|        SQL_CODE: 100      SQL_STATE: 2000                                      |
|        SECTION NUM: 00001     STMT NUM: 00058   STMT TYPE: SELECT              |
|        SQLCAID:   SQLCABC: 00000   SQLWARN:                                    |
|        SQLERRD: 0, 0, 0, 0, 0, 0  SQLERRP:                                     |
|        SQLERRML: 0  SQLERRMSG:                                                 |
|                                                                                |
|        END OF TABLE, 1 ENTRIES DISPLAYED                                       |
+--------------------------------------------------------------------------------+

The following example shows how the summary trace and the detail trace differ. The fields displayed are defined in a previous example.

+--------------------------------------------------------------------------------+
|User:   ZSTTD 8                                                                 |
|                                                                                |
|System:  CRDW0004I 14.40.32 SQL TRACE TABLE FOR I-STREAM 1                      |
|         I   SQL  SQL   SEC STMT STMT                   ECB                     |
|         O   CODE STATE NBR  NBR TYPE              PGM  ADDR        TIME        |
|         O                0   60 CONNECT TO        QXOE   6D6000                |
|         I -30041 57013   0   60 CONNECT TO        QXOE   6D6000   0.003        |
|         O                1  102 SELECT            QXOE   6D6000                |
|         I  -1024 51007   1  102 SELECT            QXOE   6D6000   0.000        |
|         O                0   79 COMMIT            QXOE   6D6000                |
|         I  -1024 51007   0   79 COMMIT            QXOE   6D6000   0.000        |
|         O                0   79 COMMIT            QXOE   6D6000                |
|         I  -1024 51007   0   79 COMMIT            QXOE   6D6000   0.000        |
|         END OF TABLE, 8 ENTRIES DISPLAYED                                      |
|                                                                                |
|User:   ZSTTD 4,DET                                                             |
|                                                                                |
|System: SQL DETAIL TRACE FOR I-STREAM 1                                         |
|        PROGRAM: QXOE      ECB ADDRESS:   6D6000    RDB: DB23PRD                |
|        SQL_CODE: -30041        SQL_STATE: 57013                                |
|        SECTION NUM: 00000     STMT NUM: 00060   STMT TYPE: CONNECT TO          |
|        SQLCAID: SQLCA     SQLCABC: 00136   SQLWARN:                            |
|        SQLERRD: 0, 0, 0, 0, 0, 198  SQLERRP: TPF0CRDA                          |
|        SQLERRML: 0  SQLERRMSG:                                                 |
|                                                                                |
|        PROGRAM: QXOE      ECB ADDRESS:   6D6000    RDB: DB23PRD                |
|        SQL_CODE: -1024        SQL_STATE: 51007                                 |
|        SECTION NUM: 00001     STMT NUM: 00102   STMT TYPE: SELECT              |
|        SQLCAID: SQLCA     SQLCABC: 00136   SQLWARN:                            |
|        SQLERRD: 0, 0, 0, 0, 0, 217  SQLERRP: TPF0CRDA                          |
|        SQLERRML: 0  SQLERRMSG:                                                 |
|                                                                                |
|        PROGRAM: QXOE      ECB ADDRESS:   6D6000    RDB: DB23PRD                |
|        SQL_CODE: -1024        SQL_STATE: 51007                                 |
|        SECTION NUM: 00000     STMT NUM: 00079   STMT TYPE: COMMIT              |
|        SQLCAID: SQLCA     SQLCABC: 00136   SQLWARN:                            |
|        SQLERRD: 0, 0, 0, 0, 0, 217  SQLERRP: TPF0CRDA                          |
|        SQLERRML: 0  SQLERRMSG:                                                 |
|                                                                                |
|        PROGRAM: QXOE      ECB ADDRESS:   6D6000    RDB: DB23PRD                |
|        SQL_CODE: -1024        SQL_STATE: 51007                                 |
|        SECTION NUM: 00000     STMT NUM: 00079   STMT TYPE: COMMIT              |
|        SQLCAID: SQLCA     SQLCABC: 00136   SQLWARN:                            |
|        SQLERRD: 0, 0, 0, 0, 0, 217  SQLERRP: TPF0CRDA                          |
|        SQLERRML: 0  SQLERRMSG:                                                 |
|                                                                                |
|         END OF TABLE, 4 ENTRIES DISPLAYED                                      |
|                                                                                |
+--------------------------------------------------------------------------------+

Related Information

See the TPF Application Requester User's Guide for more information about the structured query language (SQL) and the SQL trace table.