In the article “Debugging AS/400 Stored Procedure Calls from the Client (Part 1)” (MC, July 2000), I began a discussion about debugging stored procedures and the difficulties involved when they are called from a client application. In that article, I presented two basic techniques, or options, for debugging stored procedures (depending on your development situation), and I explored the details of one of those techniques, called Option 1. (If you would like to catch up or review last month’s article, it can be found on the MC Web site at www.midrangecomputing.com/mc. You may also want to keep a copy of that article handy as you read this.)

With this article, and with the last, there is a Visual Basic (VB) application available for download, also from the MC Web site. It has a setup that will create the SQLEXIT database server exit program on the AS/400 server, register it, create the data area, create the DBG0001 inquiry message, and create a number of stored procedures that you can play with to understand these debugging techniques better. When you are done, the cleanup tears it all down for you. The only thing it won’t do is recycle the prestart jobs.

Are you wondering what database server exit programs, data areas, inquiry messages, and recycling prestart jobs have to do with debugging? Well, you’ll see. In this article, I will analyze Option 2. Hang on to your seat, because we’re ready to go underground and dig a little deeper into this interesting topic!

This basic technique temporarily stops execution of the stored procedure call request as it comes into the database server so that debugging can be started. Then, when execution is resumed, your breakpoints can be hit. This method involves the following steps:

1. Create an AS/400 server exit program that sends an inquiry message.
2. Register the exit program.
3. Run the client application.
4. Start a service job from an AS/400 host emulator session.
5. Start AS/400 debug and set breakpoints.

Option 2

6. Answer the inquiry message.
7. Process through breakpoints.
8. End debug.

Create an AS/400 Server Exit Program

You may or may not be aware that all of the Client Access host servers support user-written exit programs. Typically, these exit programs allow system administrators to control which functions a particular client user is allowed to run for each of the specific servers. It’s more of a security mechanism, but you can also take advantage of these exit points for debugging purposes. For example, I wrote a sample exit program in CL, called SQLEXIT (see Figure
1), for the QIBM_QZDA_INIT exit point and the ZDAI0100 format of the database server. The QIBM_QZDA_INIT exit point is called whenever a new connection to the database server is started and a new QZDASOINIT job is initiated to service client database requests, such as calling a stored procedure. (All of this is documented in the IBM AS/400 Client Access Host Servers manual.)

The Basic Program (Debugging a Single User ID)

A stripped-down, simplified version of the exit program in Figure 1 could be created with about 40 lines of straightforward code. On line 1, you see that there are two parameters: STATUS and REQUEST.

STATUS is an output parameter. Returning 1 for STATUS will accept the request; returning anything other than 1 will fail the request. Again, this exit program was not written to increase or fine-tune security, so you should always return a 1 (see line 39) to accept the request. REQUEST is an input parameter structure, or format, as defined by the server exit point. Extract the user ID, server, format, and function from the REQUEST format (see lines 42 through 48).

On line 47, you need to update with the user ID that you will be using to connect with from the client application. If a match is made on the user ID (again, line 47), an inquiry message is sent to that user, asking, “Are you ready to debug?” (see line 119). I got a little fancy in the example code and created my own message ID, DBG0001, with replacement text values (see lines 110 through 117), but I could have kept it very simple. The key is that it’s an inquiry message and will temporarily halt execution of the QZDASOINIT job until the message is answered. This gives you time to start the service program and to start debug before continuing.

The job information that you need to start the service program can be captured and sent as part of the inquiry message text (see line 108). Changing your AS/400 user profile to DLVRY(*BREAK) allows you to receive the inquiry message as a break message to your emulator session.

That’s the basic exit program. I’ll continue with a couple of interesting enhancements that have been made to SQLEXIT.

Debugging Multiple Users Easily

Instead of hardcoding the user ID into the exit program, as shown on line 47 of Figure 1, suppose you removed that specific check and checked for a data area of the same name. If the data area exists, you would fall into the debug processing code and send the inquiry message; otherwise, you’re done. The advantage to this approach is that you don’t have to update the exit program every time you want to debug a different user ID; you just create a new data area.

A data area can also be used to pass additional values or options into the exit program. For example, the first byte of my data area can be one of three values (N, Y, or I) for what I call the debug mode. The N value means no debugging. The Y value means just STRDBG for this job. (This would be the case if you wanted to see the extra messages in the job log that debug provides but didn’t want to send the inquiry message and

interactively debug.) The I value means send the inquiry message and setup for interactive debug.

See lines 50 through 60 (Figure 1) for the data area check and retrieval of the debug mode value. See lines 71, 100, and 106 for debug mode processing of N, Y, and I, respectively. Line 120 sends the inquiry message to the same AS/400 user ID that the client application connected as. You may wish to add another value to the data area that specifies a different AS/400 user ID to send the message to and therefore debug from.

Debugging a Specific Client Device

There may be cases in which many users are running the same client application on different PCs and the application always connects with the same user ID. How can you debug one client device and not interrupt the other users? One of the first messages placed in the QZDASOINIT job log is Servicing user profile &1 from client &2 (CPIAD12). The client name is either an Advanced Program-to-Program Communications (APPC) remote location name or a TCP/IP remote system name. The SQLEXIT program can read this client name from the CPIAD12 message and compare it with the PC device that you want to debug from. Another good use of the data area, discussed previously, is to pass in the PC device name to debug. I did this with my data area; it happens to start at byte 2 and is 255 bytes long.

See lines 62 through 67 (Figure 1) for retrieval of the PC device name from the data area. See lines 73 through 98 and exception monitor lines 138 through 140 for retrieval of the client name from the CPIAD12 message. The CPIAD12 message is a fairly new change to the host servers. If you don’t see the message in the job log, make sure that you have PTF SF60283 for V4R3 or PTF SF60284 for V4R4.

Lastly, you can compile the SQLEXIT program by using the Create CL Program (CRTCLPGM) command.

Register the Exit Program

Now that you have the exit program created, it is time to register it to the database server exit point. As shown in Figure 2, you do this by using the Work with Registration Information (WRKREGINF) command. Note that your user profile needs *SERVICE authority to run this command. Scroll down through the list of exit points until you find the one named QIBM_QZDA_INIT, and then select option 8 to work with exit programs, followed by option 1 to add. Surprisingly, the fact that you register an exit program—or unregister one, for that matter—doesn’t mean it is going to get called or not get called. The final step is to recycle the QZDASOINIT prestart jobs.

Since these prestart jobs are already started and are essentially waiting for new client requests, they have already been initialized with exit program information. The safest way to recycle is to use the Work with Active Jobs CL command WRKACTJOB SBS(QSERVER). Press F14 to include the inactive prestart jobs. Look for the QZDASOINIT jobs that are in Program Start Request Wait (PSRW) status. Use option 4 by each entry to end them all at the same time. Since there is probably a threshold set, new prestart jobs will now be created. These will be initialized with the new exit program information. If you connect shortly after the recycle, chances are good that you will get one of the new prestart jobs.

Using this method is safest, because it will not affect connected users. But, because you did not end all of the prestart jobs, your exit program still may not be called. It is possible that a user who was connected at the time you ended the prestart jobs has been disconnected; his job has been returned to the prestart job pool, and you are assigned to it when you connect. This job still does not have the exit program initialized. I only tell you this because it can happen and so you will understand what is going on if it does.

Debugging the Client Application

If you haven’t started an emulation session to the AS/400 yet, do it now. Once you run the client application and make a connection, it will appear to be hung up. In fact, it has been stopped by the exit program and the sending of the inquiry message. When you are ready, run the client application that you want to debug. You should receive the inquiry message from the SQLEXIT program that you can check from your emulator session by using the Display Messages (DSPMSG) command. Write down the QZDASOINIT job information from the inquiry message, and exit the display messages.

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

Now you are ready to run the Start Debug (STRDBG) command and set your stored procedure source code breakpoints using the AS/400 source code debugger. See “Debugging AS/400 Stored Procedure Calls from the Client (Part 1)” for a detailed explanation on this step and setting breakpoints in the downloaded application. Remember that the client application is stopped. The SQLEXIT program has intercepted the incoming database server request and sent an inquiry message that has not yet been responded to. You are ready to do that now.

From within the AS/400 debugger, press F21 to access a command line. Issue the DSPMSG command and answer the inquiry message, specifying G for go, Y for yes, or N for no. Cancel back to your debugger screen by pressing F12, and your first breakpoint should be hit.

Debugging the Exit Program (for Extra Credit)

What do I do if the inquiry message did not get sent? What if the SQLEXIT program is not working? What is going on? Now how do I debug the exit program? The following are a few of my personal observances. First, you cannot predict which of the QZDASOINIT prestart jobs will be selected for your request. I suggest trying to run the client application a few times. If a QZDASOINIT job comes up more than once, chances are that it will come up again. I recommend starting the service job and starting debug on any job that is consistently servicing. It really is a hit or a miss. Second, and most important, you should set any breakpoints in the exit program after the successful check of the user ID. In my SQLEXIT example (Figure 1) this would be line 48. Setting a breakpoint on line 47, for example, will halt all client applications that are coming into the database server (potentially many, many PC users) until you realize that this is not the request that you want to debug and can resume the debugger from the set breakpoint. You will not be well-liked if you are holding up other users’ client applications.

Using the STRDEBUGIT Tool

I have written a CL program, called STRDEBUGIT, that will automate the process of interrogating the inquiry message, starting the service job, and starting debug; when it is done, it ends the service job and ends debug. You still need to set the breakpoints on your own once the debugger is started. It is a simple program whose source is shown in Figure
3. Give it a try when you get tired of typing all of the commands. Note that, if you have multiple emulator sessions running, you need to run the program from the session that has your message queue allocated (that is, the first session that you signed on to). If you don’t do this, the STRDEBUGIT program will fail because the Receive Message (RCVMSG) command (line 20) cannot allocate the message queue.

Process Through Breakpoints

If you are following along with the downloaded VB application and you have set the breakpoints as I’ve indicated, your first stop should be in CUSTINS. See part 1 of this article for details of this step. Otherwise, process through your own breakpoints that you have set to debug your stored procedure being called from your client application.

When you are finished debugging, you need to run the End Debug (ENDDBG) and End Service Job (ENDSRVJOB) commands to clean up. Use WRKREGINF, then option 8, followed by option 4, to remove the exit program registration. Remember to recycle the prestart jobs again, or your exit program may continue to be called. To be certain, you could just delete the SQLEXIT program object.

After analyzing Option 2, you can see that it is indeed trickier and more complicated than Option 1. With both articles, you now have the full picture of debugging AS/400 stored procedures called from the client. Sometimes Option 1 is the best choice, and sometimes Option 2 is the best choice. One thing is certain: You should feel confident that you can now debug any stored procedure that comes your way.

REFERENCES AND RELATED MATERIALS

• AS/400 Client Access Host Servers V4R2 (SC41-5740-01, CD-ROM QB3AUX01)

1 PGM PARM(&STATUS &REQUEST)
2

3 /* Declare program parameters */
4 DCL VAR(&STATUS) TYPE(*CHAR) LEN(1) /* Accept/reject indicator (output) */
5 DCL VAR(&REQUEST) TYPE(*CHAR) LEN(2000) /* Parameter structure (input) */
6

7 /* Declare input variables read from &REQUEST parameter structure */
8 DCL VAR(&USER) TYPE(*CHAR) LEN(10) /* Userid making the request */

9 DCL VAR(&SERVER) TYPE(*CHAR) LEN(10) /* Client access server name */
10 DCL VAR(&FORMAT) TYPE(*CHAR) LEN(8) /* Format of &REQUEST structure */
11 DCL VAR(&FUNCTN) TYPE(*DEC) /* Function being performed */
12

13 /* Declare variables used with RCVMSG and SNDUSRMSG commands */
14 DCL VAR(&MSGID) TYPE(*CHAR) LEN(7) /* Message ID */
15 DCL VAR(&MSGTXT) TYPE(*CHAR) LEN(255) /* First level message text */
16 DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(291) /* Message replacement text values */
17 DCL VAR(&REPLY) TYPE(*CHAR) LEN(1) /* Inquiry message reply value */
18 DCL VAR(&MSGKEY) TYPE(*CHAR) LEN(4) /* Unique message key */
19

20 /* Received replacement text values from message CPIAD12 */
21 /* CPIAD12 - Servicing user profile &1 from client &2. */
22 DCL VAR(&SUSER) TYPE(*CHAR) LEN(10) VALUE(‘ ‘) /* Servicing user profile */
23 DCL VAR(&CLIENT) TYPE(*CHAR) LEN(255) VALUE(‘ ‘) /*Client name or TCP/IP addr*/
24

25 /* Declare variables used with RTVJOBA command */
26 DCL VAR(&JNAME) TYPE(*CHAR) LEN(10) /* Server job name */
27 DCL VAR(&JUSER) TYPE(*CHAR) LEN(10) /* Server job user */
28 DCL VAR(&JNBR) TYPE(*CHAR) LEN(6) /* Server job number */
29

30 /* Declare variables used with RTVDTAARA command */
31 DCL VAR(&DBGMODE) TYPE(*CHAR) LEN(1) /*Debug mode. Y-Yes, N-No, I-Interactive*/
32 DCL VAR(&IPADDR) TYPE(*CHAR) LEN(255) /* Client name or TCP/IP address */
33

34 /* Monitor for an unexpected error message received by this program. */
35 /* If so, the message will be recorded in the joblog. But, this program will */
36 /* end gracefully. */
37 MONMSG MSGID(CPF1999) EXEC(GOTO CMDLBL(STOPDBG))
38

39 CHGVAR VAR(&STATUS) VALUE(‘1’) /* Initialize return value to accept request */
40

41 /* Extract individual values from the &REQUEST input parameter structure */
42 CHGVAR VAR(&USER) VALUE(%SST(&REQUEST 1 10)) /* Extract userid */
43 CHGVAR VAR(&SERVER) VALUE(%SST(&REQUEST 11 10)) /* Extract server name */
44 CHGVAR VAR(&FORMAT) VALUE(%SST(&REQUEST 21 8)) /* Extract &REQUEST format */
45

46 /* If the user and server name and format match what we expect... */
47 IF COND(&USER = ‘’ & &SERVER = ‘*SQL’ & &FORMAT = ‘ZDAI0100’) THEN(DO)
48 CHGVAR VAR(&FUNCTN) VALUE(%BIN(&REQUEST 29 4)) /*Extract function request*/
49 IF COND(&FUNCTN = 0) THEN(DO) /* If function is CONNECT request... */
50 /* Check for a data area that exists with the same name as the userid.*/
51 /* If so, look for debug information: debug mode and client name or */
52 /* IP address. */
53 RTVDTAARA DTAARA(%LIBRARY%/&USER (1 1)) RTNVAR(&DBGMODE)
54 /* CPF1021 - Library &1 not found for data area &2. */
55 MONMSG MSGID(CPF1021) EXEC(GOTO CMDLBL(NOLIB)) /* No library */
56 /* CPF1015 - Data area &1 in &2 not found. */
57 MONMSG MSGID(CPF1015) EXEC(GOTO CMDLBL(NODTAARA)) /* No data area */
58 /* CPF1089 - Substring specified for data area not valid. */
59 MONMSG MSGID(CPF1089) EXEC(CHGVAR VAR(&DBGMODE) +
60 VALUE(‘ ‘)) /* Can’t read &DBGMODE, default to blanks */
61

62 /* See if the user wants to debug a specific client name or device */
63 /* TCP/IP address. */
64 RTVDTAARA DTAARA(%LIBRARY%/&USER (2 255)) RTNVAR(&IPADDR)

65 MONMSG MSGID(CPF1015) EXEC(GOTO CMDLBL(NODTAARA)) /* No data area */
66 MONMSG MSGID(CPF1089) EXEC(CHGVAR VAR(&IPADDR) +
67 VALUE(‘ ‘)) /* Can’t read &IPADDR, default to blanks */
68

69 /* User data area specified (N)o for debug mode. We do not want to */
70 /* debug this userid at this time. */
71 IF COND(&DBGMODE = ‘N’) THEN(GOTO CMDLBL(STOPDBG))
72

73 /* Get the client name or IP address from the CPIAD12 message */
74 /* First, send a dummy message so that we can get a message key back. */
75 SNDPGMMSG MSG(‘SQLEXIT: This is a dummy message.’) +
76 TOPGMQ(*SAME) KEYVAR(&MSGKEY)
77 /* Remove the dummy message so that it doesn’t show up in the joblog */
78 RMVMSG MSGKEY(&MSGKEY)
79 /* We know that the CPIAD12 message is in the joblog, so let’s */
80 /* manufacture a message key, by subtracting 1 from our dummy message */
81 /* message key, and try the new key until we find the correct message.*/
82 AGAIN: CHGVAR VAR(%BIN(&MSGKEY)) VALUE(%BIN(&MSGKEY) -1) /* Create key */
83 RCVMSG PGMQ(*SAME) MSGQ(*PGMQ) MSGTYPE(*ANY) +
84 MSGKEY(&MSGKEY) RMV(*NO) MSG(&MSGTXT) +
85 MSGDTA(&MSGDTA) MSGID(&MSGID) /* Try new key */
86 /* Monitor for CPF2410 - Message key not found in message queue &1. */
87 MONMSG MSGID(CPF2410) EXEC(GOTO CMDLBL(CPF2410)) /* GOTO handler */
88

89 /* Do we have the correct message... */
90 IF COND(&MSGID = ‘CPIAD12’) THEN(DO)
91 /* Extract servicing user and client name or IP addr from the */
92 /* message data replacement text values. */
93 CHGVAR VAR(&SUSER) VALUE(%SST(&MSGDTA 1 10))
94 CHGVAR VAR(&CLIENT) VALUE(%SST(&MSGDTA 11 255))
95 ENDDO
96 ELSE DO /* We got a message. But, not the one we are looking for. */
97 GOTO CMDLBL(AGAIN)
98 ENDDO
99

100 IF COND(&DBGMODE = ‘Y’) THEN(DO) /* Data area specified (Y)es */
101 STRDBG UPDPROD(*YES)
102 SNDPGMMSG MSG(‘SQLEXIT: Issued STRDBG command to +
103 show extra messages in the joblog.’)
104 ENDDO
105

106 IF COND(&DBGMODE = ‘I’) THEN(DO) /* Request for interactive debug */
107 /* Retrieve the current QZDASOINIT job information. */
108 RTVJOBA JOB(&JNAME) USER(&JUSER) NBR(&JNBR)
109

110 /* Prepare the message replacement text values for message */
111 /* DBG0001 - Are you ready to debug job &3/&2/&1 servicing user */
112 /* profile &4 from client &5? */
113 CHGVAR VAR(%SST(&MSGDTA 1 10)) VALUE(&JNAME) /* Set &1 */
114 CHGVAR VAR(%SST(&MSGDTA 11 10)) VALUE(&JUSER) /* Set &2 */
115 CHGVAR VAR(%SST(&MSGDTA 21 6)) VALUE(&JNBR) /* Set &3 */
116 CHGVAR VAR(%SST(&MSGDTA 27 10)) VALUE(&SUSER) /* Set &4 */
117 CHGVAR VAR(%SST(&MSGDTA 37 255)) VALUE(&CLIENT) /* Set &5 */
118

119 SNDUSRMSG MSGID(DBG0001) MSGF(%LIBRARY%/MYMSGF) +
120 MSGDTA(&MSGDTA) TOUSR(&USER) MSGRPY(&REPLY)
121 IF COND(&REPLY = ‘Y’ *OR &REPLY = ‘G’) THEN(DO)
122 SNDPGMMSG MSG(‘SQLEXIT: Interactive debugging started +
123 for this job!’)
124 ENDDO
125 ELSE DO /* Reply was not (Y)es or (G)o. */
126 SNDPGMMSG MSG(‘SQLEXIT: Interactive debugging +
127 declined for this job!’)
128 ENDDO /* Check reply */
129 ENDDO /* If debug mode (I)nteractive. */
130 ENDDO /* If function is connect request. */
131 ENDDO /* If the server value and the format match what we expect. */
132

133 NOLIB: /* Library that we are looking for data queues in does not exit. */
134 NODTAARA: /* No data area exists for this user. No debugging will be done. */
135 STOPDBG: /* An unexpected error occured or some other exit condition. */
136 RETURN /* Return to caller */
137

138 /* Exception handler for CPF2410 message */
139 CPF2410: RCVMSG MSGTYPE(*EXCP) RMV(*YES) /* Remove message from joblog */
140 GOTO CMDLBL(AGAIN) /* Return to try again. */
141 ENDPGM /* End of program */

Figure 1: The SQLEXIT exit point program gets called whenever a new database connection starts.

Figure 2: Register your exit point program with the WRKREGINF command.

1 PGM
2 /* Variables used by RTVJOBA */
3 DCL VAR(&USER) TYPE(*CHAR) LEN(10)
4

5 /* Variables used by RCVMSG */
6 DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
7 DCL VAR(&MSGTXT) TYPE(*CHAR) LEN(255)
8 DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(291)
9

10 /* QZDASOINIT job information retrieved from */
11 /* MSGDTA of the DBG0001 inquiry message sent */
12 /* from the SQLEXIT program. */
13 DCL VAR(&JNAME) TYPE(*CHAR) LEN(10)
14 DCL VAR(&JUSER) TYPE(*CHAR) LEN(10)
15 DCL VAR(&JNBR) TYPE(*CHAR) LEN(6)
16

17 RTVJOBA USER(&USER) /* Retrieve current job userid */
18

19 /* Receive the inquiry message. */
20 RCVMSG MSGQ(&USER) MSGTYPE(*LAST) RMV(*NO) +
21 MSG(&MSGTXT) MSGDTA(&MSGDTA) MSGID(&MSGID)
22

23 /* If we got the correct message... */
24 IF COND(&MSGID = 'DBG0001') THEN(DO)
25 CHGVAR VAR(&JNAME) VALUE(%SST(&MSGDTA 1 10))
26 CHGVAR VAR(&JUSER) VALUE(%SST(&MSGDTA 11 10))
27 CHGVAR VAR(&JNBR) VALUE(%SST(&MSGDTA 21 6))
28 /* Start a service job */
29 STRSRVJOB JOB(&JNBR/&JUSER/&JNAME)
30 /* Start debug */
31 STRDBG PGM(%LIBRARY%/CUSTINS +
32 %LIBRARY%/CUSTDLT) +
33 UPDPROD(*YES) OPMSRC(*YES) +
34 SRVPGM(%LIBRARY%/GET_N00001)
35 /* When done debugging, end debug and end service job */
36 ENDDBG
37 ENDSRVJOB
38 SNDMSG MSG('STRDEBUGIT: Done debugging!') TOUSR(&USER)
39 ENDDO
40 ELSE DO
41 SNDMSG MSG('STRDEBUGIT: Did not find inquiry message DBG0001!') +
42 TOUSR(&USER)
43 ENDDO
44

45 ENDPGM

Figure 3: STRDEBUGIT is a simple CL program for processing the inquiry message and automating the debug process.

Also Read

TechTip: Read from and Write to an IFS File Using SQL

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL