Static SQL Data Manipulation Language Statement Reference
11.8 DECLARE CURSOR
11.8.1 CURSOR based processing
A cursor can be thought of as a pointer to a row in a result table. When a cursor is opened, the cursor points to the first data row in the result table. As you fetch rows from the table, the cursor advances to the next row. The cursor holds its position on that row until the next FETCH or CLOSE statement is executed. This behavior allows positioned updates or deletes of the current row being processed.
The following cursor based actions are supported:
1. Declare a cursor 2. Open a cursor 3. Fetch from a cursor 4. Close a cursor
5. Update the current cursor row 6. Delete the current cursor row
NOTE: RLX provides two additional statements in support of multi-row answer sets. The RLX DECLARE REXXSTEM service (described in Section 14.1) lets you copy SQL result tables directly into REXX stemmed arrays while the RLX DECLARE ISPFTABLE service (described in Section 4.2 of the RLX/ISPF User Guide) loads SQL query results directly into ISPF tables.
The cursor-name you chose must not be the same name as that of another prepared or declared statement. All declared cursors, REXXSTEMs and ISPFTABLEs should be uniquely named. You may, however, issue an explicit PURGE request (described in Section 14.2) to free the statement which allows you to reuse the name.
The sample exec in Figure 11.4 illustrates RLX support for cursor based facilities with which multi-row query results can be processed one row at a time.
_______________________________________________________________________
address rlx
arg creator (1)
'rlx declare table_names cursor for select name, type, dbname, tsname from sysibm.systables
where creator = :creator' (2)
'rlx open table_names' (3)
Say 'Tables owned by Authorization ID' creator
'rlx fetch table_names into :name, :type, :dbname, :tsname:ind1' (4)
Do while sqlcode = 0
Say name type dbname tsname
'rlx fetch table_names into :name, :type, :dbname, :tsname:ind1' (4)
End
'rlx close table_names' (5)
Return rc
where,
(1) The DB2 authorization ID used to parameterize the SQL query is passed to the EXEC as an argument. The REXX ARG instruction populates the REXX variable named creator with the DB2 Authorization ID passed as a parameter.
(2) DECLARE the SELECT statement and give it the name table_names.
(3) The OPEN statement references the cursor named table_names.
(4) The two FETCH statements also reference the cursor named table_names.
(5) The CLOSE statement references the cursor named table_names.
_______________________________________________________________________
Figure 11.4
11.9 DELETE
DELETE FROM table-name view-name
WHERE CURRENT OF cursor-name positioned delete:
searched delete:
DELETE FROM table-name
view-name correlation-name WHERE search-condition
WITH RR RS CS
The DELETE statement deletes rows from a table or view. RLX supports two forms of the DELETE statement:
• A searched DELETE is used to delete one or more rows as governed by an optional search condition; and
• A positioned DELETE is used to delete exactly one row, as determined by the current position of the referenced cursor-name.
Example:
Figure 11.5 illustrates the use of a searched DELETE statement that is qualified by a search condition.___________________________________________
address rlx
'rlx delete from some.table’,
‘where column1 between :low and :high'
___________________________________________
Figure 11.5
11.10 EXPLAIN
explainable-sql-statement EXPLAIN FOR
ALL PLAN
SET QUERYNO=integer
The EXPLAIN statement obtains information about access path selection for an explainable statement. A statement is explainable if it is a SELECT or INSERT statement, or the searched form of an UPDATE or DELETE statement. The SQL authorization ID of the issuer of EXPLAIN must also be the owner of a plan table (named PLAN-TABLE) into which DB2 inserts the information produced by EXPLAIN.
Example:
Explain a query.address rlx 'rlx explain plan set queryno = 110156 for' , ‘select * from sysibm.syscolumns’
11.11 FETCH
INTO host-variable ,
FETCH cursor-name
The FETCH statement positions a cursor on the next row of its result table and assigns the values of the selected columns of this row to the host variables appearing in the host variable list. The list of host variables must correspond to the list of column names specified on the DECLARE CURSOR statement.
Section 11.8.1 contains an annotated example of a FETCH statement used within the context of cursor based processing.
11.12 INSERT
INSERT INTO table-name view-name
subselect
host-variable VALUES
column-name ) (
,
constant )
( ,
NULL
special-register
WITH RR RS CS
The INSERT statement inserts rows into a table or view. The table or view can be at the current server or any DB2 subsystem with which the current server can establish a connection. Inserting a row into a view also inserts the row into the table on which the view is based. RLX supports two forms of the INSERT statement:
• The INSERT via VALUES form inserts a single row into the table or view using the values provided in the VALUE clause.
• The INSERT via SELECT form inserts one or more rows into the table or view using values selected from other tables and/or views.
Example:
Figure 11.6 illustrates the use of the VALUES form of the INSERT statement._____________________________________________
'RLX Insert Into Someones.Datatype_Table
(Integer_column, Character_column, Date_column) Values
(:integer_variable:integer_indicator, :Character_variable:Character_indicator :Date_variable:Date_indicator)'
_____________________________________________
Figure 11.6
11.13 OPEN
OPEN cursor-name
The OPEN statement opens the cursor named cursor-name.
The result table of the cursor is derived by evaluating the SELECT statement associated with cursor-name. The evaluation uses the current values of any host variables and /or special registers specified in the DECLAREd SELECT statement.
Section 11.8.1 contains an annotated example of an OPEN statement within the context of cursor based processing.
11.14 RELEASE
RELEASE location-name host-variable CURRENT
ALL PRIVATE ALL
SQL
The RELEASE statement places one or more connections in the release pending state.
The connection to be released may be specified as a literal location-name.
Alternatively, the location name may be contained in host-variable.
Example:
Figure 11.7 illustrates the use of the SQL RELEASE statement._________________________________________
address rlx
'rlx release SanJose' host_variable = ‘Toronto’
'rlx release :host_variable'
_________________________________________
Figure 11.7 The SQL RELEASE statement
11.15 ROLLBACK
ROLLBACK
WORK
The ROLLBACK statement ends a unit of recovery and backs out any changes made by SQL statements during that unit of recovery. A new unit of recovery is effectively started.
Example:
Figure 11.8 illustrates the use of the SQL COMMIT and ROLLBACK statements.____________________________________________________________
Address RLX
'RLX Insert Into Someones.Datatype_Table (1)
(Integer_column, Character_column, Date_column) Values
(:integer_variable:integer_indicator, :Character_variable:Character_indicator :Date_variable:Date_indicator)' If SQLCODE = 0 then
'rlx commit work' (2)
Else
'RLX rollback work' (3)
return
where
(1) An INSERT statement is executed.
(2) If the INSERT is successful (SQLCODE = 0), then COMMIT the work.
(3) If the INSERT does not succeed (SQLCODE not equal 0), then backout any SQL changes made during the current unit of recovery.
____________________________________________________________
Figure 11.8