Grep and Database Files

Hey, Ted:

I've read all your articles on Qshell and love them. I've been trying to grep files in the QSYS.LIB file system, and can do so successfully if the files are program-described physical files or source physical files with a single field. However, I have a physical file with multiple members that I'd like to search through.

Trying to grep over the members in this externally described physical file generated some errors. I talked with IBM Rochester regarding the issue and didn't get the resolution I was hoping for. I would like to believe it can be done, and have seen advertisements by a company called JGsoft that claims its software can do so. Do you have any ideas or direction on this?

--Todd

I have always understood that most Qshell utilities, including grep, cannot handle externally described database files. IBM provides the db2 utility, which I covered in "Access the Database from Qshell," but it's not going to allow you to search all members of an externally described database file. I forwarded your e-mail to Fred Kulack of IBM for more ideas. His answer (after editing, of course) follows.

--Ted

There's no single command that will perform the behavior you want, and Ted was right: most of those utilities can't handle externally described files. The reason is that most Qshell utilities are at their heart Unix utilities that deal with streams of data using Integrated File System (IFS) interfaces. The system simply doesn't support opening externally described database files with those interfaces.

The utility that will access those files needs to use SQL or AS/400 record I/O interfaces to open/read/write the file. As Ted said, the db2 utility would be a reasonable option, because it uses SQL. You could pipe the output to process it as you want. You'd need to use the SQL ALIAS construct to build an alias for any member other than the first one.

Another alternative for V5R2 is the Rfile utility, which writes the data from a record oriented file in binary form, with added newline characters between records. The Rfile utility allows you to access a member directly.

To help illustrate how you might use these tools with externally described database files, let me first create and populate an example file. (In the following examples, what I type is in boldface type. The computer's response is italicized.

CREATE TABLE KULACK/TAB2
      (COL1 INT,
      COL2 CHAR ( 10),
      COL3 NUMERIC )
insert into kulack/tab2 values(1, 'Testing', 5.5)
insert into kulack/tab2 values(2, 'Record', 100.001)
insert into kulack/tab2 values(3, 'Based', 2000.0002)
insert into kulack/tab2 values(4, 'Qshell', 9999.9999)

Notice that the cat utility (a simple representative of most Qshell utilities) doesn't like an externally described database file.

cat /qsys.lib/kulack.lib/tab2.file/tab2.mbr
  cat: 001-0023 Error found opening file
/qsys.lib/kulack.lib/tab2.file/tab2.mbr. Operation not supported.

However, the db2 utility returns the data properly.

db2 "select * from kulack.tab2"
  COL1        COL2       COL3
  ----------- ---------- -------
            1 Testing         5
            2 Record        100
            3 Based        2000
            4 Qshell       9999

    4 RECORD(S) SELECTED.

Use the stream editor, sed, to delete the header lines, the footer lines, and any blank lines. The subsequent output now matches the fields in the table, plus the white space between them.

db2 "select * from kulack.tab2" | 
sed -e '1,3d' -e '/RECORD.S. SELECTED\./d' -e '/^$/d'
            1 Testing         5
            2 Record        100
            3 Based        2000
            4 Qshell       9999

Now use grep with the -n option, and you'll see that record 4 is the only match for your search.

db2 "select * from tholts.tab2" | 
sed -e '1,3d' -e '/RECORD.S. SELECTED\./d' -e '/^$/d' |
grep -ni Qshell
4:          4 Qshell       9999

The Rfile utility handles members directly, and doesn't print out any extraneous header and trailer data. It does, however, have some minor weaknesses, primarily that the binary data that it outputs confuses many of the Qshell text-based utilities and might make parsing the output a bit more difficult.

Here's the raw output of Rfile.

rfile -r /qsys.lib/kulack.lib/tab2.file/tab2.mbr
      Testing   00005
      Record    00100
      Based     02000
      Qshell    09999

What you don't see in the previous output is the binary data in field COL1. If you wanted to see the binary data, you could pipe the previous command into the od utility.

To overcome the binary data limitations, redirect the output of Rfile to a stream file and process it from there or use the translation utility (tr) to remove non-alphanumeric characters from the output.

rfile -r /qsys.lib/kulack.lib/tab2.file/tab2.mbr | 
tr -cd '[:alnum:]\n' | 
grep -ni qshell
  4:Qshell09999

In this example, tr deletes all the binary characters (everything except for alphanumeric characters and newline). It ends up deleting data for column COL1, which is an integer. Thus, you can't search on it, but note that it still shows record 4 is the one that matches the grep.

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL