Anyone who has worked in a mainframe environment is probably familiar with REXX programming. In this article, I am going to show you how can we run a REXX application with DB2 for Linux, Unix and Window (DB2 LUW).
There are many types of REXX out there you can install but for this article, I am using Open Object Rexx(ooRexx). You can read more about it in the link http://www.oorexx.org/about.html.In addition, take note that only 32 bit versions of Rexx are supported for DB2 LUW. You can read more about it the following link, http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.gs.doc/doc/r0023474.html
- Processor: Intel(R) Petium(R) 4 CPU 2.8 GHz
- RAM : 1GB
- Operating System: Windows XP Service Pack 3
- DB2 9.7 Enterprise Edition
You can download the ooRexx in the folowing link:-
- When running the installation you can keep the default setting of the installation.
Note: you will need to check the following if you are using DB2 64-BIT on Windows:-
- IC61843: REXX APPLICATION DO NOT WORK ON DB2 64-BIT ON WINDOWS
First fixed in DB2 UDB Version 9.7, FixPak 1 (build s091114)
- JR32283: REXX APPLICATION DO NOT WORK ON DB2 64-BIT ON WINDOWS
First fixed in DB2 UDB Version 9.5, FixPak 5 (build s091123)
After ooRexx installation has finished, you can check for your Rexx version using the command rexx -version in your command prompt. The output should look like Image 1 below.
Testing The Sample ApplicationDB2 has some sample REXX applications. The sample applications are in db2_installation_path/sqllib/samples/rexx. Running a REXX command is easy, just type in rexx application_file_name in your command prompt. We are going to try and run an application called dbstart.cmd which is the sample application. This is only a simple db2start application. Below are the steps to run your application.
- Make sure you point to the directory where you sample application is placed in your command prompt. For this article, I run cd C:\Program Files\IBM\SQLLIB\samples\rexx where my sample rexx application is placed.
- Next, run the application with the following command rexx dbstart.cmd
Writing Rexx application
We are going to create a REXX application that select a table from our DB2 sample database. If you do not have a sample database, you can go to your DB2 CLP and run the command db2sampl to create the sample database. If you already have the DB2 sample database you can follow the steps below.
- I am using the DB2 sample application called dynamic.cmd and make some minor modification to it . You can find the application in the directory db2_installation_path/sqllib/samples/rexx. I have copied dynamic.cmd, and place it on my desktop, C:\Documents and Settings\Administrator\Desktop.
- Rename the application name to select.cmd.
- Modify the code to select a column called deptname from the table name your_schema_name.department. Listing 1 is the modified code.It is just a minor modification.The changes are in red.
- Next, run application select.cmd. The image below shows the result of my output.
/* REXX SELECT.CMD */
parse version rexxType .
parse source platform .
if platform == 'AIX/6000' & rexxType == 'REXXSAA' then
rcy = SysAddFuncPkg("db2rexx")
if RxFuncQuery('SQLDBS') <> 0 then
rcy = RxFuncAdd( 'SQLDBS', 'db2ar', 'SQLDBS' )
if RxFuncQuery('SQLEXEC') <> 0 then
rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' )
/* pull in command line arguments */
parse arg userid passwd .
/* check to see if the proper number of arguments have been passed in */
PARSE ARG dbname userid password .
if ((dbname = "" ) | ,
(userid <> "" & password = "") ,
) then do
SAY "USAGE: dynamic.cmd <dbname> [<userid> <password>]"
/* connect to database */
SAY 'Connect to' dbname
IF password= "" THEN
CALL SQLEXEC 'CONNECT TO' dbname
CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password
CALL CHECKERR 'Connect to '
say 'Sample REXX program: SELECT STATEMENT'
st = "SELECT deptname FROM dylan.department "
call SQLEXEC 'PREPARE s1 FROM :st' /* :rk.2:erk. */
call CHECKERR 'PREPARE'
call SQLEXEC 'DECLARE c1 CURSOR FOR s1' /* :rk.3:erk. */
call CHECKERR 'DECLARE'
parm_var = "STAFF"
call SQLEXEC 'OPEN c1 USING :parm_var' /* :rk.4:erk. */
do while ( SQLCA.SQLCODE = 0 )
call SQLEXEC 'FETCH c1 INTO :deptname' /* :rk.5:erk. */
if (SQLCA.SQLCODE = 0) then
say 'Table = ' deptname
call SQLEXEC 'CLOSE c1' /* :rk.6:erk. */
call CHECKERR 'CLOSE'
call SQLEXEC 'CONNECT RESET'
call CHECKERR 'CONNECT RESET'
if ( SQLCA.SQLCODE = 0 ) then
say '--- error report ---'
say 'ERROR occurred :' errloc
say 'SQLCODE :' SQLCA.SQLCODE
* GET ERROR MESSAGE API called *
call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
say '--- end error report ---'
if (SQLCA.SQLCODE < 0 ) then
say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
Listing 1: select.cmd
ConclusionIn this article, we have covered the following.
- How we can install REXX into our Windows environment
- How we can use the sample REXX application in DB2.
- How to modify a sample application to write a select program for DB2.
Reference and Resources