Improved SQL source-level debugging in V5R2

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.

This was first published in December 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.