Wednesday, April 1, 2015

SQL within Qshell

qshell db2 sql

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:

 

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

16 comments:

  1. Great info as I am sure ifs will become more important as we modernize and interface with other systems

    ReplyDelete
  2. Nice to know its there... But are there any advantages over runsql or cpytoimpf/stmf?

    The only one i can think of is being able to embed into unix shell scripts.

    ReplyDelete
    Replies
    1. You can read files in the IFS using SQL in Qshell. You cannot do that with the commands you mention.

      Delete
    2. That's interesting, do you mean as an alternative to grep/cat/sed or can you read structured (column based) data? I was under the impression that the ifs did not hold structured files.

      Delete
  3. Also if you use PASE (call qp2term/shell) are the files created in ASCII?

    ReplyDelete
  4. Great post.

    Would QSH run with adopted authorities?

    ReplyDelete
  5. Modify the second panel by adding an alias ie.
    STRQSH CMD('db2 select t.* from mylib.testfile t')
    and Select * will work.

    ReplyDelete
  6. Or just escape the asterisk.

    > SELECT \* from mylib.testfile

    The shell is treating the * as a file glob pattern. So "SELECT * FROM ..." turns into "SELECT (space separated list of files in current dir) FROM ..."

    ReplyDelete
  7. Csv file which is created is in unreadable format.

    ReplyDelete
  8. how to Field heading into the CSV document?
    STRQSH CMD('db2 select t.* from mylib.testfile t')

    ReplyDelete
    Replies
    1. You can use QSHell command ECHO to write directly to the csv file, then run the db2 command to fill the rest of the file. This is some CL code writing the headings via QSH.

      chgvar &qshcmd ('echo "Colheading1",+
      "colhdg2,+
      ' +
      *BCAT ' >' *tcat &STMF)
      QSH CMD(&QSHCMD)

      Delete
  9. Hi Keith,
    Thanks for advice, creating Heading is working now, but the but running DB2 to add data not working properly. Can you send me example please or correct my statement. Thanks Tyrone
    "QSH CMD('db2 select t.* from mylib.myfile t | sed -n /,
    /p >> /myfolder/myfile.CSV')

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.