This chapter contains detailed information on using RXSQL requests. The following table lists where the syntax diagrams and detailed descriptions can be found.
RXSQL handles some requests by passing them directly to the database manager in an EXECUTE IMMEDIATE statement. The description, syntax diagram and usage rules of these requests are described in the DB2 Server for VSE & VM SQL Reference manual. These direct requests are translated to uppercase using code page 037. To avoid translation problems across different environments, you should code these commands in uppercase.
| Note: | The DB2 Server for VM language restrictions on EXECUTE IMMEDIATE prevent the use of host variables or parameter markers for DELETE, DROP, INSERT and UPDATE requests as immediate commands. If you wish to use host variables or parameter markers for these requests, you must code these as dynamic statements and prepare them to be invoked via CALL or EXECUTE. |
| Request Type | RXSQL Request | Function | Refer To |
|---|---|---|---|
| Dynamic Statement | ACQUIRE DBSPACE | Obtains and names a dbspace. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | ALTER DBSPACE | Alters the percentage of free space. Also alters the lock size of a PUBLIC dbspace. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | ALTER TABLE | Adds a column to a table or manages referential constraints. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | Dynamic CALL | Executes an SQL statement defined by a previous Dynamic PREPARE or PREP. | page "Dynamic CALL" |
| Extended Statement | Extended CALL | Executes an SQL statement defined and stored in a package by a previous Extended PREPARE or XPREP statement and declared by an Extended DECLARE statement. | page "Extended CALL" |
| Dynamic or Extended Statement | CLOSE | Closes an open cursor. | page "CLOSE" |
| Dynamic Statement | COMMENT ON | Replaces or adds a comment to the description of a table, view, or column. | DB2 Server for VSE & VM SQL Reference |
| Dynamic or Extended Statement | COMMIT | Terminates a logical unit of work and commits the database changes made by that logical unit of work. | page "COMMIT" |
| Dynamic Statement | CONNECT | Switches application servers, passes a new authorization ID to the database manager or queries the current authorization id and application server. | page "CONNECT" |
| Dynamic Statement | CREATE INDEX | Defines an index on a table. | DB2 Server for VSE & VM SQL Reference |
| Extended Statement | CREATE PACKAGE | Creates an empty package into which SQL statements can be prepared. | page "CREATE PACKAGE" |
| Dynamic Statement | CREATE SYNONYM | Defines an alternate name for a table or view. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | CREATE TABLE | Defines a table. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | CREATE VIEW | Defines a view of one or more tables or views. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | Dynamic DECLARE | Gives a cursor_name to a previously dynamically prepared INSERT or SELECT statement. | page "Dynamic DECLARE" |
| Extended Statement | Extended DECLARE | Gives a name to a statement in a package. | page "Extended DECLARE" |
| Dynamic Statement | DELETE | Deletes zero or more rows from a table. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | Dynamic DESCRIBE | Returns the names, data types, CCSIDs, and labels of the columns referenced in a prepared SELECT statement. | page "Dynamic DESCRIBE" |
| Extended Statement | Extended DESCRIBE | Returns the names, data types, CCSIDs, and labels of the columns referenced in a SELECT statement that has been prepared into a package. | page "Extended DESCRIBE" |
| Dynamic Statement | DROP | Deletes a table, index, view, synonym, dbspace, or package. | DB2 Server for VSE & VM SQL Reference |
| Extended Statement | DROP STATEMENT or DROPSTMT | Deletes a statement from a package. | page "DROP STATEMENT or DROPSTMT" |
| Dynamic Statement | Dynamic EXECUTE | Executes an SQL statement that was defined by a previous Dynamic PREPARE statement. | page "Dynamic EXECUTE" |
| Extended Statement | Extended EXECUTE | Executes an SQL statement in a package. | page "Extended EXECUTE" |
| Dynamic Statement | EXECUTE IMMEDIATE or EXEC | Submits an SQL statement to be dynamically prepared and executed by the database manager. | page "EXECUTE IMMEDIATE or EXEC" |
| Dynamic Statement | EXPLAIN | Obtains information about the expected structure and execution performance of a DELETE, INSERT, SELECT or UPDATE statement. | DB2 Server for VSE & VM SQL Reference |
| Dynamic or Extended Statement | FETCH | Assigns values of a row of a result table to variables provided by your program. | page "FETCH" |
| Dynamic Statement | GRANT | Grants system authorities as well as privileges on packages, tables, or views. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | INSERT | Inserts one or more rows into a table. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | LABEL ON | Replaces or adds a label on the description of a table, view, or column. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | LOCK DBSPACE | Either prevents concurrent processes from changing a dbspace or prevents concurrent processes from using a dbspace. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | LOCK TABLE | Either prevents concurrent processes from changing a table or prevents concurrent processes from using a table. | DB2 Server for VSE & VM SQL Reference |
| RXSQL Command | NAMES | Returns all the cursor_names and statement_names known to RXSQL from previous Dynamic PREPARE, Dynamic DECLARE, and Extended DECLARE statements. | page "NAMES" |
| RXSQL Command | OP | Issues an operator command and returns the result in REXX variables. | page "OP" |
| Dynamic or Extended Statement | OPEN | Opens a cursor associated with a SELECT or an INSERT statement. | page "OPEN" |
| Dynamic Statement | Dynamic PREPARE or PREP | Prepares an SQL statement for a subsequent Dynamic CALL, Dynamic DECLARE, Dynamic EXECUTE, or OPEN statement. | page "Dynamic PREPARE or PREP" |
| Extended Statement | Extended PREPARE | Prepares an SQL statement into a package. | page "Extended PREPARE" |
| RXSQL Command | PURGE | Clears specified statements from RXSQL. | page "PURGE" |
| Dynamic or Extended Statement | PUT | Inserts a row of data into a table. | page "PUT" |
| Dynamic Statement | REVOKE | Revokes system authorities as well as privileges on packages, tables or views. | DB2 Server for VSE & VM SQL Reference |
| Dynamic or Extended Statement | ROLLBACK | Terminates a logical unit of work and backs out the database changes made by that unit of work. | page "ROLLBACK" |
| RXSQL Command | SQLDATE | Sets or returns the date format. | page "SQLDATE" |
| RXSQL Command | SQLISL | Sets or returns the isolation level. | page "SQLISL" |
| RXSQL Command | SQLTIME | Sets or returns the time format. | page "SQLTIME" |
| RXSQL Command | STATE | Returns the type and state values of the SQL statement or cursor you specify. | page "STATE" |
| RXSQL Command | STMT | Returns the statement associated with a given statement_name or cursor_name. | page "STMT" |
| RXSQL Command | TRACE | Sets the trace level for selected functions in RXSQL. | page "TRACE" |
| Dynamic Statement | UPDATE | Updates the values of one or more columns in zero or more rows of a table. | DB2 Server for VSE & VM SQL Reference |
| Dynamic Statement | UPDATE STATISTICS | Updates statistics on tables and indexes in system catalogs. | DB2 Server for VSE & VM SQL Reference |
| Extended Statement | XCALL | Executes an SQL statement in a package that does not reference REXX host variables. | page "XCALL" |
| Extended Statement | XPREP | Prepares an SQL statement in a package. | page "XPREP" |
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
>>-CALL--statement_name----------------------------------------->
>-----+-------------------------------------------+------------><
| .-USING-. |
'-+-------+--input_rexx_host_variable_list--'
|
The CALL statement is provided for compatibility with previous versions of DB2 RXSQL. It is functionally interchangeable with the Dynamic EXECUTE statement, although its syntax is slightly different. For a full description of this command, refer to Dynamic EXECUTE.
Execute the prepared statement UPDATE_JOB.
'RXSQL CALL UPDATE_JOB'
Execute the statement RAISE_JOB using a REXX host variable with a variable qualifier.
raise_stmt = 'UPDATE EMPVIEW SET SALARY = SALARY * 1.08',
'WHERE JOB = ?'
'EXECSQL PREPARE RAISE_JOB FROM' raise_stmt
'RXSQL CALL RAISE_JOB USING :job(CHAR(8))'
The REXX interpreter resolves raise_stmt before the request is passed to RXSQL.
>>-CALL--statement_name----------------------------------------->
>-----+-------------------------------------------+------------->
| .-USING-. |
'-+-------+--input_rexx_host_variable_list--'
>-----+------------------------------------------+-------------><
'-INTO--+-output_rexx_host_variable_list-+-'
'-rexx_host_stem_name------------'
|
The Extended CALL statement executes an SQL statement previously defined by an Extended PREPARE or XPREP statement and declared by an Extended DECLARE statement. The Extended DECLARE statement must be issued before the Extended CALL statement to associate a statement_name with the prepared statement.
Place holders may be used to discard unwanted values.
| Required Initial State | Resulting State |
|---|---|
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC DECLARED |
If there are parameter markers in the prepared SELECT statement, then the input_rexx_host_variable_list after the USING clause is used to resolve the parameter markers.
Declare and execute the UPDATE_SALARY statement. It is assumed that the UPDATE statement issued by this program has been prepared into the package in a previously run program.
'EXECSQL DECLARE UPDATE_SALARY FOR :stmt_number IN RAISE_SALARY' 'EXECSQL CALL UPDATE_SALARY'
>>-CLOSE----+-cursor_name--+-----------------------------------><
'-prepare_name-'
|
The CLOSE statement closes a cursor previously opened by an OPEN statement.
CLOSE leaves the cursor ready to be opened.
If a Dynamic DECLARE has identified a cursor_name for a dynamic prepared SELECT or INSERT statement, then CLOSE must use the cursor_name defined in the Dynamic DECLARE.
If a Dynamic DECLARE statement has not been executed for a dynamic prepared SELECT or INSERT statement, then CLOSE must use the prepare_name defined in the Dynamic PREPARE.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC OPEN | DYNAMIC PREPARED |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC DECLARED |
Close the cursor SELECT_EMPLOYEE.
'EXECSQL CLOSE SELECT_EMPLOYEE'
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-WORK-.
>>-COMMIT--+------+--+---------+-------------------------------><
'-RELEASE-'
|
The COMMIT statement commits all changes made to the
database since the beginning of the LUW or since the last COMMIT or ROLLBACK statement. All locks in the database acquired during the LUW are released. COMMIT severs the database connection if you specify the RELEASE option.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC DECLARED-ONLY | DYNAMIC DECLARED-ONLY |
| DYNAMIC UNPREPARED | DYNAMIC UNPREPARED |
| DYNAMIC PREPARED | DYNAMIC UNPREPARED |
| DYNAMIC OPEN | DYNAMIC UNPREPARED |
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC UNDECLARED |
DB2 RXSQL retrieves DATE and TIME specifications from the LASTING GLOBALV file the first time DB2 RXSQL is invoked or at the first invocation after a COMMIT RELEASE or ROLLBACK RELEASE. If your program issues a COMMIT RELEASE or ROLLBACK RELEASE and you wish to change DATE or TIME specifications using SQLINIT, you must do so before the next RXSQL statement is executed.
For example, if your execution sequence is
COMMIT RELEASE
EXECSQL ...
SQLINIT DATE(JIS)
DB2 RXSQL will not retrieve the new date format, JIS, but will continue using the same specification you had before.
The correct sequence in this case is as follows:
COMMIT RELEASE
SQLINIT DATE(JIS)
EXECSQL ...
Commit all changes made to the database.
'EXECSQL COMMIT'
>>-CONNECT------------------------------------------------------>
>-----+-----------------------------------------------------------------------+>
| .-BY-. |
| .-IDENTIFIED-+----+--. |
'--+-authorization_name-+---+--------------------+---+-password------+--'
'-variable_name------' '-variable_name-'
>-----+-----------------------+--------------------------------><
'-TO-+-server_name---+--'
'-variable_name-'
|
The CONNECT statement lets you
For more information about this statement refer to the DB2 Server for VSE & VM SQL Reference manual.
After you issue the CONNECT statement with no parameters, the SQLCA variables SQLERRM, SQLERRMC and SQLERRP are set. Both SQLERRM and SQLERRMC contain two message tokens
separated by the hex character 'FF'x. The first token is the current SQL authorization ID, and the second one is the application server name. SQLERRP contains the product code. The REXX statement for extracting the values from SQLERRM looks like:
Parse Var sqlerrm sqlid 'FF'x server .
The following examples produce the same result.
Connect as SQLDBA.
'RXSQL CONNECT SQLDBA IDENTIFIED BY SQLDBAPW'
Issue a CONNECT using the REXX variables userid and password.
userid = 'SQLDBA'
password = 'SQLDBAPW'
'EXECSQL CONNECT :userid IDENTIFIED BY :password'
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-PACKAGE--.
>>-CREATE----+----------+---+-package_name--+------------------->
'-PROGRAM--' '-variable_name-'
>-----+---------------------------------------------+----------><
| .-OPTIONs-. .-,--------. |
| .-USING-+---------+--. V | |
'-+--------------------+---+----option---+-+--'
'-variable_name-'
|
The CREATE PACKAGE statement creates an empty package.
The package is stored in the database after you issue a COMMIT statement.
CREATE PROGRAM is equivalent to CREATE PACKAGE and is provided for compatibility with prior versions of RXSQL.
Do not code either the DESCRIBE or the NODESCRIBE option on the RXSQL CREATE PACKAGE statement. RXSQL always adds the DESCRIBE option to the SQL CREATE PACKAGE statement to allow DB2 RXSQL to fetch data correctly. If you code either DESCRIBE or NODESCRIBE, the DESCRIBE which RXSQL adds will result in duplicate or conflicting options. In this case, the database manager will return an error condition.
The packages in the three following examples are created using the ISOL(CS) and BLOCK options to minimize locking and to improve performance by inserting and retrieving rows in groups.
Create a non-modifiable package. Statements must be added using the Basic Extended PREPARE or XPREP within the same LUW in which the package is created.
'RXSQL CREATE PACKAGE CHANGE_STAFF USING BLOCK ISOL(CS)' 'RXSQL XPREP IN CHANGE_STAFF :update_view' 'RXSQL COMMIT'
A number is returned to the REXX program in the variable SQLSTMTN after the XPREP indicating which section the statement was prepared into. This number can be referenced on subsequent RXSQL requests.
Create a non-modifiable package, and add an empty section. Later, SQL statements can be temporarily prepared into this empty section to dynamically execute an SQL statement.
'EXECSQL CREATE PACKAGE NEW_EMPLOYEE USING BLOCK ISOL(CS) ' 'EXECSQL PREPARE FROM NULL SETTING :section-number IN NEW_EMPLOYEE ' 'EXECSQL COMMIT '
A number is returned to the REXX program in the variable section_number after the PREPARE statement indicating which section is the empty one. This number is then referenced in subsequent Temporary Extended PREPARE statements.
Create a modifiable package. Statements may be added to the package and deleted from the package even after the LUW in which the package was created ends.
'EXECSQL CREATE PACKAGE RAISE_SALARY USING OPTIONS BLOCK MODIFY ISOL(CS)' 'EXECSQL COMMIT '
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-CURSOR-. .-FOR-. >>-DECLARE--cursor_name--+--------+--+-----+--prepare_name----->< |
The Dynamic DECLARE statement gives a cursor_name to an SQL INSERT or SELECT statement. Once you declare a cursor, you must use the cursor_name in subsequent OPEN, FETCH, PUT and CLOSE statements.
The use of the Dynamic DECLARE statement is optional. If you do not declare a cursor, then you must use the prepare_name on the OPEN, FETCH, PUT and CLOSE statements. The Dynamic DECLARE statement can be placed before or after the Dynamic PREPARE statement in your program.
| Required Initial State | Resulting State |
|---|---|
| none | DYNAMIC DECLARED-ONLY |
| DYNAMIC UNPREPARED | DYNAMIC UNPREPARED |
| DYNAMIC PREPARED | DYNAMIC PREPARED |
| DYNAMIC OPEN | DYNAMIC OPEN |
'PREPARE JACK FROM :jack_string ' 'PREPARE JILL FROM :jill_string ' 'DECLARE JILL CURSOR FOR JACK '
Prepare a SELECT statement, then DECLARE a cursor for the prepared statement.
select_ed = "SELECT * FROM EMPVIEW WHERE EDLEVEL >= 16" 'EXECSQL PREPARE SELECT_EDLEVEL FROM :select_ed' 'EXECSQL DECLARE EDLEVEL_16 CURSOR FOR SELECT_EDLEVEL'
.-CURSOR-. .-FOR-.
>>-DECLARE----+-cursor_name----+--+--------+--+-----+----------->
'-statement_name-'
.-IN-.
>------+-section_number-+---+----+--+-package_name--+----------><
'-variable_name--' '-variable_name-'
|
The Extended DECLARE statement has a dual role.
| Required Initial State | Resulting State |
|---|---|
| none | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC DECLARED |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC OPEN |
You must end the LUW and issue an explicit PURGE statement before reusing the name.
Declare the VIEW_EMPLOYEE cursor for the statement in the package RAISE_SALARY identified by the variable_name stmt_number.
'EXECSQL DECLARE VIEW_EMPLOYEE CURSOR FOR :stmt_number IN RAISE_SALARY'
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-USING-. .-NAMES--.
>>-DESCRIBE----+-cursor_name--+-----+-------+--+--------+------><
'-prepare_name-' +-LABELS-+
+-ANY----+
'-BOTH---'
|
The Dynamic DESCRIBE statement returns the names, labels, CCSIDs, and data types of the columns of a dynamically prepared statement, or a prepared and declared SELECT statement. The returned values are put into the REXX stem variables SQLDAN., SQLDAT., SQLDAC., and SQLDAL..
The prepare_name may be used only if a cursor_name has not been declared for it.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC UNPREPARED | DYNAMIC PREPARED |
| DYNAMIC PREPARED | DYNAMIC PREPARED |
| DYNAMIC OPEN | DYNAMIC OPEN |
All REXX variables with the prefix SQLDAN., SQLDAT., SQLDAC., and SQLDAL. are reset to empty strings
before the result is set. RXSQL sets the SQLDAN.0, SQLDAT.0, SQLDAC.0, and SQLDAL.0 variables to the number of columns in the select_list. This is also the number of result columns that would be returned if the prepared or declared SELECT statement is executed.
For each result column i, the following group of variables is set:
For example, a DESCRIBE statement on a SELECT statement that has two
columns in the select_list would cause the variables to be set
similar to the following:
sqldan.0 = 2
sqldat.0 = 2
sqldac.0 = 2
sqldal.0 = 2
sqldan.1 = LASTNAME
sqldat.1 = V 15
sqldac.1 = 500
sqldan.2 = SALARY
sqldat.2 = D 9 2 N
sqldac.2 = "
If BOTH option is specified:
sqldal.1 = Last_Name
sqldal.2 = Salary
In this example, you can see that column LASTNAME does not accept NULL values because there is no trailing N in SQLDAT.1 while the column SALARY does allow NULL values.
The abbreviations in the data type attributes are listed in Figure 39.
Figure 39. DB2 RXSQL Abbreviations for Data Type Attributes on DESCRIBE
Return information about the prepared SELECT statement.
select_stmt = 'SELECT * FROM' table_name 'EXECSQL PREPARE SELECT_TABLE FROM :select_stmt' 'EXECSQL DESCRIBE SELECT_TABLE USING ANY '
>>-DESCRIBE----------------------------------------------------->
>-----+-cursor_name------------------------------------+-------->
+-statement_name---------------------------------+
| .-IN-. |
'--+-section_number-+--+----+--+-package_name--+-'
'-variable_name--' '-variable_name-'
.-USING-. .-NAMES--.
>-------+-------+--+--------+----------------------------------><
+-LABELS-+
+-ANY----+
'-BOTH---'
|
The Extended DESCRIBE statement returns the names, labels, CCSIDs, and data types of the columns of a prepared SELECT statement in a package. The REXX stem variables SQLDAN., SQLDAT., SQLDAC., and SQLDAL. are set with the descriptor information in the same manner described under the Dynamic DESCRIBE statement.
| Required Initial State | Resulting State |
|---|---|
| none | none |
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC DECLARED |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC OPEN |
Same as Output for the Dynamic DESCRIBE.
Return information about the declared statement into DB2 RXSQL variables.
'EXECSQL DECLARE SELECT_EMPLOYEE CURSOR FOR 3 IN NEW_STAFF' 'EXECSQL DESCRIBE SELECT_EMPLOYEE'
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-IN-.
>>-+-DROP STATEMENT-+---+-section_number-+--+----+-------------->
'-DROPSTMT-------' '-variable_name--'
>-----+-package_name--+----------------------------------------><
'-variable_name-'
|
The DROP STATEMENT statement selectively deletes a section and its associated statement from a package.
DROP STATEMENT applies only to packages created by a CREATE PACKAGE statement with the MODIFY option.
DROPSTMT is provided for compatibility with previous versions of RXSQL and is functionally equivalent to DROP STATEMENT.
Drop the statement identified by the value of the REXX variable stmt_num_1 from the package UPDATE_SALARY.
'RXSQL DROPSTMT' stmt_num_1 'IN UPDATE_SALARY'
Drop the statement identified by the section variable stmt_number from the package REVIEW_DEPT.
'EXECSQL DROP STATEMENT :stmt_number IN REVIEW_DEPT'
>>-EXECUTE--prepare_name---------------------------------------->
>-----+---------------------------------------+----------------><
'-USING--input_rexx_host_variable_list--'
|
The Dynamic EXECUTE statement executes a prepared SQL statement.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC UNPREPARED | DYNAMIC PREPARED |
| DYNAMIC PREPARED | DYNAMIC PREPARED |
If you include an input_rexx_host_variable_list on the EXECUTE statement, RXSQL retrieves the values of the input variables. These values are used to replace the parameter markers in the SQL statement specified by the Dynamic PREPARE statement. If the SQL statement contained variable names rather than parameter markers when it was prepared, the input_rexx_host_variable_list will override the variable names specified in the prepared SQL statement.
Execute the prepared statement.
'EXECSQL EXECUTE INSERT_EMPLOYEE'
Execute the statement DELETE_EMPLOYEE using a REXX variable with a variable qualifier.
delete_stmt = 'DELETE FROM RXEMP WHERE EMPNO = ?' 'EXECSQL PREPARE DELETE_EMPLOYEE FROM :delete_stmt' 'EXECSQL EXECUTE DELETE_EMPLOYEE USING :emp_number(CHAR(6))'
.-IN-.
>>-EXECUTE----+-section_number-+--+----+----+-package_name--+--->
'-variable_name--' '-variable_name-'
>-----+---------------------------------------+----------------->
'-USING--input_rexx_host_variable_list--'
>-----+------------------------------------------+-------------><
'-INTO--+-output_rexx_host_variable_list-+-'
'-rexx_host_stem_name------------'
|
The Extended EXECUTE statement executes an SQL statement that was previously inserted into a package by an Extended PREPARE statement. It allows you to specify both input and output variables.
Place holders may be used to discard unwanted values.
| Required Initial State | Resulting State |
|---|---|
| none | none |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC DECLARED |
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC UNDECLARED |
If there are parameter markers in the prepared SELECT statement, then the USING input_rexx_host_variable clause is used to resolve the parameter markers.
Issue statement 3 in the package NEW_EMPLOYEE.
'EXECSQL EXECUTE 3 IN NEW_EMPLOYEE',
'USING :emp_data INDICATOR :emp_data_ind'
>>-+-EXECUTE IMMEDIATE-+---+-sql_statement-+------------------->< '-EXEC--------------' '-variable_name-' |
The EXECUTE IMMEDIATE statement submits an SQL statement to be dynamically prepared and executed by the database manager.
Since RXSQL passes the SQL statement to the database manager with no argument substitution and no data transfer, the statement must not contain variable_names or parameter markers.
Submit the SQL statement CREATE VIEW to the database manager to be executed.
create_view = 'CREATE VIEW EMP_VIEW',
'(EMPNO, FIRSTNME, MIDINIT, LASTNAME,',
'WORKDEPT, PHONENO, JOB, EDLEVEL)',
'AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,',
'PHONENO, JOB, EDLEVEL',
'FROM RXEMP'
'EXECSQL EXECUTE IMMEDIATE :create_view'
See the DB2 Server for VSE & VM SQL Reference manual for details.
>>-FETCH----+-cursor_name--+------------------------------------>
'-prepare_name-'
.-INTO-.
>-------+------+--+-output_rexx_host_variable_list-+-----------><
'-rexx_host_stem_name------------'
|
The FETCH statement obtains the next row from the result table of the
SELECT statement that was processed by an OPEN statement.
If a Dynamic DECLARE has been issued, your program must use the cursor_name on the Dynamic FETCH statement.
The prepare_name can be used only if a Dynamic PREPARE has given a prepare_name to the SELECT statement and a Dynamic DECLARE has not associated a cursor with the SELECT statement before the statement was opened.
If the number of columns in the active set or result table is
| Note: | In other languages supported by the database manager the number of output variables must match the number of columns in the result table. |
Placeholders may be used to discard unwanted columns from the active set.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC OPEN | DYNAMIC OPEN |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC OPEN |
The variables provided on the FETCH statement are set with the retrieved data.
The following examples illustrate a variety of ways in which data can be fetched using a cursor.
Get data from selected columns of the result table into REXX variables. Indicator variables are used to see if the job_title, phone_number, and sal variables contain nulls. A variable qualifier is used on the job_title variable to ensure that it is returned using CCSID 037. Placeholders are used to avoid setting variables with unwanted data.
'EXECSQL FETCH SELECT_EMPLOYEE INTO :job_title :job_indicator(CCSID 037),',
':phone_number :phone_indicator,., :lname ,.,.,:sal :sal_indicator'
Get data from selected columns of the result table into REXX variables.
'RXSQL FETCH EMPLOYEE_INFO :emp :fname :mid :lname :job'
Get data from all columns of the result table into the emp_stats. stem variable.
'EXECSQL FETCH EMPLOYEE_INFO INTO :emp_stats.'
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
>>-NAMES------------------------------------------------------->< |
The NAMES command returns all the statement_names, prepare_names and cursor_names known to DB2 RXSQL from Dynamic PREPARE statements and Dynamic and Extended DECLARE statements.
This statement sets the REXX variable RXSQLNAMES to a string of names separated by blanks. You may then use each cursor_name or statement_name in a STATE request to determine the state of each statement or, in a STMT command, to obtain the assigned SQL statement.
The RXSQLNAMES variable is set. When there are no names known to RXSQL, RXSQLNAMES will be set to a string of length zero (a null string).
Display the names of all prepared and declared statements that have not been purged.
'EXECSQL NAMES' Say rxsqlnames
Output similar to the following is displayed.
+--------------------------------------------------------------------------------+ |STATEMENT_1 STATEMENT_2(CURSOR_2) STATEMENT_3(CURSOR_3) | +--------------------------------------------------------------------------------+
>>-OP operator_command----------------------------------------->< |
The OP command issues an operator command and returns the result in REXX variables.
For example, the SHOW and COUNTER operator commands monitor the DB2 Server for VM system operation.
RXSQL passes the string of characters that follows the keyword OP, directly to the database manager. Refer to the DB2 Server for VSE & VM Operation manual for an explanation of the valid operator commands.
SQLOP.0 is set with the number of lines returned. SQLOP.1 contains the first line, SQLOP.2 contains the second line, and so on.
Issue the operator command SHOW USERS and display the contents of the stem variable sqlop..
'EXECSQL OP SHOW USERS'
Do i=1 to sqlop.0
Say sqlop.i
End
Output similar to the following is displayed.
+--------------------------------------------------------------------------------+ |Status of connected users: | | 2 users are connected to application server. | | 1 Users are active. | | User ID: MERCIER SQL ID: MERCIER | | 0 Users are waiting. | | 1 Users are inactive. | | User ID: HDOBSON SQL ID: HDOBSON | | 4 Agents are available. | | 3 User connections are available. | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
>>-OPEN---+-cursor_name--+-------------------------------------->
'-prepare_name-'
>-----+-------------------------------------------+------------><
| .-USING-. |
'-+-------+--input_rexx_host_variable_list--'
|
The OPEN statement opens a cursor associated with either a declared or prepared SELECT statement (query_cursor) or a declared or prepared INSERT statement (insert_cursor).
For a query_cursor, when the OPEN statement is executed, the cursor is positioned before the first row of the result table defined by the SELECT statement. Each execution of a FETCH statement moves the cursor forward and retrieves the next row of the result table.
For an insert_cursor, when the OPEN statement is executed, block input is established on the tables. Each execution of a PUT statement moves the cursor forward and inserts a row into the input block.
If a Dynamic DECLARE has identified a cursor_name for a dynamic prepared SELECT or INSERT statement, then OPEN must use the cursor_name defined in the Dynamic DECLARE.
If a Dynamic DECLARE statement has not been executed for a dynamic prepared SELECT or INSERT statement, then OPEN must use the prepare_name defined in the Dynamic PREPARE.
If the prepared SQL statement is a SELECT statement, any parameter markers or variable_names in the SQL statement are resolved by RXSQL with values in the input_rexx_host_variable_list on the OPEN statement. If there is no rexx_host_variable_list supplied and variable_names are included in the dynamic prepared SQL statement, RXSQL retrieves the values of the variable names from REXX and these values are used when OPEN is executed.
If the prepared SQL statement is an INSERT statement, the USING clause is ignored. Any parameter markers or variable_names in the SQL statement will be resolved by RXSQL with values in the USING clause on the PUT statement.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC UNPREPARED | DYNAMIC OPEN |
| DYNAMIC PREPARED | DYNAMIC OPEN |
| DYNAMIC OPEN | DYNAMIC OPEN |
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC OPEN |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC OPEN |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC OPEN |
RXSQL fetches the values of input_rexx_host_variables or the values of variable_names in the prepared SQL statement and passes them to the database manager as input values for SELECT.
Once the cursor is purged from DB2 RXSQL, you must end the LUW before issuing the second DECLARE and OPEN by issuing a COMMIT or ROLLBACK statement.
In this part of a REXX program, data is read from the file EMPLOYEE INPUT and inserted into the table RXEMP. The INSERT statement is prepared with parameter markers which are resolved on the PUT statement. Variable qualifiers are used so the program does not have to surround the input values with: "'". The DECLARE statement is not used in this example.
insert_row = 'INSERT INTO RXEMP VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
'EXECSQL PREPARE INSERT_EMPLOYEE FROM :insert_row'
'EXECSQL OPEN INSERT_EMPLOYEE'
/* Begin loop */
/* Read data from file */
'EXECIO 1 DISKR EMPLOYEE INPUT * (LIFO'
/* Get the data into REXX variables */
Parse Upper Pull emp fname mid lname wdpt wdpt_ind,
ph ph_ind hire hire_ind job job_ind,
ed sex sex_ind birth birth_ind,
sal sal_ind bon bon_ind comm comm_ind .
'EXECSQL PUT INSERT_EMPLOYEE USING',
':emp(CHAR(6)), :fname(VARCHAR(12)),',
':mid(CHAR(1)), :lname(VARCHAR(15)),',
':wdpt :wdpt_ind (CHAR(3)), :ph :ph_ind (CHAR(4)),',
':hire :hire_ind (CHAR(8)), :job :job_ind (CHAR(8)),',
':ed(SMALLINT), :sex :sex_ind (CHAR(1)),',
':birth :birth_ind (CHAR(8)), :sal :sal_ind (DECIMAL(9,2)),',
':bon :bon_ind (DECIMAL(9,2)), :comm :comm_ind (DECIMAL(9,2))'
/* End loop */
'EXECSQL CLOSE INSERT_EMPLOYEE'
.-FROM-. >>-+-PREPARE-+--prepare_name--+------+----+-sql_statement-+---->< '-PREP----' '-variable_name-' |
The Dynamic PREPARE statement prepares an SQL statement for a subsequent Dynamic CALL, Dynamic DECLARE, Dynamic DESCRIBE, Dynamic EXECUTE,
OPEN, or PURGE request. PREP is provided as a synonym to support programs written for previous versions of RXSQL.
A Dynamic PREPARE statement cannot use the same prepare_name, cursor_name, or statement_name as that of another prepared or declared statement unless the state of the previously prepared statement is DYNAMIC UNPREPARED or DYNAMIC DECLARED-ONLY. If it is any other state, you must issue a PURGE command before reusing the name.
If the SQL statement is a SELECT, INSERT, UPDATE or DELETE statement, it can contain variable_names in the WHERE, VALUES or SET clause. RXSQL fetches the values of these variables before a Dynamic CALL, Dynamic EXECUTE, OPEN, or PUT statement is passed to the database manager to be executed. If you provide an input_rexx_host_variable_list on a Dynamic CALL, Dynamic EXECUTE, OPEN, or PUT statement, the input_rexx_host_variables override any variables named in the SQL statement on the Dynamic PREPARE statement.
You can also use parameter markers (?)
to denote the locations of REXX host variables within the SQL statement. You must then provide an input_rexx_host_variable_list on the Dynamic CALL, Dynamic EXECUTE, OPEN (for FETCH), or PUT statement. These rexx_host_variables replace the parameter markers when the CALL, EXECUTE, OPEN, or PUT statement is executed. The rules for using parameter markers are documented in the DB2 Server for VSE & VM SQL Reference manual.
| Required Initial State | Resulting State |
|---|---|
| none | DYNAMIC PREPARED |
| DYNAMIC UNPREPARED | DYNAMIC PREPARED |
| DYNAMIC DECLARED_ONLY | DYNAMIC PREPARED |
RXSQL retrieves the variable phone_number when the PREPARE statement is invoked and retrieves the value of the variable emp when the OPEN statement is invoked.
phone_number = "SELECT EMPNO, PHONENO FROM RXEMP WHERE EMPNO = :emp" 'EXECSQL PREPARE FIND_PHONENO FROM :phone_number' 'EXECSQL OPEN FIND_PHONENO'
OPEN was invoked here without a DECLARE to demonstrate that DECLARE is optional.
Basic Extended PREPARE .-SETTING :SQLSTMTN-----.
>>-PREPARE FROM statement_variable---+-----------------------+-->
'-SETTING variable_name-'
.-IN-.
>----+----+----+-package_name--+-------------------------------->
'-variable_name-'
>-----+---------------------------------+----------------------><
'-USING----attributes_variable----'
Single Row Extended PREPARE .-ROW-.
>>-PREPARE SINGLE--+-----+----FROM statement_variable----------->
.-SETTING :SQLSTMTN-----. .-IN-.
>-----+-----------------------+--+----+----+-package_name--+---->
'-SETTING variable_name-' '-variable_name-'
>-----+---------------------------------+----------------------><
'-USING----attributes_variable----'
Empty Extended PREPARE .-SETTING :SQLSTMTN-----. .-IN-.
>>-PREPARE FROM NULL----+-----------------------+--+----+------->
'-SETTING variable_name-'
>-----+-package_name--+----------------------------------------><
'-variable_name-'
Temporary Extended PREPARE >>-PREPARE FROM statement_variable FOR---+-section_number-+----->
'-variable_name--'
.-IN-.
>----+----+----+-package_name--+-------------------------------><
'-variable_name-'
|
The Basic Extended PREPARE and the Single Row Extended PREPARE statements permit an SQL statement to be prepared and stored in a package for later execution.
The Empty Extended PREPARE statement is used to provide support for Dynamic SQL statements. It is used in conjunction with the Temporary Extended PREPARE statement.
The Basic, Single Row and Empty Extended PREPARE statements return a number to a REXX variable that designates in which section the statement is stored. This number is then used in the Extended DECLARE statement, the XCALL statement, the Extended EXECUTE statement, the DROP STATEMENT statement or the Temporary Extended PREPARE statement.
You can use the Single Row Extended PREPARE statement on any SELECT statement that will return only one row. The Extended CALL or Extended EXECUTE statement with the INTO clause can be used to fetch the single row without using the OPEN, FETCH, CLOSE sequence of statements.
The Empty Extended PREPARE statement creates an indefinite section in the package. You can temporarily fill this section with an SQL statement in a subsequent logical unit of work by issuing a Temporary Extended PREPARE statement.
If the statement is a SELECT, INSERT, UPDATE, or DELETE statement, parameter markers (?) can be used to denote the locations of variable_names within the SQL statement. You must provide an input_rexx_host_variable_list on the Extended CALL, Extended EXECUTE, OPEN, or PUT statement to give a value to all parameter markers. Variable_names are not allowed in the SQL statement.
Do not use the DT (date), TM (time), or TS (timestamp) types that are returned by the DESCRIBE statement. Use the C n or V n types instead. An integer must be substituted for the m and the n in the following abbreviation list.
Figure 40. Extended PREPARE Attributes Abbreviations
| ||||||||||||||||||||||||||||||||||||||||||||
Specifying Attributes of Input Data that May Be Null
To specify that the input data corresponding to a parameter marker may be null, place an N after the SQL data type abbreviation. If you do not place an N after the SQL data type abbreviation, the default SQL data type will be such that null input data values are not allowed.
| Note: | If you specify an N, the application server may incur some extra processing overhead. |
Keywords and numbers must be separated by blanks. For example:
attribute = 'C 3 N C 4 N CCSID 500 D 6 2'
Specifying the CCSID in Attributes Variables
The CCSID may be specified in the USING attributes_variable clause in the Extended PREPARE or XPREP statement. To specify the CCSID for the input data corresponding to a parameter marker, the keyword CCSID followed by the CCSID number n must be placed in the string following the appropriate SQL data type abbreviation.
Keywords and numbers must be separated by blanks. For example:
attribute = 'C 3 CCSID 500 S V 12 N CCSID 65535 V 20 D 6 2 C 1'
For more information on CCSID or coded character sets, refer to the DB2 Server for VSE & VM SQL Reference manual.
SQLSTMTN is set to the integer value of the statement number assigned by the database manager unless another REXX variable was specified in the SETTING variable_name clause. The Extended DECLARE, DROP STATEMENT, XCALL, Extended EXECUTE and temporary Extended PREPARE statements use the integer value to specify the corresponding prepared statement or indefinite section of the package.
| Note: | A cursor_name that is a reserved word when used in an DB2 Server for VM statement must be delimited with double quotes. |
Prepare a SELECT statement into the package DEPT_VIEW. RXSQL puts the section number into the REXX variable dept_stmt_num.
select_dept = "SELECT * FROM RXEMP WHERE WORKDEPT = ?"
'EXECSQL PREPARE FROM :select_dept',
'SETTING :dept_stmt_num IN DEPT_VIEW'
Prepare an empty section into the package identified by the package variable emp_package. DB2 RXSQL puts the section number in the REXX variable emp_stmt_num.
'EXECSQL PREPARE FROM NULL SETTING :emp_stmt_num IN :emp_package'
Prepare a DELETE statement, filling in an empty section in the package CHANGE_STAFF.
remove_employee = "DELETE FROM RXEMP WHERE EMPNO = ?"
'EXECSQL PREPARE FROM :remove_employee',
'FOR :stmt_num_1 IN CHANGE_STAFF'
Prepare a SELECT statement that will return only one row.
count_ed = "SELECT COUNT(*) FROM EMPVIEW WHERE EDLEVEL = ?"
attributes = "S"
'EXECSQL PREPARE SINGLE ROW FROM :count_ed IN EMPLOYEE_STATS',
'USING :attributes'
.-,-------------------.
V |
>>-PURGE----+----+-statement_name-+--+-+-----------------------><
| +-prepare_name---+ |
| '-cursor_name----' |
'- * --------------------'
|
The PURGE command clears one or more statements from RXSQL temporary storage allowing them to be reused.
In Dynamic SQL, if an SQL statement has both a prepare_name and a corresponding cursor_name, the purging of either name will clear the SQL statement and both names from RXSQL temporary storage immediately.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC DECLARED-ONLY | none |
| DYNAMIC UNPREPARED | none |
| DYNAMIC PREPARED | none |
| DYNAMIC OPEN | none |
| EXTENDED-DYNAMIC UNDECLARED | none |
| EXTENDED-DYNAMIC DECLARED | none |
| EXTENDED-DYNAMIC OPEN | none |
Clear the statements SELECT_EMPLOYEE and INSERT_EMPLOYEE from RXSQL temporary storage.
'EXECSQL PURGE SELECT_EMPLOYEE, INSERT_EMPLOYEE'
>>-PUT--+-cursor_name--+---------------------------------------->
'-prepare_name-'
>-----+--------------------------------------------+-----------><
| .-USING--. |
'-+--------+--input_rexx_host_variable_list--'
'-FROM---'
|
The PUT statement performs an SQL INSERT.
It is valid only for a block input statement processed by an OPEN statement.
If a Dynamic DECLARE has been issued, your program must use the cursor_name on the Dynamic PUT statement.
The prepare_name must be used if a Dynamic PREPARE has given a prepare_name to the INSERT statement and a Dynamic DECLARE has not associated a cursor with the INSERT statement before the statement was opened.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC OPEN | DYNAMIC OPEN |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC OPEN |
RXSQL fetches the values of input_rexx_host_variables or the values of variable_names in the prepared SQL statement and passes them to the database manager as input values.
For example, if the first PUT statement has a variable NUM with 4 as its value, DB2 RXSQL passes the value to the database manager with a datatype of INTEGER. On a subsequent PUT statement, if NUM has 4.1 as its value. DB2 RXSQL passes the value to the database manager with a datatype of DECIMAL(2,1).
When blocking is on, the database manager returns an error indication to RXSQL indicating that the data type has changed. When this occurs, DB2 RXSQL CLOSEs the insert_cursor, OPENs the insert_cursor again, and retries the PUT command. If PUT was invoked using EXECSQL invocation, DB2 RXSQL returns a warning indication. While this implementation bypasses the error, it may affect performance.
In this part of a REXX program, data is read from the file EMPLOYEE INPUT and inserted into the table RXEMP. The INSERT statement is prepared with parameter markers which are resolved on the PUT statement. Variable qualifiers are used to indicate what type of data is being inserted.
insert_row = 'INSERT INTO RXEMP VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
'EXECSQL PREPARE INSERT_EMPLOYEE FROM :insert_row'
'EXECSQL OPEN INSERT_EMPLOYEE'
/* Begin loop */
/* Read data from file */
'EXECIO 1 DISKR EMPLOYEE INPUT * (LIFO'
/* Get the data into REXX variables */
Parse Upper Pull emp fname mid lname wdpt ph hire job,
ed sex birth sal bon comm .
'EXECSQL PUT INSERT_EMPLOYEE USING',
':emp(CHAR(6)), :fname(VARCHAR(12)),',
':mid(CHAR(1)), :lname(VARCHAR(15)),',
':wdpt(CHAR(3)), :ph(CHAR(4)),',
':hire(CHAR(8)), :job(CHAR(8)),',
':ed(SMALLINT), :sex(CHAR(1)),',
':birth(CHAR(8)), :sal(DECIMAL(9,2)),',
':bon(DECIMAL(9,2)), :comm(DECIMAL(9,2))'
/* End loop */
'EXECSQL CLOSE INSERT_EMPLOYEE'
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-WORK-.
>>-ROLLBACK--+------+--+---------+-----------------------------><
'-RELEASE-'
|
The ROLLBACK statement backs out all changes made to the database
since the LUW began or since the last COMMIT or ROLLBACK statement. All locks in the database are removed. If you specify RELEASE, the database connection is severed.
| Required Initial State | Resulting State |
|---|---|
| DYNAMIC DECLARED-ONLY | DYNAMIC DECLARED-ONLY |
| DYNAMIC UNPREPARED | DYNAMIC UNPREPARED |
| DYNAMIC PREPARED | DYNAMIC UNPREPARED |
| DYNAMIC OPEN | DYNAMIC UNPREPARED |
| EXTENDED-DYNAMIC UNDECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC DECLARED | EXTENDED-DYNAMIC UNDECLARED |
| EXTENDED-DYNAMIC OPEN | EXTENDED-DYNAMIC UNDECLARED |
DB2 RXSQL retrieves DATE and TIME specifications from the LASTING GLOBALV file the first time DB2 RXSQL is invoked or at the first invocation after a COMMIT RELEASE or ROLLBACK RELEASE. If your program issues a COMMIT RELEASE or ROLLBACK RELEASE and you wish to change DATE or TIME specifications using SQLINIT, you must do so before the next RXSQL statement is executed.
For example, if your execution sequence is
ROLLBACK RELEASE
EXECSQL ...
SQLINIT DATE(JIS)
DB2 RXSQL will not retrieve the new date format, JIS, but will continue using the same specification you had before.
The correct sequence in this case is as follows:
ROLLBACK RELEASE
SQLINIT DATE(JIS)
EXECSQL ...
Cancel the changes made to the database since the LUW began and release the database connection.
'EXECSQL ROLLBACK RELEASE'
>>-SQLDATE----+-------+----------------------------------------><
+-Eur---+
+-Iso---+
+-Jis---+
+-Local-+
+-Reset-+
+-Usa---+
'-*-----'
|
The SQLDATE command sets the date format for the database manager or returns the current setting.
If you specify a format, DB2 RXSQL sets a flag so that any date output columns will be converted into the specified format. If you do not specify a format, the current format is returned in the REXX variable SQLDATE.
The valid formats are:
For dynamic statements, the format is taken from the LASTING GLOBALV file. If DB2 RXSQL fails to retrieve the format from this file, the date format is set to ISO.
For extended dynamic statements, the date format is set to be the same as the format in effect when the package was created.
If no option is provided, the REXX variable SQLDATE is set to a single character indicating the current date format, with one exception. If the current format is Reset, SQLDATE is set to the word RESET.
Set the date format to the Japanese industrial standard.
'EXECSQL SQLDATE J'
>>-SQLISL----+----+--------------------------------------------><
+-Rr-+
+-Cs-+
'-Ur-'
|
The SQLISL command sets the isolation level for the database manager or returns the current setting.
If you specify an option, the isolation level is set to that option; otherwise the current two-character isolation level value is returned in the REXX variable SQLISL in uppercase.
Once you specify an isolation level, any new cursor operations will be performed at that level.
The REXX variable SQLISL is set to the current isolation level if no input is provided.
Set the isolation level to cursor stability.
'EXECSQL SQLISL C'
>>-SQLTIME----+-------+----------------------------------------><
+-Eur---+
+-Iso---+
+-Jis---+
+-Local-+
+-Reset-+
+-Usa---+
'-*-----'
|
The SQLTIME command sets or returns the time format.
If you specify a format, an internal variable is set. RXSQL converts any time output columns into the desired format. If you do not specify a format, the current format is returned in the REXX variable SQLTIME.
The valid formats are:
For dynamic statements, the format is taken from the LASTING GLOBALV file. If DB2 RXSQL fails to retrieve the format from this file, the time format is set to ISO.
For extended dynamic statements, the time format is set to be the same as the format in effect when the package was created.
If no option is provided, the REXX variable SQLTIME is set to a single character indicating the current time format, with one exception. If the current format is Reset, SQLTIME is set to the word RESET.
Set the time format to the European standard.
'EXECSQL SQLTIME E'
'EXECSQL SQLTIME'
Say 'SQLTIME is' SQLTIME
/* produces SQLTIME is E */
>>-STATE----+-statement_name-+---------------------------------><
+-cursor_name----+
'-prepare_name---'
|
The STATE command returns the type and state values of the SQL
DB2 RXSQL sets the REXX variable RXSQLSTATE with a character
string consisting of the statement type and state separated by a blank.
The possible type and state combinations are as follows.
| Type | State |
|
|---|---|---|
| DYNAMIC | DECLARED-ONLY | This dynamic statement has been declared, but not prepared |
| DYNAMIC | UNPREPARED | This dynamic statement has become unprepared after COMMIT or ROLLBACK |
| DYNAMIC | PREPARED | This dynamic statement has been prepared |
| DYNAMIC | OPEN | This dynamic cursor statement has been opened |
| EXTENDED-DYNAMIC | UNDECLARED | This Extended Dynamic statement has been declared with DB2 RXSQL Extended
DECLARE, but either
|
| EXTENDED-DYNAMIC | DECLARED | This Extended Dynamic statement has been declared and opened, but its state changed to DECLARED after a CLOSE |
| EXTENDED-DYNAMIC | OPEN | This Extended Dynamic cursor statement has been opened |
When an error in processing occurs, RXSQLSTATE is set to an empty string.
Display the type and the state of the statement VIEW_DEPT.
'EXECSQL STATE VIEW_DEPT' Say rxsqlstate
Output similar to the following is displayed.
+--------------------------------------------------------------------------------+ |DYNAMIC PREPARED | +--------------------------------------------------------------------------------+
>>-STMT----+-statement_name-+----------------------------------><
+-cursor_name----+
'-prepare_name---'
|
The STMT command returns the SQL statement associated with a given statement_name, cursor_name, or prepare_name.
The statement is put into the variable RXSQLSTMT.
If the statement is dynamic, the variable RXSQLSTMT is set with the statement string value with one exception; if the state is DYNAMIC DECLARED-ONLY, RXSQLSTMT is set with an empty string.
If the statement is extended dynamic, RXSQLSTMT is set with the section number and package name associated with the statement.
When an error in processing occurs RXSQLSTMT is set to a null string.
nn IN package_name
where nn is the section number.
Display the dynamic statement associated with the cursor SELECT_EMPLOYEE.
'EXECSQL STMT SELECT_EMPLOYEE' Say rxsqlstmt
Output similar to the following is displayed.
+--------------------------------------------------------------------------------+ |SELECT * FROM EMPVIEW WHERE SALARY < :salary AND JOB = :job | +--------------------------------------------------------------------------------+
.-,----------------------------.
V |
>>-TRACE-------function_number trace_level--+------------------><
|
The TRACE command sets the trace level (0 to 3) for one or all
the functions in DB2 RXSQL. It is provided to aid IBM support personnel in problem determination.
The function numbers correspond to the following functions:
The trace levels correspond to the following events:
You can specify multiple pairs of function numbers and trace levels on one statement.
The TRACE command displays the trace information to the terminal unless redirected by the CP SPOOL command.
Specifying a level will trace all points for levels with lower numbers. For example, if you specify trace level 2, all level 2 and level 1 trace points will be displayed.
Set the trace level for all components at trace level 2.
'EXECSQL TRACE 0 2'
See the DB2 Server for VSE & VM SQL Reference manual for details.
See the DB2 Server for VSE & VM SQL Reference manual for details.
.-IN-.
>>-XCALL----+-section_number-+--+----+----+-package_name--+----><
'-variable_name--' '-variable_name-'
|
The XCALL statement processes an SQL statement that was previously inserted into a package by an Extended PREPARE statement.
XCALL is provided for compatibility with previous versions of RXSQL and it does not support input or output rexx_host_variables. If you wish to use input or output rexx_host_variables with an extended dynamic statement, you may use the Extended EXECUTE statement, or the Extended DECLARE statement in combination with the Extended CALL statement.
Issue the statement in the package SALARY_REVIEW identified by the REXX variable stmt_number.
'EXECSQL XCALL :stmt_number IN SALARY_REVIEW'
Issue the statement identified by the value of the variable section_no in the package RAISE_SALARY.
section_no = 1 'RXSQL XCALL' section_no 'IN RAISE_SALARY' /* REXX resolves this to 'RXSQL XCALL 1 IN RAISE_SALARY' */
Basic XPREP .-SETTING :SQLSTMTN-----. .-IN-.
>>-XPREP----+-----------------------+--+----+------------------->
'-SETTING variable_name-'
>-----+-package_name--+---+---------------------------------+--->
'-variable_name-' '-USING----attributes_variable----'
>-----+-sql_statement------+-----------------------------------><
'-statement_variable-'
Single Row XPREP .-ROW-. .-SETTING :SQLSTMTN-----. .-IN-.
>>-XPREP SINGLE--+-----+----+-----------------------+--+----+--->
'-SETTING variable_name-'
>-----+-package_name--+---+---------------------------------+--->
'-variable_name-' '-USING----attributes_variable----'
>-----+-sql_statement------+-----------------------------------><
'-statement_variable-'
Empty XPREP .-SETTING :SQLSTMTN-----. .-IN-.
>>-XPREP----+-----------------------+--+----+------------------->
'-SETTING variable_name-'
.-NULL-.
>-----+-package_name--+--+------+------------------------------><
'-variable_name-'
Temporary XPREP .-IN-.
>>-XPREP FOR----+-section_number-+--+----+----+-package_name--+->
'-variable_name--' '-variable_name-'
>-----+-sql_statement------+-----------------------------------><
'-statement_variable-'
|
XPREP is provided for compatibility with previous versions of DB2 RXSQL. It is functionally interchangeable with the Extended PREPARE statement, although its syntax is somewhat different.
The descriptions on how to use XPREP, and all metavariables except the following, can be found under Extended PREPARE.
If the statement is a SELECT, INSERT, UPDATE, or DELETE statement, parameter markers (?) can be used to denote the locations of variables within the SQL statement. You must provide a list of variable names on the Extended CALL, Extended EXECUTE, OPEN, or PUT statement to replace the parameter markers. Do not use variable_names in the SQL statement.
Prepare the UPDATE statement identified by the statement variable salary_stmt into the package UPDATE_SALARY using the variable attributes_list to specify the data types and length attributes for each parameter marker.
salary_stmt = "UPDATE RXEMP SET SALARY = SALARY * ? WHERE EMPNO = ?",
" AND JOB <> 'MANAGER'"
attributes_list = "D 9 2 C 6"
'EXECSQL XPREP UPDATE_SALARY USING :attributes_list :salary_stmt'
Prepare into the package SALARY_REVIEW a SELECT statement that returns only one row of data. RXSQL returns the statement number into the variable salary_statement.
average_salary = 'SELECT AVG(SALARY) FROM EMPVIEW WHERE JOB = ?'
'EXECSQL XPREP SINGLE ROW SETTING :salary_statement IN SALARY_REVIEW',
':average_salary'
Prepare an empty section into the package.
'EXECSQL XPREP :package_name NULL'
Fill in the empty section identified by the section variable stmt_number in the package CHANGE_STAFF.
delete_employee = 'DELETE FROM RXEMP WHERE EMPNO = ?' 'EXECSQL XPREP FOR :stmt_number IN CHANGE_STAFF :delete_employee'