Guru: Sorting Options For CPYTOIMPF
March 27, 2017 Ted Holt
I hope that whoever came up with the idea for the Copy to Import File (CPYTOIMPF) command was well compensated. When I think of the time and effort that that command has saved me and countless others, I feel deep gratitude. The addition of the ORDERBY parameter increased the usefulness of CPYTOIMPF, and I’d like to share that with you.
CPYTOIMPF copies a single-format database file (table, physical file, view, or logical file) to a stream file or physical file in a format that is acceptable to another system or application. Probably the most common use of this command is to build a file of comma-separated values (CSV) for import into Microsoft Excel. Understanding a bit of the internals of this command can help you make even better use of it.
When you run CPYTOIMPF, the underlying program creates a dynamic SQL statement. That is, it builds a SELECT statement in a character string variable, uses the PREPARE statement to convert the string into an executable statement, and executes the statement. Since the statement is prepared in a string variable, you can add clauses that normally follow ORDER BY in SQL queries to the end of the ORDERBY parameter.
These are the clauses that SQL syntax permits to follow an ORDER BY expression.
FETCH FIRST n ROWS OPTIMIZE FOR n ROWS FOR UPDATE FOR READ ONLY WITH <isolation-level> SKIP LOCKED DATA USE CURRENTLY COMMITTED WAIT FOR OUTCOME
Most of these options are not applicable in the context of CPYTOIMPF. For example, FOR UPDATE is useless because CPYTOIMPF doesn’t change the retrieved data. The only clauses that I know of that will benefit you are FETCH FIRST n ROWS and SKIP LOCKED DATA. You can use FETCH FIRST to limit the number of rows to be copied. SKIP LOCKED DATA ignores data that is locked by other jobs.
Here’s a CPYTOIMPF command to serve as an example. As you should have physical file QIWS/QCUSTCDT on your system, you can experiment as you desire. This command copies the data in the physical file to stream file custcdt-1.csv in the current directory.
CPYTOIMPF FROMFILE(QIWS/QCUSTCDT)
TOSTMF('custcdt-1.csv')
MBROPT(*REPLACE)
STMFCCSID(*PCASCII)
RCDDLM(*CRLF)
RMVBLANK(*TRAILING)
ORDERBY('state, city')
ADDCOLNAM(*SYS)
Notice the ORDERBY parameter. When the system builds the SQL SELECT, it places the value state, city immediately after ORDER BY. Here are more examples.
ORDERBY(*arrival)
Copy the data in arrival sequence (i.e. by relative record number).
ORDERBY('baldue desc')
Copy in descending order by balance due.
ORDERBY('baldue desc fetch first 5 rows only')
Copy in descending order by balance due, retrieving only the first five rows (records).
ORDERBY(6,5)
Sort the data by sixth column, and within that by the fifth column. This is equivalent to the first example, as the fifth and sixth columns of QCUSTCDT are city and state.
What a wonderful utility!
Nice tip. I appreciate the info.
Had to chuckle when I read this Ted – your “wonderful utility” is my bête noire! I hate it with a passion. It is the reason why the very first Open Access handler I ever wrote was to produce CSVs directly – so I could avoid it!
As cool as the ORDERBY parameter is, this change has a nasty catch. It might seem obvious by the provision of an *ARRIVAL option for the new parameter that *NONE is not necessarily going to give you arrival sequence. But because *NONE is the default, any old code you have using CPYTOIMPF may be affected by this. We had a tool that worked fine for over 9 years suddenly start doing weird stuff after we upgraded to 7.2 recently. It seems SOMETIMES when you specify *NONE (or are just using old code that gets that as a default) it will essentially preserve arrival sequence, then split the file in half and swap the halves!!