View figure.This chapter introduces some SQL and RXSQL concepts along with some DB2 RXSQL requests and the way that these requests work in your REXX programs. Also included in this chapter are illustrations on how to use Dynamic and Extended Dynamic SQL. A more detailed discussion on how to use Dynamic and Extended Dynamic statements in RXSQL is in the following chapter.
The concepts section is divided into two sections: SQL Concepts and RXSQL Concepts. The SQL concepts sections introduces some basic SQL concepts that are needed to use SQL in any programming language, while the RXSQL concepts section introduces some terms and concepts which are specific to RXSQL.
This section should be read along with the "Concepts" chapter in the DB2 Server for VSE & VM SQL Reference manual.
To understand this section you should be familiar with SQL queries and the SQL statements that can be issued interactively.
When you are using Dynamic SQL or Extended Dynamic SQL to access data, you will be using one of two types of RXSQL statement sequences, for cursor statements or for non-cursor statements.
A cursor is a pointer to a row in an active set. An active set is composed of columns and rows of one or more base tables that the database manager selects (a result table) or generates (a put block) based on information in a SELECT statement or an INSERT statement respectively. In RXSQL, a cursor is defined by preparing a SELECT or INSERT statement, and optionally declaring it to give it a cursor_name. The cursor is then referenced in subsequent RXSQL statements (OPEN, FETCH or PUT, CLOSE) by the cursor_name if it was declared, or by its prepare_name if it was not declared.
| Note: | In other host languages supported by the database manager a cursor must be declared before it can be referenced on subsequent statements. |
There are two types of cursors. If your program is retrieving data, the cursor is called a query_cursor because the active set or result table is defined by a SELECT statement. If your program is inserting data into a table, the cursor is called an insert_cursor because the active set or put block is defined by an INSERT statement.
When a cursor is opened, it is pointing to the top of the active set. Your program must open the cursor by issuing the OPEN statement, and then advance it row by row by issuing FETCH for a query_cursor or PUT for an insert_cursor. Generally, your program continues retrieving or inserting rows until the last row has been retrieved or all the data has been inserted. Then your program closes the cursor and commits the changes, if any.
You can also update or delete data with a cursor using a Positioned UPDATE or Positioned DELETE statement.
The Positioned UPDATE or Positioned DELETE statement is used to update or delete a row to which a cursor is currently pointing. This is different from the Searched UPDATE or Searched DELETE where each row that matches the search condition is updated or deleted.
Positioned UPDATE or Positioned DELETE statements use a cursor while Searched UPDATE or Searched DELETE statements do not require a cursor.
To code a Positioned UPDATE or Positioned DELETE statement, your program must first define a query_cursor where the SELECT statement has a FOR UPDATE OF column_names clause. The cursor is then used to retrieve each row of data using the FETCH statement. If the retrieved row is to be updated or deleted your program issues an UPDATE or DELETE statement with the WHERE CURRENT OF cursor_name clause.
| Note: | Coding the Positioned DELETE operation is different in other host languages supported by the database manager. See the DB2 Server for VSE & VM SQL Reference manual for more information on the Positioned DELETE operation. |
Some database operations require a cursor while others do not. The sequence needed for executing SQL statements with cursor operations is different from the sequence needed for non-cursor operations. The SQL statements that always require a cursor in RXSQL include OPEN, SELECT, Positioned UPDATE, Positioned DELETE, and CLOSE. The INSERT statement can be used as a non-cursor statement.
| Note: | This does not apply to other host languages that support the SELECT INTO statement, which does not require a cursor. |
However, if many rows are to be inserted, it is more efficient to use an insert_cursor so the rows will be inserted in blocks rather than one row at a time.
The SQL statements which do not require a cursor in RXSQL include the
following:
| ACQUIRE DBSPACE | ALTER TABLE | ALTER DBSPACE |
| COMMIT | COMMENT ON | CONNECT |
| CREATE INDEX | CREATE SYNONYM | CREATE PACKAGE |
| CREATE TABLE | CREATE VIEW | Searched DELETE |
| DROP | EXPLAIN | GRANT |
| INSERT | LABEL ON | LOCK DBSPACE |
| LOCK TABLE | REVOKE | ROLLBACK |
| Searched UPDATE | UPDATE STATISTICS |
Blocking is the process of retrieving or inserting rows of data in groups rather than one row at a time. If there are many rows to be retrieved or inserted, blocking usually improves performance. However, you should be aware that problem determination is affected by blocking. If you are inserting rows in blocks, an error condition is not detected for a PUT until the block is transmitted to the database manager. This occurs when a block is full or when CLOSE is invoked. To determine the row being inserted when the error was encountered, you must analyze the SQLCA variables as defined in the DB2 Server for VSE & VM SQL Reference.
Dynamic FETCH or PUT statements retrieve or insert data in blocks because blocking is the default for RXSQL. However, blocking is turned off when:
If you wish to use FETCH or PUT without blocking, you must use DB2 RXSQL Extended Dynamic statements to create a package with the NOBLOCK option.
The default installation procedure for DB2 RXSQL includes installing a package for DB2 RXSQL to use. This package contains forty empty sections to be used by DB2 RXSQL when your program executes dynamic SQL statements. These sections are referenced by DB2 RXSQL with statement_names S1, S2, ..., S40 and their associated cursor_names C1, C2, ..., C40.
When your program prepares a dynamic statement, DB2 RXSQL uses an available section with its corresponding statement_name Sn in the RXSQL package. DB2 RXSQL maps the statement_name which your program defines to Sn. If your dynamic statement involves a cursor operation, DB2 RXSQL maps your cursor to the cursor Cn associated with statement Sn in the DB2 RXSQL package. The statement_name and cursor_name which you define in your program are known to RXSQL, but these are not passed to the database manager. When you invoke a DB2 RXSQL request referencing the dynamic statement previously defined, DB2 RXSQL passes the request to the database manager referring only to the statement Sn or its associated cursor Cn.
The following diagrams illustrate how to use Dynamic SQL. They do not contain the complete programming syntax, but are intended to illustrate the statements needed in your program, and how RXSQL processes them to manipulate data stored by the database manager. It is assumed that all of these examples belong to one program and are executed in the sequence that they are illustrated.
The examples that are inline with the text illustrate how to code RXSQL requests, but they are not complete. For example, they do not illustrate error handling. For a complete example of how to code RXSQL applications see Appendix F, Sample Programs with Examples of RXSQL Requests.
RXSQL passes the SQL statement in the EXEC (or EXECUTE IMMEDIATE) statement directly to the database manager.
Rexx_host_variables are not allowed in SQL statements executed by the EXEC statement, but they can be used on the CALL or EXECUTE statement.
RXSQL stores the statement_name stmt1 and the statement value sqlstmt1 in a temporary storage area. Prepared statements can be opened or called until program control is returned to CMS, or until your program issues a PURGE command.
When DB2 RXSQL issues a PREPARE statement to the database manager, this statement remains active in the database manager for the duration of the LUW only. However, the prepared statement remains in DB2 RXSQL temporary storage.
If the SQL statement has variable_names, their values are passed to the database manager when the CALL statement is executed.
The following example illustrates a RXSQL CALL statement without any variables:
View figure. /* */
/* For all of these examples assume that A_TABLE exists and */
/* has 5 character type columns */
/* */
"RXSQL PREP stmt1 INSERT INTO A_TABLE",
"VALUES('HEATHER','L','DOBSON','T01','WRITER')"
'RXSQL CALL stmt1'
'RXSQL COMMIT'
'RXSQL PURGE stmt1'
The previous example will insert only one row into a table. If variable_names are coded in the INSERT statement, many rows can be inserted into the database using the same statement. The following example illustrates this:
/* */
/* Assume that there is a file containing all the input called */
/* DEPT FILE */
Do forever
'EXECIO 1 DISKR DEPT FILE * (LIFO '
parse upper pull fname mid lname department job .
If fname = '' then leave
fname = "'"fname"'" /* This ensures RXSQL will know that the */
mid = "'"mid"'" /* data type is character */
lname = "'"lname"'"
department = "'"department"'"
job = "'"job"'"
'RXSQL PREP stmt1 INSERT INTO A_TABLE',
'VALUES(:fname, :mid, :lname, :department, :job )'
'RXSQL CALL stmt1'
'RXSQL COMMIT'
'RXSQL PURGE stmt1'
End
'FINIS DEPT FILE *'
The previous example will work, but the performance will not be very good because a PREP statement is executed with each iteration of the loop. Performance would be much better if the PREP statement was executed only once, and host variables were used to substitute values into the table using the CALL statement. The following example illustrates this:
/* */
/* Assume that there is a file containing all the input called */
/* DEPT FILE */
/* */
insert_data= 'INSERT INTO A_TABLE VALUES (',
':fname,:mid,:lname,:department,:job )'
'RXSQL PREP stmt1' insert_data
Do forever
'EXECIO 1 DISKR DEPT FILE * (LIFO '
parse upper pull fname mid lname department job .
If fname = '' then leave
fname = "'"fname"'" /* this ensure RXSQL will know that the */
mid = "'"mid"'" /* data type is character */
lname = "'"lname"'"
department = "'"department"'"
job = "'"job"'"
'RXSQL CALL stmt1'
End
'FINIS DEPT FILE *'
'RXSQL COMMIT'
'RXSQL PURGE stmt1'
With each iteration of the loop new values are retrieved from the input file DEPARTMENT FILE and passed to the database manager in the CALL statement. The PREP and COMMIT statements do not have to to be executed with each iteration of the loop making the program run much more efficiently.
Another point to note is that the PURGE statement is executed the same number of times the PREP statement is executed in all the examples to ensure that the RXSQL temporary storage area does not get filled.
Figure 23. ROLLBACK
View figure.
A ROLLBACK statement will back out all uncommitted changes. Note that this is opposite to a COMMIT statement which commits all changes. Also note that the prepared statement remains in RXSQL temporary storage even though the work done in the LUW has been rolled back.
In this example, sqlstmt3 is a SELECT statement. RXSQL issues an OPEN statement to the database manager when an OPEN statement is issued in a program. RXSQL associates the cursor C3 with the statement named stmt3.
The prepared statement is an INSERT statement. When the program issues an OPEN statement, an insert-cursor is prepared for block input. Even though the program passes one row at a time to the database manager using the PUT statement, rows are inserted into the table in blocks. This is more efficient than inserting one row at a time into a table.
Figure 26. DESCRIBE
View figure.
The prepared statement is a SELECT statement. When the program issues a DESCRIBE request, DB2 RXSQL returns information about the columns to be fetched into REXX stem variables. This information includes column names and data types.
Between the time that RXSQL is invoked and control is returned to CMS, RXSQL allows 40 statements to be prepared at one time. If your program tries to prepare more than 40 statements, DB2 RXSQL will return an error indicating that you have tried to prepare more than the allowed number of statements. For this reason, you may want to use the RXSQL PURGE command to maintain your prepared statements. PURGE does not pass a COMMIT statement to the database manager but, if there is an open associated cursor, DB2 RXSQL issues a CLOSE statement to the database manager to close it.
See Appendix F, Sample Programs with Examples of RXSQL Requests for a detailed illustration of Dynamic SQL. See Chapter 10, RXSQL Request Descriptions for a detailed description of RXSQL statements and commands.
The following diagrams illustrate how to use Extended Dynamic statements. It is assumed that the examples are executed in the sequence that they are illustrated and Figure 29 to Figure 33 are executed within one program.
Once the COMMIT is issued, the package your is stored by the database manger with three statements.
The sequence of statements used in Extended Dynamic SQL is very similar to the sequence used in Dynamic SQL. However, note that Extended Dynamic statements have a different syntax to reference sections in a package.
In this example, the Extended DECLARE defines a statement name bstmt for section 2 in your package. This statement name is subsequently referenced in the Extended CALL statement to execute the statement.
Extended EXECUTE and XCALL invoke a statement in a package directly. However, you can not use rexx_host_variables in your SQL statement when using the XCALL statement. Use the Extended EXECUTE statement, or the Extended DECLARE and Extended CALL statements if you want to use rexx_host_variables in Extended Dynamic SQL.
Figure 32. DROPSTMT
View figure.
The DROPSTMT statement deletes statements from a package, but it does not remove a package from the database. The statement DROP PACKAGE removes a package from the database.
RXSQL keeps track of the statements you have declared until control is returned to CMS, or until a PURGE command is issued. Unlike the limit of 40 prepared statements when using Dynamic SQL in RXSQL, the limit in Extended Dynamic SQL is much greater and is determined by the database manager.
View figure.
View figure.
View figure.
View figure.
View figure.
View figure.
View figure.
View figure.