Using SQL in REXX/VSE

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.

The I/O capabilities of REXX/VSE provide access to VSE libraries, sequential files and the POWER/VSE queues. However, unlike REXX/VM, REXX/VSE procedures cannot access data in DB2 tables.

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

REXXSQL Calls

REXXSQL is implemented as an external REXX function, invoked using the reference call r=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
    • If a SELECT was submitted, the fetched rows are returned in REXX stem variables. Each selected column is passed in a REXX stem with a name equal to the table columnname. The number of lines in each column stem (stem.0) is equal to the number of rows selected (REXXSQL variable _nrows).

    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
     

    Using prepped (static) SQL

    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

    A REXXSQL procedure can create a DB2 package, containing multiple SQL statements (package sections). Package creation is initiated using a REXXSQL CREATE PACKAGE call.

    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 parameter marker is designated by a question mark, for example:
    INSERT INTO <table> VALUES( ?,?)

    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.
    • Best DB2 performance is achieved when the datatype and length of each hostvariable is known at prep time. Therefore, the use of hostvariables is recommended.

    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

    Additional facilities
  • the REXXSQLH function 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 procedure to submit DB2/VM-VSE operator commands and to process the command reply.
    •