Access the Database from Qshell

November 15, 2002 Timothy Prickett Morgan

Hey, Ted:

Qshell does a good job when it comes to accessing data in source physical file members and IFS files, but I need to access a database file from a Qshell script. Is that possible?

— Mike

IBM provides an undocumented utility called db2 that can execute SQL commands against the database.

It’s one of those “use it at your own risk” things.

The following command displays all the records from the COMPANIES file in library mylib.

db2 "select * from mylib.companies"

Qshell responds with this output.

COMPANY  COMPNAME
-------- -------------------------
   1     Leroy's Livery Stable
   2     Pete's Pet Shoppe

  2 RECORD(S) SELECTED.

Here’s another example. This time I retrieve the company name only, and just for the company whose number is stored in Qshell variable company.

db2 "select compname from mylib.companies where company = $company"

I enclosed the SQL command with double quotes (“) so that Qshell would interpret the company variable. If company has a value of 2, this is the output.

COMPNAME
-------------------------
Pete's Pet Shoppe

  1 RECORD(S) SELECTED.

Now I will pull out just the company name and load it into variable companyname.

companyname=$(db2 "select compname from mylib.companies where company = 
     $company" | sed -n '/--/{n; p;}')  

Even though that code wraps on your screen, it is all one line. The sed command looks for a line with two dashes (–). When it finds one, it reads and prints the following record. The $( ) characters surrounding the db2 and sed commands make Qshell redirect the output from the screen into the variable.

You can run other SQL commands besides select. I have used the db2 utility to create tables and insert data into them. Here are a few more examples. They are all one-liners, but may wrap on your browser screen.

db2 "update mylib.companies set compname='Lizzy''s Livery Stable' 
     where company = 1"

db2 "delete from mylib.companies where company = 1"

db2 "insert into mylib.companies values(3, 'Jack''s Java Palace')"

I want to point out two important aspects.

First, the table names must be qualified in *SQL format. IBM tells me that the *SYS format is not supported, and I have not been able to get the db2 utility to access the library list.

Second, if an SQL command contains single quotes (‘), enclose the SQL command in double quotes and double the single quotes where they are to be included as part of a data value.

For more information, see IBM’s “Qshell, Perl, and DB2 UDB for iSeries–DB2 Universal Database” Web page.

— Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

Business Analytics – Practical – – Cost Effective – – Easy to Deploy – SEQUEL FYI User Quote: “I love SEQUEL FYI because it lets me look at the data any way I need to see it, instantly. This is the easiest tool to manage complex product relationships that I have ever seen.” SEQUEL FYI offers outstanding OLAP business intelligence functionality for a fraction of the cost of comparable solutions. Read More > View Streaming Video