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.

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

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

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions