Wednesday, August 16, 2017

Sometimes a List API is better than the SQL View

use list api and user space to get data about spool files

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.


  1. It has been brought to IBM's attention that many of the new work management services are poorly performing, in particular qsys2.output_queue_entries is just abysmal. On our machine we have nearly a million spool files at any one time and this function takes over four hours to complete whereas a RPG program using APIs can list all spooled files in four minutes; twenty two processor system.

  2. I wonder if dynamic SQL against the Output Queue Entries SQL View would perform better.

    Possibly creating a view over the Output Queue Entries SQL view causes the Output Queue Entries SQL view to be completely instantiated (over all spool files?) before your view kicks in.


    1. As part of my testing before I decided to use the API I did try using the OUTPUT_QUEUE_ENTRIES View directly (without my View). Alas, I could not tell a difference in speed. :(

  3. The OUTPUT_QUEUE_INFO view retrieves a bunch of info for each entry on the output queues queried and has to join with OBJECT_STATISTICS to get the list of output queues to be queried. If you know which output queue you're interested in and don't need all the detailed information, you can call the OUTPUT_QUEUE_INFO() UDTF directly and pass DETAILED_INFO='*NO' to only get back the basic info. This will run faster and closer to speed of the API. Going through SQL will never be as fast as calling an API, but is massively more convenient (especially for ad-hoc queries). It's up to everyone to determine for themselves whether that trade-off is worth it.

    More info on the UDTF here:!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OUTPUT_QUEUE_ENTRIES%28%29

    1. Retrieving list of all the spools files on a single output queue, like WRKOUTQ, is OK fast.

      Try retrieving all the spool files for one user on all output queues, like WRKSPLF. That is so very slow using SQL Views.

  4. Been using the QUSLSPLF API for almost 10 years now. I use an RPGILE program to list the spool files for a specified OUTQ to a user space, then read the list and delete all SPLFs that are older than a number of days supplied to the program in a parameter. It runs weekly, cleaning 24 OUTQs in under 2 minutes, and has been invaluable in keeping disk usage under control. My vote is for the API.

  5. Simon Hutchinson, I always value your posts.

  6. In order to meet the user's original question, you could equally provide a Spooled File List Action Exit Program:

    AddExitPgm ExitPnt( QIBM_QSP_SPLF_LSTACT )
    Format( LASP0100 )
    PgmNbr( *LOW )
    Pgm( utilities/SNDSPLFPDF )
    Text( 'Spooled file list action ''M''' )
    PgmDta( *JOB 1 'M' )

    Apart from that, another valuable article!


  7. Very nice artical

  8. Very nice article...

  9. I always say get the right result, then make it faster.


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.