DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

Search Conditions

Specifies a condition that is true, false, or unknown about a row or group. The

common form of a search condition is column_name operator value. Refer to

“Predicate” on page 13 for additional functions available for search conditions.

►►

predicate

►

NOT

(search_condition)

► ▼

►◄

AND

predicate

NOT

(search_condition)

Chapter 1. DB2 Language Elements

Chapter 2. Functions

The two kinds of functions are column and scalar.

Column Functions

Produce a value from an argument having a collection of values that are derived

from one or more columns.

AVG

Calculates the average of a group of column values.

ALL

►► AVG

(

numeric_exp

)

►◄

DISTINCT column_name

COUNT

Obtains the number of rows or distinct values in a collection of rows or column

values.

►► COUNT

( DISTINCT column_name

)

►◄

(*)

MAX

Obtains the maximum value in a collection of column values.

ALL

►► MAX

(

exp

)

►◄

(1)

DISTINCT

column_name

Notes:

Although the keyword DISTINCT is allowed, it does not affect the result of

the function.

MIN

Obtains the minimum value in a set of column values.

ALL

►► MIN

(

exp

)

►◄

(1)

DISTINCT

column_name

Notes:

Although the keyword DISTINCT is allowed, it does not affect the result of

the function.

SUM

Obtains the total of all values in a group.

ALL

►► SUM

(

numeric_exp

)

►◄

DISTINCT column_name

Scalar Functions

Produce a single value from an argument having a single value.

CHAR

Creates a character representation of certain noncharacter data types.

►► CHAR

(

decimal_exp

)

►◄

timestamp_exp

date_exp

time_exp

,ISO

,USA

,EUR

,JIS

,LOCAL

DATE

Creates a date from an expression or timestamp.

►► DATE

(exp)

►◄

DAY

Extracts the day part of a value.

►► DAY

(

date_exp

)

►◄

timestamp_exp

date_dur_exp

timestamp_dur_exp

DAYS

Extracts an integer representation of a date. The result is one more than the

number of days from January 1, 0001, to the date.

►► DAYS

(

date_exp

)

►◄

timestamp_exp

date_str_exp

DECIMAL

Returns a decimal representation of a numeric value.

►► DECIMAL

( numeric_exp

)

►◄

,precision_integer

,scale_integer

DIGITS

Returns a character string representation of a number without a sign or decimal

point.

►► DIGITS

(

integer_exp

)

►◄

decimal_exp

FLOAT

Returns a floating-point representation of a number.

►► FLOAT

(numeric_exp)

►◄

HEX

Returns a hexadecimal representation of a value.

►► HEX

(exp)

►◄

HOUR

Extracts the hour part of a value.

►► HOUR

(

time_exp

)

►◄

timestamp_exp

time_dur_exp

timestamp_dur_exp

INTEGER

Returns an integer representation of a number.

►► INTEGER

(numeric_exp)

►◄

LENGTH

Returns the length of a value.

►► LENGTH

(exp)

►◄

MICROSECOND

Extracts the microsecond part of a value.

►► MICROSECOND

(

timestamp_exp

)

►◄

timestamp_dur_exp

Chapter 2. Functions

MINUTE

Extracts the minute part of a value.

►► MINUTE

(

time_exp

)

►◄

timestamp_exp

time_dur_exp

timestamp_dur_exp

MONTH

Extracts the month part of a value.

►► MONTH

(

date_exp

)

►◄

timestamp_exp

date_dur_exp

timestamp_dur_exp

SECOND

Extracts the seconds part of a value.

►► SECOND

(

time_exp

)

►◄

timestamp_exp

time_dur_exp

timestamp_dur_exp

STRIP

Removes blanks or another specified character from the end or the beginning of a

string.

,’ ’

►► STRIP

(

character_str

)

►◄

,’ ’

,strip_character

,’G< >’

graphic_str

,’G< >’

,strip_character

SUBSTR

Returns a part (substring) of string as indicated by the length and starting position.

►► SUBSTR

( str_exp, start_integer_exp

)

►◄

,length_integer_exp

TIME

Creates a time from a value.

►► TIME

(

time_exp

)

►◄

timestamp_exp

time_str_exp

TIMESTAMP

Creates a timestamp from a value or a pair of values that represent a date and

time.

►► TIMESTAMP

(

timestamp_exp

)

►◄

timestamp_str_exp

yyyymmddhhmmss_character_exp

370_storeclock_character_exp

date_exp

,time_exp

date_str_exp

,time_str_exp

TRANSLATE

Changes one or more characters in a string expression into other characters. For

example, it can be used to reorder characters in a string to uppercase.

►► TRANSLATE

(

character_str_exp

parameters-a

►◄

graphic_str_exp

parameters-b

)

parameters-a:

,’ABC...XYZ’,——’abc...xyz’

,X’000102...FDFEFF’

,to_str_exp

,’ ’

,from_str_exp

,pad_character

parameters-b:

,G’< >’

,to_str_exp

,from str_exp

,pad_character

VALUE

Returns the first nonnull result in a series of SQL expressions.

►► VALUE

( exp

▼

,exp

)

►◄

Chapter 2. Functions

Chapter 3. Queries

subselect

Specifies a result table derived from the tables or views identified in the FROM

clause. Subselect is a component of the fullselect statement, the CREATE VIEW

statement, the INSERT statement, and certain predicates.

►► select_clause from_clause

►

where_clause

group_by_clause

►

►◄

having_clause

select-clause

Produces a final result table by selecting only the columns indicated by the select

list from R, where R is the result of the previous operation. For example, if the

group-by-clause and having-clause are not specified, R is the result of the

where-clause.

ALL

►► SELECT

►◄

DISTINCT

▼

exp

table_name.*

view_name.*

correlation_name.*

from-clause

Names a single table or view, or produces an intermediate result table. The

intermediate result table contains all possible combinations of the rows of the

named tables or views.

▼

►► FROM

table_name

►◄

view_name

correlation_name

where-clause

Produces an intermediate result table by applying search-condition to each row of R,

where R is the result of the FROM clause. The result table contains the rows of R

for which the search-condition is true.

►► WHERE search_condition

►◄

group-by-clause

Produces an intermediate result table by grouping the rows of R, where R is the

result of the previous clause.

▼

►► GROUP BY

column_name

►◄

having-clause

Produces an intermediate result table by applying search-condition to each group of

R, where R is the result of the previous clause.

►► HAVING search_condition

►◄

fullselect

Specifies a result table. If UNION is not used, the result of the fullselect is the

result of the specified subselect.

▼

►►

subselect

►◄

(fullselect)

UNION

subselect

UNION ALL

(fullselect)

select-statement

Is the form of a query that can be specified or referenced in a DECLARE CURSOR

statement and in the interactive select statement.

►► fullselect

►◄

order_by_clause

with_clause

(1)

update_clause

Notes:

The interactive select-statement does not incorporate the update-clause. That

clause cannot be issued in ISQL or in the DBS Utility.

order-by-clause

Orders the rows of the result table by the values of the identified columns.

ASC

▼

►► ORDER BY

column_name

►◄

integer

DESC

update-clause

Refers to the cursor in a positioned UPDATE statement. The UPDATE statement

can update only columns in the column-name list. This update-clause cannot be

used interactively.

▼

►► FOR UPDATE OF

column_name

►◄

with-clause

Specifies the isolation level at which the statement is executed.

►► WITH

►◄

Chapter 3. Queries

Chapter 4. SQL Statements

Invocation

The letters I and P, printed to the right of the statement, indicate where each

statement can be used. The I indicates the statement can be issued interactively,

and the P indicates the statement can be embedded in an application program.

ACQUIRE DBSPACE (I,P)

Finds and names an available dbspace.

►► ACQUIRE

PUBLIC

DBSPACE NAMED

dbspace_name

►

PRIVATE

►

►◄

(1)

(

▼

NHEADER =

integer

)

128

PAGES =

integer

PCTINDEX =

integer

PCTFREE =

integer

PAGE

LOCK =

DBSPACE

ROW

STORPOOL = integer

Notes:

If any of these clauses is specified more than once, the value with the first

specification is used.

ALLOCATE CURSOR (P)

Defines a cursor and associates it with a result set locator variable.

►► ALLOCATE cursor-name CURSOR FOR RESULT SET rs-locator-variable

►◄

ALTER DBSPACE (I,P)

Changes the percentage of free space and the type of locking of a PUBLIC dbspace.

(1)

▼

►► ALTER DBSPACE dbspace_name

(

PCTFREE = integer

)

►◄

LOCK =

PAGE

DBSPACE

ROW

Notes:

If either of these clauses is specified more than once, the value with the first

specification is used.

ALTER PROCEDURE (I,P)

Alters the definition of a stored procedure.

ALTER PROCEDURE

►► ALTER PROCEDURE procedure-name

►

AUTHID authid

(1)

► ▼

►◄

options

Notes:

One or more clauses may be specified, however each clause may be specified

at most once.

options:

LANGUAGE ASSEMBLE

COBOL

PLI

EXTERNAL NAME external-program-name

SERVER GROUP

server-group-name

DEFAULT SERVER GROUP YES

DEFAULT SERVER GROUP NO

(1)

GENERAL

PARAMETER STYLE

(2)

GENERAL WITH

NULLS

STAY RESIDENT NO

YES

PROGRAM TYPE MAIN

(3)

PROGRAM TYPE SUB

RUN OPTIONS run-time-options

RESULT

SET

integer

SETS

COMMIT ON RETURN NO

YES

(4)

(5)

NOT DETERMINISTIC

(4)

(6)

DETERMINISTIC

(4)

CONTAINS SQL

(4)

NO SQL

(4)

READS SQL DATA

(4)

MODIFIES SQL DATA

(4)

NO COLLID

(4)

COLLID collection-id

(4)

WLM ENVIRONMENT

name

(name,*)

(4)

NO WLM ENVIRONMENT

(4)

ASUTIME

NO LIMIT

LIMIT integer

(4)

EXTERNAL SECURITY DB2

USER

DEFINER

(4)

NO DBINFO

(4)

DBINFO

Notes:

SIMPLE CALL may be used as an alternative to GENERAL. This is for

compatibility within the DB2 family.

SIMPLE CALL WITH NULLS may be used as an alternative to GENERAL

WITH NULLS. This is for compatibility within the DB2 family.

Currently, DB2 Server for VSE & VM supports stored procedures written as

main programs only.

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

it is ignored.

Chapter 4. SQL Statements

VARIANT may be specified as an alternative to NOT DETERMINISTIC. This

is for compatibility within the DB2 family.

NOT VARIANT may be specified as an alternative to DETERMINISTIC. This

is for compatibility within the DB2 family.

ALTER PSERVER (I,P)

Alters the definition of a stored procedure server.

(1)

▼

►► ALTER PSERVER

procedure-server

GROUP

►◄

group-name

AUTOSTART NO

AUTOSTART YES

DESCRIPTION description

Notes:

One or more clauses may be specified, however each clause may be specified

at most once.

ALTER TABLE (I,P)

Adds a new column, or adds, drops, activates, or deactivates a primary key,

foreign key, or unique constraint on a specified table.

►► ALTER TABLE table_name

ADD

column-definition-block

►◄

ADD

primary-key-block

referential-constraint-block

unique-block

DROP

PRIMARY KEY

FOREIGN KEY constraint_name

UNIQUE constraint_name

ACTIVATE

ALL

PRIMARY KEY

FOREIGN KEY constraint_name

UNIQUE constraint_name

DEACTIVATE

ALL

PRIMARY KEY

FOREIGN KEY constraint_name

UNIQUE constraint_name

DATA CAPTURE

NONE

CHANGES

column-definition-block:

column_name

data-type

(1)

fieldproc-block

data-type:

INTeger

SMALLINT

(53)

FLOAT

(integer)

REAL

DOUBLE PRECISION

(5,0)

DECimal

NUMERIC

( integer

)

,integer

(1)

CHARacter

(integer)

FOR SBCS DATA

VARCHAR

(integer)

FOR MIXED DATA

LONG VARCHAR

FOR BIT DATA

CCSID integer

(1)

GRAPHIC

(integer)

(1)

VARGRAPHIC

(integer)

CCSID integer

LONG VARGRAPHIC

DATE

TIME

TIMESTAMP

Notes:

These clauses may be specified in any order.

ASSOCIATE LOCATORS (P)

Obtains the RESULT SET LOCATOR value for each result set data type returned

by a stored procedure.

►► ASSOCIATE

LOCATOR

►

RESULT SET

(1)

LOCATORS

▼

►

(

rs-locator-variable

) WITH PROCEDURE

host-variable

►◄

procedure-name

Notes:

RESULT SET LOCATOR variables are only supported in client applications

written in Assembler, C, COBOL, and PL/I.

BEGIN DECLARE SECTION (P)

Marks the beginning of a host variable declare section, including host structures.

►► BEGIN DECLARE SECTION

►◄

Chapter 4. SQL Statements

CALL (P)

Invokes a stored procedure with a list of input/output parameters.

►► CALL

procedure-name

►◄

host-variable

(

)

▼

host-variable

constant

NULL

USING DESCRIPTOR descriptor-name

CLOSE (P)

Closes the cursor identified by cursor-name.

►► CLOSE cursor_name

►◄

Extended CLOSE (P)

Closes the cursor identified by cursor-variable.

►► CLOSE cursor_variable

►◄

COMMENT ON (I,P)

Adds or replaces comments in the catalog descriptions of tables, views, or

columns.

►► COMMENT ON

options_a

IS str_constant

►◄

table_name

(

options_b

)

view_name

options_a

TABLE

table_name

view_name

COLUMN

table_name.column_name

view_name.column_name

options_b

▼

column_name IS str_constant

COMMENT ON PROCEDURE (I,P)

Adds or replaces comments in the catalog descriptions of stored procedures.

►► COMMENT ON PROCEDURE procedure_name

►

AUTHID authid

► IS string_constant

►◄

COMMIT (I,P)

Ends the current logical unit of work and commits any changes.

WORK

►► COMMIT

►◄

RELEASE

CONNECT (I,P)

Connects an application process or a user, or both, to an application server.

►► CONNECT

►

authorization_name

IDENTIFIED BY

password

host_variable

►

►◄

server_name

host_variable

CREATE INDEX (I,P)

Creates an index on one or more columns of a table.

►► CREATE

INDEX index_name

►◄

UNIQUE

ASC

▼

►► ON table_name

(

column_name

)

►◄

DESC

PCTFREE = 10

►►

►◄

PCTFREE = integer

CREATE PACKAGE (P)

Creates a package.

Chapter 4. SQL Statements

(1)

►►

CREATE PACKAGE

package_spec

►◄

(2)

▼

USING OPTIONs

option

host_variable

Notes:

PROGRAM is equivalent to PACKAGE, and is provided for compatibility

with some older versions of the SQL/DS product.

An option may be specified only once.

Using Options

Table 1.

CCSIDSbcs (integer)

NOCHECK | CHECK | ERROR

CCSIDMixed (integer)

NODESCRIBE | DESCRIBE

CCSIDGraphic (integer)

NOEXIST | EXIST

CHARSUB (Sbcs | Mixed | Bit)

NOMODIFY | MODIFY

DATE (ISO | USA | EUR | JIS |

OWner (authorization-name)

LOCAL)

EXPLAIN (NO | YES)

QUALifier (collection-id)

ISOLation (RR | RS | CS | UR |

RELease (COMMIT | DEALLOCATE)

USER)

KEEP | REVOKE

REPLACE | NEW

LABEL (label-text)

TIME (ISO | USA | EUR | JIS |

LOCAL)

NOBLock | BLock | SBLock

CREATE PROCEDURE (I,P)

Defines a stored procedure.

►► CREATE PROCEDURE procedure-name

(

)

►

AUTHID authid

parameters

(1)

FENCED

(8)

▼

►

►◄

LANGUAGE ASSEMBLE

COBOL

PLI

EXTERNAL

NAME external-program-name

SERVER GROUP

server-group-name

DEFAULT SERVER GROUP YES

DEFAULT SERVER GROUP NO

PARAMETER STYLE

(3)

GENERAL WITH NULLS

(2)

GENERAL

STAY RESIDENT NO

STAY RESIDENT YES

PROGRAM TYPE MAIN

(4)

PROGRAM TYPE SUB

RUN OPTIONS run-time-options

RESULT SET 0

RESULT SET

integer

SETS

COMMIT ON RETURN NO

COMMIT ON RETURN YES

(5)

NOT DETERMINISTIC

(6)

DETERMINISTIC

(7)

CONTAINS SQL

(7)

NO SQL

(7)

READS SQL DATA

(7)

MODIFIES SQL DATA

(7)

NO COLLID

(7)

COLLID collection-id

(7)

WLM ENVIRONMENT

name

(name,*)

(7)

NO WLM ENVIRONMENT

(7)

ASUTIME NO LIMIT

(7)

ASUTIME LIMIT integer

(7)

EXTERNAL SECURITY DB2

(7)

EXTERNAL SECURITY

USER

DEFINER

(7)

NO DBINFO

(7)

DBINFO

Chapter 4. SQL Statements

Notes:

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

it is ignored.

As an alternative to GENERAL, SIMPLE CALL may be used. This is for

compatibility within the DB2 family.

As an alternative to GENERAL WITH NULLS, SIMPLE CALL WITH NULLS

may be used. This is for compatibility within the DB2 family.

Currently, DB2 Server for VSE & VM supports stored procedures written as

main programs only.

VARIANT may be specified as an alternative to NOT DETERMINISTIC. This

is for compatibility within the DB2 family.

NOT VARIANT may be specified as an alternative to DETERMINISTIC. This

is for compatibility within the DB2 family.

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

it is ignored.

One or more clauses may be specified, however each clause may be specified

at most once.

parameters:

▼

data-type

OUT

parameter-name

(1)

INOUT

AS LOCATOR

data-type:

INT

INTEGER

SMALLINT

REAL

FLOAT

DOUBLE

DOUBLE PRECISION

DECIMAL

DEC

( integer

)

,integer

CHARACTER

CHAR

(integer)

FOR

SBCS

DATA

MIXED

BIT

VARCHAR(integer)

FOR

SBCS

DATA

MIXED

BIT

GRAPHIC(integer)

VARGRAPHIC(integer)

Notes:

This parameter is included for compatibility with the DB2

family. If specified,

it is ignored.

CREATE PSERVER (I,P)

Defines a stored procedure server.

(1)

►►

CREATE PSERVER procedure-server

▼

►◄

GROUP group-name

AUTOSTART NO

AUTOSTART YES

DESCRIPTION description

Notes:

One or more clauses may be specified, however each clause may be specified

at most once.

CREATE SYNONYM (I,P)

Defines an alternative name for a table or view.

►► CREATE SYNONYM synonym FOR

qualified_table_name

►◄

qualified_view_name

CREATE TABLE (I,P)

Creates a new table.

►► CREATE TABLE table_name

►

(1)

▼

►

(

column-definition-block

)

►

(2)

primary-key-block

referential-constraint-block

unique-block

► ▼

►◄

IN dbspace_name

(3)

DATA CAPTURE

NONE

CHANGES

Notes:

There can be up to 255 columns in a table.

Only one primary key may be defined (either in a primary-key-block or as a

column attribute).

The same clause must not be specified more than once.

Chapter 4. SQL Statements

column-definition-block:

column_name

INTeger

►

SMALLINT

(53)

FLOAT

(integer)

REAL

DOUBLE PRECISION

(5,0)

DECimal

NUMERIC

( integer

)

,integer

(1)

CHARacter

(integer)

FOR SBCS DATA

VARCHAR

(integer)

FOR MIXED DATA

LONG VARCHAR

FOR BIT DATA

CCSID integer

(1)

GRAPHIC

(integer)

CCSID integer

VARGRAPHIC

(integer)

LONG VARGRAPHIC

DATE

TIME

TIMESTAMP

(1)

►

fieldproc-block

NOT NULL

UNIQUE

(2)

PRIMARY KEY

Notes:

These clauses may be specified in any order.

Only one primary key may be defined (either in a primary-key-block or as a

column attribute).

►►

►◄

fieldproc-block:

FIELDPROC program_name

▼

(

constant

)

primary-key-block:

(1)

ASC

PCTFREE = 10

PRIMARY KEY

(

▼

column_name

)

DESC

PCTFREE = integer

Notes:

A PRIMARY KEY can have up to 16 columns.

referential-constraint-block:

▼

FOREIGN KEY

(

column_name

►

constraint_name

► ) REFERENCES table_name

RESTRICT

ON DELETE

CASCADE

SET NULL

unique-block:

(1)

ASC

▼

UNIQUE

(

column_name

)

►

constraint_name

DESC

PCTFREE = 10

►

PCTFREE = integer

Notes:

There can be up to 16 columns on a unique constraint.

CREATE VIEW (I,P)

Creates a view on one or more tables or views.

►► CREATE VIEW view_name

►

▼

(

column_name

)

► AS subselect

►◄

WITH CHECK OPTION

Chapter 4. SQL Statements

DECLARE CURSOR (P)

Declares the cursor that you can use to fetch or put the results of a prepared

statement.

►► 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.

Extended DECLARE CURSOR (P)

Declares the cursor that you can use to fetch or put the results of a prepared

statement.

►► DECLARE cursor_variable CURSOR FOR section_variable IN package_spec

►◄

DELETE (I,P)

Deletes one or more rows from a table or view. Deleting a row from a view deletes

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

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.

DESCRIBE (P)

Retrieves information about an SQL select-statement previously prepared with a

PREPARE statement.

►► DESCRIBE statement_name INTO descriptor_name

►◄

NAMES

USING

ANY

BOTH

LABELS

Extended DESCRIBE (P)

Retrieves information about an SQL SELECT statement previously prepared with

an Extended PREPARE statement.

►► DESCRIBE statement_variable IN package_spec

►

► INTO descriptor_name

►◄

NAMES

USING

ANY

BOTH

LABELS

DESCRIBE CURSOR (P)

Obtains information about the result set that is associated with the cursor and puts

that information into a descriptor.

►► DESCRIBE CURSOR

cursor-name

INTO descriptor-name

►◄

host-variable

DESCRIBE PROCEDURE (P)

Obtains information about the result sets returned by a stored procedure and puts

that information into a descriptor.

►► DESCRIBE PROCEDURE

host-variable

INTO descriptor-name

►◄

procedure-name

DROP (I,P)

Deletes an object. Objects that are directly or indirectly dependent on that object

are also deleted.

►► DROP

DBSPACE dbspace_name

►◄

INDEX index_name

(1)

(2)

PACKAGE

package_spec

SYNONYM synonym

TABLE table_name

VIEW view_name

Chapter 4. SQL Statements

Notes:

PROGRAM is equivalent to PACKAGE and is provided for compatibility with

prior releases of SQL/DS.

DROP PACKAGE cannot support a qualified structure subfield name. A host

structure subfield name can be used as normal host variables, but must be

unqualified. If being unqualified results in an ambiguous reference, the

subfield cannot be used.

DROP PROCEDURE (I,P)

Removes a stored procedure.

►► DROP PROCEDURE procedure-name

►◄

AUTHID authid

RESTRICT

DROP PSERVER (I,P)

Removes a stored procedure pserver.

►► DROP PSERVER procedure-server

►◄

DROP STATEMENT (P)

Selectively deletes a statement from a package.

►► DROP STATEMENT section_variable IN package_spec

►◄

END DECLARE SECTION (P)

Marks the end of a host-variable declare section.

►► END DECLARE SECTION

►◄

EXECUTE (P)

Executes a prepared SQL statement.

►► EXECUTE statement_name

►◄

USING host_variable_list

USING DESCRIPTOR descriptor_name

Extended EXECUTE (P)

Executes a statement previously prepared by an Extended PREPARE statement.

►► EXECUTE section_variable IN package_spec

►

►

USING DESCRIPTOR descriptor_name_1

►

►◄

USING OUTPUT DESCRIPTOR descriptor_name_2

EXECUTE IMMEDIATE (P)

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

the statement, executes the SQL statement, and then destroys the executable form.

►► EXECUTE IMMEDIATE

string_constant

►◄

host_variable

EXPLAIN (I,P)

Retrieves information about the access path chosen for the execution of the SQL

query, and about the structure and execution performance of a DELETE, INSERT,

UPDATE or select-statement.

►► EXPLAIN

ALL

►

(1)

(2)

SET QUERYNO = integer

▼

COST

PLAN

REFERENCE

STRUCTURE

► FOR explainable_sql_statement

►◄

Notes:

The QUERYNO can be up to 2,147,483,647.

The integer constant must not be preceded by a sign.

FETCH (P)

Positions a cursor on the next row of its result table and assigns the values of that

row to the host variables.

►► FETCH cursor_name

INTO host_variable_list

►◄

USING DESCRIPTOR descriptor_name

Extended FETCH (P)

Positions a cursor on the next row of its result table and assigns the values of that

row to the host variables.

►► FETCH cursor_variable USING DESCRIPTOR descriptor_name

►◄

Chapter 4. SQL Statements

GRANT Package Privileges (I,P)

Grants the privilege to execute a package.

(1)

▼

►► GRANT EXECUTE ON

package_name

authorization_name

►

PUBLIC

►

►◄

WITH GRANT OPTION

Notes:

RUN can be specified as a synonym for EXECUTE to support applications

developed for previous releases of SQL/DS.

GRANT System Authorities (I,P)

Grants authorities to users and changes passwords.

►► GRANT

CONNECT

AUTH

►◄

DBA

RESOURCE

▼

CONNECT TO

authorization_name

(1)

ALLUSERS

SCHEDULE TO subsystemid IDENTIFIED BY

password

AUTH:

▼

authorization_name

ID:

▼

IDENTIFIED BY

password

Notes:

ALLUSERS can only be specified once and is not applicable to a VSE

application server.

GRANT Table Privileges (I,P)

Grants privileges on a table or view.

PRIVILEGES

►► GRANT

ALL

►

(1)

▼

ALTER

DELETE

(1)

INDEX

INSERT

(1)

REFERENCES

SELECT

UPDATE

(

▼

column_name

)

►

table_name

▼

authorization_name

►◄

view_name

PUBLIC

WITH GRANT

OPTION

Notes:

ALTER, INDEX, and REFERENCES do not apply to views.

INCLUDE (P)

Inserts declarations or statements into a source program.

►► INCLUDE

SQLCA

►◄

SQLDA

text_file_name

INSERT (I,P)

Inserts rows into a table or view. Inserting a row in a view inserts the row into the

tables on which the view is based.

►► INSERT INTO

table_name

►

view_name

▼

(

)

column_name

Chapter 4. SQL Statements

▼

►

VALUES

(

constant

)

►◄

host_variable_list

NULL

special_register

subselect

WITH

LABEL ON (I,P)

Adds or replaces labels in the catalog descriptions of tables, views, or columns.

►► LABEL ON

options_a

IS str_constant

►◄

table_name

(

options_b

)

view_name

options_a

TABLE

table_name

view_name

COLUMN

table_name.column_name

view_name.column_name

options_b

▼

column_name IS str_constant

LOCK DBSPACE (I,P)

Acquires a shared or exclusive lock on the dbspace-name specified.

►► LOCK DBSPACE dbspace_name IN

MODE

►◄

EXCLUSIVE

LOCK TABLE (I,P)

Acquires a shared or exclusive lock on the named table.

►► LOCK TABLE table_name IN

MODE

►◄

EXCLUSIVE

OPEN (P)

Opens a cursor.

►► OPEN cursor_name

►◄

USING host_variable_list

USING DESCRIPTOR descriptor_name

Extended OPEN CURSOR (P)

Opens a cursor.

►► OPEN cursor_variable

►◄

USING DESCRIPTOR descriptor_name

PREPARE (P)

Prepares an SQL statement for execution from a character string form of the

statement.

►► PREPARE statement_name FROM

string_constant

►◄

host_variable

Extended PREPARE (P)

Basic Extended PREPARE

Adds an SQL statement to an existing package.

►► PREPARE FROM host_variable

►◄

►► SETTING section_variable IN package_spec

►

►◄

USING DESCRIPTOR descriptor_name

Single Row Extended PREPARE

Indicates that the SELECT statement in the host-variable is a single row SELECT.

►► PREPARE SINGLE ROW FROM host_variable

►◄

►► SETTING section_variable IN package_spec

►

►◄

USING DESCRIPTOR descriptor_name

Chapter 4. SQL Statements

Empty Extended PREPARE

Allows for the creation of an indefinite section in a program.

►► PREPARE FROM NULL SETTING section_variable IN package_spec

►◄

Temporary Extended PREPARE

Prepares the statement in the host-variable and associates the output with a

previously created indefinite section.

►► PREPARE FROM host_variable FOR section_variable IN package_spec

►◄

PUT (P)

Inserts a row into a table.

►► PUT cursor-name

►◄

►►

►◄

FROM host-variable-list

USING DESCRIPTOR descriptor-name

Extended PUT (P)

Inserts a row into a table.

►► PUT cursor_variable

►◄

FROM host_variable_list

USING DESCRIPTOR descriptor_name

REVOKE Package Privileges (I,P)

Revokes the privilege to execute a package.

(1)

▼

►► REVOKE EXECUTE

ON package_name

FROM

authorization_name

►◄

(2)

PUBLIC

Notes:

RUN can be used as a synonym for EXECUTE and is provided for

compatibility with previous versions of SQL/DS.

PUBLIC is specified only once.

REVOKE System Authorities (I,P)

Revokes system authorities.

▼

►► REVOKE

CONNECT FROM

authorization_name

►◄

(1)

ALLUSERS

▼

DBA

FROM

authorization_name

RESOURCE

SCHEDULE FROM subsystemid

Notes:

ALLUSERS can only be specified once.

REVOKE Table Privileges (I,P)

Revokes privileges on a table or view.

interactive-select-statement

►►

fullselect

►

ASC

▼

ORDER BY

column_name

integer

DESC

►

►◄

FOR FETCH ONLY

WITH

FOR READ ONLY

▼

FOR UPDATE OF

(1)

column_name

Notes:

The update-clause cannot be specified if the fullselect contains an

order-by-clause.

fullselect:

▼

subselect

(fullselect)

UNION

subselect

UNION ALL

(fullselect)

Chapter 4. SQL Statements

ROLLBACK (I,P)

Ends a logical unit of work without committing any changes.

WORK

►► ROLLBACK

►◄

RELEASE

SELECT INTO (P)

Produces a result table consisting of one row, and assigns the values in that row to

host variables.

►► select_clause INTO

▼

host_variable_list

►

► from_clause

►◄

where_clause

with_clause

UPDATE (I,P)

Updates one or more column values in one or more rows of a table or view.

Updating a row of a view updates a row of its base tables.

Searched update (I,P)

►► UPDATE

table_name

►

view_name

correlation_name

▼

►

SET

column_name

expression

►

NULL

WHERE search_condition

►

►◄

WITH

Positioned update (P)

▼

►► UPDATE

table_name

SET

column_name

expression

►

view_name

NULL

► WHERE CURRENT OF cursor_name

►◄

UPDATE STATISTICS (I,P)

Updates the statistics in the catalog about the tables and indexes.

►► UPDATE

STATISTICS FOR

TABLE table_name

►◄

ALL

DBSPACE dbspace_name

WHENEVER (P)

Specifies the action to be taken when a specified exception condition occurs.

►► WHENEVER

SQLERROR

CONTINUE

►◄

SQLWARNING

(1)

STOP

GOTO

host_label

GO TO

NOT FOUND

CONTINUE

GOTO

host_label

GO TO

Notes:

STOP is not valid for C and FORTRAN.

Chapter 4. SQL Statements

Chapter 5. Preprocessing the Program

Program Preparation Command - VM Users

►►

SQLPREP

ASM

PrepParm

►

COBol

FORTran

PLI

► ( PREPname=

package_id

►

collection_id.

►

,PrepFile=

(

fileparms

)

prepparms

►

)

►

,USERid= authorization_name/password

►

sysIN

(

fileparms

)

sysPRint

(

fileparms

)

Reader

Printer

Terminal

►

sysPUnch

(

fileparms

)

Punch

(1)

(2)

►

multiple-user-mode-parms

►◄

(2)

single-user-mode-parms

Notes:

Optional for multiple-user-mode.

Valid for DB2 Server for VM only.

fileparms:

filename

filetype

filemode

prepparms:

,APOST

,NOBLocK

►

(1)

,BLocK

,CCSIDGraphic

(integer)

,Quote

(2)

,SBLocK

►

,CCSIDMixed

(integer)

,CCSIDSbcs

(integer)

,NOCHECK

(1)

►

,CHARSUB

(

Sbcs

)

,CHECK

(1)

,COBRC

Mixed

,ERROR

,COB2

Bit

,CTOKEN

(NO)

,NOEXIST

►

,CTOKEN

(

)

,DATE

(

EUR

)

,EXIST

YES

ISO

JIS

LOCAL

USA

,EXPLAIN

(NO)

►

,EXPLAIN

(

)

(3)

(4)

YES

,NOFOR

,DYNALC

Notes:

COBOL only (DB2 Server for VM only).

Not meaningful for DB2 Server for VSE; (DB2 Server for VM only).

Implied if STDSQL(89) is specified for DB2 Server for VM.

COBOL, PL/I, C, and Assember only.

prepparms (continued):

,NOGRaphic

,ISOLation

(RR)

,KEEP

►

(1)

,ISOLation

(

)

,REVOKE

,GRaphic

(2)

USER

,LineCount

(60)

►

,LABEL

(label_text)

,LineCount

(integer)

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 10