• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Access Client Solutions, ACS, FHG, Four Hundred Guru, IBM i, Run SQL Scripts, SQL

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Cloud-Based Stock Optimization Solution Integrates With IBM i Thoroughly Modern: Improving The Digital Experience With APIs

    One thought on “Guru: Debugging SQL Stored Procedures With ACS”

    • Mike says:
      April 5, 2023 at 4:36 pm

      Excellent! Thank you Mike, this is exactly what I’ve been looking for for a really long time!

      Reply

    Leave a Reply Cancel reply

TFH Volume: 32 Issue: 24

This Issue Sponsored By

  • ProData
  • Fresche Solutions
  • ARCAD Software
  • Computer Keyes
  • Raz-Lee Security

Table of Contents

  • The State Of The IBM i Base 2022: Third Party Software Conundrum
  • Thoroughly Modern: Improving The Digital Experience With APIs
  • Guru: Debugging SQL Stored Procedures With ACS
  • Cloud-Based Stock Optimization Solution Integrates With IBM i
  • Reader Feedback On State Of The IBM i Base, IBM i Salaries

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle