Tracing Dynamic SQL Statements

#### Search Results

Search

# Tracing Dynamic SQL Statements

Natural for Db2 provides a facility to trace all dynamically executed SQL statements within one machine (LPAR). This trace facility allows you to determine the Natural program from which the dynamic SQL request originates.

When tracing is enabled with the `DYSQLTR` parameter, all dynamically executed SQL statements are traced into a shared memory object above the bar. The name of that shared memory object is specified with the `DSTNAME` parameter. The first Natural for Db2 session in an LPAR creates the shared memory object and attaches its address space or region to the shared memory object. The subsequent Natural for Db2 sessions with the same `DSTNAME` and `DYSQLTR` parameter settings in an LPAR will also attach to the shared memory object if the address space is not already attached to the shared memory object.

For every dynamically executed SQL statement, the Natural for Db2 session writes a trace entry into the buffer before it calls Db2 and a trace entry after the return from Db2.

Each trace entry contains the following data:

Date and time
Natural library, program name, and line number
Username, terminal name, job name, and job number
The Db2 DBRM name (NDBIOxxx), the Db2 section number, statement number, and the statement type (OPEN, FETCH, CLOSE, etc)
The Db2 SQLCODE return information

You can start and terminate dynamic SQL tracing and retrieve the trace data in the shared memory object with the NDBDST00 Natural subprogram. If the trace buffer gets filled to the upper end, tracing continues by overwriting the content at the lower end of the buffer.

- Enabling Tracing for Dynamic SQL Statements

- Tracing Dynamic SQL Statements

## Enabling Tracing for Dynamic SQL Statements

By default, the Natural for Db2 session does not particiapte in dynamic SQL tracing. To enable dynamic SQL tracing for a Natural for Db2 session, you must set `DYSQLTR=ON` within the `NTDB2` macro in the Natural parameter module. You must also specify a name for the shared memory object where all dynamically executed SQL statements will be traced. The default name is `NDBRDC01`. For details about the `NTDB2` macro syntax, see DB2 - Parameters for SQL Database Management Interfaces.

After tracing is enabled, the Natural for Db2 session attaches to the shared memory object. If that shared memory object does not yet exist, the Natural for Db2 session creates the shared memory object specified in `DSTNAME` and attaches the Natural session to it. For this purpose, the session calls the Authorized Services Manager (ASM) that is associated with the `SUBSID` of the Natural subsystem. The session searches for the name and size of the shared memory object in the `FSSMDSTx` cards of the ASM. That is why you must define the size of the shared memory object and the type of tracing in the `ASMPARM` parameter file. Within this parameter file, you set the `FSSMDSTx` parameter:

    FSSMDSTx=(name,n,tracetype,[T])

- `FSSMDSTx` indicates this is a parameter card for the shared memory object trace buffer. x is arbitrary.

- `name` denotes the name of the shared memory object.

- `n` denotes the size of the shared memory object in units of megabytes.

- `tracetype` determines which trace records are written to the trace buffer. The possible values are:

  `S` - write trace records before and after a Db2 call

  `B` - write trace records before a Db2 call

  `A` - write trace records after a Db2 call

  `T` - don't write trace records

- `T` is an optional attribute that requests the create and attach requests to be logged to the job log.

The following example defines a shared memory object called `NDBDST01` that is 10MB in size with tracing before and after a Db2 call:

    SUBSID=NDB1
    MSGCASE=M
    FSSMDST1=(NDBDST01,10,S)

After you set up the tracing, you can use the available Natural objects to start tracing, retrieve tracing data, and terminate tracing.

## Tracing Dynamic SQL Statements

Natural provides the following objects with which you can start tracing, retrieve tracing data, and terminate tracing:

- NDBDST00 - Start Tracing, Retrieve Records, Terminate Tracing

- NDBDST_L - Description of Retrieved Dynamic SQL Trace Data

- NDBDSTPA - Write Trace Records after a Db2 Call

- NDBDSTPB - Write Trace Records before a Db2 Call

- NDBDSTPF - Read Trace Entries from Oldest to Newest

- NDBDSTPL - Read Trace Entries from Newest to Oldest

- NDBDSTPR - Reset the Trace Buffer

- NDBDSTPS - Restart Tracing

- NDBDSTPT - Terminate Tracing

These programs operate on the shared memory buffer that is specified with the `DSTNAME` parameter in the NTDB2 macro in the NATPARM module or with the `DB2=(DSTANAM=)` subparameter. To use these programs, you must also set `DYSQLTR=ON`.

### NDBDST00 - Start Tracing, Retrieve Records, Terminate Tracing

The NDBDST00 Natural subprogram starts dynamic SQL tracing, retrieves the dynamic SQL trace data, and terminates tracing. Use the following syntax in the calling Natural program:

    CALLNAT 'NDBDST00' #FUNCTION #RETCODE #KTXT #GDATA #LDATA #TRACESTATE

The syntax of this program contains the following parameters:

Parameter | Format/Length | Description
--- | --- | ---
`#FUNCTION` | A1 | Function code for input. Possible values: `A` - Start the trace after Db2 call trace entries are written `B` - Start the trace before Db2 call trace entries are written `F` - Retrieve the first/oldest trace entry `L` - Retrieve the latest/newest trace entry `N` - Retrieve the next/newer trace entry `P` - Retrieve the prior/older trace entry `R` - Reset the trace buffer. The dynamic SQL trace buffer is treated as it is initialized for the first time `S` - Start tracing `T` - Terminate tracing
`#RETCODE` | I4 | Return code. Possible values: `0` - Okay `4` - End of data. No more trace entries are available `8` - Insufficient number of parameters `12` - Unknown function code `16` - Session not attached to shared memory object `20` - Session not attached to shared memory object
`#KTXT` | B48 | Context used during calls. The caller must not modify this parameter.
`#GDATA` | A252 | Contains dynamic SQL trace data related to Natural program that calls Db2. See NDBDST-L.
`#LDATA` | A252 | Contains dynamic SQL trace data about the access to Db2. See NDBDST-L.
`#TRACESTATE` | A1 | The current state of the dynamic SQL trace. Possible values: `A` - Trace is active only after Db2 call entries are written. `B` - Trace is active only before Db2 call entries are written. `S` - Trace is active before and after Db2 call entries are written. `Blank` - Trace is inactive, no DB2 call entreis are written.

### NDBDST_L - Description of Retrieved Dynamic SQL Trace Data

The local data area NDBDSTPF contains the description of retrieved dynamic SQL trace data. It is split in two groups GDATA and LDATA. GDATA comprises the data related to the Natural program calling Db2 and LDATA comprises the data related to the access to Db2.

##### GDATA

Name | Format/Length | Description
--- | --- | ---
GBASP | A8 | Basic Product ID (NATURAL)
GVER | A4 | Basic Product ID Version (9202)
GOPS | A8 | Operating System Name (MVS/ESA)
GTPM | A8 | TP-Monitor Name (CICS, IMS/DC)
GTERM | A8 | TP-Terminal ID
GIUID | A8 | Initial User ID
GCUID | A8 | Current User ID
GCAPL | A8 | Current LOGON library
GGRP | A8 | Current LOGON Group ID
GPGM | A8 | Current program
GLINE | N4 | Current statement number
GLEV | I1 | Current program level

##### LDATA

Name | Format/Length | Description
--- | --- | ---
TDB2FUNC | A4 | BDb2 before Db2, ADb2 after Db2 call
TDB2TIST | B8 | Local Store Clock time
TDB2DBRM | A8 | DBRM name (UTF-8)
TDB2CONT | B8 | Db2 consistency token
TDB2SQLC | I4 | SQLCODE
TDB2STYPE | I2 | Db2 statement type
TDB2SECT | I2 | Db2 section number
TDB2STNR | I4 | Db2 statement number
TDB2JNAM | A8 | Job name of requestor
TDB2JNO | A8 | Job number of requestor
TDB2HOLD | A2 | Cursor WITH HOLD (NO, HO)
TDB2SCRL | A2 | SCROLL Type (NO, AS, IN, SD, SS)
TDB2RETU | A2 | Cursor WITH RETURN (NO, RT)
TDB2ROWP | A2 | Cursor WITH ROWSET processing (NO, RP)
TDB2FECO | A2 | FETCH continue Type (NO, CC, CO)
TDB2MFC | I2 | Multi Fetch Count
TDB2MODE | A1 | Dynamic (D)

### NDBDSTPA - Write Trace Records after a Db2 Call

The NDBDSTPA Natural subprogram starts the writing of trace records for a Natural for Db2 session only after a Db2 call entry.

### NDBDSTPB - Write Trace Records before a Db2 Call

The NDBDSTPB Natural subprogram starts the writing of trace records for a Natural for Db2 session only before a Db2 call entry.

### NDBDSTPF - Read Trace Entries from Oldest to Newest

The NDBDSTPF sample program demonstrates the sequential retrieval of trace entries starting from the oldest trace entry and ending with the newest trace entry.

You can use this program as a template to create your own trace retrieval programs and suit the output to your needs.

### NDBDSTPL - Read Trace Entries from Newest to Oldest

The NDBDSTPL sample program demonstrates the sequential retrieval of trace entries starting from the newest trace entry and ending with the oldest trace entry.

You can use this program as a template to create your own trace retrieval programs and suit the output to your needs.

### NDBDSTPR - Reset the Trace Buffer

The NDBDSTPR Natural subprogram resets the dynamic SQL trace buffer to its initial state. All trace entries are lost.

### NDBDSTPS - Restart Tracing

The NDBDSTPS Natural subprogram starts the writing of trace records for a Natural for Db2 session both before and after a Db2 call entry.

### NDBDSTPT - Terminate Tracing

The NDBDSTPT Natural subprogram demonstrates the termination of the trace.

### On this page

- Enabling Tracing for Dynamic SQL Statements
- Tracing Dynamic SQL Statements