Guru: Debugging SQL Stored Procedures With ACS

April 11, 2022 Mike Larsen

I use Access Client Solutions (ACS) daily to help with different tasks. I’m usually running SQL scripts or working with the IFS, but recently I was asked if there was a way to debug SQL stored procedures using ACS. More specifically, they were looking for a way that a “non-IBM i” person can debug SQL stored procedures on IBM i.

After browsing 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 it offered. Within a few minutes, I was able to debug a stored procedure and step through my code. Perfect!

To get to the system debugger, click the Run menu within Run SQL Scripts, then select System Debugger (Figure 1).

Figure 1. Locate the system debugger

When you launch the debugger, a new window will open, and it prompts you for the program name and library. I’m showing 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’s located in my personal library, MLLIB.

Figure 3. Job to debug

I point 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 as the ACS session and the debugger point to the same job on IBM i.

When you click OK in the Start Debug window, you’ll be presented with the source code for the stored procedure (Figure 4). You can set breakpoints by clicking on a line of code. A breakpoint is indicted with a red arrow.

Figure 4. Set a breakpoint

Next, I start the debugger by clicking the green Resume button in the upper left portion of the window (Figure 5).

Figure 5. Start the debugger

When I start the debugger, the button changes so it is greyed out. At that point, it’s waiting for the stored procedure to be executed. Now, I go back to the Run SQL Scripts window and execute the stored procedure (Figure 6).

Figure 6. Execute the stored procedure

When I execute the stored procedure, I’m brought back to the debugger window, and I see that I’m positioned on the break point I set. The current line of code to be executed is highlighted in yellow (Figure 7).

Figure 7. Running the debugger

Before I begin stepping through the code, it’s important to show where you can see the values of the variables as they change. Program variables are in the lower left-hand corner of the debugger window (Figure 8).

Figure 8. View program variables

I’ve highlighted the two variables in which I’m interested. 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 the code

Figure 10. View program variables

I’ve stepped through most of the code and see the values I expect. To complete the debug session, I use F11 (there are other function keys available as well) through the end of the program. When I do that, a window pops up indicating the debug is complete (Figure 11).

Figure 11. Debugging is complete

There is a small gotcha I want to point out. When using the debugger for the first time, I noticed the variables showed in hex format. Fortunately, there is an easy fix for 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 the variable format

ACS is filled with great features and debugging stored procedures is just another example. Setup and execution of the debugger is an easy process and can be used by IBM i developers and non-IBM i developers alike. Hopefully this adds another tool to your toolbox.

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