Script for DB2 Universal Database using REXX: Quick DB2 UDB scripting
Автор: Howard Fosdick
Дата: 25 августа 2005
Источник: developerWorks
Скачать в формате DOC.
If you want to learn a quick way to develop database scripts, start here. Rexx scripts not only manipulates IBM® DB2® Universal Database™ (DB2 UDB) data in the same manner as traditional languages, it can issue DB2 commands and use the administrative API to manage and control all aspects of DB2 UDB. Author Howard Fosdick includes lots of examples to get you going.
Introduction
In a previous IBM developerWorks article, I discussed why scripting is faster than developing code in traditional languages like C++, Java™, or COBOL, and I introduced the Rexx scripting language. For those who don’t know Rexx, I presented a brief tutorial on the language. Rexx distinguishes itself by combining ease of use with power. Rexx is so easy to use that you’ll be able to follow the example scripts in this article, even if you have never programmed in it before. So if you want to learn a quick way to develop database scripts, you’re in the right place. By the end of this article you’ll be able to program typical database tasks.
Rexx scripts can manipulate DB2 UDB data in the same manner as programs written in such traditional languages like C++, Java, or COBOL. In addition, IBM’s Rexx-to-DB2 interfaces give Rexx scripts extra capabilities. Rexx scripts can issue DB2 commands through a direct interface to DB2’s command line processor (CLP). Scripts can also use the DB2 Administrative interface or Administrative API. This means you can develop Rexx scripts that manage and control all aspects of DB2 UDB. For example, scripts can direct database backups and recoveries, issue all DB2 commands, and perform other database administration and management functions. IBM’s Rexx-to-DB2 interfaces come bundled with DB2 UDB for Linux, Windows®, and AIX®.
An alternative approach is to use an open source, database-independent interface. Once such product is Rexx/SQL. Rexx/SQL allows you to issue DML, DCL, and DDL SQL statements from within Rexx scripts in a database-independent manner. So writing a Rexx/SQL script to access DB2 UDB data is the same as developing a Rexx/SQL script targeting any other database. Rexx/SQL scripts can easily be redirected from one database target to another. This would be useful, for example, when converting from Oracle to DB2 UDB. It could also be useful if your organization supports more than one database product and you need a simple, consistent way to access data. Rexx/SQL supports all major databases and runs under the major operating systems, including Linux, UNIX, and Windows. Rexx/SQL makes your scripts portable and your skills transferable.
The Rexx/SQL product supports all typical database features. These include the ability to issue DML, DCL, and DDL statements; transaction control with COMMITs and ROLLBACKs; concurrent database connections, cursors, and auto-commit; dynamic statement preparation and bind variables; SQL control structures like the SQL Communcations Area and SQL error messages; and, the retrieval and setting of database interface behaviors.
Rexx/SQL conforms to the major database interface standards. These include the X/Open Call Level Interface (X/Open CLI) and the Open Database Connectivity Application Programming Interface (ODBC API). A special feature of Rexx/SQL is its ability to issue any SQL statement through a single function call. This includes SELECT statements that return multiple rows.
On the downside, Rexx/SQL is generic. It is a call-level interface intended for issuing SQL statements. It does not support some of the DB2-specific capabilities available
This article gives you a complete tutorial on scripting DB2 UDB with Rexx and Rexx/SQL. I’ll show how to create and terminate connections to the database and how to issue DML, DCL, and DDL SQL statements. I’ll demonstrate how to issue any SQL statement by calling a single Rexx/SQL function. By the end of the article, you’ll be able to script typical database tasks using Rexx with Rexx/SQL.
Downloading, installation, and set-up
To get started, you need to download two products to your Windows, Linux, or UNIX database server. First, you need a Rexx interpreter. As discussed in my previous developerWorks article, there are nine free and open source Rexx interpreters from which to choose. They run on virtually any platform. For this article I chose Regina, the most popular open source Rexx that runs on all major operating systems. You can download Regina from https://regina-rexx.sourceforge.net/. Download the self-extracting file *.exe file for Windows, or the *.tar.gz or *.rpm files for Linux or UNIX.
On Windows systems, once you’ve downloaded the product, just double-click on the self-extracting file. This leads you through a typical Windows product install using the Windows Installer. For Linux and UNIX systems, just perform a typical product install as you normally would for compressed source-code installs (using the *.tar.gz file). Or install using the Red Hat Package Manager (using the *.rpm file). Regina comes with complete installation instructions for all platforms and includes many test scripts, so it’s easy to verify that your install succeeded.
Once you’ve installed Regina Rexx, you need to download and install Rexx/SQL. This open source product is available at https://rexxsql.sourceforge.net. Rexx/SQL provides two drivers to access DB2 UDB databases. One is the native DB2 UDB interface, which provides performance advantages. The other is the standard ODBC interface, a generic interface into almost any database system including DB2 UDB. I downloaded the DB2 UDB native interface for this article. This is the file that has the keyword DB2 in its name. While the exact file name varies by release, the name of the file I downloaded was rxsql24_db2_w32.zip.
Installing Rexx/SQL is easy. For Windows, after downloading and un-zipping the file, you will have a dynamic link library or DLL. For Linux and UNIX, uncompressing the download file produces a shared library file. As explained in the Rexx/SQL installation instructions, you must set an environmental variable so that the operating system knows where to find the Rexx/SQL function library. For Windows, this means including the name of the folder in which the DLL resides in the PATH environmental variable. For Linux and UNIX, the Rexx/SQL instructions give the name of the environmental variable that should point to the shared library directory. (This name varies by the UNIX or Linux variant you run).
Rexx/SQL comes with example scripts called simple.cmd and tester.cmd. The first simply verifies that a database connection can be made, while the latter gives the Rexx/SQL-to-DB2 interface a full workout. Be sure to verify your set-up by running these two Rexx/SQL example scripts. Read the documentation at the top of each script prior to running it; this contains Rexx/SQL set-up information.
One last step may be necessary for DB2 setup. You must have bound the utilties for DB2 UDB’s Call Level Interface or CLI to the DB2 database. This only needs to be done once per DB2 database and is explained in the DB2 UDB documentation manual Application Development Guide: Building and Running Applications (SC09-4825). If you’ve ever used the CLI before, you do not need to bind it to DB2 again.
If you need to bind the CLI utilities, just issue the proper DB2 BIND command as described in the manual. For example, on a Windows server, issue this BIND command, where the symbol %DB2PATH% refers to the path where DB2 UDB is installed:
db2 bind "%DB2PATH%\bnd\@db2cli.lst" blocking all
sqlerror continue messages cli.msg grant public
For UNIX or Linux, this command will look like this, where BNDPATH is the location of the bind files:
db2 bind BNDPATH/@db2cli.lst blocking all sqlerror continue \
messages cli.msg grant public
This is a simple one-command process. Check the manual I cited above if you need a fuller explanation.
A first script
I’ve stated that Rexx is a good database scripting vehicle because it’s easy to learn. In fact, it’s so easy that you’ll be able to follow the Rexx-to-DB2 examples I present below even if you don’t know Rexx. Let’s get started.
Here is our first complete example script. It connects to DB2 UDB’s SAMPLE database and retrieves a single row of data. This row has a DEPTNO column value of C01. The whole script requires only 11 lines of executable code:
Listing 1. Example connecting to SAMPLE and retrieving one row
/*************************************************************/
/* DB2 ONE ROW: */
/* */
/* Connects to DB2 UDB, retrieves & displays one row of data */
/*************************************************************/
/* Load all SQL functions, make them accessible to the script*/
1 call RxFuncAdd 'SQLLoadFuncs','rexxsql', 'SQLLoadFuncs'
2 call SQLLoadFuncs
/* Connect to DB2 database SAMPLE, using default user/passwd */
3 call SQLConnect 'MYCON',,,'SAMPLE'
/* Retrieve and display the one row of data */
4 sqlstr = "select DEPTNO, DEPTNAME from DEPARTMENT" ,
5 "where DEPTNO = 'C01' "
6 call SQLCommand 's1', sqlstr
7 if sqlca.rowcount <> 1
8 then say "The row was not found"
9 else say "DEPTNO:" s1.DEPTNO.1 "DEPARTMENT:" s1.DEPTNAME.1
/* Disconnnect from the database and drop the SQL functions */
10 call SQLDisconnect
11 call SQLDropFuncs 'UNLOAD'
In reviewing this code, you’ll notice that I added line numbers on the left-hand side. These line numbers are not part of the Rexx code -- I’ve simply added them for easy reference during our discussion. You’ll also notice that Rexx comments are preceded by the two characters /* and terminated by the characters */. Like many programming languages, Rexx allows you to enter any commentary you like between the comment delimiters. This script starts with a comment block describing the purpose of the program, while individual comment lines precede and explain parts of the code.
Lines 1 and 2 are probably the trickiest part of this program. They set up access for the script to the Rexx/SQL interface, which is an external function library. Like many programming languages, you extend Rexx with various interfaces provided in the form of external function libraries, groups of functions provided for specific purposes. Once you’ve established access to the library, as done in lines 1 and 2, you simply code the use of the functions in the library in the same manner as you would use any functions that are built into the Rexx language.
Line 1 invokes or calls the built-in Regina Rexx function RxFuncAdd to load the single external function named SQLLoadfuncs. RxFuncAdd takes three parameters. The first is the name of the external function to load and the third is the name by which this script will refer to that function. So, line 1 loads the SQLLoadfuncs external function and will always refer to it by the same name. The second parameter is the core of the file name of the external function library. Coding the name rexxsql means that the external function library would be named rexxsql.dll under Windows, or librexxsql under Linux, for example.
Once line 1 has loaded the SQLLoadfuncs function from the external Rexx/SQL function library, line 2 simply runs SQLLoadfuncs. This function loads the rest of the Rexx/SQL library for use by this script. Now the script can issue any of the Rexx/SQL functions.
Like any database program in any programming language, this script needs to establish a connection to the database before it can perform any database processing. This script does this in line 3, by invoking the Rexx/SQL SQLConnect function. Line 3 connects to DB2 UDB SAMPLE database and names the connection MYCON. This connection employs the default userid and password.
To code a SQLConnect statement with the userid DB2ADMIN and a password of SECRET, you would code a statement like this. Note that you can enclose character string literals in either single or double quote marks in Rexx:
call SQLConnect 'MYCON','DB2ADMIN','SECRET','SAMPLE'
Lines 4 and 5 create the SQL statement to retrieve a single row of data. The key for the row is the DEPTNO column, here given a unique value of C01. Lines 4 and 5 build the character string for the SELECT statement, and enclose it in double quotes. The comma at the end of line 4 is the line continuation character in Rexx, so the string literal in line 5 is concatenated to that of line 4. These two lines build a character string that looks like this SQL statement:
select DEPTNO, DEPTNAME from DEPARTMENT where DEPTNO = 'C01'
Line 6 calls the SQLCommand function to execute the SELECT statement. Rexx/SQL allows issuing any DML, DCL, or DDL statement in one call through the SQLCommand function. This includes SELECT statements that return any number of rows. This Rexx/SQL feature goes beyond that of many database interfaces, which require “cursor processing” for SELECT statements that return more than one row.
In the SQLCommand call, the first parameter is any name you assign as a label for the statement. Here I’ve arbitrarily labeled the SQL statement s1. The second parameter is the character string representing the SQL statement you want to issue. In line 6 this refers to variable SQLSTR, which contains the SQL SELECT statement.
Like all standard database interfaces, Rexx/SQL sets values in the SQL Communications Area or SQLCA to communicate information back to the program. Line 7 tests the value Rexx/SQL sets in the variable SQLCA.ROWCOUNT, the number of rows affected by the prior SQL call. If ROWCOUNT is 1, the script knows it retrieved a single row via its SELECT statement.
The script displays the results on the user’s screen by the Rexx SAY instruction in line 9. Line 9 shows that the SAY instruction will take any number of operands you specify, concatenate them together with a single intervening space between each, and display them on the screen. The operands on the SAY statement enclosed in the double-quotes are string literals. The other operands are references to the data elements retrieved by the SELECT statement. To refer to the elements, Rexx/SQL uses this notation:
statement_identifier.column_name.row_retrieved
To refer to the DEPTNO column that was retrieved by SQL statement labelled s1, code:
s1.DEPTNO.1
You can use this handy notation to refer to any data elements retrieved by SQL statements. Line 9 uses it to write the department number and description in the database row that was retrieved to the user on his or her display.
The last two lines in the script, 10 and 11, end the program through standard statements. Line 10 issues the Rexx/SQL SQLDisconnect statement, the inverse of the SQLConnect statement of line 3. Line 11 issues the SQLDropFuncs statement to clear the Rexx/SQL functions out of memory. It is the inverse of the SQLLoadFuncs statement issued in Line 2 of the program. Just as all Rexx/SQL programs start with the coding I see in lines 1 through 3, so they typically end with the actions of lines 10 and 11.
That’s it! You’ve learned the basics of Rexx/SQL programming with DB2 UDB. You now know how programs access the Rexx/SQL function library, connect to DB2, issue DML, DCL or DDL statements, display results, and terminate their database connections. Let’s continue with more scripting examples. By the end of this article you’ll be able to write scripts that issue any DML, DCL, or DDL statement.
A more robust program
Real-world scripts typically include a bit more than our first example. For one thing, any good database program should perform error checking. The result of each SQL statement should be checked and the script should perform some error processing if the statement fails. This is standard in any production database program.
Here is the exact same program as our first example, but with error checking added. Our short, simple example script balloons from 11 lines of code to 23. But it’s not as bad as it looks. Error-checking doubles the amount of code but all the extra coding follows a simple, consistent pattern. Once you understand this pattern, it’s easy to understand the program. Take a look:
Listing 2. Script with error checking
/**************************************************************/
/* DB2 ONE ROW (version 2) : */
/* */
/* Connects to DB2 UDB, retrieves & displays one row of data. */
/* */
/* Adds error-checking code to the original script. */
/**************************************************************/
/* Load all SQL functions, make them accessible to the script */
1 if RxFuncAdd('SQLLoadFuncs','rexxsql', 'SQLLoadFuncs') <> 0 then
2 say 'rxfuncadd failed, rc: ' rc
3 if SQLLoadFuncs() <> 0 then
4 say 'sqlloadfuncs failed, rc: ' rc
/* Connect to DB2 database SAMPLE, use default user/passwd */
5 if SQLConnect('MYCON',,,'SAMPLE') <> 0 then
6 call sqlerr 'On Connect'
/* Retrieve and display the one row of data */
7 sqlstr = "select DEPTNO, DEPTNAME from DEPARTMENT" ,
8 "where DEPTNO = 'C01' "
9 if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On Select'
10 if sqlca.rowcount <> 1
11 then say "The row was not found"
12 else say "DEPTNO:" s1.DEPTNO.1 "DEPARTMENT:" s1.DEPTNAME.1
/* Disconnnect from the database and drop the SQL functions */
13 if SQLDisconnect() <> 0 then call sqlerr 'On Disconnect'
14 if SQLDropFuncs('UNLOAD') <> 0 then
15 say 'sqldropfuncs failed, rc: ' rc
16 exit 0
/* Capture any SQL error and write out SQLCA error messages */
17 sqlerr: procedure expose sqlca.
18 parse arg msg
19 say 'Program failed, message is: ' msg
20 say sqlca.interrm /* write SQLCA messages */
21 say 'SQL error is:' sqlca.sqlerrm /* write SQLCA messages */
22 call SQLDropFuncs 'UNLOAD'
23 exit 99
Notice that the CALL instructions I used to invoke functions in the original script have been altered to IF statements with nested functions. A function in Rexx is identified by the left parenthesis that immediately follows its name. The parentheses contain the function’s parameters or arguments. Rexx invokes the function, resolves it, and replaces the function in the code with its return value.
Look at line 1, for example. I have replaced the CALL to the RxFuncAdd function with coding that nests it as a function within an IF statement. RxFuncAdd is immediately followed by parentheses that contain its three required arguments.
When Rexx resolves the statement, it will replace the function invocation with the function’s return code value. For all Rexx/SQL functions, this return code value will be 0 if the function succeeded or non-zero otherwise. RxFuncAdd thus returns a value that the surrounding IF statement immediately tests. The result is that line 1 will execute the SAY instruction in line 2 if the function invocation failed. This writes an error message to the screen. If the RxFuncAdd invovation succeeds and returns 0, no error message is written. Replacing the CALLs in the original script with IF statements in this manner allows us to embed error-checking in the script.
I’ve employed this same coding style in checking for errors from the other functions in this script as well. You see it applied to SQLLoadFuncs in line 3, SQLConnect in line 5, SQLCommand in line 9, SQLDisconnect in line 13, and SQLDropFuncs in line 15. Once you understand how this works, you can see that this script is really only a simple revision of the original example script.
The Rexx/SQL functions SQLConnect, SQLCommand, and SQLDisconnect call a special error routine I’ve named SQLERR when any error occurs on a SQL statement. Look at lines 5 and 6 for example, where the program connects to DB2 UDB. If the return code from the SQLConnect function on line 5 is not successful (it’s not zero), then the program CALLs the internal subroutine named SQLERR. This is done through the CALL statement in line 6. The SQLERR subroutine is encoded to take a single input argument, a character string that tells what kind of SQL statement caused the error.
Lines 17 through 23 contain the code for the SQL error routine named SQLERR. Line 17 identifies the routine with a Rexx label, the name of the routine immediately followed by a colon. Rexx requires that any internal suboutine or function start with an identifying label. Also on this first line are the keywords PROCEDURE EXPOSE SQLCA. These ensure that this subroutine can only access the variables in the SQLCA; it cannot access or change any other variables in the parent routine.
Line 18 contains the PARSE ARG instruction. This reads in the input argument sent into this routine by the calling statement, and places the single input argument into the value called MSG. Line 19 then displays the input error string to the display screen using a SAY instruction. This tells the user which SQL statement experienced the error.
Lines 20 and 21 write information from the SQLCA to the display. These lines refer to the SQLCA variables SQLCA.INTERRM and SQLCA.SQLERRM. Here’s what output from the error routine looks like. In this example, I tried to connect to a non-existent DB2 database called NOWAY:
Listing 3. Output from error routine
Program failed, message is: On Connect
Rexx/SQL-1: Database Error
SQL error is: [IBM][CLI Driver] SQL1013N. The database alias name or database
name “NOWAY” could not be found. SQLSTATE=42705
Here’s a fun fact. If you look closely at the error message above, you’ll notice that underneath the covers, Rexx/SQL uses the CLI Driver written by IBM. These characters relate this fact: [IBM][CLI Driver].
To complete the error routine, line 22 cleans up after the program by dropping the Rexx/SQL functions from memory. Line 23 terminates the script through its EXIT instruction. The script passes a return code of 99 to the operating system to indicate that a problem occurred. Note that if this script does not terminate in error (through executing the SQLERR routine), then line 16 terminates the program. That EXIT instruction passes a return code of 0 to the operating system, indicating successful completion of this program.
So there you have it. I’ve taken our initial 11-line program and added robust error-checking. Along the way, though, I’ve doubled the length of the program. While this adds no logical complexity, it makes for a rather verbose script. Let’s fix that!
Simplifying through subroutines
The previous example showed how to encode a subroutine. Let’s expand upon this idea to simplify the program. This version of the program is almost same as the one above, except that I’ve pulled initalization and termination code "out of line" and placed it into two new subroutines. I call these two subroutines SQL_INITIALIZE and SQL_PGM_END. The former accesses the Rexx/SQL function library and connects to DB2 UDB, while the latter issues the SQLDisconnect and SQLDropFuncs functions. You’ve seen all the code in these two subroutines before, except for one thing: Both routines end with the RETURN instruction. RETURN returns control to the main program at the end of the subroutine.
Here is the complete program:
Listing 4. Subroutines
/**************************************************************/
/* DB2 ONE ROW (version 3) : */
/* */
/* Connects to DB2 UDB, retrieves & displays one row of data. */
/* */
/* Shows how to isolate common database code into subroutines.*/
/* */
/* Gets the department number from the command line and plugs */
/* it into the SELECT statement via dynamic concatenation. */
/**************************************************************/
1 call sql_initialize /* load all Rexx/SQL functions */
/* and connect to the database */
2 arg dept_input /* get department no from user */
/* Retrieve and display the one row of data */
3 sqlstr = "select DEPTNO, DEPTNAME from DEPARTMENT " ,
4 "where DEPTNO = '" || dept_input || "'"
5 if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On Select'
6 if sqlca.rowcount <> 1
7 then say "The row was not found for DEPTNO:" dept_input
8 else say "DEPTNO:" s1.DEPTNO.1 "DEPARTMENT:" s1.DEPTNAME.1
9 call sql_pgm_end /* disconnnect from database */
10 exit 0
/********************** Subroutines ***************************/
11 sql_initialize: procedure expose sqlca.
/* Load all SQL functions, make them accessible to script */
12 if RxFuncAdd('SQLLoadFuncs','rexxsql', 'SQLLoadFuncs') <> 0 then
13 say 'rxfuncadd failed, rc: ' rc
14 if SQLLoadFuncs() <> 0 then
15 say 'sqlloadfuncs failed, rc: ' rc
/* Connect to DB2 database SAMPLE, use default user/passwd */
16 if SQLConnect('MYCON',,,'SAMPLE') <> 0 then
17 call sqlerr 'On Connect'
18 return
19 sql_pgm_end: procedure expose sqlca.
/* Disconnnect from the database and drop the SQL functions */
20 if SQLDisconnect() <> 0 then call sqlerr 'On Disconnect'
21 if SQLDropFuncs('UNLOAD') <> 0 then
22 say 'sqldropfuncs failed, rc: ' rc
23 return
24 sqlerr: procedure expose sqlca. /* Common error routine */
25 parse arg msg
26 say 'Program failed, message is: ' msg
27 say sqlca.interrm /* write SQLCA messages */
28 say 'SQL error is:' sqlca.sqlerrm /* write SQLCA messages */
29 call SQLDropFuncs 'UNLOAD'
30 exit 99
Coding subroutines really simplifies the script. Since all programs perform similar initialization and clean-up code, it’s useful to isolate this code out of the main routine. This makes the logic of the main routine much simpler and clearer. You can re-use the subroutines in all your database programs.
To keep things interesting, I also added one logical change to the program. Hard-coding the value of the department number of the row to retrieve is quite limiting. A real-world program requires greater flexibility. So line 2 of the script reads the department number to search for as an input parameter to the program from the user. It uses the Rexx ARG instruction to do this. To run the program, you now specify the DEPTNO value as a command line input argument to the program. Here’s an example running the program from the Windows command line:
c:\> regina db2_one_row.rexx C01
Here’s a Linux example, giving the program a different department number row to retrieve:
$ regina db2_one_row.rexx A00
Reading the department number of the row to retrieve also changes the SELECT statement coding in lines 3 and 4. This statement must now dynamically concatenate the department number the user enters into the SELECT statement. For example, if the user entered A00, the SELECT statement the script builds must look like this:
DEPTNO, DEPTNAME from DEPARTMENT where DEPTNO = 'A00'
Lines 3 and 4 display some fancy concatenation to build this SQL statement correctly. They leverage Rexx’s ability to enclose character strings in either single or double quotation marks in building the string. One of the big advantages to Rexx is its facility for string manipulation. It really shines in situations like this. You can easily verify that the SQL statement was built correctly. Just write the SELECT statement string to the display once it has been built in the variable SQLSTR:
say sqlstr /* Display the SELECT statement for verification */
Bind variables
Here’s another approach to encoding dynamic input variables into SQL statements. Instead of dynamically concatenating a value into the SQL statement string, as done in lines 3 and 4 of the above script, use bind variables. Bind variables allow you to code the SQL statement with a missing value, represented by a placeholder variable. Rexx/SQL and DB2 UDB will dynamically substitute the correct value into the SQL statement for you. Bind variables enhance database performance.
Only two changes are needed to the above script so that it uses bind variables. First, change the SQL SELECT statement built in lines 3 and 4 to refer to the placeholder variable. The question mark (?) is the placeholder variable that will be replaced by the proper value when the statement executes. So replace lines 3 and 4 with this statement:
sqlstr= "select DEPTNO, DEPTNAME from DEPARTMENT where DEPTNO = ?"
Second, the SQLCommand coding of line 5 must change. When executing a SQL statement that includes bind variables, the SQLCommand function must specify the data type for each bind variable, and the value to substitute into the place of the question mark. To continue our example, code the new version of line 5 like this:
if SQLCommand(s1,sqlstr,"CHAR",dept_input) <> 0 then
The SQLCommand function contains two additional parameters. The first, "CHAR," specifies the data type of the bind variable. The second, DEPT_INPUT, is the name of the variable whose value will be dynamically substituted into the SQL statement to replace the placeholder variable. These are the only changes required to alter the script to use bind variables.
Data retrieval
Rexx/SQL offers several ways to retrieve multiple-row results sets from SELECT statements. One way is very easy to code and is unique to Rexx/SQL. The other approach uses cursor processing, a more demanding but standard approach.
First, let’s look at Rexx/SQL’s approach. It’s so easy, in fact, that here is the entire program to retrieve and display the rows of the DEPARTMENT table. (This listing does not include the code of our standard SQL_INITIALIZE, SQL_PGM_END, and SQLERR routines, which is the exact same as you’ve seen before.)
Listing 5. Retrieve multiple-row results sets using Rexx/SQL
1 call sql_initialize /* load all Rexx/SQL functions */
2 sqlstr = 'select * from DEPARTMENT order by DEPTNO'
3 if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On Select'
/* This loop displays all rows from the SELECT statement */
4 do j = 1 to sqlca.rowcount
5 say s1.DEPTNO.j s1.DEPTNAME.j
6 end
7 call sql_pgm_end /* disconnect, drop functions */
8 exit 0
The key to this script is the DO loop in lines 4 through 6. This loop processes all the rows retrieved by the SELECT statement, one at a time. It uses a notation similar to what we used earlier to refer to individual rows. The DO loop automatically increments the subscript j by 1 each time through the loop, thereby processing the data elements for each row retrieved, one by one.
Now, here’s the same program using cursor processing. Cursor processing is a standard method for processing multiple rows that are retrieved by a single SELECT statement:
Listing 6. Retrieve multiple-row results sets using cursor processing
1 call sql_initialize /* load all Rexx/SQL functions */
2 sqlstr = 'select * from DEPARTMENT order by DEPTNO'
3 if SQLPrepare(s1,sqlstr) <> 0 then call sqlerr 'On Prepare'
4 if SQLOpen(s1) <> 0 then call sqlerr 'On Open'
/* This loop displays all rows from the SELECT statement */
5 do while SQLFetch(s1) > 0
6 say s1.DEPTNO s1.DEPTNAME
7 end
8 if SQLClose(s1) <> 0 then call sqlerr 'On Close'
9 if SQLDispose(s1) <> 0 then call sqlerr 'On Dispose'
10 call sql_pgm_end /* disconnect, drop functions */
11 exit 0
As in standard SQL, cursor processing requires the preparation of the SQL SELECT statement. Line 3 performs this task through the SQLPrepare function. Line 4 then opens the cursor through the SQLOpen function. Then, a fetch loop retrieves and processes each row of the result set, as shown in lines 5 through 7. Notice that the fetch loop does not use subscripts to refer to individual data elements; these are not used because each iteration of the SQLFetch function provides the next row in the SELECT results set. Finally, as required in cursor processing, lines 8 and 9 close the cursor and dispose of its memory resources.
This sample script shows that cursor processing in Rexx requires the same steps you would follow for cursor processing in any other programming language:
- PREPARE the SELECT statement.
- OPEN the cursor.
- FETCH and process rows from the results set one at a time.
- Conclude processing by CLOSEing the cursor.
Data update
Of course, Rexx/SQL scripts can also issue SQL UPDATE, INSERT, and DELETE statements. Code these statements for single-line execution through the SQLCommand function. Or separately prepare and execute them. In this case, code the SQLPrepare function to prepare the statement, then use the Rexx/SQL SQLExecute function after the prepare to execute the UPDATE, INSERT, or DELETE statement. Code SQLDispose to tidy up when done.
UPDATE, INSERT, and DELETE statements may also use bind variables. Or, you may code them with dynamically concatenated values, the same as illustrated in our third example (the one labeled "Version 3" in the section Simplying through subroutines).
Here is a program that allows the user to update the department description for different departments in the DEPARTMENT table. The script first prompts the user to input a department number. Then it retrieves the row with that department number as its key, and allows the user to input a new department description. In terms of the column names used in the DEPARTMENT table, the script searches for rows with a given DEPTNO and allows the user to update their corresponding DEPTNAME. The script terminates when the user enters the word EXIT instead of a DEPTNO. Here is the script:
Listing 7. Update the description
1 call sql_initialize /* load all Rexx/SQL functions */
2 sq = "'" /* SQ is a single quote mark */
3 say "Enter DEPTNO or 'EXIT':" /* prompt for DEPTNO */
4 pull deptno . /* we'll update the DEPARTMENT */
5 do while (deptno <> 'EXIT')
/* Retrieve the DEPARTMENT description for user to update */
6 sqlstr = "select * from DEPARTMENT where DEPTNO = " ,
7 sq || deptno || sq
8 if (SQLCommand(s1,sqlstr) <> 0) then call sqlerr 'On Select'
/* If we retrieved one row, we retrieved the DEPTNO given. */
/* Go ahead and update the DEPARTMENT description */
9 if sqlca.rowcount <> 1 then
10 say 'This department number is not in the database:' DEPTNO
11 else do
12 say DEPTNO 'Current description:' DEPTNAME
13 say 'Enter new DEPARTMENT description:'
14 pull new_dept .
15 sqlstr = "update DEPARTMENT set DEPTNAME = ",
16 sq || new_dept || sq ,
17 "where DEPTNO = " ,
18 sq || deptno || sq
19 if SQLCommand(u1,sqlstr) <> 0 then call sqlerr 'On Update'
20 end
/* Commit to end the interaction, get the next DEPTNO */
21 if SQLCommit() <> 0 then call sqlerr 'On Commit'
22 say "Enter DEPTNO or 'EXIT':"
23 pull deptno .
24 end
25 call sql_pgm_end /* disconnect, drop functions */
26 exit 0
You’ve seen almost everything in this program before. There are a few new Rexx language features I should explain. For example, lines 4, 14, and 23 use the Rexx PULL instruction to read data input by the user. PULL reads one (or more) data elements into the variable(s) listed and automatically translates them to upper-case. Lines 11 and 20 show that multiple instructions in either branch of an IF statement must be enclosed in a DO-END group, just as the DO-WHILE instruction requires the END keyword coded on line 24.
From the standpoint of SQL coding, two points bear mentioning. First, notice that line 2 defines a variable that holds a single-quote mark. The lines that build the SQL statements then use this value to simplify their coding (see lines 6 and 7, and lines 15 through 18). This helps simplify the building of SQL statements. Second, line 21 shows how to COMMIT data to the database using the SQLCommit function. Rexx/SQL gives you full transactional control with DB2 COMMIT and ROLLBACK statements. It also gives you full control of DB2’s "auto-commit" feature and other DB2 UDB behaviors through its SQLVariable function.
DDL and DCL
You can issue DDL and DCL SQL statements from Rexx/SQL scripts as easily as DML. This example program shows how.
This program creates a new table with the same table definition as the DEPARTMENT table in the SAMPLE database. To do this, it first issues a SQL DROP TABLE statement for the new table, called DEPARTMENT_NEW, to ensure that this table does not already exist. Then the script CREATEs the new table, copies all rows from the DEPARTMENT table over to it, and GRANTs the public access to it.
This script shows how to issue the SQL DROP TABLE, CREATE TABLE, INSERT, and GRANT statements. (If you’re familiar with DB2 UDB, you know there are more efficient ways to accomplish this work, but I wanted to demonstrate how Rexx/SQL issues various DDL statements.) The script demonstrates that Rexx/SQL executes each of the SQL statements in a single command through the SQLCommand function. The program proves that it is as easy to issue DCL and DDL from Rexx scripts as it is to work with DML.
Here is the entire script:
Listing 8. Issue statements
1 call sql_initialize /* load all Rexx/SQL functions */
/* Ensure that the table DEPARTMENT_NEW does not already exist */
2 call SQLCommand 'd1', "drop table DEPARTMENT_NEW"
/* Create the new table named DEPARTMENT_NEW */
3 sqlstr = 'create table DEPARTMENT_NEW like DEPARTMENT'
4 if SQLCommand(s1,sqlstr) <> 0 then call sqlerr 'On Create'
/* Copy all rows from the DEPARTMENT table into DEPARTMENT_NEW */
5 sqlstr = 'insert into DEPARTMENT_NEW select * from DEPARTMENT'
6 if SQLCommand(i1,sqlstr) <> 0 then call sqlerr 'On Insert'
/* GRANT access to everyone on the new table DEPARTMENT_NEW */
7 sqlstr = 'grant all on DEPARTMENT_NEW to PUBLIC'
8 if SQLCommand(g1,sqlstr) <> 0 then call sqlerr 'On Grant'
9 call sql_pgm_end /* disconnect, drop functions */
10 exit 0
Summary
I’ve covered a lot of ground in this short article. I explained why Rexx is a good language for database scripting. Then I discussed the two Rexx-to-DB2 interfaces. IBM ships their bundled Rexx interface with DB2 UDB for Linux, Windows, and AIX. The advantage to the IBM product is that it has direct interfaces to the DB2 UDB command line processor and the administrative functions that manage and control DB2 UDB.
An alternative approach to Rexx-to-DB2 scripting leverages open source software. This approach is appealing because DB2 UDB makes an ideal database centerpiece for open source tools. This article demonstrates how to apply the Regina Rexx interpreter and the Rexx/SQL interface to DB2 UDB. Both of these open source products conform to all standards, run on many platforms, are database-independent, and support portable code. They make your skills applicable to a wide range of platforms and systems. These open source tools enable scripts to issue DML, DCL, and DDL statements to DB2 UDB and other databases. This makes Rexx a useful tool for sites converting to DB2 UDB and for those who work in heterogeneous database environments.
DB2 UDB and Rexx are a potent combination. DB2 UDB provides all the benefits and features of the world’s premier database management system, while Rexx offers quick data access, easy programmability, and scripting leverage. Together they supply great power with minimal coding effort.