Grep and Database Files
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?
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.
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.
Contact the Editors
|Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.|