APIs by Example: DB2 for i Services and SQL CLI APIs Provide Easy Access to System Reply List

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.

Download the SystemReplyList zip file containing the source code.

More Information

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
https://apimymymy.wordpress.com/2010/02/25/apis-by-example-directing-api-output-to-output-files-using-the-sql-cli-apis/

Understanding What Controls the Automatic Reply Function
http://www-01.ibm.com/support/docview.wss?uid=nas8N1018052

i Can: Inquiry Messages and System Reply Lists
https://dawnmayi.com/2014/01/15/inquiry-messages-and-system-reply-lists/

Practical CL: System Reply Lists
https://www.mcpressonline.com/programming-other/cl/practical-cl-system-reply-lists

System Reply List and CCSIDs
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/nls/rbagssysreplstccsidsuprt.htm

IBM DB2, API and RPG Documentation

DB2 for i Services
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzajq/rzajqservicessys.htm

DB2 for i Services: QSYS2.REPLY_LIST_INFO view
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewreplylistinfo.htm

SQL Call Level Interface
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cli/rzadpkickoff.htm