Appendix B. List of SQLs

The following table shows a list of the SQL statements that can be used by type. The "Under OLTP" column indicates whether or not the SQL statement can be used in an X/Open-compliant UAP running under OLTP.

Table B-1 SQL statements (definition SQL)

TypeFunctionAvailable SQL
CCO- BOLUnder OLTPDistributed database (server type)
HiRDBOther than HiRDB
ALTER PROCEDURE (Recreate SQL object of procedure)Recreates an SQL object of a procedure.YYNNN
ALTER ROUTINE (Recreate SQL objects for functions, procedures, and triggers)Recreates SQL objects for functions, procedures, and triggers.YYNNN
ALTER TABLE (Alter table definition)Adds column to a base table.
Changes the data type.
Increases the maximum length of columns of the variable-length data type.
Deletes empty base table columns.
Changes the uniqueness constraint on empty base table cluster keys.
Renames tables and columns.
YYNNN
ALTER TRIGGER
(Recreate trigger SQL object)
Recreates a trigger SQL object.YYNNN
COMMENT (Comment)Adds a comment in a table or a column.YYNNN
CREATE ALIAS (Define table alias)Defines a table alias.YYNNN
CREATE AUDIT
(Define audit target event)
Defines the audit event to be recorded as an audit trail, and its target.YYNNN
CREATE CONNECTION SECURITY
(Define CONNECT security facility)
Defines security items related to the CONNECT security facility.YYNNN
CREATE FOREIGN INDEX
(Define foreign index)
Defines a foreign index.YYNNN
CREATE FOREIGN TABLE
(Define foreign table)
Defines a foreign table.YYNNN
CREATE FUNCTION (Define function)Defines a function.YYNNN
CREATE INDEX (Define index)Defines an index (ascending or descending order) on the columns in a base table.YYNNN
CREATE PROCEDURE (Define procedure)Defines a procedure.YYNNN
CREATE SCHEMA (Define schema)Defines a schema.YYNNN
CREATE SERVER
(Define foreign server)
Defines a foreign server.YYNNN
CREATE TABLE (Define base table)Defines a base table.YYNNN
CREATE TRIGGER
(Define trigger)
Defines a trigger.YYNNN
CREATE TYPE (Define type)Defines an abstract data type.YYNNN
CREATE USER MAPPING
(Define user mapping)
Defines user mapping.YYNNN
CREATE VIEW (Define view table)Defines a view table.YYNNN
CREATE PUBLIC VIEW
(Define public view)
Defines a public view.YYNNN
DROP ALIAS (Delete table alias)Deletes a table alias.YYNNN
DROP AUDIT
(Delete audit target event)
Deletes from the audit targets definitions for which the audit target event defined in CREATE AUDIT matches the contents.YYNNN
DROP CONNECTION SECURITY
(Delete CONNECT security facility)
Deletes security items related to the CONNECT security facility.YYNNN
DROP DATA TYPE (Delete user-defined data type)Deletes a user-defined data type.YYNNN
DROP FOREIGN INDEX
(Delete foreign index)
Deletes the definition of a foreign index.YYNNN
DROP FOREIGN TABLE
(Define foreign table)
Deletes the definition of a foreign table.YYNNN
DROP FUNCTION (Delete function)Deletes a function.YYNNN
DROP INDEX (Delete index)Deletes an index.YYNNN
DROP PROCEDURE (Delete a procedure)Deletes a procedure.YYNNN
DROP SCHEMA (Delete schema)Deletes a schema.YYNNN
DROP SERVER
(Delete foreign server)
Deletes the definition of a foreign server.YYNNN
DROP TABLE (Delete table)Deletes a base table and any indexes, comments, access privilege, view tables, and triggers that are associated with it.YYNNN
DROP TRIGGER
(Delete trigger)
Deletes a trigger.YYNNN
DROP USER MAPPING
(Delete user mapping)
Deletes user mapping.YYNNN
DROP VIEW (Delete view table)Deletes a view table.YYNNN
DROP PUBLIC VIEW
(Deletes public view)
Deletes a public view.YYNNN
GRANT AUDIT
(Change auditor's password)
Changes the auditor's password.YYNNN
GRANT CONNECT (Grant connect privilege)Grants the connect privilege to a user.YYNNN
GRANT DBA (Grant DBA privilege)Grants the DBA privilege to a user.YYNNN
GRANT RDAREA (Grant RDAREA utilization privilege)Grants the RDAREA utilization privilege to a user.YYNNN
GRANT SCHEMA (Grant schema definition privilege)Grants the schema definition privilege to a user.YYNNN
GRANT access privilege (Grant access privileges)Grants access privilege to a user.YYNNN
REVOKE CONNECT (Revoke connect privilege)Revokes a user's connect privilege.YYNNN
REVOKE DBA (Revoke DBA privilege)Revokes a user's DBA privilege.YYNNN
REVOKE RDAREA (Revoke RDAREA utilization privilege)Revokes a user's RDAREA utilization privilege.YYNNN
REVOKE SCHEMA (Revoke schema definition privilege)Revokes a user's schema definition privilege.YYNNN
REVOKE access privilege (Revoke access privileges)Revokes a user's access privilege.YYNNN
Y: Can be used.
N: Cannot be used.

Table B-2 SQL statements (data manipulation SQL)

TypeFunctionAvailable SQL
CCO- BOLUnder OLTPDistributed database (server type)
HiRDBOther than HiRDB
ALLOCATE CURSOR statement
(Allocate cursor)
Allocates a cursor to the SELECT statement preprocessed by the PREPARE statement or a group of result sets returned by a procedure.YYYNN
ASSIGN LIST statement (Create list)Creates a list from a base table.YYYNN
CALL statement* (Call procedure)Calls a procedure.YYYYN
CLOSE statement (Close cursor)Closes a cursor.YYYYY
DEALLOCATE PREPARE statement
(Nullify the preprocessing)
Releases the allocation of the SQL statement preprocessed by the PREPARE statement.YYYNN
DECLARE CURSOR (Declare cursor)Declares a cursor to receive the results of retrieval by the SELECT statement, one row at a time, using the FETCH statement.YYYYY
DELETE statement (Delete row)Deletes a row that satisfies a set of specified search conditions or a row that is indicated by the cursor.YYYYY
Preparable dynamic DELETE statement: locating
(Delete row using a preprocessable cursor)
Deletes the row pointed to by a specified cursor, used to execute the statement dynamically.YYYNN
DESCRIBE statement (Receive retrieval information and I/O information)Returns to the SQL descriptor area SQL retrieval information, output information, or input information that has been preprocessed by the PREPARE statement.YYYYY
DESCRIBE CURSOR
statement
(Receive cursor retrieval information)
Returns retrieval information on the cursor that references the result set returned by a procedure to the SQL descriptor area.YYYNN
DESCRIBE TYPE statement (Receive definition information on a user-defined data type)Returns to the SQL descriptor area definition information (attribute data codes and data lengths) on a user-defined data type that is directly or indirectly contained in SQL retrieval item information that is preprocessed by a PREPARE statement.YYYNN
DROP LIST statement (Delete list)Deletes a list.YYYNN
EXECUTE statement (Execute SQL)Executes SQL preprocessed by the PREPARE statement.YYYYY
EXECUTE IMMEDIATE statement (Preprocess and execute SQL)Preprocesses and executes the SQL specified in a character string.YYYYY
FETCH statement (Fetch data)Advances to the next row the cursor indicating the row to be fetched and reads to the embedded variable specified by the INTO clause the value of a column in that row.YYYYY
FREE LOCATOR statement
(Invalidate locator)
Nullifies the locator.YYYNN
INSERT statement (Inserting row)Inserts a row into a table. Can insert one row by direct value specification. Can also insert one or more rows by using the SELECT statement.YYYYY
OPEN statement (Open cursor)Opens a cursor. Positions the cursor declared in DECLARE CURSOR or allocated by ALLOCATE CURSOR at a position preceding the first row of the retrieval results so that retrieval results can be fetched.YYYYY
PREPARE statement (Preprocess SQL)Preprocesses the SQL indicated by a character string and assigns a name (SQL statement identifier or extended statement name) to the SQL.YYYYY
PURGE TABLE statement (Delete all rows)Deletes all rows in a base table.YYNYN
One-row SELECT statement (Retrieve one row)Searches table data. When only one row of data is to be fetched from a table, the one-row SELECT statement, which fetches data without using a cursor, must be specified.YYYYY
Dynamic SELECT statement (Dynamic retrieval)Searches table data. The dynamic SELECT statement is preprocessed by the PREPARE statement. When retrieving data, either declare the cursor by using DECLARE CURSOR or allocate the cursor using the ALLOCATE CURSOR statement, and then use the cursor to fetch retrieval results row by row.YYYYY
UPDATE statement (Update data)Updates the value of a specified column in a table row that meets a specified search condition or in the row indicated by the cursor.YYYYY
Preparable dynamic UPDATE statement: locating
(Update data using a preprocessable cursor)
Updates the value of a specified column in the row pointed to by a specified cursor; this is used for dynamic execution.YYYNN
Assignment statement
(Assign value)
Assigns a value.YYYNN
Y: Can be used.
N: Cannot be used.
Note
If a procedure is called under OLTP, or when a procedure defined on a distributed server is called, and if the procedure contains PURGE TABLE, COMMIT, or ROLLBACK statements, the procedure cannot be executed.

Table B-3 SQL statements (control SQL)

TypeFunctionAvailable SQL
CCO- BOLUnder OLTPDistributed database (server type)
HiRDBOther than HiRDB
COMMIT statement (Terminate transaction normally)Terminates normally the current transaction, sets a synchronization point, and generates one unit of commitment. The transaction puts the contents of the updated database into effect.YYNYY
CONNECT statement (Connect to HiRDB)Posts an authorization identifier and password to HiRDB so that the UAP can use HiRDB.YYNY*Y*
DISCONNECT statement (Disconnect from HiRDB)Terminates normally the current transaction, sets a synchronization point, and generates one unit of commitment. Afterwards, disconnects the UAP from HiRDB.YYNYY
LOCK statement (Lock table)Locks a specified table.YYYYN
CONNECT statement with RD-node specification (Connect to distributed RD-node)Passes an authorization identifier and password to a distributed RD-node so that the UAP can use that RD-node.YYYYY
DISCONNECT statement with RD-node specification (Disconnect from distributed RD-node)Terminates the current transaction normally, establishes a synchronization point, and creates a single commitment unit. The UAP is then disconnected from the distributed RD-node.YYNYY
ROLLBACK statement (Cancel transaction)Cancels the current transaction and any database updates performed within the transaction.YYNYY
SET CONNECTION statement (Set current RD-node)Sets the current RD-node.YYYYY
SET SESSION AUTHORIZATION statement (Change executing user)Changes the currently connected user.YYYNN
Y: Can be used.
N: Cannot be used.
* A connection to the DBMS running at the distributed server is established by the system automatically during execution of the first data manipulation SQL statement that accesses the database at the distributed server, rather than during execution of the CONNECT statement. After the CONNECT statement is executed, execution of a CONNECT statement with an RD-node specification establishes a connection to the DBMS running at the distributed server.

Table B-4 SQL statements (embedded language)

TypeFunctionAvailable SQL
CCO- BOLUnder OLTPDistributed database (server type)
HiRDBOther than HiRDB
BEGIN DECLARE SECTION (Embedded SQL begin declaration)Indicates the beginning of an embedded variable declaration section. Embedded variables and indicator variables used in SQL are specified in an embedded variable declaration section.YYYCC
END DECLARE SECTION (Embedded SQL end declaration)Indicates the end of an embedded variable declaration section.YYYCC
ALLOCATE CONNECTION HANDLE (Allocate connection handle)Allocates a connection handle to be used by a UAP in an environment where a multi-connection function is used.YYNNN
FREE CONNECTION HANDLE (Release connection handle)Releases a connection handle that was allocated by ALLOCATE CONNECTION HANDLE.YYNNN
DECLARE CONNECTION HANDLE SET (Declare connection handle to be used)Declares the connection handle to be used by a UAP in an environment where a multi-connection function is used.YYNNN
DECLARE CONNECTION HANDLE UNSET (Reset all connection handles being used)Resets all declarations of connection handle usage specified in DECLARE CONNECTION HANDLE SET statements prior to this statement.YNNNN
GET CONNECTION HANDLE (Get connection handle)When the multi-connection facility is used under the X/Open XA interface environment, allocates the connection handle to be used by the UAP.YYNNN
COPY (Include library text)Includes a library text into the source program.NYYCC
GET DIAGNOSTICS (Retrieve diagnostic information)If the preceding SQL statement is the CREATE PROCEDURE or CALL statement, obtains error information and diagnostic information from the diagnostics area.NNNNN
COMMAND EXECUTE (Execute command from UAP)Executes HiRDB and OS commands from within a UAP.YNNNN
SQL prefixIndicates the beginning of an SQL.YYYCC
SQL terminatorIndicates the end of an SQL.YYYCC
WHENEVER (Embedded exception declaration)Declares UAP processing by means of a return code that has been set in the SQL communication area by HiRDB after the SQL has executed.YYYCC
SQLCODE variableReceives the return code issued by HiRDB following the execution of an SQL.YYYCC
SQLSTATE variableReceives the return code issued by HiRDB following the execution of SQL.YYYCC
PDCNCTHDL-type variable declarationDeclares the handle that has the connection information to be used in an environment where a multi-connection function is used.YNNNN
INSTALL JAR (Register JAR file)Installs a JAR file on the HiRDB server.YNNNN
REPLACE JAR (Re-register JAR file)Installs a JAR file on the HiRDB server by overwriting it.YNNNN
REMOVE JAR (Delete JAR file)Uninstalls a JAR file from the HiRDB server.YNNNN
Y: Can be used.
N: Cannot be used.
C: Can be used in a UAP that uses the distributed database facility, but not in an SQL statement that is executed at the distributed server.

Table B-5 SQL statements (routine control SQL)

TypeFunctionAvailable SQL
CCO- BOLUnder OLTPDistributed database (server type)
HiRDBOther than HiRDB
Compound statement (Execute multiple statements)Executes a group of SQL statements as a single SQL statement.YYNNN
Assignment statement (Assign value)Assigns a value to an SQL variable or an SQL parameter.YYNNN
IF statement (Execute by conditional branching)Executes SQL statements under certain conditions.YYNNN
RETURN statement (Return function return value)Returns a return value from a function.Y1Y1NNN
WHILE statement (Repeat a set of statements)Executes repeatedly a set of SQL statements.YYNNN
FOR statement (Repeat a statement on rows)Repeatedly executes SQL statements on rows in a table.Y3Y3NNN
LEAVE statement (Leave statement)Exits from a compound statement or the WHILE statement and terminates the execution of the statement.YYNNN
WRITE LINE statement
(Output of a character string to a file)
Outputs the character string of a specified value expression to a file.YYNNN
SIGNAL statement
(Error signaling)
Generates and signals an error.Y2Y2NNN
RESIGNAL statement
(Error resignaling)
Generates and resignals an error.Y2Y2NNN
Legend:
Y: This cannot be used directly in a UAP. However, it can be used to define SQL procedures, SQL functions, and trigger actions in CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER.
N: Cannot be used.
Note
The following SQL statements, other than routine control SQL statements, can be specified in a procedure definition: CALL statement, CLOSE statement, DECLARE CURSOR statement, DELETE statement, FETCH statement, INSERT statement, OPEN statement, PURGE TABLE statement, single-row SELECT statement, UPDATE statement, COMMIT statement, LOCK statement, and ROLLBACK statement. SQL statements other than routine control SQL statements cannot be used in a function.
1 This cannot be used when defining an SQL procedure and a trigger action in CREATE PROCEDURE and CREATE TRIGGER.
2 This cannot be used to define an SQL function in CREATE FUNCTION.
3 This cannot be used in CREATE FUNCTION.