When I use CL to process an OUTFILE from one of the DSPxxx command, I've always used the data in arrival sequence. Sure I could have provided DDS to build a logical view over the OUTFILE and then processed it, but I wanted a more generic solution. I could have also used OPNQRYF but it is 2014, not 1984 so I don't speak "OPNQRYF" any more.

Recently we introduced an SQL "beta" into COZTOOLS that allows you to run SQL statements within CL or from Command Entry using our QuickSQL CL command. IBM also ported a version of our oriignal RUNSQL command to IBM i in one of the technology refreshes. The problem is, RUNSQL isn't available unless the system is running that specific TR or later. In addition, the way that IBM implemented RUNSQL is the same way I implemented it 25 years ago, using the QM Query engine, so no rocket science there. In this example any dynamic SQL tool, even using RPG with an embedded EXECUTE IMMEDIATE :stmt would work fine.

Today, I had an issue with the COZTOOLS SENDSAVF (Send save files via FTP) command. It normally generated a list of Save Files using DSPOBJD and then processed the save file names in arrival sequence. A client requested that the save files be sent in Smallest-to-Largest size order so that the most save files would be sent over before any of the larger ones; they have a lot of save files.

DSPOBJD OBJ(QGPL/*ALL) OBJTYPE(*FILE) OUTPUT(*OUTFILE)  + 
       OUTFILE(QTEMP/COZSNDSAVF)

QSQL       SQL('create index QTEMP/COZOBJSORT ON +             
             QTEMP/COZSNDSAVF (ODOBSZ, ODLBNM, ODOBNM)')       
                                                                                                                            
OVRDBF     FILE(QADSPOBJ) TOFILE(QTEMP/COZOBJSORT) 
READNEXT:   RCVF       RCDFMT(QLIDOBJD)              
             MONMSG     MSGID(CPF0864) EXEC(GOTO EOF) 

I began wondering about the best way to do this. I could switch the SENDSAVF command so that it used the QGYOLOB API as is used by all other COZTOOLS functions that build lists of objects. But that API does not permit sorting by Object Size. I could use a logic view, but I don't do DDS and don't want to ship DDS. Then it hit me. I wondered if I used QSQL to build an INDEX over the OUTFILE, if I could then process that INDEX as a Database File in CL. Could I?

So I wrote the code above, and IT DID NOT WORK.

Looking at the joblog I saw a LEVEL CHECK message. I added LVLCHK(*NO) to the OVRDBF command and tried again. Bam! The list of Save File names was retrieved into the SENDSAVF command processing program in Object Size Sequence (smallest to largest). I could have just as easily reversed that sequence.

So if you need to sort the output from any of the IBM supplied DSPxxx commands, using our new QSQL command or IBM's RUNSQL may be a good choice.

Here is the full example:

DSPOBJD OBJ(QGPL/*ALL) OBJTYPE(*FILE) OUTPUT(*OUTFILE)  + 
       OUTFILE(QTEMP/COZSNDSAVF)

QSQL       SQL('drop index qtemp/cozobjsort')                  
MONMSG     MSGID(CPF0000)                                      
                                                               
QSQL       SQL('create index QTEMP/COZOBJSORT ON +             
             QTEMP/COZSNDSAVF (ODOBSZ, ODLBNM, ODOBNM)')       
                                                               
CHKOBJ     OBJ(QTEMP/COZOBJSORT) OBJTYPE(*FILE)                
MONMSG     MSGID(CPF9800) EXEC( RETURN )                       
                                                               
OVRDBF     FILE(QADSPOBJ) TOFILE(QTEMP/COZOBJSORT) LVLCHK(*NO)
 
READNEXT:   RCVF       RCDFMT(QLIDOBJD)              
            MONMSG     MSGID(CPF0864) EXEC(GOTO EOF)