Guru: Debugging SQL Stored Procedures with ACS


April 11, 2022

mike larsen

I use Access Client Solutions (ACS) daily to help me with different tasks. I usually run SQL scripts or work with IFS, but recently I was asked if there is a way to debug SQL stored procedures using ACS. Specifically, they were looking for a way for a “non-IBM i” person to debug SQL stored procedures on IBM i.

After going through the various menus in Run SQL Scripts in ACS, I found a system debugger. I had seen the system debugger before, but never took the time to explore it further. With this new request in mind, I decided to see what she offered. Within minutes I was able to debug a stored procedure and step through my code. Perfect!

To access the System Debugger, click the Run menu in Run SQL Scripts, then select System Debugger (Figure 1).

Figure 1. Locate the System Debugger

When you launch the debugger, a new window opens and asks you for the name of the program and the library. I show the important parts of the window in figures 2 and 3.

Figure 2. Enter program information

The name of my stored procedure is TEST_SP2, and it is in my personal library, MLLIB.

Figure 3. Task to debug

I’m pointing out the Job to Debug portion of the window because it’s important that you use the same ACS session for the debugger and for executing the stored procedure because the ACS session and the debugger are pointing to the same job on IBM i.

When you click OK in the Start Debugging window, the stored procedure source code is displayed (Figure 4). You can set breakpoints by clicking on a line of code. A breakpoint is indicated by a red arrow.

Figure 4. Set a breakpoint

Next, I start the debugger by clicking the green Resume button at the top left of the window (Figure 5).

Figure 5. Start the debugger

When I launch the debugger, the button changes and becomes greyed out. At this point, it is waiting for the stored procedure to be executed. Now I go back to the Run SQL Scripts window and run the stored procedure (Figure 6).

Figure 6. Execute Stored Procedure

When I run the stored procedure, I’m taken back to the debugger window and see that I’m hitting the breakpoint I set. The current line of code to be executed is highlighted in yellow (Figure 7).

Figure 7. Running the debugger

Before you start walking through the code, it’s important to show where you can see the values ​​of variables as they change. Program variables are located in the lower left corner of the debugger window (Figure 8).

Figure 8. Show program variables

I’ve highlighted the two variables I’m interested in. I use the F11 key to step through the code line by line. As I do this, I see the values ​​of the variables change (Figures 9 and 10).

Figure 9. Step through code

Figure 10. Show program variables

I’ve gone through most of the code and I see the values ​​I expect. To terminate the debugging session, I use F11 (other function keys are also available) until the program terminates. When I do this, a window pops up saying debugging is complete (Figure 11).

Figure 11. Debugging is complete

There is a small I got you I must underline. When using the debugger for the first time, I noticed that the variables were displayed in hexadecimal format. Fortunately, there is a simple solution to this challenge. In the debug window, click the Actions menu, then hover over Format. Make sure Hexadecimal is not selected (Figure 12).

Figure 12. Change variable format

ACS is full of great features and debugging stored procedures is just another example. Setting up and running the debugger is a simple process and can be used by both IBM i and non-IBM i developers. Hopefully this adds another tool to your toolbox.

Mike Larsen is a project manager and senior developer at Central Park Data Systems.

Previous UGC India Official Twitter Account Hacked and Account Restored
Next Sherrerd Teaching Award Recipients Announced