Debug of SQL Procedures, Triggers, UDFs

Resolving The Problem

IBM SQL/400 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. Starting with R510, 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 may be needed to debug compile errors or to verify warning messages. The listing is not generated when the procedure is built using most interfaces such as ODBC, JDBC, Operations Navigator, and interactive SQL. Note that R520 iSeries Navigator attempts to highlight the section of code causing a syntax error. This may help locate the error without having to resort to a listing.

To force a listing, use one the following methods:

o	For R510 operating system and later, include the SET OPTION OUTPUT=*PRINT clause on the create procedure to force a listing: 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 run SQL statement command: RUNSQLSTM SRCFILE(QCSRC) SRCMBR(SQL) OUTPUT(*PRINT)
o	Use interactive SQL 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.

Debug

R510 and later allow debug at the SQL source statement level rather then debug of the underlying C code. The following PTFs are required:
R510: SI06814, SI06310, SI06358
R520: SI06652

To enable debug of the procedure, create the procedure with the SET OPTION DBGVIEW=*SOURCE. Use labels in the procedure to simplify debug (see Example below). If debug of the underlying C code is necessary, use the *LIST option.

Note: The procedure name should 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. For additional information, refer to the cover letter for PTF SI06652.

Once the procedure, trigger, or UDF is created, it can be debugged using the ILE debugger. See the DB2 UDB for AS/400 Advanced Database Functions IBM Redbooks (SG24-4249-02) for further information.

Example Steps Showing How to Use These Functions

Part 1: Create Sample Procedure

Do the following:

1

In Operations Navigator, 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

Do the following:

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. Do the following:

1	Open a 5250 emulation session
2	On the operating system command line, type the following: STRSRVJOB JOB(123456/QUSER/QZDASOINIT) where 123456 is the job number from Part 2, Step 2. Press the Enter key.
3	On the operating system command line, type the following: 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 Operations Navigator 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 Operations Navigator session should hang. The 5250 session should break into the ILE Debugger at the line with the break point.

Part 5: Inspect variables

The following help text was taken from the PTF cover letter:

"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 would have to be specified on the EVAL, so you need to 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