With the advent of AS/400 Client Access Express for Windows, IBM has opened up the AS/400 even more by giving you new ActiveX objects. In this article, I show you a Visual Basic (VB) program that uses the Program object to interact with AS/400 system API functions, specifically how to create user spaces and retrieve job log information from the AS/400 to your Windows client program. This article demonstrates the power and flexibility of the new interfaces and should give you some ideas of how you might take advantage of this new feature. You can use either Version 5 or Version 6 of Visual Basic to execute the code in this article, and with small modification, you can execute the code in any Microsoft script environment like Word, Excel, Access, Active Server Pagers (ASP), or Windows Scripting Host. The code for the complete project is available for download at www.midrangecomputing.com/mc/. Let’s get coding!

The Parts: Some Assembly Required

Here is an overview of all the parts necessary to call API functions on the AS/400. Client Access Express comes with something called the IBM AS/400 Client Access Express ActiveX Object Library, which is stored in a Dynamic Link Library (DLL) file named CWBX. This ActiveX library contains many objects for integrating with the AS/400. The objects that I use allow me to do the following:
• Connect and validate security information
• Run programs
• Call AS/400 programs, including system APIs
• Convert data from ASCII to EBCDIC and vice versa
• Do code page conversions on data Before you can do anything, however, you need to use the AS400System object to connect to the AS/400 and validate a user ID and password. After you’ve established a connection, you can create an instance of the Program object and associate it with the connection. Set the program name and library and any parameters and invoke the Call method to execute the program or API. If the Program object detects an error, it raises an error in your Visual Basic program.

The ProgramParameters object passes data to and from the AS/400. This object allows you to define the type of parameter required and either inquire about or specify the

parameter’s value. However, because the AS/400 and PC systems represent data differently in their binary forms, you must use the converter objects to translate data from one form to another. The converter objects operate on PC or AS/400 data and allow you to transform data into the required formats. A converter object takes a value stored in a PC format, such as an integer, and converts it to a series of bytes in EBCDIC, a byte array, suitable for the AS/400. On the other end of the chain, you can take a byte array that comes back from the AS/400 and convert it into something that the PC can deal with. It’s a good thing that converter objects exist, because they abstract you from the whole EBCDIC/ASCII thing.

Some AS/400 commands and API functions require that a structure, which may be thought of as the format the data will be arranged in, be passed to the program. To support structures, an object called Structure is available to allow you to define a set of “Structure fields” and populate those fields with data. You can also harvest data from Structure after you execute the Program object.

The Program

The program presented in this article is a useful little ditty that allows you to execute an SQL Select statement and view DB2 optimizer messages placed in the AS/400 job log. You can then double-click any job log message and see its detailed help information. While it all sounds simple, a lot is going on in this program, so I’ll attack it from beginning to end.

When the VB program starts, it executes the FORM_LOAD method. Within this method, the program connects to the AS/400 via the AS400System object. The program also connects to the AS/400 via ActiveX Data Objects (ADO) through an ODBC data source and retrieves the job number under which your ODBC connection is running. As a side note, you could connect to the AS/400 with an OLE DB provider rather than ODBC just by changing the connection string argument in the Open method.

Start Debugging Against the ODBC Connection

Connecting to the AS400System object is relatively simple, and you need to make this connection so you can use the Program object to retrieve job log information. In the program, you define a global variable called AS400 to represent the AS400System object. You use the Define method to set up the AS/400 system to which you are connecting. The Define method takes a string that can represent either the system name of your AS/400 or its IP address.

After you execute the Define method, you simply set the WindowHandle, User ID, and Password properties with their appropriate values and execute the Signon method. Signon ensures that the user ID and password are correct for the user profile and provides access to the AS/400 defined by the Define method.

The program is now ready to execute AS/400 commands. However, because the utility program allows you to execute SQL statements, the next step that you must perform is to log an ActiveX Data Objects (ADO) connection to the target AS/400. To make this connection, you must define an ODBC connection to your AS/400 via the ODBC Data Sources icon in the Windows control panel. After you’ve defined a data source, use the Open method of the ADO Connection object to initiate an ODBC connection. As soon as the connection is complete, the program executes a stored procedure called Return Job Information (RETJOBI) to retrieve the job number for the ODBC connection. Figure 1 shows the source and setup instructions for RETJOBI.

Finally, the program uses a stupid ODBC trick to start debugging on its AS/400 job: Any AS/400 command or API can be called as a stored procedure even if it is not declared as one. This is accomplished by calling the QCMDEXC program in the QSYS library to execute the Start Debug (STRDBG) command. Starting debug causes all DB2 optimizer messages to be written to the job log. These messages can help in diagnosing SQL performance problems, and that is the purpose of this program.

Now that the debug is enabled and the program knows the ODBC job number, it’s time to rock and roll!

Getting the Message

At this point, a screen like the one shown in Figure 2 appears. Type in an SQL statement in the top text area, and the bottom grid displays job log information. After typing a valid SQL statement, press Execute SQL to execute the statement and retrieve job log information about the statement execution. When you execute the statement, the contents of the statement text box are copied to an ADO Command object and the command is executed. At this point, the program is done with ADO until the next execution of a statement.

Control passes to the Retrieve Job Log (RJOBLOG) function, which retrieves all job log messages caused by the statement. RJOBLOG requires the user name, job name, and job number to execute. Because all 32-bit ODBC connections always have user QUSER and job name QZDASOINIT, the first two function arguments are easy. The job number passed is the one that the program inquired about during startup.

RJOBLOG begins by creating several structure objects used to pass data to various AS/400 programs. Next, it sets the System property of the AS400Prog object, which is an instance of the Program object, to the AS400System object instantiated during FORM_LOAD. As soon as the AS400Prog is associated with a connection, the ProgramName property is set to the Create User Space (QUSCRTUS) API and the Library property is set to QSYS. Job log messages are retrieved as a list, so a user space is required for the API that retrieves the messages. More information about user spaces can be found in the documentation of the Messaging API.

QUSCRTUS requires several parameters to be set for it to be called. For these parameters, an object called ProgParms is declared. ProgParms is an instantiation of the ProgramParameters object. The first step in using ProgParms is to call the Clear method, which ensures that all required properties of the object are set to their correct states. Next, the program uses the Append method of the ProgParms object to add parameters required for the call to QUSCRTUS. The first parameter is the qualified user space name. This parameter represents where the user space is created and what it is named. In this program, I am forcing the user space to be created in library QTEMP so it is automatically cleaned up by the AS/400 in the unlikely event that the program crashes.

Now it’s time to examine in detail how the parameters are set and how conversion objects are used. The user space name is a 20-character string. First, you use the Append method of the ProgParms object to add a parameter named QUSN. The Append method takes two additional arguments: the type of parameter and its length in bytes. In this case, the parameter is an input parameter with a length of 20 bytes. You use the string converter object, Strcvtr, to convert your user space name to an AS/400 representation. Set the Length property of the Strcvtr object instance to 20 and use the ToBytes method to convert the string HOWARD QTEMP to a 20-byte array and place the array into the Parameters object.

Next, the program sets the Extended attribute, Size, Initial value, Authorization, and Description parameters and invokes the command. If the command does not return a VB error, a user space has been created. Figure 3 shows a complete code listing for the steps that I have just described.

Using User Space

Now that a user space exists, the program sets up a call to the list job log (QMHLJOBL) API, which lists job log messages. The input formats of this command are simple until you get to the format of the message selection information. That particular format is a structure and controls which messages the API returns. To create the structure required, you use an instance of the Structure object called USP. I won’t go into agonizing detail about how this

structure is populated; you can look at the code to see how it’s created. However, I should point out a few interesting things.

Inside the structure is a field for the Starting Message Key, which is passed as a 4- byte field. The program initializes a variable called LastMessageNumber, which stores the last message key that the program retrieved. Because LastMessageNumber has not yet been used, the value of the variable is binary 0. This indicates that the QMHLJOBL API should return messages starting with the first message number, which is always 0.

The other interesting thing is that you need to embed a structure within this structure. The embedded structure lists message fields that you want to retrieve with the command. The fields that I have chosen are 302 and 404. Field 302 says that I want to see message descriptions with replacement text; field 404 says that I want to retrieve message help with replacement text. For more information on the structure and these values, see the message handling API book listed in Reference.

After all parameters are set, the API call is invoked via the Call method. If an error on the AS/400 is detected, a VB error is raised. Otherwise, the call is completed successfully, and you can retrieve the contents of the list from your user space by using the QUSRTVUS, Retrieve User Space, API function.

Reading User Space

The call to set up the QUSRTVUS command is simple. Attach the qualified user space name, the beginning offset to read, and the length of the read and add a parameter with enough space to handle the returned data. In this case, I always tell the program to begin at offset 0 and read through offset 65535, the maximum possible length of the user space. Not all the space is filled with data, but this is an easy way to get the data back in one call.

Once the call is completed, the job log messages are in the buffer that you give to QUSRTVUS. Now comes the laborious process of retrieving the data from the binary buffer and formatting it into VB variables. The first step is to set the Strip property of the String Converter object to “true” to remove trailing bytes from data that you get out of your buffer. The next step is to get 4 bytes, starting at offset 104, and convert them into a number that represents the size of the user space used. The 4 bytes at offset 124 tell you where your data list begins. The final step is to grab the size of each entry in bytes and the number of items that you can expect to retrieve from the list. Armed with this information, the program begins a FOR loop to extract each of the list fields.

The FOR loop starts at the offset at which the data list begins. (The data format of the list is documented in OS/400 Message Handling APIs V4R3 under the LJOB0100 format.) The program pulls the first 4 bytes, which represent the offset to the next entry. Then, the program pulls the offset of the field data, gets the message severity and message key, and proceeds to offsets 49 and 56, where it gets the date and time at which the message was sent. The program jumps ahead to the offset where the first field data starts and enters the FOR loop to get each returned field value. The program gets the offset of the next field start and the length of the field, grabs the field data, and places the field data into the grid. These steps are repeated until all fields and messages have been returned.

Data is returned as a byte array, so you need to use the string-conversion and long- conversion objects to convert the data to VB strings and longs. To help with these conversions, I’ve written two tiny functions: AN and AG. AN takes a 4-byte binary data element and uses the long converter to return a VB number. AG takes a byte string and uses the string converter to return a VB variable of type string. I’ve found these time-savers invaluable in uncluttering my code.

Wrapping It Up

Now that you have a basic understanding of how to call system API functions, you may want to take this program apart and add it to a program in your shop. Instant access to job log messages can be useful in debugging applications, and this program makes instant

access simple. You don’t have to hunt through all the QZDASOINIT jobs to find your target job; RETJOBI finds it for you.

This article is by no means a complete tutorial on how to call AS/400 API functions. However, I’ve given you a working utility that calls API functions and has plenty of code to take apart. (Full source is available on the MC Web site at www.midrangecomputing.com/mc/.) Experiment calling API functions and see what you can create. You may want to try using file APIs to get detailed information about physical and logical files, using backup and recovery APIs to manage backup tasks from your PC, or using performance APIs to make real-time graphs of system performance.

All in all, these new features of Client Access Express open up new vistas for client/server programmers. By using these new capabilities judiciously, you can take your AS/400 programs to a new level.

Reference

OS/400 Message Handling APIs V4R3 (SC41-5862-02, CD-ROM QB3AMN02)

1. Create the following CL command:

PGM PARM(&NBR)
DCL VAR(&NBR) TYPE(*CHAR) LEN(6)
RTVJOBA NBR(&NBR)

ENDPGM

2. Compile the command, CRTBNDCL, place in QSYS2 library. Make sure it is named RETJOBIC.

3. Execute the following statement to create the stored procedure:

create procedure qsys2.retjobi (inout :NBR char(6))
external name qsys2.retjobic simple call

Figure 1: Here is the source code and setup for RETJOBI.

Figure 2: Here is the program in action, showing the statement executed

‘ Set the System property of the Program object
Set AS400Prog.System = as400

‘ Set the LibraryName property of the Program object
AS400Prog.LibraryName = “QSYS”

‘ Set the ProgramName property of the Program object
‘In this case, we are calling QUSCRTUS to create a user space
AS400Prog.ProgramName = “QUSCRTUS”

ProgParms.Clear
‘Define parameters and set all input parameter values
‘begin by adding all parameters the call will need
ProgParms.Append “QUSN”, cwbrcInput, 20
strCvtr.Length = 20
ProgParms(“QUSN”).Value = strCvtr.ToBytes(“HOWARD QTEMP”)
ProgParms.Append “EA”, cwbrcInput, 10
strCvtr.Length = 10
ProgParms(“EA”).Value = strCvtr.ToBytes(“TEMPSPACE”)
ProgParms.Append “size”, cwbrcInput, 4
ProgParms(“size”).Value = longCvtr.ToBytes(65535)
strCvtr.Length = 1
ProgParms.Append “inv”, cwbrcInput, 1
usvalue(0) = 0
ProgParms(“inv”).Value = usvalue
ProgParms.Append “auth”, cwbrcInput, 10
strCvtr.Length = 10
ProgParms(“auth”).Value = strCvtr.ToBytes(“*ALL”)
ProgParms.Append “desc”, cwbrcInput, 50
strCvtr.Length = 50
ProgParms(“desc”).Value = strCvtr.ToBytes(“My Space”)

‘Invoke the program and create the user space
AS400Prog.Call ProgParms

Figure 3: This code sets up parameters for a call to create a user space.

Also Read

RPG Academy - Database Modernization: Entity Relationship Diagrams

RPG Academy - Database Modernization: A Bit of Database Theory

Talking About Modernization Isn’t Good Enough