20
Sat, Jul
2 New Articles

Sort Fixed-Formatted Text Files on the IFS Using Qshell

RPG
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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:

 

Field

Header

Detail

Footer

MCACCT

0

Employee Account Number

999999

MCMGR

Manager Code

Manager Code

Record Count

MCFNAME

Manager First Name

Employee First Name

*BLANKS

MCLNAME

Manager Last Name

Employee Last Name

*BLANKS

MCSEQ

Sequence Number

Sequence Number

Sequence Number

 

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).

 

051811Snyder_figure01

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.

 

051811Snyder__figure02

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

 

051811Snyder__figure03

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

Thomas Snyder

Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.

Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.

Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.

Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.


MC Press books written by Thomas Snyder available now on the MC Press Bookstore.

Advanced, Integrated RPG Advanced, Integrated RPG
See how to take advantage of the latest technologies from within existing RPG applications.
List Price $79.95

Now On Sale

Extract, Transform, and Load with SQL Server Integration Services Extract, Transform, and Load with SQL Server Integration Services
Learn how to implement Microsoft’s SQL Server Integration Services for business applications.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: