Testing and debugging
Testing and debugging distributed SQL programs is similar to testing and debugging local SQL programs, but certain aspects of the process are different.
If applications are coded so that the relational database names can easily be changed by recompiling the program, by changing the input parameters to the program, or by making minor modifications to the program source, most testing can be accomplished using a single system.
After the program has been tested against local data, the program is then made available for final testing on the distributed relational database network. Consider testing the application locally on the system that will be the application server (AS) when the application is tested over a remote connection, so that only the program needs to be moved when the testing moves into a distributed environment.
Debugging a distributed SQL program uses the same techniques as debugging a local SQL program. You use the Start Debug (STRDBG) command to start the debugger and to put the application in debug mode. You can add breakpoints, trace statements, and display the contents of variables.
However, to debug a distributed SQL program, specify the value of *YES for the UPDPROD parameter. This is because i5/OS® distributed relational database support uses files in library QSYS and QSYS is a production library. This allows data in production libraries to be changed on the application requester (AR). Issuing the Start Debug (STRDBG) command on the AR only puts the AR job into debug mode, so your ability to manipulate data on the AS is not changed.
While in debug mode on the AR, informational messages are entered in the job log for each SQL statement run. These messages give information about the result of each SQL statement. A list of SQL return codes and a list of error messages for distributed relational database are provided in the Troubleshooting topic.
Informational messages about how the system maximizes processing efficiency of SQL statements are also issued as a result of being in debug mode. Because any maximization occurs at the AS, these types of messages do not appear in the AR job log. To get this information, the AS job must be put in debug mode.
A relatively easy way to start debug mode on the system, if you are using TCP/IP, is to use the QRWOPTIONS data area. However, you cannot specify a specific program to debug with this facility. For details on setup, see QRWOPTIONS data area usage. The data area can be used not only to start debug, but to start job traces, request job logs, display job output and do other things as well. You can even do the QRWOPTIONS setup on an i5/OS AR, and have the options shadowed to a System i™ platform.
If both the AR and AS are System i products, and they are connected with APPC, you can use the Submit Remote Command (SBMRMTCMD) command to start the debug mode in an AS job. Create a DDM file as described in the Setting up DDM files topic. The communications information in the DDM file must match the information in the relational database directory entry for the relational database being accessed. Then issue this command:
SBMRMTCMD CMD('STRDBG UPDPROD(*YES)') DDMFILE(ddmfile name)The (SBMRMTCMD) command starts the AS job if it does not already exist and starts the debug mode in that job. Use one of the methods for monitoring relational database activity to examine the AS job log to find the job.
The following method for putting the AS job into debug mode works with any AR and a DB2 Universal Database™ for iSeries™ AS with certain restrictions. It depends on being able to pause after the application makes a connection to do setup. It also assumes that what you want to trace or otherwise debug occurs after the connection is established.
- Sign on to the AS and find the AS job.
- Issue the Start Service Job (STRSRVJOB) command from the interactive job (the job you are using to find the AS job) as shown:
STRSRVJOB (job-number/user-ID/job-name)The job name for the (STRSRVJOB) command is the name of the AS job. Issuing this command lets you issue certain commands from your interactive job that affect the AS job. One of these commands is the Start Debug (STRDBG) command.
- Issue the (STRDBG) command using a value of *YES for the UPDPROD parameter in the interactive job. This puts the AS job into debug mode to produce debug messages on the AS job log.
To end this debug session, either end your interactive job by signing off or use the End Debug (ENDDBG) command followed by the End Service Job (ENDSRVJOB) command.
Because the AS job must be put into debug before the SQL statements are run, the application might need to be changed to allow you time to set up debug on the AS. The AS job starts as a result of the application connecting to the AS. Your application can be coded to enter a wait state after connecting to the AS until debug is started on the AS.
If you can anticipate the prestart job that will be used for a TCP/IP connection before it occurs, such as when there is only one waiting for work and there is no interference from other clients, you do not need to introduce a delay.
- Program references
When a program is created, the i5/OS licensed program stores information about all collections, tables, views, SQL packages, and indexes referred to in SQL statements in an SQL program.
Parent topic:
Preparing distributed relational database programs
Related concepts
Monitoring relational database activity
Troubleshooting
QRWOPTIONS data area
Related tasks
Setting up DDM files
Related reference
End Debug (ENDDBG) command
End Service Job (ENDSRVJOB) command
Start Debug (STRDBG) command
Start Service Job (STRSRVJOB) command
SQL CALL statement (stored procedures)
Submit Remote Command (SBMRMTCMD) command