Troubleshooting
Problem
This document describes debug methods for SQL procedures, triggers, and UDFs.
Resolving The Problem
DB2 for i SQL procedures, triggers and user defined functions (UDFs) are implemented in the operating system by generating and compiling an ILE C program that contains embedded SQL. Retrieving the listing or debugging the source may occasionally be needed to help isolate and fix compile or run time errors. The operating system has an option to debug at the SQL source level rather than only at the C source level.
Getting a Listing
A listing can be needed to debug compile errors or to verify warning messages. The listing can help locate the error too.
To locate the listing, run the WRKSPLF <user> command for the user profile specified on the Run Sql Scripts session, or issue WRKJOB, and take option 4 (Work with Spooled Files), for the STRSQL and RUNSQLSTMT options.
Part 5: Inspect variables
The label is used to qualify variables and the procedure name is used to qualify parameters. The qualifier must be specified on the EVAL and all names must be specified in uppercase. If the label is not specified or if it is a name that is not valid in C, SQL generates a name. This name must be specified on the EVAL, and you must look at the source or listing to find out what the name is.
Getting a Listing
A listing can be needed to debug compile errors or to verify warning messages. The listing can help locate the error too.
Use one the following methods to generate a listing:
| o |
Open an Access Client Solutions Run SQL Scripts session.
Include the SET OPTION OUTPUT=*PRINT clause on the create procedure:
Create Procedure SQL1 (IN Custno INTEGER, IN CrtIncr NUMERIC(4,0)) LANGUAGE SQL
SET OPTION OUTPUT = *PRINT
SQL1Src:
BEGIN
UPDATE QCUSTCDT SET CDTLMT = CDTLMT + CrtIncr WHERE CUSNUM = custno;
END;
|
| o |
Use the RUNSQLSTM command from a 5250 emulation session. The CREATE PROCEDURE statement is typed in a source physical file member. Override the desired options on the RUNSQLSTM command:
RUNSQLSTM SRCFILE(QCSRC) SRCMBR(SQL) OUTPUT(*PRINT) |
| o |
Use STRSQL to build the procedure. The procedure built does not have the correct options; however, the source for the procedure can be located in QTEMP. You can then manually recompile the procedure using the CRTSQLCI command. Specify the desired compile options on the command.
|
To locate the listing, run the WRKSPLF <user> command for the user profile specified on the Run Sql Scripts session, or issue WRKJOB, and take option 4 (Work with Spooled Files), for the STRSQL and RUNSQLSTMT options.
Debug
To enable debug of the procedure, create the procedure with the SET OPTION DBGVIEW=*SOURCE. Use labels in the procedure code to simplify the debugging (see Example below). If debug of the underlying C code is necessary, use the *LIST option.
Note: The procedure name must be a fully qualified. If it is not, the listing is generated in library QTEMP and the procedure can be debugged only by the job that issues the CREATE PROCEDURE statement.
Once the procedure, trigger, or UDF is created, it can be debugged using the ILE Source debugger. See the The ILE Source Debugger for further information.
Example Steps Showing How to Use These Functions
Part 1: Create Sample Procedure
Do the following:
To enable debug of the procedure, create the procedure with the SET OPTION DBGVIEW=*SOURCE. Use labels in the procedure code to simplify the debugging (see Example below). If debug of the underlying C code is necessary, use the *LIST option.
Note: The procedure name must be a fully qualified. If it is not, the listing is generated in library QTEMP and the procedure can be debugged only by the job that issues the CREATE PROCEDURE statement.
Once the procedure, trigger, or UDF is created, it can be debugged using the ILE Source debugger. See the The ILE Source Debugger for further information.
Example Steps Showing How to Use These Functions
Part 1: Create Sample Procedure
Do the following:
| 1 | In Access Client Solutions, open a Run SQL Scripts session. |
| 2 |
Create the sample procedure using the following SQL statement:
--***************************** start procedure source ***************************** -- If the name is fully qualified, the debug source is saved to QSQDSRC in that library; -- Otherwise, it is saved to QTEMP. Create procedure QGPL.test3 (IN x char(7), IN y int) language sql reads sql data result sets 1 set option dbgview = *source , output=*print --Use labels. Otherwise, the source must be inspected to determine the system-generated --label. A: begin declare v_test integer default 1; declare v_test2 character(6) default 'TESTER'; --Avoid delimited names and names with special characters. --Otherwise you must inspect the source to find the system generated name declare v_#@$ char(20) default 'Special chars'; declare c1 cursor for select * from qiws.qcustcdt where lstnam = x and zipcod = y; open c1; set result sets cursor c1; end ; --*************************** end procedure source *************************** |
Part 2: Retrieve Job Name of Client
Continue with the next steps:
Continue with the next steps:
| 1 | In the Run SQL Scripts session from Step 1, Select the menu option View, Joblog. |
| 2 | Note the fully qualified job name. This is the job that will be debugged (the job that will be used to call the procedure). |
Part 3: Set Break Points
This part uses the ILE system debugger:
This part uses the ILE system debugger:
| 1 |
Open a 5250 emulation session.
On the operating system command line, run the next commands:
|
| 2 |
STRSRVJOB JOB(123456/JOBUSER/QZDASOINIT) where 123456 is the job number from Part 2, Step 2. Press the Enter key. |
| 3 |
STRDBG PGM(QGPL/TEST3) UPDPROD(*YES) Press the Enter key. You will see the SQL statement source for the procedure. Select the line 9 (SET RESULT...), and set a break point with F6. |
| 4 | Press F12 - Continue. The 5250 session is now enabled for debug. |
| 5 |
To view the source, on the operating system command line type the following:
DSPPFM FILE(QGPL/QSQDSRC) MBR(TEST3) Press the Enter key. |
| 6 |
To locate the listing, run the WRKSPLF <user> command for the user profile specified on the Run Sql Scripts session. Look for a spooled file TEST3.
|
Part 4: Call the Procedure
| 1 |
In the Run SQL Scripts sessions, run the following SQL statement:
CALL TEST3('Henning', 75217) |
| 2 |
The Run SQL scripts session will hung. The 5250 emulation session will break into the ILE Debugger at the line with the break point.
|
Part 5: Inspect variables
The label is used to qualify variables and the procedure name is used to qualify parameters. The qualifier must be specified on the EVAL and all names must be specified in uppercase. If the label is not specified or if it is a name that is not valid in C, SQL generates a name. This name must be specified on the EVAL, and you must look at the source or listing to find out what the name is.
| 1 |
At the ILE Debug command prompt type the following ILE Debug commands to inspect variables.
EVAL *A.V_TEST2 :S results *A.V_TEST2:S = "TESTER" EVAL A.V_TEST results A.V_TEST = 1 |
| 2 |
Specify the label to display all the values for the variables in a compound statement.
EVAL A EVAL *TEST3.X :S results *TEST3.X:S = "Henning" EVAL TEST3.Y results TEST3.Y = 75217 |
| 3 |
Specify the procedure name to get all the values for the parameters.
EVAL TEST3 |
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHeAAM","label":"IBM i Db2"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
22783253
Was this topic helpful?
Document Information
Modified date:
04 March 2025
UID
nas8N1017447