13.1 RLX Support for IBM’s RxSQL product ... 13-2 13.2 RXSQL CALL Statement ... 13-2 13.3 RXSQL CLOSE Statement ... 13-2 13.4 RXSQL COMMIT Statement ... 13-3 13.5 RXSQL CONNECT Statement ... 13-3 13.6 RXSQL CREATE PACKAGE and CREATE PROGRAM ... 13-3 13.7 RXSQL DECLARE Statement ... 13-3 13.8 RXSQL DESCRIBE Statement ... 13-4 13.9 RXSQL DROPSTMT Command ... 13-4 13.10 RXSQL EXEC or EXECUTE IMMEDIATE Statement ... 13-4 13.11 RXSQL EXECUTE Statement ... 13-5 13.12 RXSQL FETCH Statement ... 13-5 13.13 RXSQL NAMES Command ... 13-5 13.14 RXSQL OP Command ... 13-5 13.15 RXSQL OPEN Statement ... 13-6 13.16 RXSQL PREP Statement ... 13-6 13.17 RXSQL PURGE Statement ... 13-6 13.18 RXSQL PUT Statement ... 13-6 13.19 RXSQL ROLLBACK ... 13-7 13.20 RXSQL SQLDATE Command ... 13-7 13.21 RXSQL SQLISL Command ... 13-7 13.22 RXSQL SQLTIME Command ... 13-8 13.23 RXSQL STATE Command ... 13-8 13.24 RXSQL STMT Command ... 13-8 13.25 RXSQL TRACE Command ... 13-9 13.26 RXSQL XCALL Statement ... 13-9 13.27 RXSQL XPREP Statement ... 13-9 13.28 RXSELECT exec to display SQL query results ... 13-9 13.29 RXSQLEX exec to execute single SQL statements ... 13-9
13.1 Support for IBM's RxSQL product
RXSQL is IBM’s Procedures Language Interface to SQL/DS (also known as DB2/VM).
RLX supports many RXSQL facilities to facilitate the migration of REXX SQL procedures from a VM - SQL/DS environment to OS/390 and DB2. The following sections describe RLX support for each of the RXSQL statements, commands and execs in turn.
13.2 RXSQL CALL Statement
The RXSQL CALL statement executes SQL statements processed via a previous RXSQL PREP or SQL PREPARE statement.
NOTE: DB2 Version 4.1 introduced the SQL CALL statement with which to invoke stored procedures. For this reason, support for RXSQL CALL is discontinued and replaced by RXCALL. Both RXCALL and SQL EXECUTE are functionally equivalent to the RXSQL CALL statement.
RLX supports the RXSQL CALL statement as documented in Section 12.3 (the EXECUTE statement). Note that the USING i-variable clause is not supported. The format of the CALL service is:
RXSQL RXCALL statement-name
where statement-name identifies the prepared statement to be executed. Statement-name must identify a statement that was previously and successfully prepared. The prepared statement that is the target of execute must not be a SELECT statement.
13.3 RXSQL CLOSE Statement
The RXSQL CLOSE statement closes a cursor opened by a previous OPEN statement.
RLX fully supports the the RXSQL CLOSE statement as documented in Section 11.3.
The format of the CLOSE service is:
RXSQL CLOSE cursor-name
where cursor-name identifies the name of the cursor to be closed.
13.4 RXSQL COMMIT Statement
The RXSQL COMMIT statement terminates a logical unit of recovery and commits the database changes made by that unit of work. The SQL COMMIT statement is documented in Section 11.4. Note that the RELEASE option of RXSQL COMMIT is not supported. The format of the COMMIT service is:
RXSQL COMMIT WORK
13.5 RXSQL CONNECT Statement
The RXSQL CONNECT statement is not supported. Analogous capabilities are available in DB2 through the SQL CONNECT statement documented in Section 11.5.
Additional CONNECT capabilities are also provided through the RLX CAF primitives described in the RLX/CAF manual and the SQL SET CURRENT SQLID statement documented in Section 11.19.
13.6 RXSQL CREATE PACKAGE and CREATE PROGRAM
The RXSQL CREATE PACKAGE and CREATE PROGRAM statements create an empty package (or program) into which SQL statements can be prepared. Both CREATE PACKAGE and CREATE PROGRAM are extended dynamic SQL facilities which have no analog within a DB2 environment. As such, they are not supported.
13.7 RXSQL DECLARE Statement
The RXSQL DECLARE statement gives a name to SQL statements prepared by an RXSQL XPREP statement. RLX supports the DECLARE request in its native SQL form (as documented in Section 11.6). However, RLX does not support RXSQL DECLARE syntax with which a statement prepared through the XPREP operation is readied for execution. This is because XPREP is an extended dynamic SQL facility which has no analog within a DB2 environment.
13.8 RXSQL DESCRIBE Statement
The RXSQL DESCRIBE statement returns the names and datatypes of the columns in a prepared or declared SELECT statement. RLX supports RXSQL DESCRIBE as documented in Section 12.2 and extends this support to include non-query statements such as INSERT, UPDATE and DELETE.
The format of the DESCRIBE service is:
RLX DESCRIBE statement-name
where statement-name is one of several statement-name references documented in Section 12.2.
13.9 RXSQL DROPSTMT Command
The RXSQL DROPSTMT operation is an extended dynamic SQL facility which has no analog within a DB2 environment. As such, DROPSTMT is not supported.
13.10 RXSQL EXEC or EXECUTE IMMEDIATE Statement
The RXSQL EXEC operation prepares a character string form of a static SQL statement and then executes the statement. The format of the EXEC service is:
RXSQL EXEC string-expression
where string-expression refers to any character string. The statement string must not contain references to host variables.
The SQL EXECUTE IMMEDIATE statement (documented in Section 12.4) provides the same function as the RXSQL EXEC service. The format of the EXECUTE IMMEDIATE service is:
RXSQL EXECUTE IMMEDIATE string-expression
EXEC and EXECUTE IMMEDIATE are suitable for DCL, DDL or simple DML statements that will be executed only once. If the same statement is to be executed more than once, it is more efficient to use PREPARE and EXECUTE rather than EXECUTE IMMEDIATE.
13.11 RXSQL EXECUTE Statement
The RXSQL EXECUTE statement causes a prepared SQL statement to be executed.
RLX fully supports the the RXSQL EXECUTE statement as documented in Section 12.3. The format of the EXECUTE service is:
RXSQL EXECUTE statement-name
where statement-name identifies the prepared statement to be executed. Statement-name must identify a statement that was previously and successfully prepared. The prepared statement that is the target of execute must not be a SELECT statement nor any statement that has an equivalent RXSQL request such as COMMIT or ROLLBACK.
The prepared statement may contain host variables which may change each time the prepared statement is re-executed through EXECUTE.
13.12 RXSQL FETCH Statement
The RXSQL FETCH statement assigns column values from a row of a result table to host variables coded in your REXX exec. RLX fully supports the RXSQL FETCH statement as documented in Section 11.9. The format of the FETCH service is:
RXSQL FETCH cursor-name into <list of host variables>
where cursor-name identifies an open cursor representing a SELECT statement.
13.13 RXSQL NAMES Command
The RXSQL NAMES command returns all the statement names currently known to RLX. The value of the REXX variable SQLNAMES is set to a string of symbolic statement names separated by blanks. The RXSQL NAMES service requires no operands. Its format is:
RXSQL NAMES
Each name can be used in an RXSQL STATE request to determine the state of a statement, and in an RXSQL STMT request to obtain the text of the SQL statement..
13.14 RXSQL OP Command
The RXSQL OP statement is designed to issue SQL/DS operator commands and return the results in REXX variables. The RXSQL OP statement is not supported in the DB2 environment. Analogous capabilities are available instead through the native DB2 command interface described in Chapter 33 of the RLX/IFI manual.
13.15 RXSQL OPEN Statement
The RXSQL OPEN statement opens a cursor associated with a prepared or declared SELECT statement. RLX supports the RXSQL OPEN statement as documented in Section 11.11. Note that the USING i-variable clause is not supported. The format of the OPEN service is:
RXSQL OPEN cursor-name
where cursor-name identifies a prepared or declared SELECT statement.
13.16 RXSQL PREP Statement
The RXSQL PREP statement creates an executable SQL statement from a character string form of the statement. The PREP operation readies the SQL statement for a subsequent RXCALL, EXECUTE or OPEN operation. RLX fully supports the RXSQL PREP service through the SQL PREPARE statement documented in Section 12.5. The format of the PREP service is:
RXSQL PREP statement-name statement_string
where statement-name identifies a prepared SELECT which can be referenced by a subsequent OPEN statement or an INSERT, UPDATE or DELETE statement which can be referenced by a subsequent RXCALL or EXECUTE statement.
Statement-string is a valid SQL statement which may contain host variables.
13.17 RXSQL PURGE Command
The RXSQL PURGE command provides a means to drop a previously prepared statement and free the control blocks and storage buffers associated with the statement.
RLX supports the RXSQL PURGE statement as documented in Section 14.2. The format of the PURGE service is:
RXSQL PURGE statement-name | *
where statement-name is the symbolic name assigned to a prepared statement. RLX does not support use of the asterisk * which denotes all prepared statements. See the RLX DESCRIBE service in Section 12.2 for an additional discussion of symbolic statement names.
13.18 RXSQL PUT Statement
The RXSQL PUT operation is a SQL/DS facility which has no analog within a DB2 environment. As such, it is not supported.