Use a combination of CPYTOIMPF and the sort command to export sorted, fixed-format files.

No matter how many ways I find to do something, I always run into a situation where I need to get creative. In various articles, I used SQL to export a physical file to a text file on the IFS, which gave me a lot of control over how I could sort the data that is being exported, but it didn't easily give me one thing that I needed, and that was to have the results in fixed format. Qshell to the rescue.

For my situation, I needed to create a text file in which sequence numbers would be in order. When the file was being created, the key over the file did not have the sequence number as the primary key, and the records were written out of order, so the relative record numbers were out of sequence as well.

I could have copied the data into a temporary file that would put them in the correct order and then followed up with a CPYTOIMPF to export the data to the IFS. But I needed to do this on multiple files and didn't want the mess of all the extra files, and I wanted to provide this capability for future use as well.

I know that UNIX and Linux have useful utilities for processing files and the sort command was just what I was looking for. Qshell gives us access to these commands, so I had all the tools I needed for the job.

As with many UNIX commands, there are many options that you can use with the command. With the sort command, you can sort in forward or reverse; for characters, you can ignore case; for numbers, you can sort numerically; to handle spaces, you can ignore the white space. You can mix and match commands in many different and creative ways, and a lot of information is available on the Web. I will focus on our task at hand, which is to sort a fixed-formatted file using data that is contained within specific positions of each record.

The –k option allows us to specify the key to use that will determine how the file will be sorted. The key is identified using the positions of the characters within the record. The –k option is followed by the starting and ending positions of the data.

DDS for EMPEXPORT Example File

For our example, we'll use the following DDS:

     A          R MCFMT                                                

     A            MCACCT         6S 0       COLHDG('Account Number')  

     A            MCMGR          6S 0       COLHDG('Manager Code')    

     A            MCFNAME       15A         COLHDG('First Name')      

     A            MCLNAME       15A         COLHDG('Last Name')       

     A            MCSEQ          6A         COLHDG('Seq No.')         

     A          K MCACCT                                              

     A          K MCMGR                                                

Sort Key Notation

The key option allows you to specify the starting and ending positions of a field within the data. By default, the entire record is considered one field. So we will need to indicate field 1 with the starting and ending positions. Another option would be to use delimiters to indicate the fields within the record; then we could sort on the field, but we are using fixed-format for this example and will not be using delimiters.

Here is the notation for our particular example of using the key on the field in the fixed file.

sort -k field_number[.first_character][, field_number[.last_character]]

Upon review of our DDS, we can calculate (Or use DSPFFD) that the MCSEQ field is located at starting position 43 and ending position 48.

Note: Determining 43 and 48 as the positions in the resulting text file will work for this example because our fields are determined as alpha. But this may not be the case for other data types because you may get additional characters for additional attributes, such as the sign of a number. To gain the true positions of the file, it is sometimes easiest to just look at the resulting text file.

By using 43 through 48 as the position of our key sort, our resulting sort command will look like this:

sort –k 1.43,1.48 empExportUnsorted.txt

Data Specifications

To use a semi-useful and simple example, we'll use the following specifications for our file:

Header

The Header record will be first, before the detail records, and will contain the manager code, along with the manager's name. The sequence number will ensure it is positioned correctly in front of the rest. A header record will be generated as the first record and at the change of manager groups.

Detail

The Detail record will contain detailed information on the employee, including the account number, manager code, and name. The employees will be grouped together by the manager code and ordered between the header and the footer by the sequence number.

Footer

The footer will be generated at the close of each manager grouping. It will contain all nines in the account number; will contain the number of records in the group, including the header and footer record; and will use the sequence number to ensure it is at the end of the manager group.

Blah! Specs! They're a necessary evil to give purpose to what we intend to create at the end of our example. The main point is that the last field, the sequence number, needs to be in order before going off to its destination.

CPYTOIMPF

To create our fixed file, we will use the CPYTOIMPF command to export our physical file data to a text file on the IFS. Here is the command that we will use to do this:

CPYTOIMPF FROMFILE(AIRLIB/EMPEXPORT) TOSTMF('/home/TSnyder/empExportUnsorted.txt')

MBROPT(*REPLACE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) DTAFMT(*FIXED) 

CPYTOIMPF Output

Now let's look at our output. You can view the file in Qshell using the cat command as follows:

1. Enter Qshell using the STRQSH command on the green-screen command line.
2. Navigate to the directory where you exported the file using the cd command.
3. View the file using the cat command followed by the file name (cat empExportUnsorted.txt).

Figure 1: Here's the result of using Qshell. (Click image to enlarge.)

When I created the file, I entered everything in order of the primary key. Then I deleted the first record and reentered it to force it to be the last relative record number (RRN) in the file. When I executed the CPYTOIMPF command, it ordered the export file by the RRN.

Notes: 

• The primary key of the physical file has the first field starting in the first position and is six characters long; the second key follows immediately afterward and is also six characters. If you enter in order or primary key, then delete and re-add the record that is first in terms of the primary key (Account Code: 000000, Manager Code: 000001), the CPYTOIMPF exports in RRN order.
• The sequence number is the last field in the data to the far right. The desired output will have these in sequential order.
• If I were to create a copy of the file and use CPYF to copy to a temporary file, the RRN sequence would be rebuilt to match the primary key.

Desired Output

To get our data in the desired output, we want the data to be ordered by the sequence number. This is what the data should look like when we are done.

Figure 2: This is what we want our output to look like.

The Sort

At this point, we have our data and we know what it should look like. So let's use the sort command to make it that way. We'll enter Qshell and navigate to the location where we exported the file. We will be sorting the file on the sequence number, which is the located in positions 43 through 48. Using the sort command and parameters determined earlier, let's run the command to see the actual output:

sort -k 1.43,1.48 empExportUnsorted.txt

Figure 3: We're getting the output we want!

Success! We're almost there. But our data is currently being displayed only on the screen. Notice that no file was ever created. That's because we need to redirect our output to a file.

Output to a File (Using STDOUT Redirect)

As with other UNIX commands, we can using the output redirection "greater than" symbol (>) to redirect the standard output (STDOUT):

sort -k 1.43,1.48 empExportUnsorted.txt > empExportSorted.txt

Got ASCII?

It all looks good so far, but if we were to download the files, we would see that we still don't have what we're looking for because it is in EBCDIC. When we ran the CPYTOIMPF command, we specified *PCASCII, and if we downloaded that file, we would see that it is in ASCII. But when we manipulate the file in Qshell, it is converted to EBCDIC because that's what Qshell uses.

So, if we use Qshell to do the sort, then we will need to incorporate the iconv command to specify the desired CCSID of the resulting data.

sort -k 1.43,1.48 empExportUnsorted.txt|iconv –f 37 –t 819 > empExportSorted.txt

Using These Capabilities from RPG

In December, I wrote an article that discussed the difference between *NOPASS and *OMIT. It contained a simple procedure to use the CPYTOIMPF command to export files. We can reuse that procedure here and also build a new procedure to perform the sorting. With these two procedures, we can export database files to the IFS as flat files and sort them, right from within an RPG program.

          P sortFile...

     P                 B                   EXPORT

     D sortFile...

     D                 PI             1N

     D   argFromFile                512A   const

     D   argBegPosi                  10I 0 const

     D   argEndPosi                  10I 0 const

     D   argToFile                  512A   const options(*NOPASS:*OMIT)

     D* Local Variables

     D   svReturn      S              1N

     D   svToFile      S            512A

     D   svCmdString   S            512A

      /free

        svReturn = *OFF;

        //---------------------------------------------------------

        // Initialize the Defaults.

        //---------------------------------------------------------

        svToFile = %trim(argFromFile) + '.out';

        if %parms > 3;

          if %addr(argToFile) <> *NULL;

            svToFile = %trim(argToFile);

          endif;

        endif;

         //-------------------------------------------------------------

         // Qshell sort

         //-------------------------------------------------------------

         // Sort the IFS Fixed Format File

         svCmdString = 'STRQSH CMD(''sort -k'

                     + ' 1.' + %char(argBegPosi)

                     + ',1.' + %char(argEndPosi) + ' '

                     + %trim(argFromFile)

                     + '|iconv -f 37 -t 819 > '

                     + %trim(svToFile) + ''')';

         monitor;

           ExecuteCommand(%trim(svCmdString):%len(%trim(svCmdString)));

         on-error;

           // Exception

           svReturn = *ON;

         endmon;

        return svReturn;

      /end-free

     P                 E

In this procedure, we are just using the STRQSH command to execute the sort command in Qshell. To make it flexible enough to be a procedure, we will pass the file name to be processed, along with the starting and ending points for the key to be sorted in the file. If the output file name is not specified, it will default to concatenate the .out extension onto the original file name.

Now we can just use our procedures for any files we want to export and sort.

      /free

       if (exportFile('EMPEXPORT':*OMIT:*OMIT:

                      '/Public/empExport.txt'));

         dsply 'Error on Export!';

       endif;

       if (sortFile('/Public/empExport.txt':43:49

                   :'/Public/empExportSorted.txt'));

         dsply 'Error on Sort!';

       endif;

       *inlr = *ON;

      /end-free           

By taking the time to put our code into procedures, we now have a quick and easy way to handle exports within RPG code. And we can use it over and over again.

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1