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.
Finds and names an available dbspace.
>>-ACQUIRE----+-PUBLIC--+--DBSPACE NAMED--dbspace_name---------->
'-PRIVATE-'
>-----+-------------------------------------------+------------><
| .-,-----------------------------. |
| V (1) | |
'-(----+--------------------------+--+---)--'
| .-8-------. |
+-NHEADER = --+-integer-+--+
| .-128-----. |
+-PAGES = --+-integer-+----+
| .-33------. |
+-PCTINDEX = --+-integer-+-+
| .-15------. |
+-PCTFREE = --+-integer-+--+
| .-PAGE----. |
+-LOCK = --+-DBSPACE-+-----+
| '-ROW-----' |
'-STORPOOL = --integer-----'
Notes:
|Defines a cursor and associates it with a result set locator |variable.
|>>-ALLOCATE--cursor-name--CURSOR FOR RESULT SET--rs-locator-variable--> | |>-------------------------------------------------------------->< |
Changes the percentage of free space and the type of locking of a PUBLIC dbspace.
>>-ALTER DBSPACE--dbspace_name---------------------------------->
.-,--------------------------------.
V (1) |
>----(------------+-PCTFREE = integer---+--+---)---------------><
'-LOCK =--+-PAGE----+-'
+-DBSPACE-+
'-ROW-----'
Notes:
Alters the definition of a stored procedure.
ALTER PROCEDURE
>>-ALTER PROCEDURE---procedure-name----+-----------------+------>
'-AUTHID--authid--'
.-,-----------------.
V | (1)
>--------+-------------+--+------------------------------------><
'-| options |-'
Notes:
options
|--+-LANGUAGE-+-ASSEMBLE-+---------------------------------+----|
| +-C--------+ |
| +-COBOL----+ |
| '-PLI------' |
+-EXTERNAL NAME--external-program-name------------------+
+-SERVER GROUP--+-------------------+-------------------+
| '-server-group-name-' |
+-+-DEFAULT SERVER GROUP YES-+--------------------------+
| '-DEFAULT SERVER GROUP NO--' |
| (2) |
+---+-----------------+---+-GENERAL-----------------+---+
| '-PARAMETER STYLE-' | (3) | |
| '-GENERAL WITH NULLS------' |
+---STAY RESIDENT-+-NO--+-------------------------------+
| '-YES-' |
+-+-PROGRAM TYPE MAIN-----+-----------------------------+
| | (4) | |
| '-PROGRAM TYPE SUB------' |
+-RUN OPTIONS--run-time-options-------------------------+
+-RESULT--+-SET--+---integer----------------------------+
| '-SETS-' |
+-COMMIT ON RETURN-+-NO--+------------------------------+
| '-YES-' |
| (1) (5) |
+-+-NOT DETERMINISTIC-----------+-----------------------+
| | (1) (6) | |
| '-DETERMINISTIC---------------' |
| (1) |
+-+-CONTAINS SQL-----------+----------------------------+
| | (1) | |
| +-NO SQL-----------------+ |
| | (1) | |
| +-READS SQL DATA---------+ |
| | (1) | |
| '-MODIFIES SQL DATA------' |
| (1) |
+-+-NO COLLID-------------------+-----------------------+
| | (1) | |
| '-COLLID--collection-id-------' |
| (1) |
+-+-WLM ENVIRONMENT------+-name-----+-+-----------------+
| | '-(name,*)-' | |
| | (1) | |
| '-NO WLM ENVIRONMENT----------------' |
| (1) |
+-ASUTIME-------+-NO LIMIT--------+---------------------+
| '-LIMIT--integer--' |
| (1) |
+-+-------------------------------+---------------------+
| '-EXTERNAL SECURITY-+-DB2-----+-' |
| +-USER----+ |
| '-DEFINER-' |
| (1) |
'-+-NO DBINFO------+------------------------------------'
| (1) |
'-DBINFO---------'
Notes:
Alters the definition of a stored procedure server.
>>-ALTER PSERVER------------------------------------------------>
.-,------------------------------.
V (1) |
>-----procedure-server----+---------------------------+--+-----><
+-GROUP--+------------+-----+
| '-group-name-' |
+-+-AUTOSTART NO--+---------+
| '-AUTOSTART YES-' |
'-DESCRIPTION--description--'
Notes:
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)-' | | (1) | |
| +-VARCHAR--(integer)-------+ +-----------------+ |
| '-LONG VARCHAR-------------' +-FOR SBCS DATA---+ |
| +-FOR MIXED DATA--+ |
| +-FOR BIT DATA----+ |
| '-CCSID--integer--' |
| .-(1)-------. |
+--+-GRAPHIC--+-----------+-+---+------------------------+-+
| | '-(integer)-' | | (1) | |
| +-VARGRAPHIC--(integer)--+ '--------CCSID--integer--' |
| '-LONG VARGRAPHIC--------' |
| |
+-DATE-----------------------------------------------------+
+-TIME-----------------------------------------------------+
'-TIMESTAMP------------------------------------------------'
>---------------------------------------------------------------|
Notes:
|Obtains the RESULT SET LOCATOR value for each result set data type |returned by a stored procedure.
|>>-ASSOCIATE----+----------------+---+-LOCATOR-------+----------> | '-| RESULT SET |-' | (1) | | '-LOCATORS------' | | .-,----------------------. | V | |>-----(-----rs-locator-variable---+---)---WITH PROCEDURE--------> | |>-----+-host-variable--+--------------------------------------->< | '-procedure-name-' |
|Notes:
Marks the beginning of a host variable declare section, including host structures.
>>-BEGIN DECLARE SECTION---------------------------------------><
Invokes a stored procedure with a list of input/output parameters.
>>-CALL----+-procedure-name-+----------------------------------->
'-host-variable--'
>-----+-------------------------------------+------------------><
+-(--+-------------------------+---)--+
| | .-,------------------. | |
| | V | | |
| '----+-host-variable-+--+-' |
| +-constant------+ |
| '-NULL----------' |
'-USING DESCRIPTOR--descriptor-name---'
Closes the cursor identified by cursor-name.
>>-CLOSE--cursor_name------------------------------------------><
Closes the cursor identified by cursor-variable.
>>-CLOSE--cursor_variable--------------------------------------><
Adds or replaces comments in the catalog descriptions of tables, views, or columns.
>>-COMMENT ON--------------------------------------------------->
>-----+--| options_a |------------------------IS--str_constant----+>
| .-,--------------------------------. |
| V | |
'--+-table_name-+---(-----column_name--IS--str_constant---+---)-'
'-view_name--'
>--------------------------------------------------------------><
options_a
|--+-TABLE--+-table_name-+--------------+-----------------------|
| '-view_name--' |
'-COLUMN--+-table_name.column_name-+-'
'-view_name.column_name--'
Adds or replaces comments in the catalog descriptions of stored procedures.
>>-COMMENT ON PROCEDURE--procedure_name----+------------------+->
'-AUTHID--authid---'
>----IS--string_constant---------------------------------------><
Ends the current logical unit of work and commits any changes.
.-WORK-.
>>-COMMIT--+------+--+---------+-------------------------------><
'-RELEASE-'
Connects an application process or a user, or both, to an application server.
>>-CONNECT------------------------------------------------------>
>-----+--------------------------------------------------------------+>
'--+-authorization_name-+--IDENTIFIED BY----+-password------+--'
'-host_variable------' '-host_variable-'
>-----+------------------------+-------------------------------><
'-TO--+-server_name---+--'
'-host_variable-'
Creates an index on one or more columns of a table.
>>-CREATE--+--------+--INDEX--index_name-----------------------><
'-UNIQUE-'
.-,-----------------------.
V .-ASC--. |
>>-ON--table_name--(-------column_name--+------+--+--)---------><
'-DESC-'
.-PCTFREE=10------. >>-+-----------------+----------------------------------------->< '-PCTFREE=integer-'
Creates a package.
(1)
>>-CREATE PACKAGE----------------------------------------------->
>----package_spec--+------------------------------------------+-><
| .-----------------. |
| V (2) | |
'-USING OPTIONs--+-----------option---+-+--'
'-host_variable--------'
Notes:
| CCSIDSbcs (integer) | NOCHECK | CHECK | ERROR |
| CCSIDMixed (integer) | NODESCRIBE | DESCRIBE |
| CCSIDGraphic (integer) | NOEXIST | EXIST |
| CHARSUB (Sbcs | Mixed | Bit) | NOMODIFY | MODIFY |
| DATE (ISO | USA | EUR | JIS | LOCAL) | OWner (authorization-name) |
| EXPLAIN (NO | YES) | QUALifier (collection-id) |
| ISOLation (RR | RS | CS | UR | USER) | RELease (COMMIT | DEALLOCATE) |
| KEEP | REVOKE | REPLACE | NEW |
| LABEL (label-text) | TIME (ISO | USA | EUR | JIS | LOCAL) |
| NOBLock | BLock | SBLock |
Defines a stored procedure.
>>-CREATE PROCEDURE---procedure-name----+-----------------+--(-->
'-AUTHID--authid--'
>-----+----------------+--)------------------------------------->
'-| parameters |-'
.-,------------------------------------------------------.
| (1) |
V .-FENCED------------------------------------. | (8)
>---------+-+-------------------------------------------+-+----+----->
| +-LANGUAGE-+-ASSEMBLE-+---------------------+ |
| | +-C--------+ | |
| | +-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----------'
>--------------------------------------------------------------><
Notes:
parameters
.-,-------------------------------------------------------------------.
V .-IN----. |
|-----+-------+---+----------------+--| 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:
Defines a stored procedure server.
.-,-------------------------------.
V |
>>-CREATE PSERVER--procedure-server-----+---------------------------+--+->
| (1) |
+---------------------------+
+-GROUP--group-name---------+
| .-AUTOSTART NO--. |
+-+---------------+---------+
| '-AUTOSTART YES-' |
'-DESCRIPTION--description--'
>--------------------------------------------------------------><
Notes:
Defines an alternative name for a table or view.
>>-CREATE SYNONYM--synonym--FOR----+-qualified_table_name-+----><
'-qualified_view_name--'
Creates a new table.
>>-CREATE TABLE--table_name------------------------------------->
.-,---------------------------------------------.
V (1) |
>----(------------+-| column-definition-block |------+--+---)--->
| (2) |
+-| primary-key-block |------------+
+-| referential-constraint-block |-+
'-| unique-block |-----------------'
.-----------------------------------.
V |
>--------+-----------------------------+--+--------------------><
+-IN--dbspace_name------------+
| (3) |
'-DATA CAPTURE--+-NONE------+-'
'-CHANGES---'
Notes:
column-definition-block
|--column_name-------------------------------------------------->
>-----+-INTeger--------------------------------------------------+>
+-SMALLINT-------------------------------------------------+
| .-(53)------. |
+-+-FLOAT--+-----------+-+---------------------------------+
| | '-(integer)-' | |
| +-REAL-----------------+ |
| '-DOUBLE PRECISION-----' |
| .-(5,0)------------------------. |
+--+-DECimal-+---+------------------------------+----------+
| '-NUMERIC-' '-(--integer--+----------+--)--' |
| '-,integer-' |
| .-(1)-------. |
+--+-CHARacter--+-----------+-+---+-----------------+------+
| | '-(integer)-' | | (1) | |
| +-VARCHAR--(integer)-------+ +-----------------+ |
| '-LONG VARCHAR-------------' +-FOR SBCS DATA---+ |
| +-FOR MIXED DATA--+ |
| +-FOR BIT DATA----+ |
| '-CCSID--integer--' |
| .-(1)-------. (1) |
+--+-GRAPHIC--+-----------+-+---+-----------------+--------+
| | '-(integer)-' | '-CCSID--integer--' |
| +-VARGRAPHIC--(integer)--+ |
| '-LONG VARGRAPHIC--------' |
+-DATE-----------------------------------------------------+
+-TIME-----------------------------------------------------+
'-TIMESTAMP------------------------------------------------'
(1)
>-----+---------------------------------+----------------------->
'-NOT NULL--+------------------+--'
+-UNIQUE-----------+
| (2) |
'-PRIMARY KEY------'
(1)
>------| fieldproc-block |--------------------------------------|
Notes:
fieldproc-block
|--FIELDPROC--program_name----+-------------------------+-------|
| .-,-----------. |
| V | |
'-(-----constant---+---)--'
primary-key-block
.-,------------------------------.
V (1) .-ASC--. |
|--PRIMARY KEY--(--------------column_name--+------+--+--)------>
'-DESC-'
.-PCTFREE = 10----.
>-----+-----------------+---------------------------------------|
'-PCTFREE=integer-'
Notes:
referential-constraint-block
.-,--------------.
V |
|--FOREIGN KEY--+-----------------+--(-------column_name---+---->
'-constraint_name-'
>----)--REFERENCES--table_name----+---------------------------+-|
| .-RESTRICT--. |
'-ON DELETE--+-CASCADE---+--'
'-SET NULL--'
unique-block
|--UNIQUE--+-----------------+---------------------------------->
'-constraint_name-'
.-,------------------------------.
V (1) .-ASC--. |
>----(------------column_name--+------+--+---)------------------>
'-DESC-'
.-PCTFREE=10------.
>-----+-----------------+---------------------------------------|
'-PCTFREE=integer-'
Notes:
Creates a view on one or more tables or views.
>>-CREATE VIEW--view_name----+----------------------------+----->
| .-,--------------. |
| V | |
'-(-----column_name---+---)--'
>----AS--subselect--+-------------------+----------------------><
'-WITH CHECK OPTION-'
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:
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------------------------------------------><
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--+-RR-+--'
'-CS-'
Positioned delete (P)
>>-DELETE FROM----+-table_name-+-------------------------------->
'-view_name--'
(1)
>----WHERE CURRENT OF-------cursor_name------------------------><
Notes:
Retrieves information about an SQL select-statement previously prepared with a PREPARE statement.
>>-DESCRIBE--statement_name--INTO--descriptor_name-------------->
>-----+-------------------+------------------------------------><
| .-NAMES--. |
'-USING--+-ANY----+-'
+-BOTH---+
'-LABELS-'
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-'
|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-' |
|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-' | |>-------------------------------------------------------------->< |
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------------------'
Notes:
Removes a stored procedure.
>>-DROP PROCEDURE---procedure-name----+-----------------+------->
'-AUTHID--authid--'
>-----+----------+---------------------------------------------><
'-RESTRICT-'
Removes a stored procedure pserver.
>>-DROP PSERVER---procedure-server-----------------------------><
Selectively deletes a statement from a package.
>>-DROP STATEMENT--section_variable--IN--package_spec----------><
Marks the end of a host-variable declare section.
>>-END DECLARE SECTION-----------------------------------------><
Executes a prepared SQL statement.
>>-EXECUTE--statement_name-------------------------------------->
>-----+------------------------------------+-------------------><
+-USING--host_variable_list----------+
'-USING DESCRIPTOR--descriptor_name--'
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--'
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---'
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-----------------+--------------------------->
| .-,--------------. |
| V | |
'----+-COST------+--+-'
+-PLAN------+
+-REFERENCE-+
'-STRUCTURE-'
>-----+-----------------------------------+--------------------->
| (1) (2) |
'-SET QUERYNO =--integer------------'
>----FOR--explainable_sql_statement----------------------------><
Notes:
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--'
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--><
Grants the privilege to execute a package.
(1)
>>-GRANT EXECUTE ON--------package_name------------------------->
.-,-----------------------.
V |
>----TO----+-authorization_name-+--+--+-------------------+----><
'-PUBLIC-------------' '-WITH GRANT OPTION-'
Notes:
Grants authorities to users and changes passwords.
>>-GRANT-------------------------------------------------------->
>-----+--+-CONNECT--+--TO----| AUTH |--+--------+----------+---><
| +-DBA------+ '-| ID |-' |
| '-RESOURCE-' |
| .-,-----------------------. |
| V | |
+-CONNECT TO----+-authorization_name-+--+------------+
| | (1) | |
| '-ALLUSERS-----------' |
'-SCHEDULE TO--subsystemid--IDENTIFIED BY--password--'
AUTH
.-,---------------------.
V |
|------authorization_name---+-----------------------------------|
ID
.-,-----------.
V |
|---IDENTIFIED BY-----password---+------------------------------|
Notes:
Grants privileges on a table or view.
>>-GRANT-------------------------------------------------------->
.-PRIVILEGES-.
>-----+-ALL--+------------+------------------------------+------>
| .-,-------------------------------------------. |
| V (1) | |
'----+-ALTER----------------------------------+--+-'
+-DELETE---------------------------------+
| (1) |
+-INDEX----------------------------------+
+-INSERT---------------------------------+
| (1) |
+-REFERENCES-----------------------------+
+-SELECT---------------------------------+
'-UPDATE--+----------------------------+-'
| .-,--------------. |
| V | |
'-(-----column_name---+---)--'
.-,-----------------------.
V |
>----ON--+-table_name-+---TO----+-authorization_name-+--+------->
'-view_name--' '-PUBLIC-------------'
>----+-------------------+-------------------------------------><
'-WITH GRANT OPTION-'
Notes:
Inserts declarations or statements into a source program.
>>-INCLUDE----+-SQLCA----------+-------------------------------><
+-SQLDA----------+
'-text_file_name-'
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--'
>-----+--------------------------------+------------------------>
| .-,------------------. |
| V | |
'-(-----+-------------+---+---)--'
'-column_name-'
.-,-----------------------.
V |
>-----+-VALUES--(----+-constant-----------+--+---)--+----------><
| +-host_variable_list-+ |
| +-NULL---------------+ |
| '-special_register---' |
'-subselect--+---------------+----------------'
'-WITH--+-RR-+--'
'-CS-'
Adds or replaces labels in the catalog descriptions of tables, views, or columns.
>>-LABEL ON----------------------------------------------------->
>-----+--| options_a |------------------------IS--str_constant----+>
| .-,--------------------------------. |
| V | |
'--+-table_name-+---(-----column_name--IS--str_constant---+---)-'
'-view_name--'
>--------------------------------------------------------------><
options_a
|--+-TABLE--+-table_name-+--------------+-----------------------|
| '-view_name--' |
'-COLUMN--+-table_name.column_name-+-'
'-view_name.column_name--'
Acquires a shared or exclusive lock on the dbspace-name specified.
>>-LOCK DBSPACE--dbspace_name--IN----+-SHARE-----+--MODE-------><
'-EXCLUSIVE-'
Acquires a shared or exclusive lock on the named table.
>>-LOCK TABLE--table_name--IN----+-SHARE-----+--MODE-----------><
'-EXCLUSIVE-'
Opens a cursor.
>>-OPEN--cursor_name----+------------------------------------+-><
+-USING--host_variable_list----------+
'-USING DESCRIPTOR--descriptor_name--'
Opens a cursor.
>>-OPEN--cursor_variable---------------------------------------->
>-----+------------------------------------+-------------------><
'-USING DESCRIPTOR--descriptor_name--'
Prepares an SQL statement for execution from a character string form of the statement.
>>-PREPARE--statement_name--FROM----+-string_constant-+--------><
'-host_variable---'
Adds an SQL statement to an existing package.
>>-PREPARE FROM--host_variable---------------------------------><
>>-SETTING--section_variable--IN--package_spec------------------>
>-----+------------------------------------+-------------------><
'-USING DESCRIPTOR--descriptor_name--'
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--'
Allows for the creation of an indefinite section in a program.
>>-PREPARE FROM NULL SETTING--section_variable------------------> >----IN--package_spec------------------------------------------><
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------------------------------------------><
Inserts a row into a table.
>>-PUT--cursor-name--------------------------------------------><
>>-+------------------------------------+---------------------->< +-FROM--host-variable-list-----------+ '-USING DESCRIPTOR--descriptor-name--'
Inserts a row into a table.
>>-PUT--cursor_variable----------------------------------------->
>-----+------------------------------------+-------------------><
+-FROM--host_variable_list-----------+
'-USING DESCRIPTOR--descriptor_name--'
Revokes the privilege to execute a package.
(1)
>>-REVOKE EXECUTE--------ON--package_name----------------------->
.-,-----------------------.
V |
>----FROM----+-authorization_name-+--+-------------------------><
| (2) |
'-PUBLIC-------------'
Notes:
Revokes system authorities.
>>-REVOKE------------------------------------------------------->
.-,-----------------------.
V |
>-----+-CONNECT FROM----+-authorization_name-+--+-------+------><
| | (1) | |
| '-ALLUSERS-----------' |
| .-,---------------------. |
| V | |
+--+-DBA------+---FROM-----authorization_name---+-+
| '-RESOURCE-' |
'-SCHEDULE FROM--subsystemid----------------------'
Notes:
Revokes privileges on a table or view.
.-PRIVILEGES-.
>>-REVOKE----+-ALL--+------------+-------+--ON------------------>
| .-,--------------------. |
| V (1) | |
'----+-ALTER-----------+--+-'
+-DELETE----------+
| (1) |
+-INDEX-----------+
+-INSERT----------+
| (1) |
+-REFERENCES------+
+-SELECT----------+
'-UPDATE----------'
.-,-----------------------.
V |
>-----+-table_name-+--FROM----+-authorization_name-+--+--------><
'-view_name--' | (2) |
'-PUBLIC-------------'
Notes:
Ends a logical unit of work without committing any changes.
.-WORK-.
>>-ROLLBACK--+------+--+---------+-----------------------------><
'-RELEASE-'
Produces a result table consisting of one row, and assigns the values in that row to host variables.
.-,---------------------.
V |
>>-select_clause--INTO-------host_variable_list---+------------->
>----from_clause--+--------------+--+-------------+------------><
'-where_clause-' '-with_clause-'
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-'
.-,----------------------------------.
V |
>----SET-----column_name-- = --+-expression-+--+---------------->
'-NULL-------'
>-----+--------------------------+---+---------------+---------><
'-WHERE--search_condition--' '-WITH--+-RR-+--'
'-CS-'
Positioned update (P)
>>-UPDATE----+-table_name-+------------------------------------->
'-view_name--'
.-,-----------------------------.
V |
>----SET-----column_name--+-expression-+--+--------------------->
'-NULL-------'
>----WHERE CURRENT OF--cursor_name-----------------------------><
Updates the statistics in the catalog about the tables and indexes.
>>-UPDATE--+-----+--STATISTICS FOR------------------------------>
'-ALL-'
>-----+-TABLE--table_name------+-------------------------------><
'-DBSPACE--dbspace_name--'
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: