SQL within Qshell

Qshell is a command interface for the IBM i's POSIX environment. I have found it most useful for its ease of use when using the IFS, Integrate File System, and interfacing data between the traditional IBM i environment and the IFS.

The Qshell command I use the most is db2, I have Paul Wren to thank for introducing me to it. The db2 Qshell command uses the SQL CLI, Call Level Interface, and allows me to run SQL commands on files in the IBM i and IFS environments. I am not suggesting that this will replace the STRSQL, RUNSQL, and RUNSQLSMT commands, Query Management queries (*QMQRY), and SQL embedded in RPG. Think of learning how to use db2 in Qshell as another asset to add to your IBM i knowledge and skill set.

I tend to use the Qshell interactively or in CL programs. You can start the Qshell environment with one of the following commands, that both do the same thing.

• STRQSH
• QSH

I am going to use examples using the STRQSH in a CL program. Let me start with a simple example that list all of the values in the column FRUIT in my test file. Notice that as I am using the SQL naming convention that the separator between the library name and the file name is not a slash, "/", but a dot/period, ".".

01 PGM 02 STRQSH CMD('db2 select fruit from mylib.testfile') 03 ENDPGM

In my experience I have found that the db2 does not support SELECT *, for example:

STRQSH CMD('db2 select * from mylib.testfile')

This produces the following error:

**** CLI ERROR ***** SQLSTATE: 42601 NATIVE ERROR CODE: -104 Token 1150201 was not valid. Valid tokens: + - AS .

The only ways I have found around this is to either list all the columns or to contain the SQL statement in a source member.

If I am going to use the SQL statements contained within a source member I need to use a parameter -f to indicate the SQL statements are in a source member. I also have to use the long qualified name format (NAMEFMT 1) to identify the member in a multiple member file, such as a source file. In the example below the SQL statements are contained in the member SQL_STMT2, in the source file DEVSRC, in the library MYLIB, which, like all other libraries, is in library QSYS.

STRQSH CMD('db2 -f /qsys.lib/mylib.lib/devsrc.file/sql_stmt2.mbr')

The SQL statement in the source member, below, is basically the same as the example I gave above. This time I can use the SELECT * without an error.

SELECT * FROM MYLIB.TESTFILE

Qshell also offers the ability to use multiple Qshell commands in one statement. Each command needs to be separated by a semicolon, ";". I work in a multi lingual environment so at times I have CCSID issues with certain fields. If I want to ensure which CCSID is used by the SQL statement I can use the following two commands:

STRQSH CMD('QIBM_CCSID=37;+ db2 -t -f /qsys.lib/mylib.lib/devsrc.file/src_stmt1.mbr;')

QIBM_CCSSID=37 changes the CCSID used.

In the db2 command I have used an additional command parameter -t. This is used to indicate that a semicolon marks the end of the db2 statement.

I mainly use the db2 Qshell command to create files in the IFS that I can then email using the SNDSMTPEMM command. Below is a source member containing the SQL statement to make a CSV file:

SELECT ORDNO || ',' || CHAR(CAST(ORDQTY AS INT)) || ',' || CHAR(DATE(TIMESTAMP_FORMAT(CHAR(DUEDTE+19000000),'YYYYMMDD')),ISO) || ',' || ITEM FROM MYLIB.ORDHDR WHERE DUEDTE BETWEEN 1150201 AND 1150207 ;

In the SQL statement I am converting all of the numeric fields to character using SQL's CHAR function. The dates in this file are numeric fields that contain the date in *CYMD format (CYYMMDD). The fields are interspersed with commas to act as the column separators. The double pipes, "||" are used to concatenate everything together.

Typically I would use three Qshell statements:

STRQSH CMD('rm /myfolder/test.csv;+ touch -C 819 /myfolder/test.csv;+ db2 -t -f + /qsys.lib/mylib.lib/devsrc.file/src_stmt1.mbr + > /myfolder/test.csv;')

I am going to describe the three commands in reverse order as, I think, it makes it easy to understand why I have used them.

db2 -t -f /qsys.lib/mylib.lib/devsrc.file/src_stmt1.mbr > /myfolder/test.csv; - I have explained what the -t and -f parameters are used for. The next part of the statement indicates in which source member the SQL statement is found. The greater than symbol, ">", indicates that the output from the SQL statement is to be directed to an output file. In this case the output file is in the IFS folder "myfolder" and is called "test.csv".

If the db2 command is the only one used then the output file would be in EBCDIC character format. The only way to ensure that the output file will be in ASCII format is to create the output file in that format.

touch -C 819 /myfolder/test.csv; - Creates the output file to use ASCII code page 819 (ISO 8859-1 or Latin-1) which is compatible with US English.

rm /myfolder/test.csv; - Before any of the other commands are performed it is useful to delete the output file if it already exists.

This article should have given you an idea of how to use Qshell and its db2 command. There is a whole lot more you can do with Qshell, but that will have to be the subject of later posts.

`

---

Update - April 8, 2015

In the comments below Steve Crowley offered a solution for the problem of not being able to do a SELECT * in Qshell.

His solution is to qualify the file name, and then the * too. In this example I have qualified the file with the letter A.

STRQSH CMD('db2 select a.* from mylib.testfile a')

---

You can learn more about these on the IBM website:

• Qshell db2
• IBM DB2 for i: Tips inc. Qshell db2
• Qshell utilities
• Qshell touch
• Qshell rm

 

This article was written for IBM i 7.2, and it should work with earlier releases.