REXX | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 12. DB2 Traditional Programming

REXX

The OS/390 REXX language offers an interface to DB2 so that REXX programs can use SQL DML and execute IFI commands. Even though REXX is an interpreted language, DB2 provides the same capabilities as other languages in that a REXX program can run in any OS/390 address space, and REXX programs can run as stored procedures. In fact, a stored procedure written in REXX was delivered with this feature, as it is one of the many components of the SQL procedures implementation.

You can write SQL application programs in the REXX programming language, and dynamic SQL is issued from REXX with no precompiles or link edit. REXX is an interpreted language and has been around for a long time. It is SYSPROG and "techie-loved," and you can build quick and easy scripts in REXX. The REXX language can be used for DB2 stored procedures and is supported across the DB2 family.

REXX stored procedures are written in the interpreted language of REXX with no precompiler, compiler, or linker. They can contain SQL, and the SQLCA is automatically included. All parameters are passed as strings, and integers are passed as numeric strings. There is only one OUT parameter allowed. This is basically simple syntax to identify to REXX a SQL string, allowing retrieval, storing, and processing of DB2 data.

Prior to the REXX/DB2 API, we needed freeware or vendor utilities to execute REXX. REXX is an interpretive string-based language. This allows for dynamic SQL execution via REXX. We can put programs and queries for DBA functions (such as REORGs) into a REXX stored procedure and have the ability to execute it remotely. The ability to quickly and easily write applications in REXX makes it a powerful scripting language to generate reports or commands from DB2 objects, such as the system catalog. Scripting languages have been used extensively on Windows and UNIX platforms to aid DBAs and developers with the development of simple tools to produce reports , generate SQL or commands, debug SQL statements, emulate table access sequences, and create simple applications. The REXX API allows this same sort of functionality on the OS/390 or z/OS platforms.

NOTE

REXX is not for performance as much as it is for function. This is an interpretive language using dynamic SQL, and the interpretive nature of REXX is suited for ease of use and power, not for high performance.

REXX API

The DB2 REXX language support includes the following application programming interfaces:

The CONNECT API, which provides the connection to the appropriate subsystem using the user ID assigned to the address space from which the REXX application is executing:

 ADDRESS DSNREXX 'CONNECT' '<subsystem id or REXX variable'

The EXECSQL API, which executes SQL statements in the REXX procedure:

 ADDRESS DSNREXX 'EXECSQL' '<SQL statement or REXX variable>'

The DISCONNECT API, which releases all acquired DB2 resources and disconnects from the subsystem:

 ADRESS DSNREXX 'DISCONNECT'

Error Handling

DB2 automatically provides a SQLCA to a REXX procedure in either a separate set of variables or a REXX compound variable, depending upon the manner in which the connection is performed. The SQLCODE or SQLSTATE variables can be tested after each SQL statement execution, as can the REXX RC return code variable, which is set according to Table 12-4.

Table 12-4. REXX RC Return Codes

Return code	Meaning
	No SQL warning or error occurred
1	A SQL warning occurred
1	A SQL error occurred

Embedding SQL in a REXX Program

You can code SQL statements in your REXX program anywhere you can issue REXX commands. The SQL statements must be directed to the DSNREXX external environment either by preceding each SQL statement with the ADDRESS DSNREXX command or by issuing the ADDRESS DSNREXX command, without any SQL command following it, to inform REXX that external commands will be going the DSNREXX environment. DB2 REXX Language Support allows all SQL statements that DB2 for OS/390 or z/OS supports, except the following:

 BEGIN DECLARE SECTION  END DECLARE SECTION DECLARE STATEMENT INCLUDE SELECT INTO WHENEVER

Each SQL statement must begin with EXECSQL and either be enclosed in single or double quotes, or be a REXX variable reference. For example,

 EXECSQL "COMMIT"  EXECSQL 'COMMIT' SQLSTMT="COMMIT" EXECSQL SQLSTMT

These are all valid executions of a COMMIT statement. In addition, SQL statements can begin or end anywhere on a line and can be continued by utilizing commas or concatenation operators and commas:

 EXECSQL,  "SET :TSTAMP = ",  "CURRENT TIMESTAMP" "EXECSQL " , " SET :TSTAMP = " , " CURRENT TIMESTAMP"

REXX DB2 Application Support provides specific names for cursors and prepared statements that must be used within your DB2 REXX program.

C1 to C100 : Cursor names for DECLARE CURSOR, OPEN, CLOSE, and FETCH statements. By default, C1 to C50 are defined with the WITH RETURN clause, and C51 to C100 are defined with the WITH HOLD clause. You can use the ATTRIBUTES clause of the PREPARE statement to override these attributes to make your cursor scrollable.

C101 to C200 : Cursor names for ALLOCATE, DESCRIBE, FETCH, and CLOSE statements that are used to retrieve result sets in a program that calls a stored procedure.

S1 to S100 : Prepared statement names for DECLARE STATEMENT, PREPARE, DESCRIBE, and EXECUTE statements.

You can use only the predefined names, and when you associate a cursor name with a statement name in a DECLARE CURSOR statement, the cursor name and the statement must have the same number. For example,

 EXECSQL 'DECLARE C1 CURSOR FOR S1'

REXX Programming Examples

Cursor Processing

The following simple example establishes a connection to DB2 and then opens and fetches from a simple cursor:

 /*REXX*/  /* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */ /* IF IT IS NOT, THEN ESTABLISH IT.                              */ /* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM)          */ /*                                                               */ /* TRACE ?I   */ 'SUBCOM DSNREXX' IF RC THEN    S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') ADDRESS DSNREXX "CONNECT" "DSN1" IF SQLCODE \= "0" THEN    DO     SAY "FAILURE TO CONNECT TO DATABASE"     EXIT 8    END /* SET A VARIABLE EQUAL TO THE DESIRED SQL STATMENT.             */ /*                                                               */ SQLSTMT="SELECT 'HELLO' FROM SYSIBM.SYSDUMMY1" /* PREPARE THE STATEMENT FOR EXECUTION BY DECLARING THE CURSOR,  */ /* DOING A PREPARE OF THE STATEMENT, AND OPENING THE CURSOR.     */ /*                                                               */ ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" IF SQLCODE \= "0" THEN    DO     SAY "DECLARE CURSOR FAILED WITH SQLCODE = " SQLCODE     EXIT 8    END "EXECSQL PREPARE S1 FROM :SQLSTMT" IF SQLCODE \= "0" THEN    DO     SAY "PREPARE FAILED WITH SQLCODE = " SQLCODE     EXIT 8    END "EXECSQL OPEN C1" IF SQLCODE \= "0" THEN    DO     SAY "OPEN FAILED WITH SQLCODE = " SQLCODE     EXIT 8    END /*                                                               */ /* FETCH LOOP. FOR EACH ROW RETURNED SHOW THE RESULT             */ /*                                                               */ JOBCNT = 0 DO UNTIL SQLCODE \= "0"    "EXECSQL FETCH C1 INTO :TESTMSG"   IF SQLCODE = "0" THEN       DO        SAY TESTMSG        END    END /* HERE THE FETCH LOOP HAS ENDED (SQLCODE <> 0), WHY?            */ /*                                                               */ SELECT      WHEN SQLCODE = 0 THEN DO        END      WHEN SQLCODE = 100 THEN DO        END      OTHERWISE        SAY "FETCH FAILED WITH SQLCODE = " SQLCODE        SAY SQLERRM    END /* ALL IS WELL, SO THE CURSOR IS CLOSED.                         */ /*                                                               */ "EXECSQL CLOSE C1" IF SQLCODE \= "0" THEN    DO     SAY "CLOSE FAILED WITH SQLCODE = " SQLCODE    END /* CLEANUP.                                                      */ /* DISCONNECT FROM THE DATABASE, AND RELEASE ALLOCATIONS.        */ /*                                                               */ ADDRESS DSNREXX "DISCONNECT" S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') EXIT 0

Processing a Result Set Returned from a Stored Procedure

The next example demonstrates a call to a stored procedure with the processing of a result set:

 /*REXX*/  /* **************************************************************/ /* TRACE ?I */ /* CLEAN UP PREVIOUS VERSIONS OF OUTPUT                         */ /*                                                              */ /* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE*/ /* IF IT IS NOT, THEN ESTABLISH IT.                             */ /* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM)         */ /*                                                              */ /* 'SUBCOM DSNREXX' */ /* IF RC THEN */    S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') ADDRESS DSNREXX "CONNECT" "DSN1" IF SQLCODE \= "0" THEN    DO     SAY "FAILURE TO CONNECT TO DATABASE"     EXIT 8    END /*                                                              */ CREATOR="YLA" ADDRESS DSNREXX EXECSQL "CALL SPRSLT (:CREATOR)" SAY "SQL CODE IS " SQLCODE IF SQLCODE \= "466" THEN    DO     SAY "CALL FAILED WITH SQLCODE = " SQLCODE     SAY "SQLERRM IS " SQLERRMC     SAY "SQLSTATE IS" SQLSTATE     SAY SQLWARN.0     SAY "SQLERRD IS " SQLERRD.1 "," SQLERRD.2 "," SQLERRD.3,         "," SQLERRD.4 "," SQLERRD.5 "," SQLERRD.6     EXIT 8    END "EXECSQL ASSOCIATE LOCATOR (:LOC1) WITH PROCEDURE CDBSP004" IF SQLCODE \= "0" THEN    DO     SAY "ASSOC FAILED WITH SQLCODE = " SQLCODE     SAY "SQLERRM IS " SQLERRMC     SAY "SQLSTATE IS" SQLSTATE     SAY SQLWARN.0     SAY "SQLERRD IS " SQLERRD.1 "," SQLERRD.2 "," SQLERRD.3,         "," SQLERRD.4 "," SQLERRD.5 "," SQLERRD.6     EXIT 8    END "EXECSQL ALLOCATE C101 CURSOR FOR RESULT SET :LOC1" IF SQLCODE \= "0" THEN    DO     SAY "ALLOC FAILED WITH SQLCODE = " SQLCODE     SAY "SQLERRM IS " SQLERRMC     SAY "SQLSTATE IS" SQLSTATE     SAY SQLWARN.0     SAY "SQLERRD IS " SQLERRD.1 "," SQLERRD.2 "," SQLERRD.3,         "," SQLERRD.4 "," SQLERRD.5 "," SQLERRD.6     EXIT 8    END "EXECSQL FETCH C101 INTO :ACTID, :CUSTID" IF SQLCODE \= "0" THEN    DO     SAY "FETCH FAILED WITH SQLCODE = " SQLCODE     SAY "SQLERRM IS " SQLERRMC     SAY "SQLSTATE IS" SQLSTATE     SAY SQLWARN.0     SAY "SQLERRD IS " SQLERRD.1 "," SQLERRD.2 "," SQLERRD.3,         "," SQLERRD.4 "," SQLERRD.5 "," SQLERRD.6     EXIT 8    END SAY "CUSTOMER ID IS:" CUSTID ", AND ACCOUT ID IS:" ACTID "EXECSQL CLOSE C101" ADDRESS DSNREXX "DISCONNECT" EXIT 0

Host Variables in an REXX Program

It is important to note that you do not need to declare host variables in an REXX program. When you need a new variable, you simply reference it in a REXX command. A colon always precedes host variable references in SQL statements. All REXX data is string data, and when REXX variables are referenced within an SQL statement, DB2 performs the appropriate data type conversion. You can also use the SQLDA to identify the appropriate data type conversion to DB2. For the most part, DB2 does a good job of converting the strings into the appropriate data types. However, if you need a string of numbers , you should use quotes to force the appropriate data type conversion. This variable will be converted into an integer:

 VAR1 = "1234"

This variable will be converted into a character string (either CHAR or VARCHAR):

 VAR2 = "'"1234" '"

When executing your REXX program, you have a choice of isolation levels by using one of four REXX packages, listed in Table 12-5.

Table 12-5. Isolation Levels in REXX Packages

Package Name	Isolation Level
DSNREXRR	Repeatable read (RR)
DSNREXRS	Read stability (RS)
DSNREXCS	Cursor stability (CS)
DSNREXUR	Uncommitted read (UR)

You can utilize the SET CURRENT PACKAGESET command to change the package that the REXX DB2 interface uses and subsequently change the isolation level.


Team-Fly

Top