Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
How to SORT Files in CL
has no ratings.
Published: 29 Jan 2014
Revised: 29 Jan 2014 - 1245 days ago
Last viewed on: 27 Jun 2017 (3240 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

How to SORT Files in CL Published by: Bob Cozzi on 29 Jan 2014 view comments(4)

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) 

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to SORT Files in CL
Posted: 3 years 4 months 29 days 11 hours 45 minutes ago

Not that this isn't somewhat nifty, but why not just use OPNQRYF?

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to SORT Files in CL
Posted: 3 years 4 months 29 days 11 hours 4 minutes ago
Edited: Wed, 29 Jan, 2014 at 10:54:09 (1245 days ago)

Just when I thought I was out, they pull me back in!

First, thanks Dale, I appreciate the Nifty comment.

I had considered mentioning OPNQRYF in the article, but never actual considered using it since it is now 2014 and not 1984. Need I go on? For the record, even the guy who wrote it, George Timms from IBM, thinks it should have been retired more than a decade ago.

[EDIT: I have gone into the article and mentioned OPNQRYF]

Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to SORT Files in CL
Posted: 3 years 4 months 29 days 10 hours 10 minutes ago

So I guess FMTDTA is out of the question? Mwahaha!

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to SORT Files in CL
Posted: 3 years 4 months 29 days 10 hours 4 minutes ago