I have been reading a lot about QSHELL since it was introduced for the iSeries. After learning about this shell I started promoting this piece of technology to my colleagues. Time and time again I was asked, "What is the usage of this technology when most of the functionalities can be done with CL or RPG?" Due to the lack of shell commands at that time, I restricted my answer to, "It helps in compiling Java programs." At that time,...
Java was an unusual language for iSeries users.
I have since discovered that QSHELL has become much more. It is now the equivalent of any other UNIX shell. However, now I'm asked, "Why should we adopt UNIX, when we have such a good operating system as OS/400 and the advance scripting language CLLE?" The answer is that QSHELL helps CL rather than competes with it. Here are few ways that you can use QSHELL in your day-to-day life of programming.
Usage for database operation
CL has a major restriction of using Data Manipulation Language (DML) such as Update, insert or selected delete with the DB2/400 database. To achieve this, we use roundabout ways such as RUNSQLSTM, writing new RPG program, etc.
Qshell makes life easier by using the following command:
Syntax: QSH CMD('db2 "<Db2/400 sql statement">)
QSH CMD('db2 "UPDATE MYLIB.MYFILE SET MYFLD = ''P''" ')
QSH CMD('db2 "INSERT INTO MYLIB.MYTABLE VALUES(''jagannath'', 104.20)"')
Things to note:
- The query naming convention is SQL not SYS. The table/file is qualified with Library.Table not Library/Table.
- The QSH command cannot access the objects, which are created in QTEMP using command line or CL programs. That's because shell commands are executed in a separate job other than the parent jobs. These jobs are triggered automatically when QSH command is executed.
If there is a file as QTEMP/MYFILE and I want to update it using this QSH command
QSH CMD('db2 "UPDATE QTEMP.MYFILE SET MYFLD = ''P''" ')It will generate an error message such as this one:
NATIVE ERROR CODE: -204 MYFILE in QTEMP type *FILE not found. Press ENTER to end terminal session.
Usage for IFS operation
It is always a headache to handle IFS files using RPG programs. We have to write separate RPG programs using C APIs to handle this functionality. But QShell commands ease this process.
To replace the contents of the IFS and write the fresh text
QSH CMD('print It is working > /home/mydir/myfile.txt')
To append to the contents of the IFS file in next line
QSH CMD('print It is working >> /home/mydir/myfile.txt')
To use other features of print, go through the QSHELL manual of IBM.
I found this functionality helped me while creating CSV files out of database files using CPYTOIMPF. With this command we create the comma separated files out of the database file. To add the report headings, field descriptions, etc., the print command helps a lot.
QSH CMD('print ", , ,Report for Health Care," > /home/mydir/myexport.csv') QSH CMD('print "Patient,Date of Adm,Doctor Attended, Remarks" >> /home/mydir/myexport.csv') CPYTOIMPF FROMFILE(MYLIB/HLTHCAR) TOSTMF('/home/jlenka/myexport.csv') MBROPT(*ADD) RCDDLM(*CRLF)
The output of the QSHELL commands is normally displayed in the standard output. (In UNIX terms, standard output (screen) is also called as a kind of file.) However, we can redirect this output to an IFS file. Since regular programmers are more acquainted with DB2/400 files, let's see how we can redirect the output to the DB2/400 files.
Let's first look at the mapping of the naming conventions between OS/400 and IFS.
|Sl No||OS/400 Naming Convention||IFS Naming Convention|
In QSHELL 'ls' is used similarly to WRKOBJ *ALL in OS/400. If you want to redirect the output result of ls to a DB2/400 file, do the following:
- CRTPF FILE(MYLIB/MYFILE) RCDLEN(300)
- QSH cmd('ls /qsys.lib/mylib.lib/* > /qsys.lib/myfile.lib/myfile.file/myfile.mbr')
If I want find the name of the file that ends with "PGL" in MYLIB library, what should I do? WRKOBJ command does not support WRKOBJ *PGL. We can use one of the versatile file searching utilities called find.
- CRTPF FILE(MYLIB/OUTPUT) RCDLEN(300)
- QSH CMD(find /qsys.lib/MYLIB.LIB -name '*PGL.FILE > /qsys.lib/mylib.lib/output.file/output.mbr')
Things to note:
- The searching pattern (e.g.,*PGL.FILE) must be in capital letters
- You can do lots of wild card searches
- Starting with A ending with X , find /qsys.lib/MYLIB.LIB -name 'A*B.FILE'
- Starting with A and in between CD, find /qsys.lib/MYLIB.LIB -name 'A*CD*.FILE'
- Search in library that starts with JA and JL,
find /qsys.lib/JL*.LIB /qsys.lib/JA*.LIB -name 'XML*.FILE'
find /qsys.lib/JL*.LIB /qsys.lib/JA*.LIB -user 'JLENKA'
find /qsys.lib/JL*.LIB /qsys.lib/JA*.LIB -user 'JLENKA' –name '*.PGM'
i.e. qsh cmd('find /qsys.lib/JL* -user ''JLENKA'' -name ''*.PGM''')
Counting the results
Want to count how many programs are in one library? You could do a WRKOBJ and then transfer to a file and use query. But it can be done using wc:
Qsh CMD('find /qsys.lib/MYLIB.LIB -name ''*'' | wc -l')
The only way to find strings or a pattern of strings in the members of the source physical file is with the FNDSTRPDM command. This command has lots of restrictions, such as it does not let you search more than one pattern (string) at one time. One of the best searching utilities in the industry is grep, which is used in UNIX shells. Let's see how we can use it to replace FNDSTRPDM.
- 1. To search a string "it is ok" in MYLIB/QRPGLESRC and redirect to a file with the text lines containing this string:
QSH CMD('grep ''it is ok'' /qsys.lib/jlenka.lib/qrpglesrc.file/* >/qsys.lib/jlenka.lib/myfile.file/myfile.mbr')
- To search a string "it is ok" in MYLIB/QRPGLESRC and redirect to a file and only show the file names no text lines:
QSH CMD('grep –l ''it is ok'' /qsys.lib/jlenka.lib/qrpglesrc.file/* >/qsys.lib/jlenka.lib/myfile.file/myfile.mbr')
- To search a string "it is ok" or 'it is correct" in MYLIB/QRPGLESRC and redirect to a file:
QSH CMD('egrep –l ''it is ok|it is correct'' /qsys.lib/jlenka.lib/qrpglesrc.file/* >/qsys.lib/jlenka.lib/myfile.file/myfile.mbr')
- . To search a string "it is ok" and "it is correct" in MYLIB/QRPGLESRC in one line and redirect to a file:
qsh cmd('grep ''IFO'' /qsys.lib/mylib.lib/qrpglesrc.file/* |
grep ''and'' >
More you can do
There are many other useful commands that can help you program lots of complicated stuff in CL using QSH. Try going through Rfile to read or write to the OS/400 files or jar to zip the IFS files to transmit them by mail. Explore the other QSH commands in the iSeries Infocenter.
About the author: Jagannath Prasad Lenka is a programmer/analyst at Infosys Technologies Ltd. He has worked as programmer and senior programmer on the iSeries for this company for the past five years.