Db2 call trace listing

When the Db2® call trace option is used, IMS Batch Terminal Simulator prints the call trace information for Db2 SQL and IFI calls that were issued by the application.

Db2 SQL call trace information consists of the following information:

Statement type
Program or DBRM name
PLAN name (only in a BTS batch environment)
Statement number
Section number
Error status in the SQL communication area (SQLCA)
IFI SQL statement information (see notes)
Input and output variables

Notes:

When the IFI call trace option is used, only the IFI call trace is available; the statistics report is not printed.
To obtain IFI SQL statement information, Db2 monitor trace class 1 must be active for IFCID 124, and your Db2 authorization ID must have MONITOR2 privilege or SYSADM authority.
If the thread is not currently executing an SQL statement in Db2, IMS Batch Terminal Simulator might not be able to obtain SQL statement data from Db2 IFI. In this case, IMS Batch Terminal Simulator issues the following information as IFI SQL statement information:
```
IFI INFORMATION:
  SQL STATEMENT INFORMATION UNAVAILABLE
```
If ./O SQL=ALL or TSOSQL=ALL is specified, host variable values are printed on BTSOUT or displayed on the TSO terminal in the following cases:
- SQL STATEMENT VARS (values from the applications) and VARS FROM DB2 (values from Db2) when the SQL call ends normally.
- SQL STATEMENT VARS when the SQL call ends abnormally.
- If you use the LOB (large objects) data type, and you specify ./O SQLOBLN=, IMS Batch Terminal Simulator puts the LOB data type and the length on the BTSOUT or the TSO terminal display as follows:
  - LEN= shows the actual data length
  - DATA= shows the LOB data

Subsections:

Db2 call trace options for printing Db2 call trace listing
Examples of the Db2 SQL call trace listing
Description of the Db2 SQL call trace
Example of the IFI call trace listing
Description of the IFI call trace fields

Db2 call trace options for printing Db2 call trace listing

To manage the Db2 call trace listing, specify the following options:

./O SQL= to print SQL call traces
./T SQLTRACE= to print a SQL call trace for each SQL call that is issued by the application program

Examples of the Db2 SQL call trace listing

The following example shows the SQL call trace in the default format.

Figure 1. Example of the Db2 SQL call trace listing

****  SQL CALL- TYPE=CREATE TABLESPACE , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000071, SECTION=00000001  -SQL-    RC= 0000
                                                                                                                           
RESULT OF SQL STATEMENT:                                                                                                   
 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                                                             
 CREATE TABLESPACE  SUCCESSFUL                                                                                             
****  SQL CALL- TYPE=ALTER TABLESPACE  , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000074, SECTION=00000002  -SQL-    RC= 0000
                                                                                                                           
RESULT OF SQL STATEMENT:                                                                                                   
 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                                                             
 ALTER TABLESPACE   SUCCESSFUL                                                                                             
****  SQL CALL- TYPE=CREATE TABLE      , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000076, SECTION=00000003  -SQL-    RC= 0000
                                                                                                                           
RESULT OF SQL STATEMENT:                                                                                                   
 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                                                             
 CREATE TABLE       SUCCESSFUL                                                                                             
****  SQL CALL- TYPE=LOCK              , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000105, SECTION=00000004  -SQL-    RC= 0000
                                                                                                                           
RESULT OF SQL STATEMENT:                                                                                                   
 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                                                             
 LOCK               SUCCESSFUL                                                                                             
****  SQL CALL- TYPE=INSERT            , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000107, SECTION=00000005  -SQL-    RC= 0000
                                                                                                                           
IFI INFORMATION:                                                                                                           
 INSERT INTO BTSDBSS1 . TABLE1 VALUES ( : H , : H , : H , : H , : H , : H  ) ;                      
SQL STATEMENT VARS:                                                                                                        
 VAR #001:  TYPE=CHAR                NULL=N, LEN=       4                                                                  
            DATA='DBST'  
 VAR #002:  TYPE=BIGINT              NULL=N, LEN=       8                                                               
            DATA='  987654321098765432'                                                                                 
 VAR #003:  TYPE=VARCHAR             NULL=N, LEN=      36                                                               
            DATA='DBSTEST DBSTEST DBSTEST DBSTEST DBST'                                                                 
 VAR #004:  TYPE=INTEGER             NULL=N, LEN=       4                                                               
            DATA='        9876'                                                                                         
 VAR #005:  TYPE=DECIMAL             NULL=N, LEN=     9.2                                                               
            DATA='        345000.00'                                                                                    
 VAR #006:  TYPE=FLOAT               NULL=N, LEN=       8                                                               
            DATA=' 7.8900000000000E+02'

The following example shows the SQL call trace in the single-line message format. This format is helpful in identifying each call. You can display the call trace in this format by specifying ./O SQL=YES and ELAPTIME=YES.

Figure 2. Example of the Db2 call trace listing (single-line messages)

****  SQL CALL- TYPE=CREATE TABLESPACE , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000071, SECTION=00000001  -SQL-    RC= 0000 ELAPSED SEC=0.022107
                                                                                                                           
****  SQL CALL- TYPE=ALTER TABLESPACE  , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000074, SECTION=00000002  -SQL-    RC= 0000 ELAPSED SEC=0.010000
                                                                                                                           
****  SQL CALL- TYPE=CREATE TABLE      , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000076, SECTION=00000003  -SQL-    RC= 0000 ELAPSED SEC=0.045123
                                                                                                                           
****  SQL CALL- TYPE=LOCK              , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000105, SECTION=00000004  -SQL-    RC= 0000 ELAPSED SEC=0.010000
                                                                                                                           
****  SQL CALL- TYPE=INSERT            , PROGRAM=PGMF0010, PLAN=BTSTEST1, STATEMENT=00000107, SECTION=00000005  -SQL-    RC= 0000 ELAPSED SEC=0.0234567

Description of the Db2 SQL call trace

The following table summarizes the information that is available for each SQL statement.

Table 1. Db2 SQL call trace listing fields
Field	Description
TYPE=	The type of the SQL statement.
PROGRAM=	The program or DBRM name. This information is available for each call.
PLAN=	The plan name. This information is available only in BTS batch environment.
STATEMENT=	The statement number generated by the precompiler.
SECTION=	The internally used identifier for SQL statements.
RC=	The return code that is returned from Db2.
Error Status	The error status returned from Db2 in the SQL communication area (SQLCA). This includes a message giving the type of error, if any, and specific names and codes when present. Error Status also includes the number of rows inserted, updated, or deleted after a multi-row operation.
ELAPSED SEC=	The elapsed seconds of the SQL statement. This information is available only when ./O ELAPTIME=Y is specified.

In addition, the following information is provided for each type of SQL statement.

Table 2. Db2 SQL call trace listing information for each SQL statement type
SQL statement	Information provided
OPEN	Host variable or parameter marker values (if used) IFI SQL statement information (DECLARE CURSOR or SELECT statement string)
FETCH	Column values for the fetched row (with data types) All column names (if DESCRIBEd)
SELECT	Host variable values in search condition (if used) Column values for the selected row (with data types) IFI SQL statement information (SELECT statement string)
INSERT	Host variable values (if used) Number of rows INSERTed IFI SQL statement information (INSERT statement string)
DELETE	Host variable values (if used) Number of rows DELETEd IFI SQL statement information (DELETE statement string)
UPDATE	Host variable values (if used) Number of rows UPDATEd IFI SQL statement information (UPDATE statement string)
PREPARE	String expression SQLDA field values (if INTO used)
EXECUTE	Parameter marker values (if used) IFI SQL statement information
EXECUTE IMMEDIATE	String expression
DESCRIBE	SQLDA field values IFI SQL statement information

Related reading: For information about the SQL statements, see the Db2 for z/OS® SQL Reference.

Example of the IFI call trace listing

Figure 3. Example of the IFI call trace listing

****  IFI CALL- FUNC=COMMAND , IFI RETURN CODE= 00, IFI REASON CODE= 00000000
****  IFI CALL- FUNC=WRITE   , IFI RETURN CODE= 00, IFI REASON CODE= 00000000
****  IFI CALL- FUNC=READA   , IFI RETURN CODE= 00, IFI REASON CODE= 00000000
****  IFI CALL- FUNC=COMMAND , IFI RETURN CODE= 00, IFI REASON CODE= 00000000

Description of the IFI call trace fields

The following table summarizes the information that is available for each IFI statement.

Table 3. Db2 IFI call trace listing fields
Field	Description
FUNC=	The function of the IFI statement.
IFI RETURN CODE=	The return code is returned from Db2 in the IFI communication area (IFCA), and is displayed in decimal notation.
IFI REASON CODE=	The reason code is returned from Db2 in the IFI communication area (IFCA), and is displayed in hexadecimal notation.
ELAPSED SEC=	The elapsed seconds of the IFI statement. This information is available only when ./O ELAPTIME=Y is specified.