Testing and debugging distributed SQL programs is similar to testing and debugging local SQL programs, but certain aspects of the process are different.
More than one server will eventually be required for testing. If applications are coded so that the relational database names can easily be changed by recompiling the program, changing the input parameters to the program, or making minor modifications to the program source, most testing can be accomplished using a single server.
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 server 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, you must 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 Troubleshooting.
Informational messages about how the server 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 will 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 server 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 iSeries™ AR, and have the options shadowed to an iSeries server server.
If both the AR and AS are iSeries servers, 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 Set 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 the 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.
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.
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.