Data stored by the database manager can be accessed by Static, Dynamic or Extended Dynamic SQL statements. RXSQL supports only Dynamic and Extended Dynamic SQL. Dynamic SQL statements are prepared and executed when your program is run and the operational form of the prepared statements does not persist beyond the logical unit of work. Extended dynamic statements support both static and dynamic access to data and are used for the direct creation and maintenance of packages.
Programs that use Dynamic SQL are easier to program and maintain than programs which use Extended Dynamic SQL; however there are situations where Extended Dynamic SQL has definite advantages over Dynamic SQL. The most prominent advantage that Extended Dynamic SQL has over Dynamic SQL is the ability to access data using Static SQL statements.
With Dynamic SQL, the database manager checks the statements being prepared for three things; existence, usage and authority. The database manager checks whether the objects referenced in the prepared statement exist and are being used correctly. The database manager also checks whether the authorization ID of the person executing the program has the authority and privileges required by the prepared statements. The database manager does this checking by looking up information in the catalog tables. This can cause many locks on these tables and slow the execution of the program.
With Static SQL, the database manager checks the statements being prepared for existence, usage and authority only once, when the statements are prepared and stored into a package. The privileges on the objects referenced in the prepared statements must be held by the authorization ID of the person who creates the package. These privileges may then be shared by granting the execute privilege on the package to others.
There are many situations where Static SQL is advantageous. They include the following:
Within Dynamic, static Extended Dynamic and dynamic Extended Dynamic SQL, some of the statements are used to manipulate cursors while others are used to execute statements which do not require a cursor. The structure of the following sections on Dynamic, static Extended Dynamic and dynamic Extended Dynamic SQL matches this division of SQL statements into those which require cursors and those which do not require cursors.
You can write queries, manipulate, control and define data using Dynamic SQL in RXSQL. The sequence of RXSQL statements you use depends on whether or not you require a cursor.
Cursors can be used to retrieve, insert, update or delete data. To retrieve data your program defines a query_cursor and retrieves rows using the FETCH statement. To insert data your program defines an insert_cursor and inserts rows using the PUT statement. You can also use a cursor to update or delete selected rows of an active set or result table by using the Positioned UPDATE or Positioned DELETE statement.
To execute query_cursor or insert_cursor statements in Dynamic SQL your program must issue the following RXSQL statements:
| Note: | The DECLARE cursor is optional, and may be executed before or after the PREPARE statement. If your program does not DECLARE a cursor for the INSERT or SELECT statement, then RXSQL will require sql_statement on the OPEN, FETCH, PUT and CLOSE statements. Other host languages supported by the database manager do not allow statement names to be used on OPEN or CLOSE statements. |
To execute Positioned UPDATE or Positioned DELETE statements in Dynamic SQL you have a choice of two sequences, illustrated as follows:
Use the EXECUTE IMMEDIATE statement with the imbedded select_statement to update or delete the row retrieved by the query_cursor.
The following example illustrates this sequence for a Positioned UPDATE.
/*** UPDATE the third column when the value in the second column = 300 ***/
/* The paired outside quotes are stripped off by REXX and the imbedded */
/* single quotes are passed to the database manager. */
"EXECSQL PREP stmt1 SELECT col2 FROM table1 WHERE col1='WRITER'",
"FOR UPDATE OF col3"
'EXECSQL DECLARE query_cursor CURSOR FOR stmt1'
/* open the cursor and position it before first row */
'EXECSQL OPEN query_cursor'
Do forever
/* position cursor on a row and fetch data */
'EXECSQL FETCH query_cursor col2_value'
/* leave the loop when the cursor reaches the end of the result table */
if SQLCODE=100 then leave
if col2_value = 300 then
"EXECSQL EXECUTE IMMEDIATE UPDATE table1 SET col3= 'S01' ",
"WHERE CURRENT OF query_cursor"
End /* Do forever */
'EXECSQL CLOSE query_cursor'
'EXECSQL COMMIT'
Prepare the select_statement and then issue the CALL or EXECUTE statement to update or delete the row retrieved by the cursor.
The UPDATE or DELETE sql_statement has a WHERE CURRENT OF cursor_name clause. If the DECLARE statement is not issued, the select_statement must be used on the OPEN, FETCH and CLOSE statements. The following example illustrates this sequence for a Positioned UPDATE.
/***** UPDATE the third column when the value in the second column = 300 ****/
"RXSQL PREP select_stmt SELECT col2 FROM table1 WHERE col1='WRITER'
"FOR UPDATE of col3"
'RXSQL PREP stmt2 UPDATE table1 SET col3=:value',
'WHERE CURRENT OF select_stmt'
/* open the cursor and position it before first row */
'RXSQL OPEN select_stmt'
Do forever
/* Position cursor on a row and fetch data */
'RXSQL FETCH select_stmt INTO col2_value '
/* leave the loop when the cursor reaches the end of the result table */
if SQLCODE=100 then leave
if col2_value = 300 then
Do
/* get the value that you want the column to be updated to */
say 'Type in the value you want and press enter'
parse pull value .
value="'"value"'"
'RXSQL CALL stmt2 '
End
End
'RXSQL CLOSE select_stmt'
'X' COMMIT'
The DECLARE statement was not used in the preceding example to illustrate that RXSQL requires the prepare_name on the OPEN, FETCH and CLOSE statements, and in the WHERE CURRENT OF clause, when a cursor has not been declared.
To execute SQL statements which do not require a cursor, you have a choice of two different sequences. They are all illustrated as follows:
Imbed the statement in an EXECUTE IMMEDIATE statement.
Prepare and then execute the statement.
Sequence 1 supports previous versions of DB2 RXSQL.
Sequence 2 provides for preparing a non-cursor statement once and invoking it as often as required while the program is active. This sequence may execute more efficiently if the statement is to be invoked many times.
There are a few basic differences between using Dynamic SQL and Extended Dynamic SQL in RXSQL. The most obvious one is that you have to create a package when using Extended Dynamic SQL. This is generally done in a separate REXX program from the program that executes the prepared statements. This minimizes the number of times a statement is prepared.
For introductory illustrations see Illustrations of Extended Dynamic SQL in RXSQL.
A package is created when the LUW that contains the CREATE PACKAGE statement, is ended by execution of the COMMIT statement. Packages are composed of sections which are added by the Extended PREPARE statement. There are two kinds of sections: those which are permanently filled with an SQL statement, and those which are empty. Those sections which are permanently filled with an SQL statement contain static SQL statements while those which are empty are ready for dynamic SQL statements to be temporarily prepared into them.
The type of section added to a package depends on the format of the Extended PREPARE statement used. Of the four formats of the Extended PREPARE statement, two are used to prepare Static SQL statements, while the other two are used for Dynamic SQL statements.
The Empty Extended PREPARE must be issued in a LUW prior to the LUW that issues the Temporary Extended PREPARE. The package must exist, along with the empty section, before an SQL statement can be prepared into it. The empty section is filled for the duration of the LUW in which the Temporary Extended PREPARE statement is executed. When the LUW ends, the database manager resets the section to its original, empty condition.
There are restrictions on the use of the four formats of the Extended PREPARE statement depending on the type of package you create. You can create a non-modifiable package or a modifiable package.
A non-modifiable package supports all four formats of the Extended PREPARE statement and thus supports both static and dynamic SQL statements. The Basic, Single Row and Empty Extended PREPARE statements must be executed in the same LUW as the one in which the CREATE PACKAGE was issued. A non-modifiable package cannot be changed after the LUW in which it was created ends. Temporary Extended PREPARE statements must be executed in a later LUW. Static statements that are prepared into a non-modifiable package cannot be executed until package creation is complete, when the LUW ends.
In contrast, a modifiable package can be modified after the LUW in which it was created ends. Further, any committed modifications made to the package remain in force for subsequent LUWs. A modifiable package supports only the Basic Extended PREPARE and Single Row Extended PREPARE statement, and thus supports only Static SQL. A modifiable package can exist without any statements in it. Statements can be added to or dropped from a modifiable package at any time. They can also be executed in the same LUW as the one in which they were prepared without the necessity of first completing the creation of the package by committing the LUW.
When your program issues a Basic or Single Row Extended PREPARE statement, RXSQL returns a section number to your program in a REXX variable to indicate which section of the package your statement is in. Your program must record this number because it is used to refer to the statement when declaring or executing it.
Static sections containing static statements are added to a package in the following sequence:
There can be many Extended PREPARE statements before the COMMIT WORK statement ends the LUW.
There must be two statements prepared to execute a Positioned UPDATE or Positioned DELETE statement. The first one is a SELECT statement and the second one is an UPDATE or DELETE statement with a WHERE CURRENT OF cursor_name clause. Both the SELECT and the UPDATE or DELETE statements must be prepared into the same package. The following example illustrates this for a Positioned DELETE.
'RXSQL XPREP IN pkg1 SELECT col1, col2 FROM table1'
'FOR UPDATE of col1,col2,col3'
/******* RXSQL returns section number 1 in SQLSTMTN; save it ****/
'RXSQL XPREP IN pkg1 DELETE FROM table1 WHERE CURRENT OF cursor1'
/******* RXSQL returns section number 2 in SQLSTMTN; save it ****/
The program that declares the cursor and executes the statements references the same cursor_name (cursor1) as the second XPREP statement. The cursor declared to retrieve rows must reference the section number (1) returned by the first XPREP statement. To delete a row satisfying the program requirements, the DELETE statement is invoked by referencing the section number (2) returned from the second XPREP statement. For example,
'RXSQL DECLARE cursor1 CURSOR FOR 1 IN pkg1' /* cursor1 is the name of the query_cursor */ 'RXSQL DECLARE delstmt FOR 2 IN pkg1' /* delstmt will be referenced in an Extended CALL request */
The SELECT statement must have a FOR UPDATE OF clause when
The FOR UPDATE OF clause is necessary to turn off blocking.
After a package is created, a second REXX program defines the cursor or executes the prepared statements by referring to the saved section number. The sequences of statements you can choose from for the second REXX program are similar to the sequences for cursor and non-cursor statements illustrated earlier.
To use query_cursors or insert_cursors in static Extended Dynamic SQL your program must issue the following RXSQL statements:
Two statements must be prepared to execute a Positioned UPDATE or Positioned DELETE statement. One is a prepared SELECT statement while the other is an UPDATE or DELETE statement with a WHERE CURRENT OF clause. The cursor_name in the WHERE CURRENT OF clause must have the same name as the cursor_name referenced in the DECLARE, OPEN, FETCH and CLOSE statements.
To execute a Positioned UPDATE or Positioned DELETE statement in static Extended Dynamic SQL, you have a choice of two sequences depending on whether a statement_name is declared for the Positioned UPDATE or Positioned DELETE statement.
If declared, the statement_name may be used in subsequent Extended CALL requests for the statement. Otherwise, the section_number and package_name must be used in subsequent Extended EXECUTE or XCALL requests for the statement.
The sequences are illustrated as follows:
To execute static Extended Dynamic statements which do not require a cursor, you have a choice of two sequences.
DECLARE and execute the statement.
| Note: | This sequence is unique to RXSQL. |
The Extended DECLARE statement gives a name to a prepared statement which is used on the Extended CALL.
Execute the statement directly.
The Empty Extended PREPARE and Temporary Extended PREPARE support dynamic access to data. The Empty Extended PREPARE statement is used to add empty sections to your package. Then the Temporary Extended PREPARE statement is used to temporarily fill these empty sections. Empty sections are added to your package in the same LUW in which the package is created, while the Temporary Extended PREPARE statement is issued in a separate LUW.
When your program issues an Empty Extended PREPARE statement, RXSQL returns a section number to your program indicating which section of the package your statement is in. The section number must be saved to be used by a subsequent Temporary Extended PREPARE statement.
Dynamic sections are empty when they are added to a package so that statements can be dynamically prepared into them when the program is executing. The following sequence illustrates how empty sections are added to a package.
There can be many Static or Dynamic sections added to a package before the COMMIT statement ends the LUW. Two Dynamic sections must be available to execute a Dynamic Positioned UPDATE or a Positioned DELETE statement.
By preparing an Empty Extended PREPARE statement into your package, you have an empty section that is dynamically filled in a separate LUW by a Temporary Extended PREPARE statement.
To use query_cursors or insert_cursors in dynamic Extended Dynamic SQL, your program must issue the following RXSQL statements:
To execute a Positioned UPDATE or Positioned DELETE statement in dynamic Extended Dynamic SQL you have a choice of two sequences depending on whether a statement_name is declared for the Positioned UPDATE or Positioned DELETE statement.
If declared, the statement_name may be used in subsequent Extended CALL requests for the statement. Otherwise, the section_number and package_name must be used in subsequent Extended EXECUTE or XCALL requests for the statement.
The sequences are illustrated as follows:
When executing statements which do not require a cursor you have a choice of two sequences depending on whether a statement_name is declared for the statement.
If declared, the statement_name may be used in subsequent Extended CALL requests for the statement. Otherwise, the section_number and package_name must be used in subsequent Extended EXECUTE or XCALL requests for the statement.
The sequences are illustrated as follows:
Prepare the statement, declare the statement to name it, then invoke it.
| Note: | This sequence is unique to RXSQL. |
Prepare the statement and invoke it.