Looking at the past few releases it becomes evident that IBM for some time now has been focusing on delivering more new DB2 for i Services, rather than adding to the system API repository. The DB2 for i Services offer access to a variety of information through system-provided SQL views, procedures, and functions, which in IBMs own words “provide an SQL interface to access, transform, order, and subset the information without needing to code to a system API”. Access to information is however only half the way, further efforts go into making putting this access to useful work. In this APIs by Example article I show you how you can employ the SQL CLI APIs to access this SQL interface directly. The resulting User API encapsulates the SQL function and exposes a very simply interface for other programs to access. Using the associated source code as a model, you can very easily adapt it to provide similar interfaces to the many other available DB2 for i Services.
In many cases the new DB2 for i Services practically replicate existing system APIs in terms of the information they make available, but in some cases they provide the only supported interface to the information exposed. One example of such a service is the QSYS2.REPLY_LIST_INFO view, which returns a list of the system reply list entries. In case you’re wondering what kind of entity the system reply list constitutes, please run the command WRKRPYLE (Work with Reply List Entries) on a command line on your System i, and examine the list panel displayed. To dive even deeper into the system reply list topic and the associated capabilities, please also refer to the “More information” section at the end of this article.
As opposed to system APIs, which are part of the base operating system, the DB2 for i Services require the chargeable IBM DB2 Query Manager and SQL Development Kit for i (5770-ST1) product to access the services, using system i programming languages such as RPG, COBOL, C, C++, and PL/I. The SQL CLI (Call Level Interface) APIs, however, are included with the operating system at no extra expense. As with many other intriguing discoveries, it was Scott Klement who pointed me in the direction of the SQL CLI APIs back in 2006. In his article “Retrieve an SQL Result Set with RPG”, Scott very thoroughly explained the process of setting up and executing SQL statements using the SQL CLI APIs.
I then later took advantage of Scott’s introduction to the SQL CLI APIs in my APIs by Example article “Directing API Output to Output Files Using the SQL CLI APIs”. Since the mentioned articles are no longer accessible on-line, I have included printed pdf-versions of these articles, as well as a few other articles covering the SQL CLI APIs, with the download zip-file associated with this article. To get things started, let’s have a look at the IBM documentation for the QSYS2.REPLY_LIST_INFO, which reveals the following information explaining the view format, column names and data types:
System Column Name Column Name Data Type SEQUENCE_NUMBER SEQNO SMALLINT MESSAGE_ID MSGID VARCHAR(7) MESSAGE_REPLY REPLY VARCHAR(32) COMPARISON_DATA COMPDATA VARGRAPHIC(28) Nullable CCSID 1200 COMPARISON_DATA_OFFSET OFFSET SMALLINT Nullable DUMP_JOB DUMPJOB VARCHAR(3)
In the RPG IV program that is going to declare and execute the SQL statement to access the QSYS2.REPLY_LIST_INFO view, I have translated the above specifications into the following data structure subfields – note the Nullind data structure subfield keyword, which is only supported as of release 7.3:
**-- SQL field values: D SQLValue Ds Qualified D SeqNbr 5i 0 D MsgId 7a Varying D MsgRpy 32a Varying D CmpDtaC 28c Varying CcsId( 1200 ) Nullind D CmpDtaOfs 5i 0 Nullind D DmpJob 3a
If you have access to the Start SQL Interactive Session (STRSQL) command or the Run SQL Scripts interface from IBM Access Client Solutions (ACS), you can inspect the current system reply list entries by running the SQL following statement:
SELECT * FROM QSYS2.REPLY_LIST_INFO
If run from an interactive session, and your job’s CCSID (Coded character set identifier) attribute is currently set to 65535 (*HEX), I recommend that you first change the CCSID attribute to the value specified for the job’s Default coded character set identifier.
You can use the command DSPJOB OPTION(*DFNA), to verify the current settings for both job attributes. If the job’s CCSID attribute is set to 65535 when the above SQL statement is run, you will not be able to see the actual value of the reply list entry’s Comparison data field, because the conversion of the field value from UTF-16 (CCSID 1200) will fail, if a valid target CCSID is not specified for the interactive job.
I have listed the steps involved in order to access the system reply list information using the SQL CLI APIs in the list below, which outlines the individual SQL CLI API calls. Steps 1 to 4 together initialize the SQL runtime environment and each API call gives you access to configure the set of runtime parameters controlled by the API in question. Steps 5 to 6 run the actual SQL statements accessing the table or function specified in your SQL statement as well as returning the produced result set. This includes executing the SQL statement, binding the result set variables to corresponding program variables, and a fetch loop repeated until the complete result set has been returned. Steps 7 to 9 then takes care of reversing steps 1 to 4 in order to release all the resources initially acquired. Here’s the sequence of all events performed:
1. Allocate an environment handle (only one per job allowed) - SQLAllocEnv() 1.1. Set environment attribute(s) for environment - SQLSetEnvAttrI() 2. Allocate a connection handle - SQLAllocConnect() 2.1. Set connection attribute(s) for connection - SQLSetConnectAttrI() 3. Establish a connection to the target database - SQLConnect() 4. Allocate a statement handle associated with the connection - SQLAllocStmt() 5. Directly execute the allocated SQL statement - SQLExecDirect() 5.1. Associate columns in the result set to program variables - SQLBindCol() 6. Fetch result set row data directly into associated variables - SQLFetch() 6.1. Repeat fetch while SQL_SUCCESS is returned 7. Deallocate statement handle - SQLFreeStmt() 8. Disconnect connection to target database - SQLDisconnect() 8.1. Deallocate connection handle - SQLFreeConnect() 9. Deallocate environment handle - SQLFreeEnv()
I have encapsulated the whole process outlined above in a separate List System Reply List module acting as a simple API returning two parameters. The first is an integer holding the number of system reply list entries returned and the second is an array data structure holding the complete list of all the entries. Due to the null indicator support introduced with release 7.3 and employed in the module, there’s also an alternative version of the module which will compile at release 7.2 and earlier.
In this API by Example article I present the Retrieve Reply List Entry (RTVRPYLE) command, which will help me demonstrate how to call and use the simple List System Reply List API, here in the context of a CL retrieve command. The Retrieve Reply List Entry (RTVRPYLE) command has the following appearance when prompted:
Retrieve Reply List Entry (RTVRPYLE) Type choices, press Enter. Sequence number . . . . . . . . 1-9999, *FIRST, *LAST, *MSGID Relationship . . . . . . . . . . *SAME *SAME, *NEXT, *PRV Message identifier . . . . . . . *ANY Name, *ANY CL var for RTNSEQ (4 0) . . Number CL var for RTNMSGID (7) . . Character value CL var for CMPDTA (28) . . Character value CL var for CMPDTAOFS (3 0) . . Number CL var for MSGRPY (32) . . Character value CL var for DMPJOB (4) . . Character value
Similar to most other retrieve commands, the RTVRPYLE command is valid only within a CL program or a REXX procedure. The command has an associated on-line help text panel group that explains both the command in general as well as all parameters in particular.
In terms of practical use of the RTVRPYLE command, one option is to request sequence number *FIRST which would retrieve the system reply list entry with the lowest sequence number, and then in a subsequent do while-loop, request the returned sequence number with relationship *NEXT, until all system reply list entries have been returned. This final event is signaled by the RTVRPYLE command when it returns the sequence number zero. Similarly, you could specify sequence number *LAST and loop through all reply list entries when you specify relationship *PRV on all subsequent command calls.
Another option would be to specify the sequence number special value *MSGID, and then specify a message identifier to locate for the MSGID parameter. If the specified message identifier is found, the associated sequence number is returned, otherwise the sequence number zero is returned.
The following source members are included in the zip-file containing all the system reply list command and objects mentioned in this article:
CBX3151 RPGLE List System Reply List CBX3151T RPGLE List System Reply List - Test CBX3151.72 RPGLE List System Reply List – Release 7.2 and earlier CBX3152 RPGLE Retrieve Reply List Entry - CPP CBX3152H PNLGRP Retrieve Reply List Entry - Help CBX3152T CLP Retrieve Reply List Entry - Test CBX3152X CMD Retrieve Reply List Entry CBX315M CLP Retrieve Reply List - Build command
To create all above CL commands download all source members into the respective source files on your System i. Compile and run the CBX315M program, and it will take care of running all the necessary create-commands. The CBX315M program expects a single 10-byte character parameter, specifying the library containing the aforementioned source files. All command objects created will also be placed in this library.
You can find more information explaining the system reply list concept and services following the links below:
APIs by Example: Directing API Output to Output Files Using the SQL CLI APIs
Understanding What Controls the Automatic Reply Function
i Can: Inquiry Messages and System Reply Lists
Practical CL: System Reply Lists
System Reply List and CCSIDs
IBM DB2, API and RPG Documentation
DB2 for i Services: QSYS2.REPLY_LIST_INFO view
SQL Call Level Interface
DB2 for i CLI Functions
DB2 for i CLI API SQLBindCol – Bind a column to an application variable
RPG CCSID control keyword
RPG Built-in %CHAR (Convert to Character Data)
Everyone should be coding the CCSID(*CHAR) keyword
Database Null Value Support
Working with null capable fields in RPG – Barbara Morris, IBM