Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Improved SQL source-level debugging in V5R2

A new feature in V5R2 and PTFs for previous OS versions greatly improves the debug process.

If you're an SQL programmer, you may find that on many occasions you wish to perform program source-level debugging on the SQL routines that you create. Since V4R2, you have been able to do this by using the syntax SET OPTION DBGVIEW = *STMT or *LIST with the CREATE FUNCTION, PROCEDURE, or TRIGGER syntaxes. But the source-level listing you can see in the debugger view is displayed in C-generated codes only, which tends to be quite long and thus difficult to identify the SQL entities of your interest. ISeries programmers wanting to debug these SQL objects soon found that they had to step through the C code generated by DB2 UDB, which is not a pleasant experience for an RPG or Cobol programmer.

To improve the debug process in V5R2, an SQL source-level debugger has been delivered so that the programmer sees and debugs only the SQL statements he coded. The SQL source-level debugger is activated by embedding this SQL statement, SET OPTION DBGVIEW = *SOURCE within the SQL procedure, trigger or function definition. After the routine is created, you can use the OS/400 command:

STRDBG lib/<routine name>
to invoke the source debugger. You can see SQL code debug view by pressing F15 and select "SQL Output View" option.

The following PTFs make the V5R2 SQL *SOURCE debug view for SQL Procedures, functions and triggers available in V5R1:


The original support of the SQL source-level debugger was not available through iSeries Navigator. You had to use a 5250 session for this support. It was also important that you use the OS/400 command RUNSQLSTM to execute the SQL scripts that create the routines (with SET OPTION DBGVIEW = *SOURCE) and then run STRDBG command in that same 5250 session. That is because when the SQL routine was created, its source codes were added as a member (with the same name as the routine's name) into a source file named QSQDSRC, which is always created in QTEMP library.

The debugger then uses this member to display the source codes in the debugger view. That meant, that only the job that created the SQL Procedure, function or trigger could debug it with the more user-friendly *SOURCE debug view because it would disappear as soon as the user signed off.

There is good news: The following PTFs now enable the *SOURCE debug view to be permanently created in the library specified on the associated SQL Create statement. These PTFs also make it much easier to use *SOURCE view with the iSeries Graphical System Debugger when using this tool to debug SQL procedures, functions and triggers:

V5R1 -- SI06814
V5R2 -- SI06652

It is also important that you use OS/400 command RUNSQLSTM to execute the SQL scripts that create the routines (with SET OPTION DBGVIEW = *SOURCE).

To have a look at the iSeries system debugger see the following figure:

Figure 1: The iSeries system debugger.


About the author: Hernando Bedoya is an IT Specialist at the International Technical Support Organization (ITSO) in Rochester, Minn. He writes extensively and teaches IBM classes worldwide in all areas of DB2 UDB for iSeries.


  • Debugging and testing programs
    Search400.com member Dean Schweitz says when debugging and testing programs try the RGPLE compile options: OPTION(*SHOWCPY *EXPDDS) DBGVIEW(*LIST).
  • Debugging batch jobs
    Using your interactive screen to debug a batch job submitted to the job queue allows you to put the batch job into debug mode and to set breakpoints without having to change the program to run interactively.
  • Debugging an RPGLE program when its source does not exist
    How can you debug an RPGLE program when its source doesn't exist? Site expert John Kohan tells you.

Dig Deeper on iSeries system performance and monitoring

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.