03
Thu, Oct
2 New Articles

Objects of my Connection

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

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





Objects_of_my_Connection05-00.png 668x466

‘ 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.

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: