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).
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.
The name of my stored procedure is TEST_SP2, and it’s located in my personal library, MLLIB.
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.
Next, I start the debugger by clicking the green Resume button in the upper left portion of the window (Figure 5).
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).
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).
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).
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).
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).
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).
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.