Debugging SQL procedures

Debugging SQL procedures is very similar to debugging *PGM objects, because generated SQL procedures are *PGM objects. When debugging SQL procedures, you can have the Debugger editor display a source view after the source-level debug view is created.

About this task

To debug SQL procedures, you first need to launch the Integrated i Debugger. For further information about starting a debug session, see the related topic below.

Make sure that your debug preferences are set to always using the Debugger editor. To set debug preferences, select Window > Preferences > Run/Debug > Compiled Debug > Debug Editors. Select the Always use Debugger Editor while debugging check box.

Set a service entry point on the generated *PGM object of the SQL procedure. You can do this by right-clicking on the *PGM object and selecting Debug(service entry) > Set service entry point. Then run the program that calls the SQL procedure. When the SQL procedure is called, a debugging session is started automatically.

Alternatively, if you have a caller program which invokes your SQL procedure, you can perform one-step debugging. To do this, locate the caller program in the Remote System Explorer and then right-click it and choose Debug As > type_of_application from the pop-up menu. After the debugging session launches, you can run to the line which calls the SQL procedure. After this, stepping into the SQL procedure will cause the source statement of the SQL procedure to display in the Debugger editor. At this stage, you will be able to perform typical debug tasks, such as setting breakpoints, stepping into or over, issuing step returns, and monitoring variables.

If you do not have a caller program for your SQL procedure, you can invoke the procedure with interactive SQL. To do this, launch the debugger with full prompt, as follows:

Procedure

  1. Start a 5250 interactive job.
  2. Open the Debug launch configurations dialog box. Instructions for doing this can be found in Starting to debug.
  3. Create an i Job launch configuration.
  4. In the launch configuration What to Debug tab, specify the job attributes of the 5250 job and enter the generated SQL procedure *PGM object as the program. Other instructions for using the launch configuration can be found in Debugging a job with full prompt.
  5. After the debug session launches, switch back to the 5250 session and issue an SQL CALL statement using interactive SQL.
  6. The source statement of the SQL procedure will open in the Debugger editor and you will be able to perform typical debug tasks, such as setting breakpoints, stepping into or over, and monitoring variables.

Feedback