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