DB2 Server for VSE & VM SQL Reference (Version 7 Release 5)

DECLARE CURSOR

The DECLARE CURSOR statement defines the cursor through which a user may

OPEN, FETCH, PUT, or CLOSE the results of a statement prepared using

PREPARE. There are two types of cursor:

v A query cursor is a cursor associated with a select-statement and used by an

application to access rows in a result table.

v An insert cursor is a cursor associated with an insert-statement and used by an

application to insert rows into an active set.

Invocation

This statement can only be embedded in an application program. It is not an

executable statement.

Authorization

No authorization is required to use this statement except in the case of Fortran.

Programs in these languages will fail if the authorization ID is not the same as that

used to preprocess the program.

To use the OPEN statement for the cursor, the privileges held by the authorization

ID of the statement are outlined below.

The cursor must always be linked to a select-statement or an insert-statement. This

linked statement may be identified in one of three ways. The authorization

required to manipulate the cursor varies accordingly.

1. If the statement is a fullselect of the form identified by select-statement, then the

authorization ID is the one that is used to preprocess the program. This

authorization ID must have SELECT privileges on every table and view

identified in the SELECT.

2. If the statement is an INSERT (using VALUES), then the authorization ID is the

one that preprocesses the program. This authorization ID must have INSERT

authority on that table.

3. If the statement is a prepared SELECT or INSERT (using VALUES) statement

named by a statement_name clause, then the authorization ID is the run-time

authorization ID. Depending on whether the statement to be prepared is a

SELECT or INSERT (using VALUES), this authorization ID must have

appropriate SELECT or INSERT privileges.

Someone with DBA authority may do any of the above.

Syntax

►► DECLARE cursor-name CURSOR

FOR

►

WITH RETURN

(1)

WITH HOLD

► select-statement

►◄

statement-name

Notes:

Note that DB2 Server for VSE & VM does not support CURSOR WITH HOLD.

Chapter 6. Statements

235

DECLARE CURSOR

Description

cursor_name

Provides a name for the cursor. The name must not be the same as the name of

another cursor declared in your source program. In REXX, cursor_name must

not be the same as a statement_name prepared in the program.

A cursor in the open state designates an active set (for query cursors this is also

known as the cursor’s result table) and a position relative to the rows of that active

set. The active set is specified by the SELECT or INSERT statement of the cursor.

A program may contain many DECLARE CURSOR statements that define different

cursors and associate them with different queries or inserts. During processing of a

program, several of these cursors may be in the open state at one time. The

DECLARE CURSOR statement that defines a cursor must occur earlier in the

program than any cursor manipulation statement operating on that cursor. The

DECLARE CURSOR statement does not result in any actual processing when the

program is run (that is, it does not automatically open the cursor).

The DECLARE CURSOR statement must precede all statements that explicitly

reference the cursor by name.

Following is a description of each form of DECLARE CURSOR.

DECLARE CURSOR for SELECT

select-statement

Specifies the SELECT statement of the cursor.

The select-statement must not include parameter markers, but can include

references to host variables. In host languages, other than assembler and REXX,

the declarations of the host variables must precede the DECLARE CURSOR

statement in the source program. Host variable declarations can follow the

DECLARE CURSOR statement in assembler and host variables are not

declared at all in REXX.

The result table is read-only if any of the following are true:

v The first FROM clause identifies more than one table or view.

v The first FROM clause identifies a read-only view.

v The first SELECT clause specifies the keyword DISTINCT.

v The outer subselect contains a GROUP BY clause.

v The outer subselect contains a HAVING clause.

v The first SELECT clause contains a column function.

v The select-statement contains a subquery such that the base object of the outer

subselect and of the subquery is the same table.

v The select-statement contains a UNION or UNION ALL operator.

v The select-statement includes an ORDER BY clause.

v Isolation UR is used.

If the select-statement of a cursor contains CURRENT DATE, CURRENT TIME, or

CURRENT TIMESTAMP, all references to these special registers will yield the same

value on each FETCH. This value is determined when the cursor is opened.

Examples:

Example 1: In a PL/I program, use the cursor C1 to fetch the values for a given

project (PROJNO) from the first four columns of the EMP_ACT table a row at a

time and put them into the following host variables: EMP (char(6)), PRJ (char(6)),

236

SQL Reference

DECLARE CURSOR

ACT (smallint), and TIM (dec(5,2)). Obtain the value of the project to search for

from the host variable SEARCH_PRJ (char(6)).

EXEC SQL BEGIN DECLARE SECTION;

DCL EMP

CHAR(6);

DCL PRJ

CHAR(6);

DCL SEARCH_PRJ

CHAR(6);

DCL ACT

BINARY FIXED(15);

DCL TIM

DEC

FIXED(5,2);

EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE C1 CURSOR FOR

SELECT EMPNO, PROJNO, ACTNO, EMPTIME

FROM EMP_ACT

WHERE PROJNO = :SEARCH_PRJ;

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;

IF SQLSTATE = ’02000’ THEN

CALL DATA_NOT_FOUND;

ELSE

DO WHILE (SUBSTR(SQLSTATE,1,2) = ’00’ | SUBSTR(SQLSTATE,1,2) =

’01’);

EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;

END;

EXEC SQL CLOSE C1;

Example 2: In a PL/I program, declare a cursor named INCREASE to return from

the EMPLOYEE table all the employee numbers (EMPNO), surnames

(LASTNAME) and price (SALARY increased by 10 percent) of people who have

the job of clerk (JOB). Order the result table in descending order by the increased

salary.

EXEC SQL DECLARE INCREASE CURSOR FOR

SELECT EMPNO, LASTNAME, SALARY * 1.1

FROM EMPLOYEE

WHERE JOB = ’CLERK’

ORDER BY 3 DESC;

Example 3: In a PL/I program, declare a cursor named UP_CUR to update all the

columns of the DEPARTMENT table.

EXEC SQL DECLARE UP_CUR CURSOR FOR

SELECT *

FROM DEPARTMENT

FOR UPDATE OF DEPTNO, DEPTNAME, MGRNO, ADMRDEPT;

Example 4: In a PL/I program, declare a cursor named DEL_CUR to examine, and

potentially delete, rows in the DEPARTMENT table.

EXEC SQL DECLARE DEL_CUR CURSOR FOR

SELECT *

FROM DEPARTMENT;

DECLARE CURSOR for INSERT

insert-statement

This is an INSERT using VALUES statement as defined with the INSERT

statement. The insert-statement must not include parameter markers, but can

include references to host variables. In host languages, other than assembler

Chapter 6. Statements

237

DECLARE CURSOR

and REXX, the declarations of the host variables must precede the DECLARE

CURSOR statement in the source program. In assembler host variable

declarations can follow the DECLARE CURSOR statement. In REXX, host

variables are not declared at all.

Once a cursor has been defined and opened, you may insert new rows into the

table using the PUT statement.

Example 5: This example shows portions of a pseudo COBOL program. In this

program, use the cursor C2 to insert a row into the DEPARTMENT table based on

the values in the host variables DPT_NO (char(3), DPT_NM (varchar(29)),

MGR_NO (char(6)), and DPT_AD (char(3)).

* in working storage:

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

77 DPT-NO

PIC X(3).

77 MGR-NO

PIC X(6).

77 DPT-AD

PIC X(3).

01 DPT-NM.

49 DPT-NM-LEN

PIC S9(4) COMP VALUE +29.

49 DPT-NM-VAL

PIC X(29)

VALUE SPACES.

EXEC SQL END DECLARE SECTION END-EXEC.

* at start of processing:

EXEC SQL DECLARE C2 CURSOR FOR

INSERT INTO DEPARTMENT

VALUES (:DPT-NO, :DPT-NM, :MGR-NO, :DPT-AD) END-EXEC.

EXEC SQL OPEN C2

END-EXEC.

* loop as many times as necessary:

* solicit values from screen and assign to DPT-NO, DPT-NM, MGR-NO, DPT-AD

EXEC SQL PUT C2

END-EXEC.

* at end of processing

EXEC SQL CLOSE C2

END-EXEC.

DECLARE CURSOR for Dynamic Queries

statement_name

Identifies a SELECT or INSERT statement defined in a PREPARE statement.

(When communicating with an application server that is not DB2 Server for

VM or DB2 Server for VSE, this restriction might not be enforced.) The

DECLARE CURSOR statement and its associated PREPARE statement must be

in the same logical unit of work. They may be specified in either order, except

in Fortran programs when the string-constant form of the PREPARE statement

is used. For information on this restriction, see “PREPARE” on page 313.

Example 6

This example is similar to Example 1 under DECLARE CURSOR for SELECT. The

difference is that the right hand side of the WHERE clause is to be specified

dynamically; thus the entire select-statement is placed into a host variable and

dynamically prepared.

EXEC SQL BEGIN DECLARE SECTION;

DCL EMP

CHAR(6);

DCL PRJ

CHAR(6);

DCL SEARCH_PRJ

CHAR(6);

DCL ACT

BINARY

FIXED(15);

DCL TIM

DEC

FIXED(5,2);

DCL SELECT_STMT

CHAR(200)

VARYING;

EXEC SQL END DECLARE SECTION;

SELECT_STMT = ’SELECT EMPNO, PROJNO, ACTNO, EMPTIME ’ ||

238

SQL Reference

DECLARE CURSOR

’FROM EMP_ACT ’ ||

’WHERE PROJNO = ?’;

EXEC SQL PREPARE SELECT_PRJ FROM :SELECT_STMT;

EXEC SQL DECLARE C1 CURSOR FORSELECT_PRJ;

EXEC SQL OPEN C1 USING :SEARCH_PRJ;

EXEC SQL FETCH C1 INTO :EMP, :PRJT, :ACT, :TIM;

IF SQLSTATE = ’02000’ THEN

CALL DATA_NOT_FOUND;

ELSE

DO WHILE (SUBSTR(SQLSTATE,1,2) = ’00’ | SUBSTR(SQLSTATE,1,2) = ’01’);

EXEC SQL FETCH C1 INTO :EMP, :PRJ, :ACT, :TIM;

END;

EXEC SQL CLOSE C1;

DECLARE CURSOR WITH RETURN

WITH RETURN

Specifies that the cursor, if declared in a stored procedure, can return a result

set to a caller.

Example 7: The following statements could be included in a stored procedure. If

the cursors are opened and not closed, the result sets are returned to the requester.

EXEC SQL DECLARE CURS1 CURSOR WITH RETURN FOR

SELECT A.X,Y,Z FROM TABLEX A, TABLEY B WHERE A.X = B.X

EXEC SQL DECLARE CURS2 CURSOR WITH RETURN FOR STMT1

Overall Notes

The scope of cursor_name is the source program in which it is defined; that is, the

program submitted to the preprocessor. Thus, you can only reference a cursor by

statements that are preprocessed with the cursor declaration. For example, a

program called from another separately preprocessed program cannot use a cursor

that was opened by the calling program.

The NOFOR Option

The NOFOR preprocessor option concerns the use of the UPDATE clause when a

cursor is declared for a static (embedded) query. With NOFOR in effect, this clause

is optional. When the clause is used, updates are restricted to the columns

designated within it. NOFOR is only useful when the UPDATE statements are

static. See the DB2 Server for VSE & VM Application Programming manual for more

details on the NOFOR preprocessor option.

Chapter 6. Statements

239

Extended DECLARE CURSOR

The Extended DECLARE CURSOR statement defines the cursor through which a

user may OPEN, FETCH, PUT, or CLOSE the results of a statement prepared using

Extended PREPARE. There are two types of cursor:

v A query cursor is a cursor associated with a select-statement and used by an

application to access rows in a result table.

v An insert cursor is a cursor associated with an insert-statement and used by an

application to insert rows into an active set.

Invocation

This statement can only be embedded in an application program written in

Assembler or REXX.

Authorization

The authorization ID of the statement must have one of the following:

v ownership of the package

v DBA authority

v EXECUTE privilege on the package.

Syntax

►► DECLARE cursor_variable CURSOR FOR section_variable IN package_spec

►◄

Description

cursor_variable

Provides a name for the cursor. The name placed into cursor_variable must be

unique within the logical unit of work in which it is used.

CURSOR FOR section_variable

Identifies a select-statement or insert-statement defined in an Extended

PREPARE statement. A cursor need not be declared in the same logical unit of

work or program in which the statement was prepared.

IN package_spec

Identifies the package in which the referenced SQL statement resides. The

package_spec must identify a package that exists at the application server.

Notes

Cursors are associated with a prepared select-statement or insert-statement by the

value returned in the section_variable and the package_spec specified in the Extended

DECLARE CURSOR statement. Extended DECLARE CURSOR may be used for

any select-statement or insert-statement in a package created using the CREATE

PACKAGE statement.

A cursor name used in a WHERE CURRENT OF clause of a DELETE statement or

an UPDATE statement cannot be specified from a host variable. Therefore, at

execution time, the content of cursor_name in the Extended DECLARE CURSOR

statement must be the same as the cursor_name hard-coded in the WHERE

CURRENT OF clause.

240

SQL Reference

DELETE

The DELETE statement deletes rows from a table or view. Deleting a row from a

view deletes the row from the table on which the view is based.

There are two forms of this statement:

v The Searched DELETE form deletes one or more rows (optionally determined by

a search condition).

v The Positioned DELETE form deletes exactly one row (as determined by the

current position of a cursor).

Invocation

A Searched DELETE statement can be embedded in an application program or

issued interactively. A Positioned DELETE must be embedded in an application

program. Both Searched DELETE and Positioned DELETE are executable

statements that can be dynamically prepared.

A Positioned DELETE in Fortran, and programs prepared using extended dynamic

SQL cannot be used with the DRDA protocol.

Authorization

The privileges held by the authorization ID of the statement must include at least

one of the following:

v Ownership of the table

v The DELETE privilege for the table or view

v DBA authority.

The DELETE privilege on a view is only inherent in DBA authority. Ownership of

a view does not necessarily include the DELETE privilege on the view because the

privilege may not have been granted when the view was created, or it may have

been granted, but subsequently revoked.

If the search-condition includes a subquery, the privileges designated by the

authorization ID of the statement must also include the SELECT privilege on every

table or view identified in the subquery. The privilege may have been explicitly

granted or may be inherent in another privilege. The SELECT privilege on a table

or view is inherent in DBA authority and ownership of a table or view.

242

SQL Reference

DELETE

Syntax

Searched delete (I,P)

►► DELETE FROM

table_name

►◄

view_name

correlation_name

►►

►◄

WHERE search_condition

WITH

Positioned delete (P)

(1)

►► DELETE FROM

table_name

WHERE CURRENT OF

cursor_name

►◄

view_name

Notes:

A Positioned DELETE in Fortran, and programs prepared using Extended dynamic

SQL cannot be used with DRDA protocol.

Description

FROM table_name or view_name

Identifies the table or view from which rows are to be deleted. The name must

identify a table or view that exists at the application server, but must not

identify a catalog table, a view of a catalog table, or a read-only view. (For an

explanation of read-only views, see “CREATE VIEW” on page 231.)

Note: Someone with DBA authority may delete rows from a few of the catalog

tables. See “Updateable Columns” on page 371.

correlation_name

Can be used within the search_condition to designate the table or view. (For an

explanation of correlation_name, see Chapter 3.)

WHERE

Specifies the rows to be deleted. You can omit the clause, give a search

condition, or name a cursor. If you omit the clause, all rows of the table or

view are deleted.

search_condition

Is any search condition as described in Chapter 3. Each column_name in the

search condition, other than in a subquery, must name a column of the

table or view.

The search_condition is applied to each row of the table or view and the

deleted rows are those for which the result of the search_condition is true.

If the search condition contains a subquery, the subquery can be thought of

as being processed each time the search condition is applied to a row, and

the results used in applying the search condition. In actuality, a subquery

with no correlated references is processed once, whereas a subquery with a

correlated reference may have to be processed once for each row.

Chapter 6. Statements

243

DELETE

The following restriction is enforced when a DELETE statement is prepared

or preprocessed with a WHERE clause containing a subquery. Let T2

denote the object table of a DELETE statement, and let T1 denote a table

that is referenced in the FROM clause of a subquery of that statement, T1

must not be a table that can be affected by the DELETE on T2. The

following example demonstrates the relationships.

DELETE FROM T2 WHERE FIELD2 IN (SELECT FIELD1 FROM T1);

The following rules apply to the above situation:

v T1 and T2 must not be the same table.

v T1 must not be a dependent of T2 in a relationship with a delete rule of

CASCADE or SET NULL.

v T1 must not be a dependent of another table T3 in a relationship with a

delete rule of CASCADE or SET NULL if deletes of T2 cascade to T3.

WITH

Specifies the isolation level used when locating the rows to be deleted by

the statement.

Repeatable read

Cursor stability

The default isolation level of the statement is the isolation level of the

package. WITH can only be specified on a SEARCHED delete; it is

incompatible with the WHERE CURRENT OF clause.

CURRENT OF cursor_name

Identifies the cursor to be used in the delete operation. The cursor_name

must identify a declared cursor as explained in “DECLARE CURSOR” on

page 235. The cursor_name can be a delimited identifier. If cursor_name is a

reserved word, it must be a delimited identifier.

The table or view specified must also be specified in the FROM clause of

the SELECT statement of the cursor, and the result table of the cursor must

not be read-only. (For an explanation of read-only result tables, see

“DECLARE CURSOR” on page 235.)

When the DELETE statement is processed, the cursor must be positioned

on a row; that row is the one deleted. The cursor goes into a between state

in which it remains open but has no current row until you reposition it

with a FETCH statement. You cannot use the cursor for further deletions or

updates while it is in the between state.

To maintain data integrity between tables when data is deleted from a parent table,

the database manager checks that delete rules are followed. The delete rule in a

referential constraint clause defines what action should be taken by the system

when a parent row is deleted. The delete rules are:

v The RESTRICT rule prevents the deletion of a parent row unless all the

dependent rows have been deleted first. This is the default rule.

v The CASCADE rule tells the database manager to delete the descendent rows as

well as the parent row. Multi-level cascade is supported, subject to the

restrictions described in “Definition Restrictions” on page 16.

244

SQL Reference

DELETE

v The SET NULL rule tells the database manager to set all nullable columns of the

foreign key to null before deleting the parent row. At least one column of the

foreign key must be nullable.

Notes

If an error occurs during the execution of any delete operation, no rows are

deleted. If an error occurs during the execution of a Positioned DELETE, the

position of the cursor is unchanged. However, it is possible for an error to make

the position of the cursor incorrect, in which case the cursor is closed. It is also

possible for a delete operation to cause a rollback, in which case the cursor is

closed.

If an error occurs during the execution of a Searched DELETE, it is necessary to

inspect SQLWARN6 to determine the extent of the failure. The following are

current settings of SQLWARN6 along with possible responses:

1. SQLWARN6 is set to 'S'. A severe error has occurred, leaving the system in an

unusable state.

v No further requests are possible. The application must end, or, in a DB2

Server for VSE & VM environment, may switch to another database

2. SQLWARN6 is set to 'W'. An error occurred causing the LUW to be rolled back

automatically. The system is still in a usable state. The application can either:

v begin a new LUW and proceed or

v end.

3. SQLWARN6 is blank. An error has occurred, but the LUW is still active. For

recoverable storage pools any changes made by the request have been rolled

back, hence the failing request has not left any partial results in the database.

For more information about recoverable storage pools, see the DB2 Server for

VM System Administration or DB2 Server for VSE System Administration manual.

The application can do one of the following:

v Continue forward processing of the LUW

v Commit the changes made before the failing request

v Roll back the LUW.

Unless appropriate locks already exist, one or more exclusive locks are acquired by

executing a successful DELETE statement. Until the locks are released, they can

prevent other application processes from performing operations on the table. For

further information about locking, see the description of the COMMIT WORK,

ROLLBACK WORK, LOCK TABLE, and LOCK DBSPACE statements. The isolation

level associated with the application process defines the degree to which rows

deleted by one process are visible to other concurrent processes.

If an application process deletes a row on which any of its cursors are positioned,

those cursors are positioned before the next row of their result table. Let C be a

cursor that is positioned before row R (as a result of an OPEN, a DELETE through

C, a DELETE through some other cursor, or a searched DELETE). In the presence

of INSERT, UPDATE, and DELETE operations that affect the base table from which

R is derived, the next FETCH operation referencing C does not necessarily position

C on R. For example, the operation can position C on R’, where R’ is a new row

that is now the next row of the result table.

When a DELETE statement is completed, the number of rows deleted is returned

in SQLERRD(3) in the SQLCA. The value in SQLERRD(3) does not include the

number of rows that were deleted as a result of a CASCADE delete rule.

Chapter 6. Statements

245

DELETE

SQLERRD(5) in the SQLCA shows the number of rows affected by referential

constraints. It includes rows that were deleted as a result of a CASCADE delete

rule and rows in which foreign keys were set to NULL as the result of a SET

NULL delete rule.

If you preprocess your program with the BLOCK option, and you wish to process

a Positioned DELETE dynamically, the cursor must be a SELECT...FOR UPDATE

statement, even if you do not plan to process any updates with the cursor. The

FOR UPDATE clause is needed to tell the database manager that blocking should

be overridden when the SELECT statement is prepared. If you do not use the FOR

UPDATE clause in this instance, an error will occur on your DELETE statement at

execution time.

Examples

Example 1

Delete department (DEPTNO) ‘D11’ from the DEPARTMENT table.

DELETE FROM DEPARTMENT

WHERE DEPTNO = ’D11’

Example 2

Delete all the departments from the DEPARTMENT table (that is, empty the table).

DELETE FROM DEPARTMENT

Example 3

Use a PL/I program statement to delete all the subprojects (MAJPROJ is NULL)

from the PROJECT table for a department (DEPTNO) equal to that in the host

variable HOSTDEPT (char(6)).

EXEC SQL DELETE FROM PROJECT

WHERE DEPTNO = :HOSTDEPT AND MAJPROJ IS NULL;

Example 4

Code a portion of a PL/I program that will be used to display retired employees

(JOB) and then, if requested to do so, remove certain employees from the

EMPLOYEE table.

EXEC SQL DECLARE C1 CURSOR FOR

SELECT *

FROM EMPLOYEE

WHERE JOB = ’RETIRED’;

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 INTO ...

;

PUT ...

;

GET LIST (REMOVE);

IF REMOVE = ’YES’ THEN

EXEC SQL DELETE FROM EMPLOYEE

WHERE CURRENT OF C1;

EXEC SQL CLOSE C1;

246

SQL Reference

DESCRIBE

The DESCRIBE statement obtains information about a prepared statement. It is

primarily used for describing a SELECT statement. For an explanation of prepared

statements, see “PREPARE” on page 313.

Invocation

This statement can only be embedded in an application program. It is an

executable statement that cannot be dynamically prepared.

Authorization

None required. See “PREPARE” on page 313 for the authorization required to

create a prepared statement.

Syntax

►► DESCRIBE statement_name INTO descriptor_name

►◄

NAMES

USING

ANY

BOTH

LABELS

Description

statement_name

Identifies the statement about which information is to be obtained. When the

DESCRIBE statement is processed, the name must identify a statement

dynamically prepared in the same logical unit of work.

INTO descriptor_name

Identifies an SQL descriptor area (SQLDA). Before the DESCRIBE statement is

processed, the following variable in the SQLDA must be set:

SQLN Indicates the number of variables represented by SQLVAR. (SQLN acts

as a dimension of the SQLVAR array.) SQLN must be set to a value

greater than or equal to zero before the DESCRIBE statement is

processed. When the USING clause is set to NAMES, LABELS, or ANY,

this should specify the maximum number of expected select list items.

When the USING clause is set to BOTH, twice the expected number of

select list items should be specified.

When the DESCRIBE statement is processed, the database manager assigns

values to the variables of the SQLDA as follows:

SQLDAID This field serves only as an SQLDA eye-catcher. It is set to

'SQLDA' by the database manager when a DESCRIBE is first

processed.

SQLDABC

16 + SQLN*44 (the length of the SQLDA).

SQLD

For a SELECT statement, the number of columns described by

occurrences of SQLVAR (or, if USING BOTH was specified on

DESCRIBE, twice the number of columns).

For a non-SELECT statement, 0.

SQLVAR

This is an array with an arbitrary number of occurrences of the

Chapter 6. Statements

247

DESCRIBE

five variables listed below. If the value of SQLD is 0, or greater

than the value of SQLN, no values are assigned to occurrences

of SQLVAR.

If the value of SQLD is n, where n is greater than 0 but less

than or equal to the value of SQLN, values are assigned to the

first n occurrences of SQLVAR so that the first occurrence of

SQLVAR contains a description of the first column of the result

table, the second occurrence of SQLVAR contains a description

of the second column of the result table, and so on.

In cases where the USING clause is set to BOTH, the database

manager returns twice as many SQLVAR entries as there are

columns in the select list. Given that there are n columns, the

first n SQLVAR entries are for column names and the second n

entries are for column labels.

SQLTYPE

A code showing the data type of the column

and whether it can contain null values. For

information about the SQLTYPE codes returned

following the execution of a DESCRIBE

statement, see Table 22 on page 362.

SQLLEN

A length value depending on the data type of

the result columns. For the possible values of

SQLLEN, see Table 22 on page 362.

SQLDATA

Contains the CCSID of a string column, as

shown in Table 23 on page 363.

SQLIND

Indicates the subtype of a character column, if

using the SQLDS protocol. Does not provide

any information if using the DRDA protocol.

For values, see Table 21 on page 360.

SQLNAME

Contains the name or label associated with the

column used in the select list of the DESCRIBE

statement. Exceptions to this are select list

items that are unnamed, such as built-in

functions (SUM(SALARIES)), constants (’ABC’),

and expressions (A+B+C). In these cases,

position 1 of SQLNAME is blank (X'40'), and

positions 3 through 30 contain a description of

the unnamed field. Because a blank is not

allowed in the first byte of SQL identifiers, the

application program can tell whether a column

name is returned.

If no column name is returned, the following

rules govern the content and format of the

SQLNAME field.

If the select list item involves:

v A basic function: SQLNAME contains the

name of the function followed by the

column name in parentheses (for example,

SUM(SALARIES)). Position 2 of SQLNAME

is blank.

v A DISTINCT object of a function: SQLNAME

contains the name of the function, followed

248

SQL Reference

DESCRIBE

by the keyword DISTINCT and the name of

the column in parentheses (for example,

SUM(DISTINCT SALARIES)). If this entire

description is too long to fit in positions 3

through 30 of SQLNAME, it is truncated,

and position 2 is set to X'FF'.

v An expression: SQLNAME is set to the

character string EXPRESSION n, where n is a

number that identifies the nth expression in

the select list. For example, for the sixth

expression in the select list, the database

manager sets positions 3 through n of

SQLNAME to EXPRESSION 6. Position 2 is

blank. This rule is true even for expressions

that contain built-in functions, and, because

expressions include constants, for constants

such as 'ABC'.

v A function whose object is an expression:

SQLNAME contains the name of the

function followed by the character string

EXPRESSION n in parentheses (for example,

SUM(EXPRESSION 7)). Position 2 is blank.

USING

Indicates what value to assign to each SQLNAME variable in the SQLDA.

If the requested value does not exist, SQLNAME is set to a length of 0.

NAMES

Assigns the name of the column. This is the default.

LABELS

Assigns the label of the column. (Column labels are defined by the

LABEL ON statement.)

ANY

Assigns the column label, and if the column has no label, the column

name.

BOTH

Assigns both the label and name of the column. In this case, two

occurrences of SQLVAR per column are needed to accommodate the

additional information. The first n occurrences of SQLVAR for each of

the columns in the result table contain the column names. The second

n occurrences contain the column labels.

Notes

Before the DESCRIBE statement is processed, the value of SQLN must be set to

indicate how many occurrences of SQLVAR are provided in the SQLDA and

enough storage must be allocated to contain SQLN occurrences. To obtain the

description of the columns of the result table of a prepared SELECT statement, the

number of occurrences of SQLVAR must not be less than the number of columns.

Allocating the SQLDA

Among the possible ways to allocate the SQLDA are the three described below.

First Technique: Allocate an SQLDA with enough occurrences of SQLVAR to

accommodate any select list that the application will have to process. At the

Chapter 6. Statements

249

DESCRIBE

extreme, the number of SQLVARs could equal the maximum number of columns

allowed in a result table. Having done the allocation, the application can use this

SQLDA repeatedly.

This technique uses a large amount of storage that is never deallocated, even when

most of this storage is not used for a particular select list.

Second Technique: Repeat the following two steps for every processed select list:

1. Process a DESCRIBE statement with an SQLDA that has no occurrences of

SQLVAR; that is, an SQLDA for which SQLN is zero. The value returned for

SQLD is equal to the required number of occurrences of SQLVAR.

2. Use the returned value of SQLD to allocate an SQLDA with enough

occurrences of SQLVAR. Then process the DESCRIBE statement again, using

this new SQLDA.

This technique allows better storage management than the first technique, but it

doubles the number of DESCRIBE statements.

Third Technique: Allocate an SQLDA that is large enough to handle most, and

perhaps all, select lists but is also reasonably small. If an execution of DESCRIBE

fails because the SQLDA is too small, allocate a larger SQLDA and process

DESCRIBE again. For the new SQLDA, use the value of SQLD returned from the

first execution of DESCRIBE for the number of occurrences of SQLVAR.

This technique is a compromise between the first two techniques. Its effectiveness

depends on a good choice of size for the original SQLDA.

Examples

In a PL/I program, process a DESCRIBE statement with an SQLDA that has no

occurrences of SQLVAR. If SQLD is greater than zero, use the value to allocate an

SQLDA with the necessary number of occurrences of SQLVAR and then process a

DESCRIBE statement using that SQLDA.

EXEC SQL BEGIN DECLARE SECTION;

DCL STMT1_STR CHAR(200) VARYING;

EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLDA;

EXEC SQL DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;

... /* code to prompt user for a query, then to generate */

/* a select-statement in the STMT1_STR

EXEC SQL PREPARE STMT1_NAME FROM :STMT1_STR;

... /* code to set SQLN to zero and to allocate the SQLDA */

EXEC SQL DESCRIBE STMT1_NAME INTO :SQLDA;

... /* code to check that SQLD is greater than zero, to set */

/* SQLN to SQLD, then to re-allocate the SQLDA

EXEC SQL DESCRIBE STMT1_NAME INTO :SQLDA;

... /* code to prepare for the use of the SQLDA

EXEC SQL OPEN DYN_CURSOR;

... /* loop to fetch rows from result table

EXEC SQL FETCH DYN_CURSOR USING DESCRIPTOR :SQLDA;

250

SQL Reference

Extended DESCRIBE

The Extended DESCRIBE statement obtains information about a select-statement

prepared by an Extended PREPARE statement.

Invocation

This statement can only be embedded in an application program written in

Assembler or REXX.

Authorization

The authorization ID of the statement must have one of the following:

v ownership of the package

v DBA authority

v EXECUTE privilege on the package.

Syntax

►► DESCRIBE section_variable IN package_spec

►

► INTO descriptor_name

►◄

NAMES

USING

ANY

BOTH

LABELS

Description

section_variable

Identifies a statement defined by an Extended PREPARE statement (see

“Extended PREPARE” on page 317). The Extended DESCRIBE statement does

not have to be in the same logical unit of work or program as the PREPARE

statement that was originally used to process the statement.

IN package_spec

Identifies the package in which the referenced SQL statement resides. The

package_spec must identify a package that exists at the application server.

The DESCRIBE option must have been specified on the CREATE PACKAGE

statement that was used to create the package.

INTO descriptor_name

Identifies an output SQLDA structure that is to receive information about the

columns that are to be retrieved by the described SQL statement. This is

identical to the descriptor used for the dynamic DESCRIBE statement.

USING

This works the same as in the dynamic DESCRIBE statement, and follows the

same rules. (See “DESCRIBE” on page 247 for more information). The labels

returned in the SQLDA are those which were in the SYSCOLUMNS catalog

table when the SQL statement was prepared.

Examples

DESCRIBE :STMID IN :USERID.:PACKNAME INTO MYSQLDA

Chapter 6. Statements

251

DESCRIBE CURSOR

The DESCRIBE CURSOR statement obtains information about the result set that is

associated with the cursor. The information, such as column information, is put

into a descriptor. Use DESCRIBE CURSOR for result set cursors from stored

procedures. The cursor must be defined with the ALLOCATE CURSOR statement.

Invocation

This statement can be embedded in an application program only. It is an

executable statement that cannot be dynamically prepared.

Authorization

None required.

Syntax

►► DESCRIBE CURSOR

cursor-name

INTO descriptor-name

►◄

host-variable

Description

cursor-name or host-variable

Identifies a name for the cursor. The name specified for cursor-name must be

unique within the logical unit of work in which it is used. It is an ordinary

identifier.

If a host-variable is specified, the following rules apply:

v It must be a character string variable with a length attribute that is not

greater than 18 bytes (A C NULL-terminated character string may be up to

19 bytes).

v It must be preceded by a colon and must not be followed by an indicator

variable.

v The cursor name must be left justified within the host variable and must not

contain embedded blanks.

v If the length of the cursor name is less that the length of the host variable, it

must be padded on the right with blanks.

INTO descriptor-name

Identifies an SQL descriptor area (SQLDA). The information returned in the

SQLDA describes the columns in the result set associated with the named

cursor. The considerations for allocating and initializing the SQLDA are similar

to those of a DESCRIBE statement used for describing a SELECT statement.

After executing the DESCRIBE CURSOR statement, the contents of the SQLDA

are the same as the DESCRIBE of a SELECT statement, with the following

exceptions:

v The first five bytes of the SQLDAID field are set to ’SQLRS’.

v Bytes 6 to 8 of the SQLDAID field are reserved. If the cursor is declared

WITH HOLD in a stored procedure, the high-order bit of the eighth byte is

set to one.

Note: DB2 Server for VSE & VM does not support CURSOR WITH HOLD.

As a result,

neither does its requester. If a cursor is opened WITH

252

SQL Reference

DESCRIBE CURSOR

HOLD by a stored procedure, it will be implicitly closed by the DB2

Server for VSE & VM requester when the unit of work is committed.

Notes

1. For the DESCRIBE CURSOR statement to be successful, the application must be

connected to the site at which the stored procedure was executed.

Examples

The statements in the following examples are assumed to be in PL/I programs.

Example 1

Place information about the result set associated with cursor C1 into the descriptor

named by :sqlda1:

EXEC SQL DESCRIBE CURSOR C1 INTO :sqlda1

Example 2

Place information about the result set associated with the cursor named by :hv1

into the descriptor named by :sqlda2:

EXEC SQL DESCRIBE CURSOR :hv1 INTO :sqlda2

Chapter 6. Statements

253

DESCRIBE PROCEDURE

The DESCRIBE PROCEDURE statement obtains information about the result sets

returned by a stored procedure. The information, such as the number of result sets,

is put into a descriptor.

Invocation

This statement can be embedded in an application program only. It is an

executable statement that cannot be dynamically prepared.

Authorization

None required.

Syntax

►► DESCRIBE PROCEDURE

host-variable

INTO descriptor-name

►◄

procedure-name

Description

host-variable or procedure-name

Identifies the stored procedure to describe. The procedure name may be

specified either directly or within a host-variable.

If a host-variable is specified, it must be a character-string variable and it must

not include an indicator variable. Note that the value is not converted to

uppercase. Procedure name must be left-justified.

If procedure-name is specified, it must be an ordinary identifier, which implies

that it cannot contain blanks or special characters, and the value is converted

to uppercase. Therefore, if it is necessary to use a lowercase name that contains

blanks or special characters, then the name must be specified in a host

variable. The form in which a procedure name exists varies according to the

server where the procedure is stored.

DB2 Server for VSE & VM:

The name of the procedure to execute. The name can be up to 18

characters long and must match a value in the NAME column of the

SYSTEM.SYSROUTINES catalog table.

DB2 Common Server/UDB:

procedure-name

The name (with no extension) of the procedure to execute. This

is used both as the name of the stored procedure library and

the function name within that library.

procedure-library!function-name

The exclamation point character acts as a delimiter between the

library name and the function name of the stored procedure.

absolute-path!function-name

The absolute-path specifies the complete path to the stored

procedure library.

In all of these cases the total length of the procedure name including

its implicit or explicit full path must not be longer than 254 bytes.

254

SQL Reference

DESCRIBE PROCEDURE

DB2 for MVS V4 or DB2 for OS/390 V5 Server:

An implicit or explicit three-part name. The parts are as follows:

high order

The location name of the server where the procedure is stored.

middle

SYSPROC

low order

Some value in the PROCEDURE column of the

SYSIBM.SYSPROCEDURES catalog table.

DB2 for OS/400 (V3.1 or later) Server:

The external program name is assumed to be the same as the

procedure-name. For portability, the procedure-name should be

specified as a single token no larger than eight bytes. The ASSOCIATE

LOCATORS statement can only be executed against a stored procedure

that has already been invoked by the program using the SQL CALL

statement.

INTO descriptor-name

Identifies an SQL descriptor area (SQLDA). The information returned in the

SQLDA describes the result sets returned by the stored procedure. Before the

DESCRIBE PROCEDURE statement is processed, the following variable in the

SQLDA must be set:

SQLN Indicates the number of variables represented by SQLVAR. (SQLN acts

as a dimension of the SQLVAR array.) SQLN must be set to a value

greater than or equal to zero before the DESCRIBE PROCEDURE

statement is processed. This value should reflect the expected number

of result sets the stored procedure is to return.

When the DESCRIBE PROCEDURE statement is processed, the database

manager assigns values to the variables of the SQLDA as follows:

SQLDAID

This field serves only as an SQLDA eye-catcher. It is set to 'SQLPR'.

SQLD This field is set to the total number of result sets. A value of zero in the

field indicates there are no result sets.

SQLVAR

This is an array with an arbitrary number of occurrences of the

variables listed below, and others that are not mentioned. There is one

SQLVAR entry for each result set. If the value of SQLD is zero, or

greater than the value of SQLN, no values are assigned to the

occurrences of SQLVAR. If the value of SQLD is n, where n is greater

than zero but less than or equal to the value of SQLN, values are

assigned to the first n occurrences of SQLVAR. Therefore, the first

occurrence of SQLVAR contains a description of the first result set, the

second occurrence of SQLVAR contains a description of the second

result set, and so on.

SQLDATA

This field of each SQLVAR entry is set to the result set locator

value associated with the result set.

SQLIND

This field of each SQLVAR entry is set to the estimated number

of rows in the result set.

Chapter 6. Statements

255

DESCRIBE PROCEDURE

SQLNAME

This field is set to the name of the cursor used by the stored

procedure to return the result set.

Notes

1. A value of -1 in the SQLIND field indicates that an estimated number of rows

in the result set is not provided.

2. DESCRIBE PROCEDURE does not return information about the parameters

expected by the stored procedure.

Examples

The statements in the following examples are assumed to be in PL/I programs.

Example 1

Place information about the result sets returned by stored procedure P1 into the

descriptor named by :sqlda1:

EXEC SQL DESCRIBE PROCEDURE P1 INTO :sqlda1

Example 2

Place information about the result sets returned by stored procedure named by

:hv1 into the descriptor named by :sqlda2:

EXEC SQL DESCRIBE PROCEDURE :hv1 INTO :sqlda2

256

SQL Reference

DROP

The DROP statement deletes an object. Any objects that are directly or indirectly

dependent on that object are also deleted. Whenever an object is deleted, its

description is deleted from the catalog and any packages that reference the object

are invalidated.

Invocation

This statement can be embedded in an application program or issued interactively.

It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least

one of the following:

v Ownership of the table, view, index, synonym, dbspace or package.

v DBA authority.

Syntax

►► DROP

DBSPACE dbspace_name

►◄

INDEX index_name

(1)

PACKAGE

package_spec

SYNONYM synonym

TABLE table_name

VIEW view_name

Notes:

PROGRAM is equivalent to PACKAGE and is provided for compatibility with older

versions of SQL/DS.

Description

DBSPACE dbspace_name

Identifies the dbspace to be dropped. It must be a dbspace that exists at the

application server. Dropping a dbspace destroys the contents of a dbspace.

When the logical unit of work is committed, the dbspace is available to be

acquired. All existing packages with dependencies on tables within the

dropped dbspace are automatically marked unusable. Both private and public

dbspaces can be dropped, but only someone with DBA authority can drop a

public dbspace. No user, even with DBA authority, can drop the dbspace

containing the database manager catalogs.

INDEX index_name

Identifies the index to be dropped. It must be an index that exists at the

application server. The table on which the index is defined is not affected. All

existing packages that use the dropped index are marked unusable.

An index created by a primary key cannot be dropped.

PACKAGE package_spec

Identifies the package to be dropped. It must be a package that exists at the

application server. Once a package is dropped, the program that uses that

package cannot be run. An owner can only drop packages which that owner

has preprocessed. Only someone with DBA authority can drop another user’s

package.

Chapter 6. Statements

257

DROP

DROP PACKAGE cannot support a qualified host structure subfield name in

the package_spec. A host structure subfield name may be used here as a normal

host_variable but must be unqualified. If being unqualified results in an

ambiguous reference, the subfield identifier name cannot be used with DROP

PACKAGE.

If the package was created using a host identifier which was not an ordinary

identifier (such as a package name beginning with a number), it must be

dropped using a host identifier; otherwise an SQL error will result. For

example, if a package named 071PACK was created using a host identifier and

DROP PACKAGE 071PACK

statement is issued, an SQLCODE of -105 (SQLSTATE of 37501) will result.

SYNONYM synonym

Identifies a synonym to be dropped. In a static DROP SYNONYM statement,

the name must identify a synonym that is owned by the owner of the package.

In a dynamic DROP SYNONYM statement, the name must identify a synonym

that is owned by the authorization ID that is executing the statement.

Dropping a synonym has no effect on the table or view that it references.

Dropping a synonym does not affect the packages of existing programs that

use the synonym, because in the packages the synonym has already been

resolved to a real table name. However, a program containing a dropped

synonym cannot be preprocessed successfully, either automatically or by user

request.

TABLE table_name

Identifies a table to be dropped. It must be a base table that exists at the

application server and cannot be a catalog table. The table is deleted from the

database and the contents of the table are lost. All indexes, keys, constraints,

and views defined on the table, and all privileges granted on the table, are also

dropped. Synonyms are not dropped. No user, even with DBA authority, can

drop a table which forms part of the database manager system catalog.

All existing packages affected by dropping the table are marked unusable. The

unusable packages remain in the database until they are explicitly dropped by

a DROP PACKAGE statement. When an SQL statement attempts to invoke an

unusable package, the database manager tries to dynamically rebind the

package. However, if the SQL statement refers to a dropped DBSPACE or table,

that SQL statement returns an error code at execution time.

VIEW view_name

Identifies the view to be dropped. It must be a view that exists at the

application server. The definition of the view is deleted from the catalog. The

definition of any view that is directly or indirectly dependent on that view is

also deleted. Whenever the definition of a view is deleted from the catalog, all

privileges on that view are also deleted.

All existing packages that use the dropped view are marked unusable.

Notes

If a DROP statement is issued for an object while some program that depends on

the object is running and has a logical unit of work in progress, the DROP

statement does not take effect until the end of the running logical unit of work.

Meanwhile, the program that has issued the DROP waits.

258

SQL Reference

DROP

When dropping a table, the database manager temporarily requires additional

space so it can restore the table in case the logical unit of work is not committed.

The database manager behaves as though a table approximately doubles in size

immediately before it is dropped. The empty pages are taken from the DBSPACE

from which the table was dropped. If the number of empty pages is less than

approximately double the table size, the database manager will stop processing

and will not issue a ROLLBACK. Note that if all rows of a table have previously

been deleted, such additional space is not required.

Examples

Example 1

Drop your table named MY_IN_TRAY.

DROP TABLE MY_IN_TRAY

Example 2

Drop your view named MA_PROJ.

DROP VIEW MA_PROJ

Example 3

Drop the package named PACKA.

DROP PACKAGE PACKA

Example 4

Drop the dbspace named MYSPACE that is owned by MIKE. (Note that the

authorization id submitting this statement must have DBA authority.)

DROP DBSPACE MIKE.MYSPACE

Chapter 6. Statements

259

DROP PROCEDURE

The DROP PROCEDURE statement removes the definition of a stored procedure

from the database manager, and takes the information for that procedure out of the

cache.

The STOP PROC command must be issued with the REJECT option before the

DROP PROCEDURE statement will be accepted.

Invocation

This statement can be issued from an application program or interactively. It is an

executable statement that can be dynamically prepared.

Authorization

The issuer of the DROP PROCEDURE statement must have DBA authority.

Syntax

►► DROP PROCEDURE procedure-name

►◄

AUTHID authid

RESTRICT

Description

procedure-name

must identify a stored procedure that has been defined (that is, a CREATE

PROCEDURE has been processed for it).

Note that DROP PROCEDURE removes the definition of the procedure only;

the package associated with the procedure, as well as the load module or

phase, is untouched.

authid

The authorization ID for the stored procedure. If specified, then only the

version of procedure-name that is accessible only by authid will be dropped.

RESTRICT

This is included for compatibility with the DB2 family. If specified, it is

ignored.

Examples

Example 1

DROP PROCEDURE MYPROC

260

SQL Reference

DROP PSERVER

The DROP PSERVER statement removes the definition of a stored procedure server

from the database manager, and takes the information for that server out of the

cache.

The STOP PSERVER command must be issued with the NOIMPLICIT option

before the DROP PSERVER statement will be accepted.

A stored procedure server cannot be dropped if the following are all true:

v The stored procedure server is the only one in its group

v Stored procedures exist that must run in this stored procedure server’s group.

Note: If the drop fails for this reason, issue the ALTER PROCEDURE statement

and use the SERVER GROUP clause to indicate that the procedure is to be

moved to a different group, then issue the DROP PSERVER statement again.

Invocation

This statement can be issued from an application program or interactively. It is an

executable statement that can be dynamically prepared.

Authorization

The issuer of the DROP PSERVER statement must have DBA authority.

Syntax

►► DROP PSERVER procedure-server

►◄

Description

procedure-server

The name of the stored procedure server. This name must be an ordinary

identifier of 1 to 8 characters.

Examples

Example 1

DROP PSERVER SRV1

Chapter 6. Statements

261

DROP STATEMENT

The DROP STATEMENT statement selectively deletes a statement from a package.

DROP STATEMENT applies only to packages created with a CREATE PACKAGE

statement with the MODIFY option.

Invocation

This statement can only be embedded in an application program written in

Assembler or REXX.

Authorization

The authorization ID of the statement must have one of the following:

v ownership of the package

v DBA authority

v EXECUTE privilege on the package.

Syntax

►► DROP STATEMENT section_variable IN package_spec

►◄

Description

section_variable

Identifies the statement defined by an Extended PREPARE statement.

IN package_spec

Identifies the package in which the referenced SQL statement resides. The

package_spec must identify a package that exists at the application server.

Notes

When a statement references an incorrect package, dynamic re-preprocessing will

occur to restore the package to a usable state. If the package has any unresolved

dependencies, the re-processing will fail and a message will be issued.

Examples

DROP STATEMENT :STMID IN :USERID.:PACKNAME

262

SQL Reference

EXECUTE

The EXECUTE statement processes a prepared SQL statement.

Invocation

This statement can only be embedded in an application program. It is an

executable statement that cannot be dynamically prepared.

Authorization

See “PREPARE” on page 313 for the authorization required to create a prepared

statement.

Syntax

►► EXECUTE statement_name

►◄

USING

host_variable_list

USING DESCRIPTOR descriptor_name

Description

statement_name

Is an ordinary identifier that identifies the prepared statement to be processed.

Statement_name must identify a statement that was previously prepared within

the logical unit of work and the prepared statement must not be a SELECT

statement.

USING

Introduces a list of host variables, host structures, or both, whose values are

substituted for the parameter markers (question marks) in the prepared

statement. (For an explanation of parameter markers, see “PREPARE” on page

313.) If the prepared statement includes parameter markers, the USING clause

must be used. USING is ignored if there are no parameter markers.

host_variable_list

Identifies one or more host variable, host structure, or both that must be

declared in the program in accordance with the rules for declaring host

variables and host structures.

The total number of host variables and host structure subfields must be the

same as the number of parameter markers in the prepared statement. The

nth variable or subfield corresponds to the nth parameter marker in the

prepared statement.

DESCRIPTOR descriptor_name

Identifies an input SQLDA structure that provides information concerning

input variables that were specified as parameter markers (?) when the

statement was prepared.

Before the EXECUTE statement is processed, the user must set the

following fields in the SQLDA:

v SQLN to indicate the number of SQLVAR occurrences provided in the

SQLDA

v SQLDABC to indicate the number of bytes of storage allocated for the

SQLDA

v SQLD to indicate the number of variables used in the SQLDA when

processing the statement

264

SQL Reference

EXECUTE

v SQLVAR occurrences to indicate the attributes of the variables and the

addresses of the data areas allowed to contain the result.

The SQLDA must have enough storage to contain all SQLVAR occurrences.

Therefore, the value in SQLDABC must be greater than or equal to 16 +

SQLN*(44).

SQLD must be set to a value greater than or equal to zero and less than or

equal to SQLN. It must be the same as the number of parameter markers

in the prepared statement. The nth variable described by the SQLDA

corresponds to the nth parameter marker in the prepared statement. (For a

description of an SQLDA, see “SQL Descriptor Area (SQLDA)” on page

359.)

Parameter Marker Replacement

Before the prepared statement is processed, each parameter marker in the

statement is effectively replaced by its corresponding host variable or host

structure subfield. The replacement is an assignment operation in which the source

is the value of the host variable or host structure subfield and the target is a

variable within the database manager. The assignment rules are those described for

assignment to a column in “Assignments and Comparisons” on page 53. The

attributes of the target variable depend on the role that the parameter marker plays

in its SQL statement. The rules for the various roles are shown below. In those

rules, “P” represents the parameter marker in question.

Arithmetic Operand: When P is an operand for an infix operator, the other

operand cannot also be a parameter marker. The data type, scale, and precision of

the target for P are the same as those of the other operand. When P is the operand

of unary minus, the data type of the target is double precision floating point.

The Pattern in a LIKE Predicate: With P in this role, the target is a varying length

string.

v If the first operand in the predicate is a short character string column, the target

is a VARCHAR(n), where n is 10 more than the length attribute of the column,

with this exception: if that length attribute is greater than 244, n is 254.

v If the first operand in the predicate is a long character string column, the target

is VARCHAR(255).

v If the first operand is a short graphic string column, the target is

VARGRAPHIC(n), where n is 5 more than the length attribute of the column,

with the following exception: if that length attribute is greater than 122, n is 127.

v If the first operand in the predicate is a long graphic string column, the target is

VARGRAPHIC(128).

Comparand: In this case, P can be a comparand in a basic predicate (for example,

“?>10”), in an IN predicate, or in a BETWEEN predicate. At least one of the

comparands in such a predicate must not be a parameter marker.

For a basic predicate, the other comparand cannot be a parameter marker.

When the parameter marker is specified as a comparison operand in the

BETWEEN predicate,

v If there is an operand that is specified solely as a column name (or a column

function with the argument being a column with a field procedure defined on

it), then the attributes of the leftmost operand are used.

v Otherwise, the attributes of the leftmost operand that is not a parameter marker

are used.

Chapter 6. Statements

265

EXECUTE

When the parameter marker is specified as a comparison operand in the IN

predicate,

v The attributes of the leftmost operand that is not a parameter marker are used.

The attributes of the target for P are the same as those of the other comparand in

the predicate, unless the data type of that comparand is DATE, TIME, or

TIMESTAMP, in which case the target is effectively CHAR(254).

Assignment Operand: For this case, P must be the value for a column in an INSERT

or UPDATE. The attributes of the target are the same as those of the column, with

the following exceptions:

v If the column has the data type DATE, the target is CHAR(n), where n is the

value of the LOCAL DATE LENGTH install option. If that option is not

specified, n is 10.

v If the column has the data type TIME, the target is CHAR(n), where n is the

value of the LOCAL TIME LENGTH install option. If that option is not

specified, n is 8.

v If the column has the data type TIMESTAMP, the target is CHAR(26).

If the column has the data type DATE, TIME, or TIMESTAMP, trailing blanks are

removed from the resulting string before assignment to the target. This is the one

exception to the rule that the target is treated like a column.

General Rules: Let V denote a host variable that corresponds to a parameter

marker P. The value of V is assigned to the target variable for P in accordance with

the rules for assigning a value to a column:

v V must be compatible with the target.

v If V is a string, its length must not be greater than the length attribute of the

target. (Trailing blanks are included in the length of the string.)

The following is an exception to the rule:

- If V is a fixed length host variable and the target is short varying-length

column, all the trailing blanks of V, if any, are always truncated before

assignment. Hence, if V’s length attribute is greater than the target’s length

attribute and all the excess positions in V contain blanks, the assignment is

completed without an error being returned.

v If V is a number, the absolute value of its integral part must not be greater than

the maximum absolute value of the integral part of the target.

v If the attributes of V are not identical to the attributes of the target, the value is

converted to conform to the attributes of the target.

When the prepared statement is processed, the value used in place of P is the

value of the target variable V. For example, if V is CHAR(6) and the target is

CHAR(8), the value used in place of P is the value of V padded on the right with

two blanks.

Examples

This example of portions of a COBOL program shows how an INSERT statement

with parameter markers is prepared and processed.

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

77 EMP

PIC X(6).

01 PROJECT.

05 PRJ

PIC X(6).

05 ACT

PIC S9(4) COMP-4.

05 TIM

PIC S9(3)V9(2).

266

SQL Reference

Extended EXECUTE

The Extended EXECUTE statement processes an SQL statement that was prepared

previously using an Extended PREPARE statement.

Invocation

This statement can only be embedded in an application program written in

Assembler or REXX.

Authorization

The authorization ID of the statement must have one of the following:

v ownership of the package

v DBA authority

v EXECUTE privilege on the package.

Syntax

►► EXECUTE section_variable IN package_spec

►

USING DESCRIPTOR descriptor_name1

►

►◄

USING OUTPUT DESCRIPTOR descriptor_name2

Description

section_variable

Identifies a statement defined by an Extended PREPARE statement. The

Extended EXECUTE statement does not have to be in the same logical unit of

work or program as the Extended PREPARE statement that was originally used

to process the statement.

IN package_spec

Identifies the package in which the referenced SQL statement resides. The

package_spec must identify a package that exists at the application server.

USING DESCRIPTOR descriptor_name1

Identifies an input SQLDA structure that provides information concerning

input variables that were specified as parameter markers (?) when the

statement was prepared.

USING OUTPUT DESCRIPTOR descriptor_name2

Identifies an output SQLDA structure that provides information about

variables into which individual fields are to be returned by the query.

This clause is only valid when using the EXECUTE statement against a section

created by the PREPARE SINGLE ROW statement and in such cases the clause

is required.

Before the Extended EXECUTE statement is processed, the user must set the fields

in the SQLDA described in the “Description” section of “EXECUTE” on page 264

and Table 20 on page 360.

268

SQL Reference

Extended EXECUTE

Notes

When the statement is processed, the host variables specified in the SQLDA are

substituted, in order, into the statement in place of the parameter markers (?) that

were given in the Extended PREPARE statement. Each variable must be of a data

type that is compatible with its usage in the “prepared” SQL statement. Extended

EXECUTE will fail if the prepared statement was a select-statement (in this case, an

Extended DECLARE CURSOR coupled with an Extended OPEN, FETCH, and

CLOSE should be used).

Examples

EXECUTE :STMID IN :USERID.:PACKNAME

USING DESCRIPTOR INSQLDA

USING OUTPUT DESCRIPTOR OUTSQLDA

Chapter 6. Statements

269

EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE statement:

v Prepares an executable form of an SQL statement from a character string form of

the statement

v Processes the SQL statement

v Destroys the executable form.

EXECUTE IMMEDIATE combines the basic functions of the PREPARE and

EXECUTE statements. It may be used to prepare and process SQL statements that

contain neither host variables nor parameter markers.

Invocation

This statement can only be embedded in an application program. It is an

executable statement that cannot be dynamically prepared.

Authorization

The authorization rules are those defined for the SQL statement specified by

EXECUTE IMMEDIATE. For example, see “INSERT Rules” on page 300 for the

authorization rules that apply when an INSERT statement is processed using

EXECUTE IMMEDIATE. The authorization ID is the run-time authorization ID.

Syntax

►► EXECUTE IMMEDIATE

string_constant

►◄

host_variable

Description

string_constant

String constants are supported in all languages except Assembler and C.

It is advisable to avoid using either delimited identifiers or DBCS strings in

statements specified in string constants.

host_variable

Identifies a host variable that must be described in the program in accordance

with the rules for declaring host variables. An indicator variable must not be

specified.

In Assembler, C, COBOL, REXX, the host variable must be a varying-length

string variable. In C, it cannot be a NUL-terminated string. In Fortran, the

host_variable must be a fixed-length string variable. In PL/I, the host variable

can either be a fixed-length or varying-length string variable. The host variable

must have a maximum length of 8192.

See “PREPARE” on page 313 for more information on the use of DBCS

constants in prepared statements in PL/I Version 2 programs.

The string_constant or host_variable must contain one of the following SQL

statements:

ACQUIRE DBSPACE

ALTER DBSPACE

270

SQL Reference

EXECUTE IMMEDIATE

ALTER PROCEDURE

ALTER PSERVER

ALTER TABLE

COMMENT ON

CREATE INDEX

CREATE PROCEDURE

CREATE PSERVER

CREATE SYNONYM

CREATE TABLE

CREATE VIEW

DELETE

DROP

DROP PROCEDURE

DROP PSERVER

EXPLAIN

GRANT Package Privileges

GRANT System Authorities

GRANT Table/View Privileges

INSERT

LABEL ON

LOCK DBSPACE

LOCK TABLE

REVOKE Package Privileges

REVOKE System Authorities

REVOKE Table/View Privileges

UPDATE

UPDATE STATISTICS

Furthermore, the statement string must not:

v Begin with EXEC SQL and end with a statement terminator

v Include references to host variables or parameter markers

v Include comments.

Notes

When an EXECUTE IMMEDIATE statement is processed, the specified statement

string is parsed and checked for errors. If the SQL statement is incorrect it is not

processed and the error condition that prevents its execution is reported in the

SQLCA. If the SQL statement is valid, but an error occurs during its execution, that

error condition is reported in the SQLCA.

If the same SQL statement is to be processed more than once, it is more efficient to

use the PREPARE and EXECUTE statements rather than the EXECUTE

IMMEDIATE statement.

Chapter 6. Statements

271

EXPLAIN

The EXPLAIN statement places information about the structure and execution

performance for a DELETE, INSERT, UPDATE, or SELECT statementinto one or

more user-supplied tables.

The information applies to the statement for which the EXPLAIN was issued, and

for any statements that have been generated internally by the database manager.

Internal statements are generated to ensure referential integrity.

The result tables used by the EXPLAIN statement are updated during

preprocessing of the containing program.

Invocation

This statement can be embedded in an application program or issued interactively.

It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include both:

v Ownership of an explanation table for each of the specified options

v The proper privileges to process the SQL statement defined by the

explainable_sql_statement.

Syntax

►► EXPLAIN

ALL

►

SET QUERYNO = integer

▼

COST

PLAN

REFERENCE

STRUCTURE

► FOR explainable_sql_statement

►◄

Description

COST

Inserts into the COST_TABLE the complete cost of the command being

analyzed and for any statements internally generated by the database manager

to enforce referential integrity.

PLAN

Inserts information into the PLAN_TABLE about the order in which tables are

accessed during execution of the statement being analyzed and for any

internally generated statements used to enforce referential integrity. Also

describes the indexes used to access the tables, the methods that the database

manageruses to do joins, and the sorts done as part of processing.

REFERENCE

Inserts one row into the REFERENCE_TABLE for each column referenced in

the statement and for any statements internally generated by the database

managerto enforce referential integrity.

Chapter 6. Statements

273

EXPLAIN

STRUCTURE

Inserts one row into the STRUCTURE_TABLE for each query block in the

statement.

ALL

Inserts information into all four of the above tables.

SET QUERYNO=integer

An integer constant that can fit into an INTEGER field. The SET QUERYNO

clause lets you place an integer value into the QUERYNO fields of the rows in

the explanation tables. Assigning a different number on each EXPLAIN will

make it easier to identify information collected. The integer value must not be

preceded by a sign and may range from 1 to 2147483647.

The SET QUERYNO clause is optional. If you omit it, a null value is placed in

the fields of the rows inserted by the EXPLAIN statement.

FOR explainable_sql_statement

The SQL statement to be analyzed. You can analyze UPDATE, DELETE, and

INSERT statements as well as SELECT statements. (SELECT statements are

considered the primary candidates for EXPLAIN analysis.)

explainable_sql_statement is not a quoted-string and must not be put in a host

variable. Host variables may not be placed in the statement; rather parameter

markers must be used and the entire EXPLAINstatement must be dynamically

prepared and processed.

The length of the SQL statement is limited to 8192 characters.

The database manager supplies customizable macros to build a set of EXPLAIN

tables for each authorization ID that needs it. For IBM VM systems, the macro file

is ARISEXP MACRO; for VSE systems, the macro is an A-type member, ARISEXP.

Both macros contain comments describing the required customizing procedure.

EXPLAIN may be invoked either explicitly as an SQL statement or implicitly with

the EXPLAIN(YES) option for CREATE PACKAGE statement, application program

preprocessing and the EXPLAIN(YES) option of the DBSU REBIND PACKAGE

command. The following tables describe the columns required in each table

associated with EXPLAIN. For more information about interpreting the data in

these tables, see the DB2 Server for VSE & VM Performance Tuning Handbook,

GC09-2987.

Table 10. Columns in COST_TABLE

Column Name

Data Type

Description

QUERYNO

INTEGER

Query number is intended to distinguish among

queries. QUERYNO is set to the value specified in

the SET QUERYNO clause. If the clause is omitted,

QUERYNO is set to NULL.

For an entry generated by the EXPLAIN(YES)

option during program preprocessing, QUERYNO

corresponds to the section number in the package

for the statement being explained.

274

SQL Reference

EXPLAIN

Table 10. Columns in COST_TABLE (continued)

Column Name

Data Type

Description

RINO

SMALLINT NOT NULL

RINO is set to zero for the user’s original statement

and will be automatically incremented by one for

each internally-generated statement that is processed

for referential integrity or cascade delete. RINO is

intended to distinguish among queries and

internally-generated queries. If RINO reaches 32,767,

the next internally-generated statement will have a

corresponding RINO value of 1, and so on.

QBLOCKNO

SMALLINT NOT NULL

Query block number, where 1 is the outer-level

query block. Different query blocks (as occur in

subqueries) receive different numbers.

PKGNAME

CHAR(8) NOT NULL

This identifies the name of the package in which

this SQL statement originated. This field is blank for

explicit EXPLAIN processing invoked by the

EXPLAIN statement.

PKGOWNER

CHAR(8) NOT NULL

This identifies the owner of the package in which

this SQL statement originated. This field is blank for

explicit EXPLAIN processing invoked by the

EXPLAIN statement.

COST

FLOAT NOT NULL

When QBLOCKNO is 1, this is a floating point

number that represents the total estimated cost of

executing the statement for which the EXPLAIN is

issued and for any statement internally generated by

the database managerto enforce referential integrity.

For other values of QBLOCKNO, this is the cost of

the subquery that has this query block as its root (as

opposed to the cost of the query block alone). To

find the cost of the query block alone, use

information from the STRUCTURE_TABLE. The

technique for doing this is described in the DB2

Server for VSE & VM Database Administration

manual.

TIMESTAMP

TIMESTAMP NOT NULL

The time at which the EXPLAIN statement was

processed.

Table 11. Columns in PLAN_TABLE

Column Name

Data Type

Description

QUERYNO

INTEGER

Query number is intended to distinguish among queries. (See

COST_TABLE for a description of QUERYNO.)

RINO

SMALLINT

RINO is intended to distinguish among queries and

NOT NULL

internally-generated queries. (See COST_TABLE for a description of

RINO.)

QBLOCKNO

SMALLINT

Query block number, where 1 is the outer level query block (which

NOT NULL

may have subqueries). Different query blocks receive different

numbers. The plans for executing different query blocks do not

refer to each other. However, STRUCTURE_TABLE provides the

parent block for each query block, and indicates when the query

block is done. This information is always implicitly part of the

execution plan.

PKGNAME

CHAR(8) NOT

This identifies the name of the package in which this SQL

NULL

statement originated. This field is blank for explicit EXPLAIN

processing invoked by the EXPLAIN statement.

Chapter 6. Statements

275

DB2 Server for VSE & VM SQL Reference (Version 7 Release 5) - page 7