An Improved DB2 Command for QShell

By:

I really like the db2 command available in QShell. It's a simple shell command that runs an SQL statement it receives as a parameter. It will even return result sets from SQL SELECT or CALL statements. Not only is this tool useful from QShell, but it's also useful in CL programs, because CL doesn't have its own embedded SQL like other ILE languages do.

Unfortunately, the db2 command from QShell is missing some details--small things that seriously reduce its value as a programming tool. If it fails, it doesn't report the failure via its exit status like other QShell utilities do, and that makes error handling very difficult. It also doesn't support the system naming convention that's widely used in native applications, which means you can't use the library list. It always outputs column heading information whether you want it or not. These limitations drove me crazy, so I wrote my own utility as a replacement. My utility is intended to work exactly like the QShell tool, except that it solves these problems and adds a few new features.

Problems to Be Solved

Here are the things that drive me nuts about the IBM db2 QShell utility:

Whether it succeeds or fails, it always returns an exit status of 0. This is counter to every other QShell tool IBM provides. All other tools return an exit status of 0 to indicate success and a non-zero value to indicate failure.
When an error is detected, it prints the error message to standard output. Every other QShell tool prints errors to standard error.
It doesn't support the system naming convention. Since the system convention is required to use the library list, this is a big issue for me.
It's tricky to strip the column headings, record count, and other stuff from the output.

My version, which I named "sql," solves those problems. It's a regular ILE program, written in ILE C (I prefer to use RPG when I can, but this was a situation in which it was easier to use C.) It solves all the preceding problems, because it

sets the exit status to 0 only if all statements were successful.
prints error messages to standard error.
supports system naming.
provides a simple command switch to remove the column headings and other text.

This means that you can now do error handling like this:

   ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y) REPLACE(*YES)
   STRQSH CMD('sql "select prod, desc from itmmast"')
   MONMSG MSGID(QSH0000) EXEC(DO)
      /* code to run if SQL statement failed in error */
   ENDDO

Additional Features

In addition to fixing the problems I mention above, I also added several new features that I thought would be useful:

You can completely strip column headings and similar information.
You can provide a string to be placed at the start of each row, the end of each row, and between each column. This makes it easy to generate CSV, tab-delimited, or even XML files.
You can run multiple SQL statements from the command line.
You can prefix a statement with - to have the program ignore any failures in that particular statement.

For example, with the new tool, you could do the following and it would create a CSV file:

sql -QqS -b '"' -a'"' -c '","' "select * from custmas"

The -b (before) switch prints a string before each record. Likewise, -a prints a string after each record, and -c (column) prints text between the columns. This makes it easy to generate a delimited file like the one above.

The same technique could be used to generate an HTML file:

export HTML /home/klemscot/test.html
echo '<html><body><table style="border-collapse: collapse">' >  $HTML

sql -SQq -b "<tr><td>" -a "</td></tr>" -c "</td><td>"
    "select improd, imdesc from itmmast" >> $HTML

echo "</body></html>" >> $HTML

Of course, if you substitute XML tags for HTML tags, you've created an XML document. Isn't that cool?

If you run this new QShell utility and pass -h only as a parameter, it will print out some help for the command to make it easier for you to get started. Here's what that help looks like:

Usage: sql -h = display help
    or sql [-vqQWS] [-t | -d | -T<char>] [-r RDBNAME [-u USER] -p PASS] ]
           [-b STRING] [-a STRING] [-c STRING] "statement; statement ..."
    or sql [-vqQWS] [-t | -d | -T<char>] [-r RDBNAME [-u USER] -p PASS] ]
           [-b STRING] [-a STRING] [-c STRING] -f FILENAME DEFAULT_LIB [LIBL]

The first syntax (with -h) displays this help file.

The second syntax runs sql statements provided on the command line. Multiple statements can be separated by a semicolon or whatever is specified with -t, -d, or -T. Statements beginning with @ will be executed as CL commands, others as SQL statements.

The third syntax runs sql statements provided in a script file. Multiple statements are separated by a newline (or whatever is specified with -t, -d, or -T). Lines beginning with -- or the statement separator are comments. Lines beginning with @ are CL commands, and others are SQL statements. FILENAME must be in IFS syntax. If FILENAME is -, statements are read from stdin. If DEFAULT_LIB is given, it will be the default library for unqualified filenames. If -Q is given, you may also specify a space-delimited library list, following the default library.

OPTIONS:

-v = print each statement before executing
-t = use semicolon as statement separator (default)
-d = use bang (exclamation) as statement separator
-T = use <char> as statement separator
-q = quiet mode. Omit column headings.
-Q = use qualified object syntax (naming=*SYS)
-W = suppress warning messages (shows errors, but not warnings)
-S = suppress whitespace that lines up columns, and trim trailing blanks
-a = string printed after each record. default=NONE
-b = string printed before each record. default=NONE
-c = string printed between each column. default=" "
-r = connect to RDBNAME database
-u = (if -r given) use user profile USERNAME
-p = (if -p given) use password PASSWORD
-f = read commands from FILENAME. Use library DEFAULT_LIB for unqualified tables. If using qualified naming, you can optionally add a library list.

How to Get It

You can download the source code as well as installation instructions. [2] I hope you find it useful!

Problems to Be Solved

Additional Features

How to Get It

Previous Articles Discussing the QShell db2 Command