Preparing and Executing the Program › Debugging the Application › SQL Trace Facility

SQL Trace Facility

You can use the SQL trace facility to trace execution of the SQL statements in a batch program.

You activate the SQL trace facility by specifying the SYSIDMS parameter SQLTRACE=ON.

In this example, the SQL trace facility reports on the SQL processing for a SELECT statement submitted through IDMSBCF, the batch Command Facility. The trace facility shows the steps in dynamically executing the SELECT, including an automatic CONNECT.

 SELECT R.REFTABLE AS "PARENT",
        K.REFCOLUMN AS "PARENT COLUMN",
        R.NAME AS "RELATIONSHIP"
    FROM SYSTEM.CONSTRAINT R,
         SYSTEM.CONSTKEY   K
    WHERE R.SCHEMA = K.SCHEMA
      AND R.NAME = K.NAME
      AND R.SCHEMA = 'REL'
      AND R.TABLE     = 'C_EMPLOYEE'
      AND R.UNIQUE >= ' '
      OR  R.COMPRESS <= ' ' ;
Verb=07  CONNECT TO SYSSQL                                                              Caller=IDMSBCF   SQLSEQ=000001  *** S Q L
Verb=20  PREPARE-─►  SELECT R.REFTABLE AS "PARENT",                                     Caller=IDMSBCF   SQLSEQ=000008  *** S Q L
Verb=11  DESCRIBE                                                                       Caller=IDMSBCF   SQLSEQ=000005  *** S Q L
Verb=19  OPEN                                                                           Caller=IDMSBCF   SQLSEQ=000007  *** S Q L
Verb=16  FETCH                                                                          Caller=IDMSBCF   SQLSEQ=000006  *** S Q L
  S Q L  SQLCODE=0100  REASON CODE=0000
Verb=03  CLOSE                                                                          Caller=IDMSBCF   SQLSEQ=000002  *** S Q L

PARENT              PARENT COLUMN                     RELATIONSHIP

C_DEPARTMENT        C_DEPT_ID                         DEPT_EMPLOYEE
C_PROJECT           C_PROJ_ID                         EMP_PROJECT

2 rows processed
Verb=05  COMMIT continue                                                                Caller=IDMSBCF   SQLSEQ=000003  *** S Q L
 .
 .
 .

You can activate and deactivate the SQL trace facility within the logic of the program. You do this by issuing calls to the IDMSIN01 entry point to the IDMS module.

Note: For more information about the requirements for calling IDMSIN01 to activate or deactivate the SQL trace facility, see the CA IDMS Callable Services Guide.