DB2 UDB simplified the debug of SQL procedures, functions, and triggers in V5R2 with the SQL *SOURCE debug view (see "4 Cool Things About DB2 UDB in V5R2," December 2002, article ID 15625), but you can make debugging SQL procedures and triggers even simpler by using another V5R2 feature, the Toolbox for Java iSeries System Debugger. Let's look at these two new V5R2 enhancements (and the V5R1 equivalent support) and see how you can use them together.

SQL *SOURCE Debug View

SQL procedures have used C code beneath the covers since they were first introduced way back in V4R2. The C code is really no issue for RPG and Cobol programmers until they need to debug an SQL procedure, function, or trigger. Instead of showing the original SQL procedural statements, the iSeries debugger (the STRDBG, or Start Debug, command) shows the generated C code that's being used to implement the original SQL procedural statements. Figure 1 shows the source of an SQL procedure statement in the new debugger, and Figure 2 shows the C-based debug view that programmers were forced to use before V5R2.

With the arrival of the *SOURCE debug view in V5R2, programmers have a way to work with original SQL source code in debug mode instead of the complex C code generated by DB2. You can create the SQL *SOURCE debug view in one of two ways. One way is to specify the debug option in the source of the SQL procedure, function, or trigger. As Figure 3 shows, you do this by specifying DBGVIEW=*SOURCE on the SET OPTION clause. An alternative method is to specify a *SOURCE value for the DBGVIEW parameter on the the RUNSQLSTM (Run SQL Statements) CL command.

With the *SOURCE debug view specified, DB2 UDB creates an extra SQL source-level debug view as it generates the C program. (This change is hot off the press. With the original V5R2 support, the SQL source-level debug view was always stored in QTEMP.) Only the job that created the SQL procedure, function, or trigger will be able to use the *SOURCE debug view. Once that job ends, the next debugging session of the SQL object will use the normal C listing debug view and not the SQL source-level debug view. Although this restriction may seem severe, it is eased by the fact that iSeries Navigator makes it very easy to re- create an existing SQL procedure, function, or trigger with the *SOURCE debug view. By simply right-clicking one of these SQL objects in iSeries Navigator, you can choose the Generate SQL task to retrieve the SQL source code and deposit it into an OS/400 source file member. The RUN SQLSTM command can then use that source file member to create the SQL procedure and make the *SOURCE debug view available to your job.

New support recently became available via V5R1 and V5R2 PTFs to store the *SOURCE debug view in a permanent library. With these PTFs, if the procedure, trigger, or function name is qualified with a library name, then the *SOURCE debug view is stored in the specified library. If the library name is not specified, the *SOURCE debug view will be created in QTEMP.

There are several other nuances with the SQL source-level debug that you must be aware of. One is that comments are not saved in the *SOURCE debug view. Another is that when you're stepping through the *SOURCE debug view in debug mode, the debugger may stay on the same SQL statement for several steps if that SQL statement implementation required multiple lines of generated C code.

The final nuance involves accessing the value of SQL variables and parameters during the debug session. You still use the EVAL command, but you must prefix the variable and parameter name with a label and enter them in capital letters. For parameters, the label name is the procedure name. EVAL SHIP_IT.ORDNUM will display the value of the first input parameter of the procedure in Figure 3. The variables use the label specified on the BEGIN statement, so EVAL SP.RATECALC would be the command to execute in the debug session to display the variable value in Figure 3. If the variable contains a character string, then the variable name must be prefixed with an asterisk (*) — something like EVAL *PROC.CHARVAR.

Graphical iSeries System Debugger

The new graphical iSeries System Debugger, which is part of the Toolbox for Java, lets you debug programs that run on the iSeries. This state-of-the art debugger includes an integrated call stack window, breakpoint groups, variable monitors, and a local variables display. You can use the graphical debugger to debug practically any scenario that a developer could debug using the green-screen interface (STRDBG) without retraining.

The graphical debugger supports the following ILE languages:

• ILE C & C++
• ILE RPG
• ILE Cobol
• ILE CL
• Java

Because SQL procedures, functions, and triggers are implemented by DB2 UDB as generated ILE C code, they are also supported by the graphical debugger (this includes support for the *SOURCE SQL debug view). Note that you can use Original Program Model (OPM) RPG and Cobol programs with the graphical debugger if *LSTDBG or *SRCDBG are specified on the compile and the debug session is started with OPMSRC(*YES) specified.

The graphical debugger comprises four components:

• Client-based debug manager
• Client-based debug interface
• Host-based debug hub
• Host-based debug server

The debug manager is used to register a user with the debug hub for graphical debug on a particular iSeries server. It functions as a convenient launch point from which to start debug sessions. There's only one debug manager for a given client.

The debug interface also runs on the client and provides the actual debug interface used to set breakpoints, step through programs, inspect variables, and so on. There's one debug interface instance for every job that's being debugged on an iSeries server. The debug interface can debug programs that are running in existing jobs on the system or use the System Debugger to launch and then debug programs in a system batch job. You can set up the System Debugger to start automatically, manually from a workstation command prompt, or manually via the debug manager interface.

The debug hub (QTESDBGHUB) is the server-side component responsible for (1) keeping track of which users are currently registered for debug and (2) starting debug server jobs on their behalf. When you use the STRDBG command from an emulation session, it contacts the debug hub to see whether the user executing the command is registered with the debug manager. It also checks to see whether the command being executed is from the same TCP/IP addess as the debug manager. When these qualifications are met, the graphical iSeries System Debug application is started instead of the traditional debug environment.

The debug server is a TCP/IP server job that is started by the debug hub when a request to start debugging has been issued. The server job then services the job that is being debugged and issues the appropriate debugger APIs and commands. If the user is registered for graphical debug through the debug manager and enters the STRDBG command, the debug server runs in the same job that the STRDBG command was issued in. Otherwise, the debug server runs its own job and then uses the STRSRVJOB (Start Server Job) command to service another job to be debugged.

Debug Interface Components

Figure 4 shows the debug interface window with a series of tabbed areas. Each tab provides a different view of the overall debug environment. The debugger interface is a multiple document window, so you can view more than one source file at once. Most compilers are capable of presenting several source code views. Each debug view (e.g., compiler listing, statement listing) can be used during a debug session.

The Programs tab lists the current program or Java class files that are currently under debug. You can save the program objects and breakpoint information to an environment file. To eliminate repetitive setup of breakpoints for a program, you can load the saved environment file into a future debug session.

The Breakpoints tab enables the manipulation and listing of all program breakpoints. You can specify conditions and thread-specific information when defining a breakpoint. In addition, you can group breakpoints logically and assign them each a name and color. You can disable and re-enable the breakpoints in a groups with a single mouse click.

The Locals tab (Figure 5) displays the local variables associated with the program currently being debugged. Structured variables have their children organized in a tree hierarchy. For SQL procedures, functions, and triggers, the SQL variables and parameters are organized under each label. As with monitored variables, you can edit locals directly from this panel.

The Monitors tab keeps track of variables or expressions that aren't found in the local program scope. You can establish a monitor by simply highlighting a variable or expression in a source-code window. The value of each monitor is updated at each breakpoint or debug step.

The Console tab (Figure 6) lets users directly enter the same debug commands (e.g., EVAL) that they use on the green-screen debugger interface. The graphical debugger supports all of the green-screen debug commands.

The Call Stack tab displays the call stack associated with the program in debug mode. You can visit any call frame in the call stack by using the mouse or a pull-down menu.

The Threads tab displays all active threads in the program. If the code being executed by the thread is in debug mode, the program information (including line numbers) is displayed. Any source code being executed in a thread can be accessed via a mouse click or a pull-down menu.

Debugger Installation & Configuration

To run the graphical iSeries system debugger, your client system must meet the following hardware and software requirements:

Hardware

• CPU: 400-500 MHz
• Memory: 128 MB mininum, 256 MB recommended

Software

• One of the following:
       — Java 2 Platform, either the Standard Edition (J2SE) or the Enterprise Edition (J2EE), version 1.3 or later
       — Java 2 Runtime Environment (JRE), Standard Edition, version 1.3.1 or later
• jhall.jar (one of the jar files in JavaHelp)

You can download the software from the Sun Java site (http://java.sun.com/downloads/index.html). Here are the installation steps:

1. Load V5R2 (SI05473 and SI06652) on your iSeries server.



2. The Toolbox for Java is required by the graphical debugger, so you need the Toolbox for Java on the client. Toolbox for Java is a component of iSeries Navigator. Instead of installing iSeries Navigator on the client, you can copy the jt400.jar file out of the following directory on the iSeries server: /QIBM/ProdData/HTTP/Publi/jt400/lib/.



3. Copy the Graphical System Debugger file (tes.jar) out of the following directory on the iSeries server: /QIBM/ProdData/HTTP/Public/jt400/lib/.



4. Modify the CLASSPATH variables to include access to the jt400.jar, tes.jar, and jhall.jar files on your client. Here's a CLASSPATH setting for a typical client:

   C:\Program Files\IBM\Client Access\jt400\ 
      lib\jt400.jar; C:\Program Files\IBM\ 
      Client Access\jt400\lib\tes.jar;< 
   C:\Program Files\IBM\Client Access\JRE\Lib

5. Start the host debug server on your iSeries system with the command STRTCPSVR *DBG (on V5R1, you start the debug server by issuing the following program call: CALL QSYS/QTESSTRSVR).

Graphical Debug of Procedural SQL

Here are the basic steps of getting the graphical debugger up and running for an SQL stored procedure:

1. Create the SQL procedure with the *SOURCE debug view in an emulation session. In this case, the source code for the SQL procedure in Figure 3 has been copied into a source physical file member called MYLIB /MYSRC(SPTEST). So you execute the following RUN SQLSTM command to create the SQL procedure with the debuggable source-level debug:

   RUNSQLSTM SRCFILE(MYLIB/MYSRC) 
             SRCMBR(SPTEST) COMMIT(*NONE) 
             NAMING(*SQL)

2. Start the Debug Manager on the client by issuing the following command at an MS-DOS prompt:

   java utilities.DebugMgr

   This will launch the graphical debug manager, where you must click the Edit pulldown menu to register your ID and iSeries server for a graphical debug session. After registration is completed, your DebugMgr view should look like Figure 7.




3. Now, go back to the same emulation session where the SQL procedure was created. Start debug mode for the newly created SQL procedure with the following command:

   STRDBG PGM(SHIP_IT) UPDPROD(*YES)

   This command will cause the iSeries Graphical Debugger to be started on your client and load the SQL source-level debug view for the SHIP_IT stored procedure from Figure 3.
         Note: An SQL procedure, function, or trigger created without a library name using iSeries Navigator cannot be debugged at the SQL source level using the graphical debugger because the *SOURCE debug view is stored in QTEMP when a library name is not specified.



4. Set a breakpoint with a single left-click on line 4 (IF ORDTYPE='I'). An enabled breakpoint is indicated by the red arrow shown in Figure 4. Now that a breakpoint has been set, click the Green Resume arrow on the tool bar.



5. Return to your emulation session, and issue the following SQL CALL statement using either RUNSQLSTM command or Interactive SQL:

   CALL SHIP_IT(33, 'I', 5.1)

The debug client will then take control at the breakpoint specified in the previous step. Figure 5 shows how the debugger uses yellow highlighting to indicate where execution was stopped for the breakpoint.


6. To view the contents of the ORDTYPE input parameter to determine which leg of the IF statement will be executed, you need to left-click the Console tab in the lower left corner and enter the following EVAL statement in the Command window:

   EVAL *SHIP_IT.ORDTYPE

   The contents of this variable are then displayed in the Console window (Figure 6). You can display all of the procedure parameter values by simply entering EVAL SHIP_IT. When you're using the graphical debugger over the normal debug views, variable values are automatically displayed when you move the cursor over a variable name in a line of code. This flyover display of variables is not available with the *SOURCE debug view for SQL procedures, functions, and triggers.



7. To view the calculated shipping rate before the stored procedure ends, just right-click line 11 and select the "Run to Cursor" task. This will allow the debugger to execute all of the code up to line 11. When line 11 is reached, you can issue the following command in the Console window to display the computed shipping rate:

   EVAL SP.RATECALC

8. To complete execution of the SHIP_IT stored procedure, simply click the green resume arrow on the tool bar.

Only the Beginning

I've only scratched the surface of the new graphical debugger, but hopefully you've learned enough to leverage this new debug interface in conjunction with the SQL source-level debug view. Working together, these two enhancements should simplify the analysis and debugging of your SQL procedures, functions, and triggers.

The V5R1 *SOURCE debug view PTFs are SI06814, SI06359, SI06310, and SI06358. The V5R1 PTFs for the graphical debugger are SI05799, SI02871, SI02849. For more information about the graphical iSeries System Debugger, you can look at the debugger tool Help by pressing F1. The iSeries InfoCenter also has information on the debugger; just search the InfoCenter for "graphical debugger".

Kent Milligan is a DB2 UDB for iSeries technology specialist in PartnerWorld for Developers, iSeries. Kent spent the first eight years of his IBM career as a member of the DB2 development group in Rochester. He speaks and writes regularly about relational database topics. You can reach him at kmill@us.ibm.com.