24
Wed, Jul
0 New Articles

Debugging AS/400 Stored Procedure Calls from the Client (Part 1)

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Stored procedure development is difficult, and it’s even more difficult when called from a client application running Windows. In this article, I’ll explain not only how to call stored procedures from ActiveX Data Object (ADO) but also the likely next step of debugging the call when problems arise. I will show you how to determine the AS/400 server job that is handling the request and how to debug the stored procedure—and other events that may occur—during client-initiated execution. The goal is to show you how to set breakpoints and debug a stored procedure (an SQL function) and a trigger, all at the same time, on the host when execution is initiated from the client.

Because of space limitations, the source code for the stored procedure and the other host programs mentioned in this article are not printed here. However, the source code is not important, just the debugging concepts. All of the source and a nice Visual Basic (VB) application that you can use to play with the debugging techniques discussed in this article can be downloaded from the MC Web site (www.midrangecomputing.com/mc).

Application Overview

To give us some common ground to work on, I first had to come up with an application that would be interesting to debug. I decided to start by writing a stored procedure in RPG code called CUSTINS, which simply inserts a new row into a customer table. Figure 1 shows the parameters of the stored procedure.

The first task of the CUSTINS stored procedure is to call a user-defined SQL function called Get_Next_CustID to generate the next unique customer ID. An SQL function is very interesting because, behind the covers, it becomes a generated ILE C program as it is being created. In fact, the ILE C compiler is required for creating SQL functions and stored procedures that use the SQL procedure language.

Next, CUSTINS inserts the new row into the customer table. To make things even more interesting, I used the Add Physical File Trigger (ADDPFTRG) command to add an after-insert trigger to the customer table that will call another program called CUSTDLT. CUSTDLT is an ILE C program that deletes the row from the customer table that has the lowest customer ID. This means that, whenever I insert a new customer row, another one


gets taken out. This also implies that the customer table should start out with some rows already in it. (Hey, I said the program was interesting, not practical!) Finally, CUSTINS finishes execution by returning the newly inserted customer ID in the output parameter CustID.

Calling the Stored Procedure Using ADO

A little background on calling a stored procedure is in order. Calling a stored procedure from the client using ADO is really quite simple. Figure 2 shows the VB code to call CUSTINS. Note a few key points about the code. First, the call is surrounded by braces ({}) to indicate a stored procedure. Second, the command type is adCmdText. Third, a parameters refresh must be done to build the parameter name and type information into the ADO command object. ADO does not automatically ask for parameter information, because many providers do not support it. IBMDA400 does. If you don’t do this step, ADO will assume parameter type information that will most likely be incorrect, and your request will fail, typically with some sort of data mapping or data conversion error. Fourth, the AS/400 connection is established when the connection object is opened. Or is it? Actually, with the IBMDA400 provider, the connection to the AS/400 does not happen until the first actual request for work. In this case, the first connection occurs on line 10 when the parameters are refreshed. This will become very important when you must find the database server job (QZDASOINIT) that is servicing the stored procedure call. Finally, the newly inserted customer ID is the first parameter and, therefore, will be returned in the first index of the ADO parameters collection. The parameters collection is a zero-based collection, so the first entry is at index 0.

Debugging Options

There are two basic techniques for debugging host code called from a client application. Both of these techniques are general enough to apply to almost all client/server debugging situations, but, for the purposes of this article, I will discuss stored procedures only.

If you happen to be the developer of the client code, you can use your client integrated development environment (IDE) (VB, Delphi, PowerBuilder, Visual C++, Lotus Notes, etc.) to set a stop. Or, if your client application automatically pauses to display forms, accept user input, or wait for a command button to be pressed, the stop may allow debugging to be started on the host. This option is the simplest and most straightforward of the two and involves the following steps:

1. Set a breakpoint in the client IDE or client application.
2. Find the AS/400 server job that is servicing your request.
3. Start a service job from an AS/400 host emulator session.
4. Start AS/400 debug and set breakpoints.
5. Continue from the client IDE or client application.
6. Process through breakpoints.
7. End debug.

The second technique involves creating and registering an AS/400 database server exit program that will temporarily stop execution of the request as it comes into the server so that debugging can be started. This option is more involved to set up but can be useful when you have only a client executable and not an IDE to work with. This technique is also useful if the client developer and the host developer are different people and you are tired of bothering the client person to set a stop (maybe the more realistic case).


Option 1

Option 2

Because the topic of debugging from client applications is lengthy and complex, the remainder of this article will deal only with the steps of option 1 in greater detail. I will cover the steps and details of option 2 in Part 2 of this article in the August issue of MC. Read on and probe the details of option 1.

Debugging a Procedure Call

If you have VB, the ILE C compiler (5769CX2), the DB2 UDB Query Manager (5769ST1), and the SQL Development Kit (5769ST1) and you would like to play with this application and follow along with the breakpoints that I am about to set, it is available for download from the MC Web site (www. midrangecomputing.com/mc). The download contains an automated setup routine that will create the test library, the customer file, all of the programs, the stored procedure, and the SQL function; the routine will also add the trigger for you and add a cleanup routine that will tear it all down. I have tried to make it as easy as possible for you to use this application and follow along step-by-step with this article. Being able to do so is well worth the download time. But even if you cannot have the application at hand, please continue to follow along and picture what is happening. You can surely apply these concepts to your own application scenario.

Ensuring that Procedures Can Be Debugged

Before you can debug a procedure, trigger, or SQL function, you need to ensure that the correct options are specified when the programs are compiled. For embedded SQL RPG programs, you will use the Create SQL RPG Program (CRTSQLRPG) command, and you must specify OPTION(*LSTDBG). SQL functions need to be created via the Run SQL Statements (RUNSQLSTM) command, and you must specify DBGVIEW(*LIST). You may be aware that there is a very nice SQL editor interface through Operations Navigator that can also be used to create SQL functions and SQL stored procedures. The only problem with using the Operations Navigator interface is that you cannot specify an option to compile for debug. Therefore, you must use RUNSQLSTM if you want to compile for debug. Also, when you use a long name for an SQL function, the underlying ILE C program that is generated will have a semimangled name. For example, GET_N00001 is the program name that is generated for my function Get_Next_CustID. Finally, note that a user-defined function is created as a service program (*SRVPGM) object and not as a normal program (*PGM) object. For the ILE C programs, always make sure you specify DBGVIEW(*SOURCE) when you use the Create SQL ILE C object (CRTSQLCI) command to ensure that the programs can be debugged.

Set a Breakpoint in the Client IDE or Client Application

Start your IDE. During my testing, I used Visual Basic 6.0 as my IDE for developing a client application running on Microsoft Windows NT 4.0 with V4R4M0 Client Access Express connected to a V4R4M0 AS/400 server. All products had the latest service packs and PTF packages installed. As shown in Figure 2, you should set a breakpoint at line 11. At this point, you know that the database server has been initiated and that you have not yet called the CUSTINS stored procedure. Now, run the client application until the breakpoint is hit.

Find the AS/400 Server Job that Is Servicing Your Request

Using a PC 5250 emulator session, sign onto the AS/400. Issue the Work with Object Locks (WRKOBJLCK *USRPRF) command, where is the user ID specified on the connection from the client application. In Figure 3, you should see at least two jobs listed. One will be your emulator session, and the other will be named QZDASOINIT. This second one is your database server job. Specify option 5 to work with the QZDASOINIT job, and then write down the job name, job user, and job number that are shown. Actually, the job name will always be QZDASOINIT, and the job user will


always be QUSER. The only information that you really need to remember, then, is the job number. If you specify option 10 to view the job log, you can confirm from the informational messages that this job is indeed servicing your client application user profile.

Start a Service Job

With your QZDASOINIT job information nearby, issue the Start Service Job (STRSRVJOB) command, specifying your database server job as the job you want to service. Note that your user profile must have *SERVICE authority to run this command.

Start AS/400 Debug and Set Breakpoints

Now you are ready to run the Start Debug (STRDBG) command, as follows:

STRDBG PGM(ZZADODEBUG/CUSTINS +

ZZADODEBUG/CUSTDLT) +

UPDPROD(*YES) OPMSRC(*YES) +

SRVPGM(ZZADODEBUG/GET_N00001)

You will see that I have specified the stored procedure and trigger programs in addition to the user-defined function service program. I have also specified UPDPROD(*YES) and OPMSRC(*YES) as options. You need OPMSRC(*YES) to debug the source code of the RPG program. Once you start the debug, you should see a screen that looks like the one shown in Figure 4.

Welcome to the debugger! I am not going to spend a lot of time here, as it is pretty self-explanatory, but the following are the primary function keys that you will use:

• F14—Allows you to move between your three source modules
• F6—Adds a stop in the code
• F11—Displays the value of the variable that your cursor is positioned on
• F10—Steps through your code and stops one line at a time
• F12—Allows you to resume or continue until the next breakpoint or until the program has ended
• F21—Brings up a CL command line from the debugger

If you are following along with the downloaded VB application, set breakpoints at lines 101 (Get_Next_CustID), 110 (Insert Statement), and 121 (Commit) in CUSTINS. Set a breakpoint at 157 (Set NewCustID) in GET_N00001. If the debugger cannot set a breakpoint at the line requested, it will choose the closest line at which it can. In this case, the breakpoint will actually be set on line 173. Set a breakpoint at line 12 (the built-in minimum) in CUSTDLT.

Continue from the Client IDE or Client Application

Now everything is set. The client application is stopped at a breakpoint. You have found the database server job and started a service job on it. You have started the debug and set the breakpoints on the AS/400. The only thing left is to continue the client application and let it hit the breakpoints on the host.

Process Through Breakpoints

If you set the breakpoints as I’ve indicated, your first stop should be in CUSTINS at line 103 just before the call to the Get_ Next_CustID function. Press F12 to resume. At line 173 in Get_Next_CustID, position your cursor to the SQLP_L2.MAXCUSTID variable and press F11. This is the maximum customer ID. Now you need to increment it. Press F10 to proceed step-by-step until you reach line 180. Now check SQLP_L2.


NEWCUSTID to see the incremented value. Press F12 to resume. You should be back in CUSTINS at statement 113 before the call to insert the new customer row. Press F12 to resume. You are now stopped in the CUSTDLT trigger at line 12, ready to run the built-in minimum. Press F10 multiple times to proceed step-by-step until you have reached line 18. Check the value of :DltCustID. This is the customer ID that will be deleted. Press F12 to resume. Finally, you are back in CUSTINS at line 123 just before the commit. Press F21 to bring up a command line. Type WRKOBJLCK *USRPRF, find your QZDASOINIT job again, and view the job log. You will see that there are many messages in the job log that you don’t normally see there. That is because you have debugging turned on. Sometimes these extra debug mode messages can be very helpful in determining the source of your problem. Press F12 until you get back to the debugger and then press F12 again to resume. Your client IDE or application should now regain control, as you have resumed with no more breakpoints present.

End Debug

When you are finished debugging, you need to run the End Debug (ENDDBG) and End Service Job (ENDSRVJOB) commands to clean up. When you stop running your client application, the QZDASOINIT job will no longer be servicing your requests and will be returned to the prestart job pool to be used again by you or by someone else.

More to Come

As you can see by analyzing debug option 1, it provides a viable solution to the client application debugging problem—if you have control of the client-side application. You have also seen that debugging different source code languages, trigger programs, and even SQL-generated programs is all possible. Stay tuned for option 2 and database server exit programs, inquiry messages, and an automated test program called STRDEBUGIT that you can use when client control is not possible or desired.

REFERENCES AND RELATED MATERIALS

• Midrange Computing Web site: www.midrangecomputing.com

Parameter Name Type Direction Comment

CustID Integer Output Customer ID LName Char(30) Input Last name FName Char(30) Input First name Address Char(100) Input Address

Figure 1: The example RPG stored procedure CUSTINS—its parameters are shown here—inserts a new row into a customer table.


1 Dim cn400 As New ADODB.Connection 2 Dim cm As New ADODB.Command
3 Dim parms As Variant
4

5 cn400.Open "provider=ibmda400;data source=MySystem", "MyUserID", "MyPassword" 6

7 cm.ActiveConnection = cn400 8 cm.CommandText = "{call zzadodebug.custins

(?,?,?,?)}" 9 cm.CommandType = adCmdText 10 cm.Parameters.Refresh
11 cm.Prepared = True
12

13 parms = Array(0, "Klepel", "Brant", "IBM") 14 cm.Execute , parms
15

16 MsgBox "Added new customer with Customer ID of " & cm.Parameters(0).Value

Figure 2: Visual Basic and ADO coding make it easy to call an AS/400 stored procedure, like CUSTINS.

Debugging_AS-_400_Stored_Procedure_Calls...06-00.png 400x286

Figure 3: Using the WRKOBJLCK command on your user profile is the quickest way to find your database server job.


Debugging_AS-_400_Stored_Procedure_Calls...07-00.png 400x286

Figure 4: The AS/400 source code debugger (STRDBG) is easy to use.


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: