Export physical files directly to the IFS with headers.

In my previous articles about SQL on the IBM i, I have used common commands that are available directly from the command line. In this article, I will use the db2 command in Qshell to export files directly to the IFS. And when I create the file, I will also satisfy a common need to include header information with the data.

Running a Query Using DB2 from Qshell

We will first enter Qshell using the STRQSH command. Once in Qshell, we can access the db2 command. We can use the db2 utility in Qshell to execute SQL statements by specifying the query to run in quotes after the db2 command.

We will use the MC_PHONE file for our example, which has the following DDS:

A          R MCFMT

A            MCACCT         6S 0       COLHDG('ACCOUNT NUMBER')

A            MCAREA         3A         COLHDG('AREA CODE')

A            MCPHONE        7A         COLHDG('PHONE NBR')

A            MCDATE         8S 0       COLHDG('CHANGE DATE')

To perform a Select query over all of the data in the table, we will execute the following command on the Qshell command line:

 db2 -v 'select * from mylib.mc_phone'

This action displays the following results:

Figure 1: We have selected the data from mylib.mc_phone.

The -v option indicates that the output should be verbose and will echo the results to the standard output.

Using Redirection to Export Data Directly to an IFS File

So far, our output is similar to what we've seen with the interactive SQL command using the STRSQL command. But, because we are in Qshell, we can combine the capabilities of one command with another. To get our output into a text file on the IFS, we will use redirection.

Redirection means that we will override our standard output to go to a different location than the default. This can be accomplished using the output redirection "greater than" symbol (>).

So, to export the data from our previous select query, we will redirect our output to a file named sqlOut.txt on the IFS using the redirection symbol as follows:

db2 -v 'select * from mylib.mc_phone' > sqlOut.txt

When you execute the command, you will not see the results of the query displayed on the screen because they went into the file instead. You can list and view the files in your home directory in multiple ways. Here are two ways using of doing that, in and out of Qshell:

In Qshell:

• List the files using the ls command.View the files using the cat command:
• cat sqlOut.txt.

Out of Qshell:

• Execute the WRKLNK command.
• Place a 5 in front of the file.

The problem here is that there is too much information in the output. It even contains the SQL statement that was executed and the words executing and done! We'll remove the -v option so that this information is not exported.

db2 'select * from mylib.mc_phone' > sqlOut.txt

Removing Lines from the Output Using sed

Removing the verbose option command removed some of the undesirable information but not all of it. You may want the header information in the file, but you most likely will not want the second line to be contained in the data that you are exporting. DB2 does have a -x option, but that would remove all of the header and footer information, and it isn't available for Qshell anyway. So we'll have to do a little extra work to get the information the way we want it.

This is where we'll take advantage of another utility available to Qshell called sed (stream editor). sed is a powerful UNIX text-processing utility that has many capabilities. We will use this utility to target specific lines in our output to be deleted.

I have downloaded the text file output into a text editor that displays the line numbers to show the actual results that were generated (Figure 2).

Figure 2: The text file output generated these results.

As you can see here, the header separator line that we will be removing is on line 3, not line 2. There is a blank line for line 1. You can also see that the footer is on line 9 and has two blank lines following it.

To delete the third line from a file, we will use the sed command. The first parameter of the command will consist of the number 3 to indicate the line number, followed by the lowercase letter d, which will indicate the delete operation. The file name will be passed as the second parameter of the sed command.

So, to delete the third line from our sqlOut.txt file, we would execute the following command:

sed '3d' sqlOut.txt

This will display the results to the screen. But, if you were to display the file using the cat command, you would see that the file is unchanged. This is because the standard output of the sed command was not changed, which is why it was displayed on the screen. To actually change the file, we will redirect the output to the new file sqlOut2.txt.

sed '3d' sqlOut.txt > sqlOut2.txt

Now, when we display the sqlOut2.txt file, we will see that the third line is removed.

Removing  the Footer Information

Almost there! Just need to remove that footer information. This is where the fun part comes in. The header was easy because we know the line number, but we do not know how many records will be contained in the results, so we need to figure out how to delete the last three lines.

Sed uses the  dollar sign ($) to indicate the last line. It would have been easy if we needed to remove only the last line; we'd just use this:

sed '$d' sqlOut2.txt

But we need to remove the last three lines, and there isn't an easy way of doing that with sed. Using tail is easier.

Using the Tail Utility

Tail is a command that will output the last part of a file. The tail command has the option to start at a particular line from the beginning, and it also has the ability to output the data in reverse order using the -r option.

If we use tail with the -r option on our file, we'll see the file displayed in reverse order:

Figure 3: The -r option allows us to display the file in reverse order 
 

If we were to use tail with the +4 option, we would see the file displayed with everything except the first four records in the original sequential order:

Figure 4: The +4 option displays the file with everything except the first four records in the original sequential order.

Individually, these capabilities won't give us what we're looking for, so we'll have to combine them using pipes.

Passing the Standard Output Using Pipes

With the pipe symbol (|), the output of one utility can be passed as the input of another utility. So, if we were to first reverse the order of the output using tail –r and then use the pipe to pass that output to another tail +4, we would have the last four lines removed because the data is upside down. Then all we would need to do is reverse the data again to put it back into its original order with the last four lines removed.

Here are the steps that will be piped together to remove the last four lines:

1. Reverse the order of the sqlOut2.txt file.
2. Force the output to start four lines from the beginning, which is actually the end because it is in reverse order.
3. Reverse the order back to its original order; minus the last four lines.

Here is the command to do that:

tail -r sqlOut2.txt|tail +4|tail -r > sqlOut3.txt

Putting It All Together

This may look like a lot, but it's only because I have broken down each step into smaller pieces.

Now that we have all of the parts that we will need, we can combine them to generate our desired output using pipes. We could put these into a script and execute them sequentially, or we can combine them, which is what we will be doing here.

db2 'select * from mylib.mc_phone'|sed '3d'|tail -r|tail +4|tail -r > sqlOutFinal.txt

Now that's a mouthful! We have SQL statements and Qshell/UNIX utilities exporting, removing lines, flipping data, reorganizing, and redirecting to produce the final output.

More Information

You can find more information on the Qshell db2 utility on the IBM Web site by clicking here. As for the Qshell commands, the UNIX command documentation on the Web is bountiful.

Happy St. Patrick's Day!