Obtaining a diagnostic trace for a stored procedure

You can trace a stored procedure with the ODBC diagnostic trace if the stored procedure is an ODBC application program. Use the ODBC diagnostic trace only under the direction of IBM® Support.

Procedure

To obtain a diagnostic trace for a stored procedure:

Set DIAGTRACE=1, DIAGTRACE_BUFFER_SIZE=nnnnnnn, and DIAGTRACE_NO_WRAP=0 or 1 in the common section of the Db2 ODBC initialization file. nnnnnnn is the number of bytes to allocate for the diagnostic trace buffer.
For example:
```
[COMMON]
DIAGTRACE=1
DIAGTRACE_BUFFER_SIZE=2000000
DIAGTRACE_NO_WRAP=1
```
Recommendation: When tracing 64-bit applications, consider specifying a diagnostic trace buffer size that is up to 10% bigger than what you would specify for 31-bit applications.
Specify a z/OS® DSNAOINI DD statement in the JCL for the stored procedure address space. The DD statement references the Db2 ODBC initialization file, as shown in the following examples:
```
//DSNAOINI DD DISP=SHR,DSN=CLI.DSNAOINI
```
```
//DSNAOINI DD PATH='/u/cli/dsnaoini'
```
Specify a DSNAOTRC DD statement in the JCL for the stored procedures space. The DD statement references a preallocated sequential data set with DCB attributes RECFM=FB,LRECL=80, or a z/OS UNIX environment HFS file to contain the unformatted diagnostic data, as shown in the following examples:
```
//DSNAOTRC DD DISP=SHR,DSN=CLI.DIAGTRC
```
```
//DSNAOTRC DD PATH='/u/cli/diagtrc'
```
Execute the client application that calls the stored procedure.
After the Db2 ODBC stored procedure executes, stop the stored procedure address space by using the z/OS command, "VARY WLM,APPLENV=name,QUIESCE". name is the WLM application environment name.
Submit either the formatted or unformatted diagnostic trace data to IBM Support. To format the raw trace data at your site, run the DSNAOTRC command (or the DSNAOTRX command in the XPLINK environment or the DSNAO64T command in the 64-bit environment) with the FMT or FLW options against the diagnostic trace data set.