Troubleshooting
Problem
This document provides a sample exit program for starting various diagnostics and traces on applications that use the QSQSRVR jobs. Examples include applications that use IBM i CLI or native JDBC.
For more information about Database Monitor impacts, see this technote: Impact of collecting a DBMON trace - Start Database Monitor - STRDBMON
Resolving The Problem
IBM i SQL CLI provides an exit point. The exit program is called after the connection is made to the QSQSRVR job but before it connects to the relational database. It is called within the scope of the QSQSRVR job (RTVJOBA returns QSQSRVR and not the source job). The Exit point can be used to start diagnostics such as DBMON. It can also be used to place the job on hold for debug purposes or to delay the job while the CLI trace is started.
Note: The user profile used for the CLI connection must be authorized to any diagnostics or commands placed in the exit program.
To register the exit program, run the command WRKREGINF QIBM_QSQ_CLI_CONNECT. Select Option 8=Work with exit programs, then select Option 1=Add, or use the following command:
ADDEXITPGM EXITPNT(QIBM_QSQ_CLI_CONNECT) FORMAT(CLIC0100) PGMNBR(1) PGM(QGPL/CLIEXIT)
To remove the exit program, run the command WRKREGINF QIBM_QSQ_CLI_CONNECT. Select Option 8=Work with exit programs, then select Option 4=Remove, or use the following command:
RMVEXITPGM EXITPNT(QIBM_QSQ_CLI_CONNECT) FORMAT(CLIC0100) PGMNBR(1) PGM(QGPL/CLIEXIT)
The exit point changes affect only new prestart jobs. There are two methods to enable the change. The first method is to end and restart all QSQSRVR jobs by using the ENDPJ and STRPJ commands to end all active jobs. The second method is to issue the CHGPJE command to change the MAXUSE parameter to 1. All new connections then pick up the exit point change. Remember to later change the value back to the default value of 200 or performance might be affected.
A sample CL program:
Note: The user profile used for the CLI connection must be authorized to any diagnostics or commands placed in the exit program.
To register the exit program, run the command WRKREGINF QIBM_QSQ_CLI_CONNECT. Select Option 8=Work with exit programs, then select Option 1=Add, or use the following command:
ADDEXITPGM EXITPNT(QIBM_QSQ_CLI_CONNECT) FORMAT(CLIC0100) PGMNBR(1) PGM(QGPL/CLIEXIT)
To remove the exit program, run the command WRKREGINF QIBM_QSQ_CLI_CONNECT. Select Option 8=Work with exit programs, then select Option 4=Remove, or use the following command:
RMVEXITPGM EXITPNT(QIBM_QSQ_CLI_CONNECT) FORMAT(CLIC0100) PGMNBR(1) PGM(QGPL/CLIEXIT)
The exit point changes affect only new prestart jobs. There are two methods to enable the change. The first method is to end and restart all QSQSRVR jobs by using the ENDPJ and STRPJ commands to end all active jobs. The second method is to issue the CHGPJE command to change the MAXUSE parameter to 1. All new connections then pick up the exit point change. Remember to later change the value back to the default value of 200 or performance might be affected.
A sample CL program:
/**********************************************************************/
/* CLI EXIT PROGRAM SAMPLE THAT SHOWS HOW TO START VARIOUS */
/* DIAGNOSTICS. ADJUST THE COMMENTS BELOW TO INCLUDE THE DESIRED */
/* DIAGNOSTICS. */
/* */
/* WRKREGINF EXITPNT(QIBM_QSQ_CLI_CONNECT) */
/* SET MAXUSE TO 1 FOR EASIER DEBUG: */
/* CHGPJE SBSD(QSYSWRK) PGM(QSQSRVR) MAXUSE(1) */
/* DXD V1.2 */
/**********************************************************************/
PGM PARM(&USERID)
DCL VAR(&USERID) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBUSER) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBNUM) TYPE(*CHAR) LEN(6)
DCL VAR(&TRCNAME) TYPE(*CHAR) LEN(10)
DCL VAR(&HLDMSG) TYPE(*CHAR) LEN(128)
DCL VAR(&MSGQ) TYPE(*CHAR) LEN(20)
DCL VAR(&MSGQ2) TYPE(*CHAR) LEN(10)
DCL VAR(&KEY) TYPE(*CHAR) LEN(4)
DCL VAR(&BIN4) TYPE(*CHAR) LEN(4) /* BINARY VARS */
DCL VAR(&MSGLENB) TYPE(*CHAR) LEN(4) /* BINARY */
DCL VAR(&WAITB) TYPE(*CHAR) LEN(4) /* BINARY */
/* GET THE QSQSRVR JOB NAME */
RTVJOBA JOB(&JOBNAME) USER(&JOBUSER) NBR(&JOBNUM)
/* GENERATE A NAME FOR THE TRACE OUTPUT */
CHGVAR VAR(&TRCNAME) VALUE('CLI' *CAT &JOBNUM)
/* APPLY DIAGNOSTICS TO ONLY ONE SPECIFIC USER */
/* IF COND(&USERID *NE 'DDILLING') THEN(GOTO EXIT) */
/* CHANGE JOB'S LOGGING LEVEL */
CHGJOB LOG(4 00 *SECLVL)
/* SEND NOTIFICATION MESSAGE TO A SPECIFIC USER */
/* SNDMSG MSG('User:' *CAT &USERID *CAT 'Jobnum:' *CAT +
&JOBNUM *CAT 'JOBNAME: ' *CAT +
&JOBNAME *CAT 'TRCNAME: ' *CAT +
&TRCNAME ) TOUSR(DDILLING) */
/* MONMSG MSGID(CPF9999) */
/* START TRACE JOB */
/* TRCJOB MAXSTG(16000) /* V4 JOB TRACE */
/* STRTRC SSNID(&TRCNAME) JOB(*) MAXSTG(128000) /* V5 JOB TRACE */
/* R510 TRACE CAN BE PRINTED USING THE FOLLOWING COMMANDS */
/* ENDTRC SSNID(CLI123456) DTAOPT(*LIB) DTALIB(QUSRSYS) +
RPLDTA(*YES) PRTTRC(*YES) */
/* DLTTRC DTAMBR(CLI123456) DTALIB(QUSRSYS) */
/* START DBMON IF NEEDED */
/* STRDBMON OUTFILE(QUSRSYS/&TRCNAME) +
JOB(&JOBNUM/&JOBUSER/&JOBNAME) TYPE(*DETAIL) */
/* R510: SET QSQ DEBUG INFO */
/* ADDENVVAR ENVVAR(QIBM_SQL_SERVICE) +
VALUE('sqlcode=sql0501 dmp=ocl,ca,da') */
/* SET A QAQQINI FILE */
/* CHGQRYA QRYOPTLIB(DDILLING) */
/* INCLUDE DEBUG MESSAGES */
STRDBG UPDPROD(*YES)
/*@1.2 JAVA STORED PROCEDURE USER TRACE */
/* CALL PGM(QSYS/QP0WUSRT) PARM('-l 3' '-c 20') */
/* PLACE JOB ON HOLD */
CHGVAR VAR(&MSGQ) VALUE('CLIX' *CAT &JOBNUM *CAT +
'QGPL ')
CHGVAR VAR(&MSGQ2) VALUE('CLIX' *CAT &JOBNUM)
CHGVAR VAR(&HLDMSG) VALUE('JOB ' *CAT &JOBNUM *CAT +
'/' *CAT &JOBUSER *CAT '/' *CAT &JOBNAME +
*CAT ' FOR USER ' *CAT &USERID *CAT 'IS +
HELD. ENTER G TO CONTINUE.')
CHGVAR VAR(%BINARY(&MSGLENB)) VALUE(128) /* MSG LEN */
CHGVAR VAR(%BINARY(&BIN4)) VALUE(1)
CHGVAR VAR(%BINARY(&WAITB)) VALUE(-1) /* WAIT TIME */
CHGJOB INQMSGRPY(*DFT)
DLTMSGQ MSGQ(QGPL/&MSGQ2)
MONMSG MSGID(CPF9999)
CRTMSGQ MSGQ(QGPL/&MSGQ2)
MONMSG MSGID(CPF9999)
CALL PGM(QMHSNDM) PARM(' ' +
' ' &HLDMSG &MSGLENB +
'*INQ ' '*SYSOPR ' &BIN4 +
&MSGQ &KEY 0 0)
/* IGNORE REPLY, CONTINUE ON ANY RESPONSE */
CALL PGM(QMHRCVM) PARM(' ' &MSGLENB RCVM0100 +
&MSGQ '*RPY ' &KEY +
&WAITB '*REMOVE ' 0 0)
DLTMSGQ MSGQ(QGPL/&MSGQ2)
MONMSG MSGID(CPF9999)
EXIT:
ENDPGM
[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Data Access","Platform":[],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Historical Number
23790932
Was this topic helpful?
Document Information
Modified date:
02 July 2025
UID
nas8N1017304