Sometimes a List API is better than the SQL View

Sometimes it is just "better" to use a list API than it is to use the SQL View that "replaced" it. This week has given me an example of this. I was asked to create a program that would allow users to email themselves reports. I knew I would have to present the users with a list of spool files from which they could select the one they would want to email.

"No problem", I thought, "I will just use the Output Queue Entries SQL View."

I created a View over the OUTPUT_QUEUE_ENTIRES View, and then created a program using a multiple row fetch to get more than one row from the View. In the "test" IBM i partition the program ran a bit slow the first time I ran it, then ran faster each time after as the DB2 query engine created a temporary access path for me. Being a "test" partition there are not many output queues or spool files when compared to the "live" partition. I moved the objects I created to "live" and called the program there. It took more than 20 minutes to load the subfile with the list of user's spool files. With several hundred users and a very large number of spool files (many thousands, does anyone ever delete spool files?) in the "live" partition it was searching through all of them to find the few spool files belonging to me. The time taken made this method unacceptable. Back to the "drawing board".

I had initially rejected using the List Spooled Files API, QUSLSPL, under the assumption it would be as fast, or slow, as the OUTPUT_QUEUE_ENTIRES View. Following my disappointment with my program using the View I created a test program using the QUSLSPL API. I was very pleasantly surprised how much faster using the APIs was compared to using the View. When I ran the test program on the "live" system I got the list of the user's spool files in a couple of seconds at most. As I write "modular" code using procedures I could just rewrite the procedure that gathered the data for user's spool files. The rest of the procedures would remain unchanged.

I am not going to give the whole program. It is similar to this example program which uses SQL to gather the data and RPG to load a subfile. I am just going to give parts of the my program that gets the data from the QUSLSPL API and loads a data structure array, that I then use to load the subfile.

I am not going to show all of the code as even this example program is really too long for one post. I am going to start with the procedure definitions of the APIs I will be using:

001 dcl-pr CrtUserSpace extpgm('QUSCRTUS') ; 002 *n char(20) const ; // Name 003 *n char(10) const ; // Attribute 004 *n int(10) const ; // Initial size 005 *n char(1) const ; // Initial value 006 *n char(10) const ; // Authority 007 *n char(50) const ; // Text 008 *n char(10) const options(*nopass) ; // Replace existing 009 *n char(32767) options(*varsize:*nopass) ; // Error feedback 010 end-pr ; 011 dcl-pr DltUserSpace extpgm('QUSDLTUS') ; 012 *n char(20) const ; // Name 013 *n char(32767) options(*varsize:*nopass) ; // Error feedback 014 end-pr ; 015 dcl-pr GetPointer extpgm('QUSPTRUS') ; 016 *n char(20) const ; // Name 017 *n pointer ; // Pointer to user space 018 *n char(32767) options(*varsize:*nopass) ; // Error feedback 019 end-pr ; 020 dcl-pr UserSplf extpgm('QUSLSPL') ; 021 *n char(20) const ; // Name 022 *n char(8) const ; // Format name 023 *n char(10) const ; // User 024 *n char(20) const ; // Qualified outq 025 *n char(10) const ; // Form type 026 *n char(10) const ; // User-specified data 027 *n char(32767) options(*varsize:*nopass) ; // Error feedback 028 end-pr ;

As you have seen I have added comments to describe what each of the parameters are.

I have used the following keywords with the parameter definitions:

• CONST - parameter passed by reference. What this means is that the value passed cannot be changed and returned to this program.
• OPTIONS(*VARSIZE) - denotes that the contents passed or returned may be greater or less than the value given to define the parameter, in other words variable in length.
• OPTIONS(*NOPASS) - the variable does not have to be passed to the called program.

There are four APIs will be using. As the list APIs use User Spaces for output the first three APIs are for User Space handling.

Lines 1 – 10: CrtUserSpace is the name I have called the QUSCRTUS that, as my name suggests, creates the User Space.

Lines 11 – 14: DltUserSpace no prize for guessing what the QUSDLTUS API does, it deletes the User Space.

Lines 15 – 19: GetPointer is the API, QUSPRTUS, I need to use to get the pointer for the User Space I have created.

The other API, QUSLSPL is the list API that will give me the data about the spool files. As a list API its output is written to a User Space.

The next few lines are data structures this program will be using.

029 /copy qsysinc/qrpglesrc,qusec // Error DS for APIs 030 /copy qsysinc/qrpglesrc,quslspl // DS for QUSLSPL API 031 dcl-ds ListDetail based(DetailPointer) likeds(QUSF0300) ; 032 dcl-ds ListHeader based(HeaderPointer) qualified ; 033 Offset int(10) pos(125) ; 034 Count int(10) pos(133) ; 035 Size int(10) pos(137) ; 036 end-ds ; 037 dcl-ds Data qualified dim(9999) ; 038 Outq char(10) ; 039 OutqLib char(10) ; 040 Created timestamp ; 041 SplfName char(10) ; 042 User char(10) ; 043 UserData char(10) ; 044 Status char(4) ; 045 Pages packed(6) ; 046 FormType char(10) ; 047 JobName char(28) ; 048 FileNbr packed(6) ; 049 end-ds ;

Lines 29 and 30: I do not have to enter the code myself for the data structures the APIs use. There is a member for the standard error data structure, QUSEC, and all the output data structures for the QUSLSPL API in the source file QRPGLESRC in the library QSYSINC. Conveniently the member names are the same of the standard data structure and API. By using the /COPY I can include these into my code.

Line 31: I can then define the data structure I will be using with QUSLSPL output with a LIKEDS to the data structure in the QUSLSPL source member. I am doing this so I can use a pointer with this data structure, which is not defined as part of the original data structure.

Lines 32 – 36: This data structure will be used to return the information about the data in the User Space.

Lines 37 – 49: This is the data structure array I need to move the data from the user space into. In my original program I was using this data structure array to load the subfile.

The definition of the stand alone variables follows:

050 dcl-s UserOrOutq char(1) inz('1') ; 051 dcl-s SortOrder char(1) inz('A') ; 052 dcl-s UserOutq char(10) inz('QHOLD') ; 053 dcl-s RowsFetched uns(5) ; 054 dcl-s UserSpace char(20) inz('USERSPACE QTEMP') ; 055 dcl-s ParmUser char(10) ; 056 dcl-s ParmOutq char(20) ; 057 dcl-s i int(10) ; 058 dcl-s wkISO char(10) ; 059 dcl-s wkHMS char(8) ; 060 dcl-s wkTimestamp char(26) ; 061 dcl-s StatusDesc char(4) dim(12) ctdata perrcd(1) ;

I am not going to say much about these except…

lines 50 – 52: In my original programs these were parameters passed from a calling program. For this example I am going to initialize them with values similar to those that would have been passed to the original program.

Line 61: I cannot remember if I have ever used a Table in an example program. This Table will be used to translate the spool file's status from the value returned from the API to something more meaningful. The Table, which would be at the end of the program, looks like:

106 ** StatusDesc 107 RDY 108 OPN 109 CLO 110 SAV 111 WTR 112 HLD 113 MSGW 114 PND 115 PRT 116 FIN 117 SND 118 DFR

I only use the APIs in the next 11 lines of code:

062 DltUserSpace(UserSpace:QUSEC) ; 063 CrtUserSpace(UserSpace:'':1:x'00':'*ALL': 'Example User Space':'*YES':QUSEC) ; 064 if (UserOrOutq = '1') ; // User 065 ParmUser = '*CURRENT' ; 066 ParmOutq = '*ALL' ; 067 else ; // Outq 068 ParmUser = '*ALL' ; 069 ParmOutq = UserOutq + '*LIBL' ; 070 endif ; 071 UserSplf(UserSpace:'SPLF0300':ParmUser:ParmOutq:'*ALL': '*ALL':QUSEC) ; 072 GetPointer(UserSpace:HeaderPointer) ;

I wrote about using User spaces before. Rather than repeat what I said before I am only going to point out what is different with this example.

Line 62: I am deleting the User Space before I use it. If it is not in my QTEMP an error message will be returned to the QUSEC data structure, which I am just going to ignore.

Line 63: I create the User Space I will be using. I am defining it to have a size of 1, this is the third parameter of the API. After my initial post about User Space it was brought to my attention that if a User Space is created with a size of 1 it become "automatically extendable", i.e. it will increase in size as I add data to it.

Lines 64 – 70: The original program would allow the user to select if they wanted to see only their spool files, or all the spool files in an Output Queue.

Line 71: This is the call to the QUSLSPL API. I am passing to it:

1. Name of the User Space
2. The format (data) I want returned
3. User id or *ALL
4. Output queue or *ALL
5. Form type or *ALL
6. User data or *ALL

The last parameter is the standard error data structure.

line 72: As the QUSLSPL loaded the User Space I need to get the User Space header information. This I do using the QUSPTRUS, or I have "renamed" it to GetPointer, which will assign a value to the pointer HeaderPointer.

Before I start retrieving information from the User Space I need to determine how many occurrences of the data is in the User Space. I can use the Count subfield of the ListHeasder data structure to do this.

073 if (ListHeader.Count = 0) ; 074 *inlr = *on ; 075 return ; 076 elseif (ListHeader.Count < %elem(Data)) ; 077 RowsFetched = ListHeader.Count ; 078 else ; 079 RowsFetched = %elem(Data) ; 080 endif ;

Lines 73 - 75: If noting was retrieved quit this program.

Lines 76 – 77: If the number of occurrences retrieved is less than the number of the elements in Data, the data structure array, use the number of occurrences.

Line 79: As my data structure array has 9,999 elements, if I have more occurrences than that in my User Space I have nowhere to put the extra ones, thus, I only want to retrieve the first 9,999 occurrences.

The next part of the program uses a For loop to extract the information from the User Space and write it to the data structure array.

081 for i = 1 to RowsFetched ; 082 DetailPointer = HeaderPointer + ListHeader.Offset + (ListHeader.Size * (i - 1)) ; 083 Data(i).Outq = ListDetail.QUSOQ00 ; 084 Data(i).OutqLib = ListDetail.QUSOQL03 ; 085 Data(i).Outq = ListDetail.QUSOQ00 ; 086 wkISO = %char(%date(ListDetail.QUSSOD:*cymd0):*iso) ; 087 wkHMS = %char(%time(ListDetail.QUSSOT:*hms0):*hms.) ; 088 wkTimestamp = wkISO + '-' + wkHMS + '.000000' ; 089 Data(i).Created = %timestamp(wkTimestamp) ; 090 Data(i).SplfName = ListDetail.QUSSFILN11 ; 091 Data(i).User = ListDetail.QUSUN17 ; 092 Data(i).UserData = ListDetail.QUSUD04 ; 093 Data(i).Pages = ListDetail.QUSSTP ; 094 Data(i).FormType = ListDetail.QUSFT09 ; 095 Data(i).FileNbr = ListDetail.QUSSFILN12 ; 096 Data(i).Status = StatusDesc(ListDetail.QUSSFILS01) ; 097 Data(i).JobName = %trimr(ListDetail.QUSJNBR13) + '/' + %trimr(Data(i).User) + '/' + %trimr(ListDetail.QUSJN14) ; 098 endfor ;

Line 82: This is where the pointer to the data in the User Space is moved to the appropriate occurrence of the data.

Lines 83 – 97: This is where the data is moved from the User space's detail data structure to the data structure array Data.

There are just a few more lines of the program:

099 DltUserSpace(UserSpace:QUSEC) ; 100 if (SortOrder = 'A') ; 101 sorta(A) %subarr(Data(*).Created:1:RowsFetched) ; 102 else ; 103 sorta(D) %subarr(Data(*).Created:1:RowsFetched) ; 104 endif ; 105 *inlr = *on ;

Line 99: As I have loaded the data structure array I have no reason for the User Space, therefore, I delete it.

Lines 100 – 104: The user can see the spool file data either in ascending or descending order by the timestamp for the time the spool file was created.

Line 105: And the program ends.

The Table I showed above would, of course, be at the bottom of this program.

 

I now have a program, which uses these APIs, that loads in a fraction of the time my original version, where I used the SQL View. This proves that list APIs should remain in your and my programming repertoire.

 

You can learn more about the QUSLSPL API from the IBM website here.

 

This article was written for IBM i 7.3, and should work for earlier releases too.