IBM Support

Debugging Stored Procedures and Exit Programs

Troubleshooting


Problem

This document contains information on how to debug an external stored procedure program called from a remote client.

Resolving The Problem

The following information describes how to debug an external stored procedure program called from a remote client. The instructions and job names used are for the operating system database server program - the server used by IBM i Access ODBC, OLE DB, ADO.NET, and the Java toolbox JDBC driver. The same technique can be used to debug most Host Server exit programs. Programs called through the Program Call and Remote Command server (QZRCSRVS) and stored procedures called by other server jobs such as SQL CLI (QSQSRVR) and IBM DRDA (QRWTSRVR).

This technique can also be used on SQL Stored procedures; however, the code being debugged is the C code that is generated by the database. Even if you are not familiar with the C programming language, you might still be able to derive useful debug information from the various SQL structures and parameters.

Overview

Stored procedures and exit programs can be debugged by using the standard OPM or ILE operating system debugger. When you debug problems from a remote client such as an IBM i Access ODBC program, the trick is to determine and locate the correct operating system job. First, make the connection to the database server and pause the client program. You can then locate the client job, use a 5250 emulation session to start a service job, start debug on the program, and then set the breakpoints. Finally, release the client program. When the breakpoint is hit, the emulation session opens a notification screen (OPM) or the ILE debugger. The PC application appears to hang while the program is stopped in the debugger.

Locate the Operating System Server Job

There are three methods that can be used to locate the server job depending on the design of the application. The first method described is the easiest method to implement. It works only if the application can be paused between the time of a connect and the call to the exit point or stored procedure and if the user profile is unique. If the application cannot be paused after it connects, Method 2 or Method 3 must be used.

Method 1:

Start the application, connect to the IBM i, and then locate the operating system job.

On the PC:
  • Sign on to the application with a unique user profile. End any other application, including IBM Navigator for i.
  • Start the application that makes the stored procedure call and connect to the host server.
  • Pause the application.
From a 5250 emulation session:
                             Work with Object Locks                            
                                                             System:   RCHASK50
 Object . . . . :   MIKSWENS              Type . . . . . :   *USRPRF            
   Library  . . :     QSYS                ASP device . . :   *SYSBAS            
                                                                               
 Type options, press Enter.                                                    
   4=End job   5=Work with job   8=Work with job locks                          
                                                                               
 Opt   Job          User         Lock      Status          Scope     Thread    
       QPADEV000L   MIKSWENS     *SHRRD     HELD           *JOB                
                                 *SHRRD     HELD           *JOB                
                                 *SHRRD     HELD           *JOB                
       QZDASOINIT   QUSER        *SHRRD     HELD           *JOB                
                                 *SHRRD     HELD           *JOB                
                                 *SHRRD     HELD           *JOB                
       QZLSFILET    QUSER        *SHRRD     HELD           *JOB                
                                 *SHRRD     HELD           *JOB                
                                                                               
                                                                               
                                                                               
                                                                               
                                                                         Bottom
 F3=Exit   F5=Refresh   F12=Cancel                                              
 
  • Locate the prestart jobs by using the WRKACTJOB JOB(QZDASOINIT) command. You must then press F14 (Include) to include the prestart jobs that are started but not "connected". Locate and find the QZDASOINIT jobs in a status of PSRW. These jobs are the prestart jobs that are waiting for a connection from a user.


Method 2:

Use this method only if you cannot pause the application after a connect but before the program call. In this method, all but one prestart job is ended so that we can predict which prestart job is selected when the application issues a connect.

Note: To use this method, you must prevent any other user from connecting to the server program under debug until after you complete these steps.

On the PC:
  • Verify that the application ended and that you are not connected.
From a 5250 emulation session:
  • Modify the prestart job entry so that the threshold and addition number of jobs values will allow for us to easily limit the system to only one possible job to connect the next request to. Display the prestart job entry for the prestart job by using option 10 from the DSBSBSD QUSERWRK command. Select the details for the QZDASOINIT prestart job entry. After you make note of the current values, so that they can be restored later, modify the prestart job entry: CHGPJE QUSRWRK QZDASOINIT INLJOBS(1) THRESHOLD(1) ADLJOBS(1).
  • Locate the prestart jobs by using the WRKACTJOB JOB(QZDASOINIT) command. You must then press F14 (Include) to include the prestart jobs that are started but not "connected". Locate and find the QZDASOINIT jobs in a status of PSRW. These jobs are the prestart jobs that are waiting for a connection from a user.
                             Work with Active Jobs                        RCHASK50
                                                                04/10/08  17:16:15
 CPU %:     1.1     Elapsed time:   00:01:03     Active jobs:   455

 Type options, press Enter.
   2=Change   3=Hold   4=End   5=Work with   6=Release   7=Display message
   8=Work with spooled files   13=Disconnect ...                                
                     Current                                                    
 Opt  Subsystem/Job  User        Type  CPU %  Function        Status            
        QZDASOINIT   MIKSWENS    PJ       .0                   TIMA
        QZDASOINIT   MIKSWENS    PJ       .2                   TIMW
        QZDASOINIT   QUSER       PJ       .0                   PSRW
        QZDASOINIT   QUSER       PJ       .0                   PSRW
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                         Bottom
 Parameters or command                                                          
 ===>                                                                          
 F3=Exit   F5=Refresh       F7=Find      F10=Restart statistics
 F11=Display elapsed data   F12=Cancel   F23=More options   F24=More keys      
                                                                               
  • Use menu option 4=End to end all but one prestart job.
Note: If the system is too busy to allow this technique to work, you can set up an alternate subsystem for your re-create client to use. Ask your IBM support representative to share information with you on how to create a custom subsystem and route your client IP address connection requests to that subsystem. The steps are documented in internal note 636659.


Method 3:

In some n-Tier environments, methods 1 and 2 cannot be used. Another option is to alter the stored procedure so that it immediately sends a break message to a message queue. The break message pauses the job when the stored procedure is called so you can locate the job. To locate the job, you can include the job name or user profile in the message (use the RTVJOBA command) or look for a job in MSGW status. If the procedure cannot be modified, then a final option is to write an exit program that sends the break message. An exit program registered on QIBM_QZDA_INIT is called once at the start of each connection. You could also register on QIBM_QZDA_SQL2 and screen for the call statement being passed in SQL statement field. Search IBM Documentation for 'Server Exit Programs' for further information on exit points.


Starting Debug

Once you locate the job, you can start a debug session on the job and set breakpoints.

From an operating system command prompt:
  • Record the fully qualified job name (<job number>/<user>/<job name>)
    You can use menu option 5=Work with from the WRKACTJOB or WRKOBJLCK screens described previously. It results in a Work with Job screen like the one shown next.
     
 Work with Job                                  
                                                            System:   S10TR014
 Job:   QZDASOINIT     User:   QUSER          Number:   619934                  
                                                                               
Select one of the following:                                                  
                                                                               
     1. Display job status attributes                                          
     2. Display job definition attributes                                      
     3. Display job run attributes, if active                                  
     4. Work with spooled files                                                
                                                                               
    10. Display job log, if active or on job queue                            

...
  • Start a service job on the job to be debugged.
    Use the STRSRVJOB 619934/QUSER/QZDASOINIT command where the fully qualified job name is the name determined in the previous step.
  • Start debug and set the breakpoints.
    Use the STRDBG PGM(MYLIB/MYPGM) UPDPROD(*YES) command. The options you use on the start debug command can vary depending on whether you use an ILE compiler. Setting a breakpoint is also different in each environment. See the Programmers Guide manual for your specific compiler for details on how to use the appropriate debugger. To retrieve help with debug related operating system commands, type the GO CMDDBG command.
  • Resume the program.
    If you are debugging an ILE program, press the F12=Resume key.

On the PC:
  • Now that the breakpoint is set for the prestart job, start and run the application. If the previous steps were completed successfully and no other user connected to the waiting prestart job, the emulation session breaks when the breakpoint is hit. The client application appears to hang while the application is stopped in the debugger.
Cleanup

On the System i system:
  • Run the ENDDBG command to end the debug session.
  • Run the ENDSRVJOB command to stop servicing the host server job.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

13763143

Document Information

More support for:
IBM i

Software version:
Version Independent

Operating system(s):
IBM i

Document number:
642491

Modified date:
17 March 2022

UID

nas8N1018253

Manage My Notification Subscriptions

Page Feedback