Using REXX and DB2

You can also create applications using REXX that access and manipulate DB2 data. REXX is a procedural programming language developed at IBM and used by most mainframe organizations. REXX was designed to make easy the manipulation of the kinds of symbolic objects that people normally deal with such as words and numbers .

NOTE

Although REXX began as an IBM-only language, there is now an ANSI standard for the language. The American National Standard for REXX is called "Programming Language ”REXX," and its number is X3.274-1996.

In general, you can code SQL statements in a REXX procedure wherever you can use REXX commands. The only SQL statements that are not supported are

• BEGIN DECLARE SECTION

• DECLARE STATEMENT

• END DECLARE SECTION

• INCLUDE

• SELECT INTO

• WHENEVER

Furthermore, you cannot execute a SELECT , INSERT , UPDATE , or DELETE statement that contains host variables . Instead, you must use parameter markers and then PREPARE the statement as with typical dynamic SQL statements.

Each SQL statement in a REXX procedure must begin with EXECSQL . Following the EXECSQL you must code either an SQL statement enclosed in single or double quotation marks or a REXX variable that contains the SQL statement. If the SQL statement spans multiple lines, follow the REXX rules for statement continuation. For example, you can code

 EXECSQL , "UPDATE DSN8810.EMP" , "SET BONUS = 1000" , "WHERE EMPNO = '000340'" 

NOTE

You cannot include REXX comments ( /* ... */ ) or SQL comments ( -- ) within SQL statements. However, you can include REXX comments anywhere else in the REXX procedure.

When DB2 prepares a REXX procedure that contains SQL statements, DB2 automatically includes an SQL communication area (SQLCA) in the procedure. Do not attempt to use the INCLUDE SQLCA statement to include an SQLCA in a REXX procedure. Remember, INCLUDE is not allowed when using REXX.

If you will be using any of the following SQL statements you will need to code an SQLDA in your REXX procedure:

 CALL...USING DESCRIPTOR  descriptor-name  DESCRIBE  statement  INTO  descriptor-name  DESCRIBE CURSOR  host-variable  INTO  descriptor-name  DESCRIBE INPUT  statement  INTO  descriptor-name  DESCRIBE PROCEDURE  host-variable  INTO  descriptor-name  DESCRIBE TABLE  host-variable  INTO  descriptor-name  EXECUTE...USING DESCRIPTOR  descriptor-name  FETCH...USING DESCRIPTOR  descriptor-name  OPEN...USING DESCRIPTOR  descriptor-name  PREPARE...INTO  descriptor-name  

A REXX procedure can contain multiple SQLDAs. Once again, though, you cannot use INCLUDE to bring the SQLDA into a REXX procedure.

Before attempting to code a REXX procedure to access DB2, be sure to refer to the IBM documentation for assistance.

REXX Guidelines

Consider these guidelines when building REXX procedures that access DB2.

A REXX Null Is Not a DB2 NULL

As you build REXX procedures that access DB2 data, keep in mind that a REXX null and an SQL NULL are not the same thing. The REXX language has a null string that is a string of length 0 and a null clause that is a clause that contains only blanks and comments.

Recall from Chapter 5 that the SQL NULL is a special value that denotes the absence of a value. Assigning a REXX null to a DB2 column does not make the column a DB2 NULL .

REXX Host Variables

Host variables are not declared in REXX procedures. Instead, when a new variable is needed, you simply use it in a REXX command. As with COBOL host variables, when you use a REXX variable as a host variable in an SQL statement, be sure to precede the variable name with a colon .

REXX and Data Types

All REXX data is string data. Therefore, when a REXX procedure assigns input data to a table column, DB2 converts the data from a string type to the table column type. When a REXX procedure assigns column data to an output variable, DB2 converts the data from the column type to a string type.

When you assign input data to a DB2 table column, you can either let DB2 determine the type that your input data represents, or you can use an SQLDA to tell DB2 the intended type of the input data.

To use the SQLDA to convert the string to an INTEGER , DECIMAL , FLOAT , VARCHAR , or VARGRAPHIC data type, refer to the chart of valid SQLTYPE (SQLDA field) values as shown in Chapter 12 (Table 12.2).