Tracing Java Stored Procedure Calls

Troubleshooting

Problem

This document contains instructions on how to trace Java stored procedure calls.

Resolving The Problem

The following instructions may be used to trace problems calling or resolving the Java procedure.

The trace is enabled by adding the environment variable QIBM_COMPONENT_TRACE_LEVEL with a value of 'SQJAVA,3' to the job where the Java stored procedure is run. The trace must be enabled before the first procedure call is made in the job. The trace ends when the job ends. Job-level and system-level tracing are supported.

Tracing a specific job:

1.	Locate the job to be traced Locate the database server job to be traced and record the fully qualified job name. The WRKOBJLCK command might be helpful in locating the target job. For example,: *WRKOBJLCK <usrprf> USRPRF Where the <usrprf>** is the IBM i user profile used for the signon.
2.	Enable the trace From any emulation session, run the following commands to enable the trace: `ADDENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) VALUE('SQJAVA,3') LEVEL(JOB) CHGUSRTRC JOB(123456/SomeUser/SomeJob) MAXSTG(4000)` Where the MAXSTG* value is different than the previous value and JOB is the database server job to be traced. Press the Enter key after each command.
3.	Attempt the call to the Java procedure Re-create the problem with traces running.
4.	Dump the trace The raw trace data is stored in a user space object named QUSRSYS/QP0Zxxxxxx where xxxxxx is the job number. The following command can be used to verify the trace was enabled correctly: `WRKOBJ OBJ(QUSRSYS/QP0Z) OBJTYPE(USRSPC)` For example: `Opt Object Type Library Attribute Text QP0Z001234 USRSPC QUSRSYS 001234/QUSER/QZDASOINIT` Format the data by running the DMPUSRTRC* command as follows: `DMPUSRTRC JOB(123456/SomeUser/MYCLIJOB)` The dump command formats the data and writes it to the physical file QTEMP/QAP0ZDMP. Copy this file to a user library. Then, use FTP (ASCII) or IBM i Access data transfer (CCSID 65535 conversion checked) to send the file to a PC. `CPYF FROMFILE(QTEMP/QAP0ZDMP) TOFILE(MYLIB/QAPZDMP) MBROPT(ADD) CRTFILE(YES)` To dump the trace directly to a spooled file, use the following command: `CPYF FROMFILE(QTEMP/QAP0ZDMP) TOFILE(*PRINT)` IBM i Access Client Solutions can be used to copy the spooled file to a PC.
5.	End the Trace The trace is disabled when the job ends.

Tracing from an SQL client:

See "CALL QSYS2.QCMDEXC" instructions here:

HTTP function overview

Tracing All Jobs:

To enable tracing for all jobs on the system, the environment variable can be set system-wide. Only newly started jobs pick up changes to system-wide environment variables. The following instructions assume that subsystem customizations are not in effect. If subsystem customizations are in effect, the subsystem parameters must be changed to the subsystem that is running the database server jobs. The following groups of jobs are used for database serving: IBM i Access Client Solutions ODBC, OLEDB, and .Net and the IBM Toolbox for Java JDBC driver use the database host server jobs QZDASOINIT or QZDASSIINIT (SSL). Both jobs run in the QUSRWRK subsystem by default. Native JDBC and SQL CLI connections to a local database use the QSQSRVR jobs that run in the QSYSWRK subsystem by default. IBM DB2 Connect ODBC, JDBC, and .Net providers as well as IBM i native JDBC and SQL CLI remote database access use the QRWTSRVR jobs. QRWTSRVR jobs run in the QUSRWRK subsystem by default. The recommended procedure to enable system-wide tracing for an entire group of jobs is described next.

1.	Restart the database jobs System-wide environment variables are read only when a job starts. To restart the jobs, do one of the following: Method 1: Temporarily change the MAXUSE parameter to 1. The following command assumes the target job is the database host server (non-SSL). `CHGPJE SBSD(QUSRWRK) PGM(QZDASOINIT) MAXUSE(1)` Method 2: End all jobs (including active jobs), and restart them. The following commands assume the target job is the database host server (non-SSL). `ENDPJ SBS(QUSRWRK) PGM(QZDASOINIT) OPTION(*IMMED) STRPJ SBS(QUSRWRK) PGM(QZDASOINIT)`
2.	Add the environment variable To add the environment variable, run the following command: `ADDENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) VALUE('SQJAVA,3') LEVEL(*SYS)`
3.	Re-create the problem A new connection must be made to pick up the new environment value.
4.	Dump the trace Locate the target jobs, and dump as described previously.
5.	Disable tracing Remove the environment variable by using the following command: `RMVENVVAR ENVVAR(QIBM_COMPONENT_TRACE_LEVEL) VALUE('SQJAVA,3') LEVEL(*SYS)` Existing jobs being traced must be ended for the change to go into effect. Depending on which method was used in Step 1, reset the MAXUSE parameter to the original value by using the following command: `CHGPJE SBSD(QUSRWRK) PGM(QZDASOINIT) MAXUSE(200)` Or end and restart all prestart jobs again as described in Step 1.

Enabling Trace in an Exit Program

To enable tracing for specific database server jobs (QZDASOINIT jobs) or native JDBC or SQL CLI jobs (QSQSRVR jobs), an exit program can be used. IBM technote: Using the SQL CLI Exit Program to Start Diagnostics and Traces contains an example of using an exit program to start traces and diagnostics. The same type of exit can be used for database host server jobs (QZDASOINIT).

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]

Historical Number

25640251

Was this topic helpful?

Document Information

More support for:
IBM i

Software version:
Version Independent

Operating system(s):
IBM i

Document number:
644337

Modified date:
07 May 2025

UID

nas8N1019415

IBM Support

Tips