REXX is an extremely versatile language. It offers efficient programming structures, powerful functions and extensive mathematical capabilities. Commands to host environments can be freely intermixed with REXX statements. This makes the language particularly suitable for command procedures, application prototyping or TCP/IP connectivity.

REXXSQL, a product developed by Software Product Research, provides an SQL interface for REXX/VM EXECs.

REXXSQL Calls

REXXSQL is implemented as an external REXX routine, invoked using the call "REXXSQL input_argument ".

The input argument is a character string, a REXX variable or expression. It contains or refers to the SQL statement to be executed. After submitting the statement to DB2, REXXSQL returns the completion status and the execution results to the invoking procedure as REXX variables or stems.

The completion status includes:

• the DB2 SQLCODE and other meaningful fields of the SQLCA (such as the SQLERRM)
• the number of rows processed by the statement in the REXXSQL variable _nrows
• the estimated statement cost in the REXXSQL variable _cost

All SQL statements valid in an application program can be submitted to REXXSQL. This includes CONNECT, SELECT, UPDATE, DELETE, INSERT, COMMIT and DDL statements like CREATE or DROP. In dynamic mode, these SQL statements may be issued against any DB2 platform that can be reached from DB2/VM-VSE. Static REXXSQL requests can be used against DB2/VM-VSE databases only, as the "extended dynamic execution" mode implied is unknown in DB2 platforms other than DB2/VM-VSE.

Submitting dynamic SQL statements

The dynamic REXXSQL mode is the simplest interface to DB2. Input to the call is a character string or a REXX expression that contains the SQL statement to be executed. If a SELECT is submitted, the fetched columns are returned in REXX stems. Execution status is returned for all statements, as described above.

REXXSQL issues an SQL "prepare" for the submitted text. A SELECT statement is processed using an OPEN / FETCH / CLOSE sequence on a dynamic cursor. Non-SELECT statements are processed using an EXECUTE IMMEDIATE call.

Sample REXX/SQL procedure

Dynamic FETCH interface

A SELECT statement that returns a large number of rows may need considerable amounts of storage for the column stems. To avoid storage problems, a FETCH interface has been designed to select one table row at a time. The interface is opened with a "REXXSQL OPEN" call. Each "REXXSQL FETCH" call transfers a single table row. A "REXXSQL CLOSE" call terminates the fetch sequence.

Sample REXX/SQL procedure

While the dynamic interface is easy to use, it incurs the overhead of DB2 "prepare" processing. Since this overhead is not trivial, SQL statements that are executed often can be prepped and executed in the "static" mode.  

Prepping SQL statements

Each SQL statement is added to the new package by means of a "REXXSQL PREPARE" call. The statement is identified by a name that will be used as a reference to the statement, when executing the package. REXXSQL uses a package control table to store the relationship between the statement name and the corresponding package section number, assigned by DB2.

If the prepped statement contains variables, the following applies:

• REXXSQL determines the datatype and length of the SELECT output hostvariables automatically, using a DESCRIBE call.
• The user must specify the input variables for INSERT and UPDATE statements and for the variables occurring in the WHERE predicates. These variables are passed either as a parameter marker or as a host variable.

A hostvariable definition starts with a semicolon, followed by the datatype and length of the hostvariable, for example:
INSERT INTO <table> VALUES ( :INTEGER , :CHAR( 8) ).

Since parameter markers do not specify the format of the hostvariable at prep time, an implicit definition must take place during execution, depending on the actual contents of the variables.

• Data enclosed in quotes are submitted with the CHARACTER datatype and the actual length of the character string.
• Numerical data are passed as INTEGER.
• Numerical data containing a decimal point are submitted as DECIMAL, with the precision and the scale of the actual value.

Executing prepped SQL statements

The "REXXSQL EXECUTE" call executes a named statement in a named package. If the prepped statement contains parameter markers or host variables, the substitution data is passed in the USING clause of the EXECUTE call. After execution, a number of status variables are available as for a dynamic execution (SQLCODE, SQLERRM, _NROWS). If the executed statement is a SELECT, the selected columns are returned in REXX stems. REXX programs can execute statements from different packages within the same LUW.

Like the dynamic interface, the static interface allows to fetch single rows. The static fetch interface is initiated with a "REXXSQL OPEN statement_name" call, eventually followed by a USING clause. Each table row is fetched by a "REXXSQL FETCH statement_name" call. A "REXXSQL CLOSE statement_name" call terminates the fetch sequence. Since each fetch sequence is identified by a statement name (which corresponds to an open cursor), multiple FETCH sequences can be open concurrently.

The "REXXSQL LOCATE packagename" call can be used to determine whether a package exists and to automatically generate it when it does not, as shown in the following example.

Sample REXX/SQL procedure using prepped SQL

Stored Procedure support provided by REXX/SQL

• using the REXXPP interface, a REXX/SQL exec can be used as a stored procedure
• using the REXXSQL CALL statement, a REXX/SQL exec can call a stored procedure

Additional facilities

• the REXXSQLH routine provides the helptext for a given SQLCODE from the SYSTEXT1 and SYSTEXT2 tables
• the LOCATE PACKAGE function tests whether a named package exists (so that the procedure can create it, if it does not exist)
• the DB2COM function allows a REXX exec to submit DB2/VM-VSE operator commands and to process the command reply.