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.
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.
Natural provides the following objects with which you can start tracing, retrieve tracing data, and 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.
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:
|
#RETCODE |
I4 |
Return code. Possible values:
|
#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:
|
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.
| 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 |
| 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) |
The NDBDSTPA Natural subprogram starts the writing of trace records for a Natural for Db2 session only after a Db2 call entry.
The NDBDSTPB Natural subprogram starts the writing of trace records for a Natural for Db2 session only before a Db2 call entry.
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.
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.
The NDBDSTPR Natural subprogram resets the dynamic SQL trace buffer to its initial state. All trace entries are lost.
The NDBDSTPS Natural subprogram starts the writing of trace records for a Natural for Db2 session both before and after a Db2 call entry.
The NDBDSTPT Natural subprogram demonstrates the termination of the trace.