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)
Type | Function | Available SQL | ||||
---|---|---|---|---|---|---|
C | CO- BOL | Under OLTP | Distributed database (server type) | |||
HiRDB | Other than HiRDB | |||||
ALTER PROCEDURE (Recreate SQL object of procedure) | Recreates an SQL object of a procedure. | Y | Y | N | N | N |
ALTER ROUTINE (Recreate SQL objects for functions, procedures, and triggers) | Recreates SQL objects for functions, procedures, and triggers. | Y | Y | N | N | N |
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. | Y | Y | N | N | N |
ALTER TRIGGER (Recreate trigger SQL object) | Recreates a trigger SQL object. | Y | Y | N | N | N |
COMMENT (Comment) | Adds a comment in a table or a column. | Y | Y | N | N | N |
CREATE ALIAS (Define table alias) | Defines a table alias. | Y | Y | N | N | N |
CREATE AUDIT (Define audit target event) | Defines the audit event to be recorded as an audit trail, and its target. | Y | Y | N | N | N |
CREATE CONNECTION SECURITY (Define CONNECT security facility) | Defines security items related to the CONNECT security facility. | Y | Y | N | N | N |
CREATE FOREIGN INDEX (Define foreign index) | Defines a foreign index. | Y | Y | N | N | N |
CREATE FOREIGN TABLE (Define foreign table) | Defines a foreign table. | Y | Y | N | N | N |
CREATE FUNCTION (Define function) | Defines a function. | Y | Y | N | N | N |
CREATE INDEX (Define index) | Defines an index (ascending or descending order) on the columns in a base table. | Y | Y | N | N | N |
CREATE PROCEDURE (Define procedure) | Defines a procedure. | Y | Y | N | N | N |
CREATE SCHEMA (Define schema) | Defines a schema. | Y | Y | N | N | N |
CREATE SERVER (Define foreign server) | Defines a foreign server. | Y | Y | N | N | N |
CREATE TABLE (Define base table) | Defines a base table. | Y | Y | N | N | N |
CREATE TRIGGER (Define trigger) | Defines a trigger. | Y | Y | N | N | N |
CREATE TYPE (Define type) | Defines an abstract data type. | Y | Y | N | N | N |
CREATE USER MAPPING (Define user mapping) | Defines user mapping. | Y | Y | N | N | N |
CREATE VIEW (Define view table) | Defines a view table. | Y | Y | N | N | N |
CREATE PUBLIC VIEW (Define public view) | Defines a public view. | Y | Y | N | N | N |
DROP ALIAS (Delete table alias) | Deletes a table alias. | Y | Y | N | N | N |
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. | Y | Y | N | N | N |
DROP CONNECTION SECURITY (Delete CONNECT security facility) | Deletes security items related to the CONNECT security facility. | Y | Y | N | N | N |
DROP DATA TYPE (Delete user-defined data type) | Deletes a user-defined data type. | Y | Y | N | N | N |
DROP FOREIGN INDEX (Delete foreign index) | Deletes the definition of a foreign index. | Y | Y | N | N | N |
DROP FOREIGN TABLE (Define foreign table) | Deletes the definition of a foreign table. | Y | Y | N | N | N |
DROP FUNCTION (Delete function) | Deletes a function. | Y | Y | N | N | N |
DROP INDEX (Delete index) | Deletes an index. | Y | Y | N | N | N |
DROP PROCEDURE (Delete a procedure) | Deletes a procedure. | Y | Y | N | N | N |
DROP SCHEMA (Delete schema) | Deletes a schema. | Y | Y | N | N | N |
DROP SERVER (Delete foreign server) | Deletes the definition of a foreign server. | Y | Y | N | N | N |
DROP TABLE (Delete table) | Deletes a base table and any indexes, comments, access privilege, view tables, and triggers that are associated with it. | Y | Y | N | N | N |
DROP TRIGGER (Delete trigger) | Deletes a trigger. | Y | Y | N | N | N |
DROP USER MAPPING (Delete user mapping) | Deletes user mapping. | Y | Y | N | N | N |
DROP VIEW (Delete view table) | Deletes a view table. | Y | Y | N | N | N |
DROP PUBLIC VIEW (Deletes public view) | Deletes a public view. | Y | Y | N | N | N |
GRANT AUDIT (Change auditor's password) | Changes the auditor's password. | Y | Y | N | N | N |
GRANT CONNECT (Grant connect privilege) | Grants the connect privilege to a user. | Y | Y | N | N | N |
GRANT DBA (Grant DBA privilege) | Grants the DBA privilege to a user. | Y | Y | N | N | N |
GRANT RDAREA (Grant RDAREA utilization privilege) | Grants the RDAREA utilization privilege to a user. | Y | Y | N | N | N |
GRANT SCHEMA (Grant schema definition privilege) | Grants the schema definition privilege to a user. | Y | Y | N | N | N |
GRANT access privilege (Grant access privileges) | Grants access privilege to a user. | Y | Y | N | N | N |
REVOKE CONNECT (Revoke connect privilege) | Revokes a user's connect privilege. | Y | Y | N | N | N |
REVOKE DBA (Revoke DBA privilege) | Revokes a user's DBA privilege. | Y | Y | N | N | N |
REVOKE RDAREA (Revoke RDAREA utilization privilege) | Revokes a user's RDAREA utilization privilege. | Y | Y | N | N | N |
REVOKE SCHEMA (Revoke schema definition privilege) | Revokes a user's schema definition privilege. | Y | Y | N | N | N |
REVOKE access privilege (Revoke access privileges) | Revokes a user's access privilege. | Y | Y | N | N | N |
Table B-2 SQL statements (data manipulation SQL)
Type | Function | Available SQL | ||||
---|---|---|---|---|---|---|
C | CO- BOL | Under OLTP | Distributed database (server type) | |||
HiRDB | Other 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. | Y | Y | Y | N | N |
ASSIGN LIST statement (Create list) | Creates a list from a base table. | Y | Y | Y | N | N |
CALL statement* (Call procedure) | Calls a procedure. | Y | Y | Y | Y | N |
CLOSE statement (Close cursor) | Closes a cursor. | Y | Y | Y | Y | Y |
DEALLOCATE PREPARE statement (Nullify the preprocessing) | Releases the allocation of the SQL statement preprocessed by the PREPARE statement. | Y | Y | Y | N | N |
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. | Y | Y | Y | Y | Y |
DELETE statement (Delete row) | Deletes a row that satisfies a set of specified search conditions or a row that is indicated by the cursor. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | N | N |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | N | N |
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. | Y | Y | Y | N | N |
DROP LIST statement (Delete list) | Deletes a list. | Y | Y | Y | N | N |
EXECUTE statement (Execute SQL) | Executes SQL preprocessed by the PREPARE statement. | Y | Y | Y | Y | Y |
EXECUTE IMMEDIATE statement (Preprocess and execute SQL) | Preprocesses and executes the SQL specified in a character string. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | Y | Y |
FREE LOCATOR statement (Invalidate locator) | Nullifies the locator. | Y | Y | Y | N | N |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | Y | Y |
PURGE TABLE statement (Delete all rows) | Deletes all rows in a base table. | Y | Y | N | Y | N |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | Y | N | N |
Assignment statement (Assign value) | Assigns a value. | Y | Y | Y | N | N |
Table B-3 SQL statements (control SQL)
Type | Function | Available SQL | ||||
---|---|---|---|---|---|---|
C | CO- BOL | Under OLTP | Distributed database (server type) | |||
HiRDB | Other 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. | Y | Y | N | Y | Y |
CONNECT statement (Connect to HiRDB) | Posts an authorization identifier and password to HiRDB so that the UAP can use HiRDB. | Y | Y | N | Y* | 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. | Y | Y | N | Y | Y |
LOCK statement (Lock table) | Locks a specified table. | Y | Y | Y | Y | N |
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. | Y | Y | Y | Y | Y |
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. | Y | Y | N | Y | Y |
ROLLBACK statement (Cancel transaction) | Cancels the current transaction and any database updates performed within the transaction. | Y | Y | N | Y | Y |
SET CONNECTION statement (Set current RD-node) | Sets the current RD-node. | Y | Y | Y | Y | Y |
SET SESSION AUTHORIZATION statement (Change executing user) | Changes the currently connected user. | Y | Y | Y | N | N |
Table B-4 SQL statements (embedded language)
Type | Function | Available SQL | ||||
---|---|---|---|---|---|---|
C | CO- BOL | Under OLTP | Distributed database (server type) | |||
HiRDB | Other 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. | Y | Y | Y | C | C |
END DECLARE SECTION (Embedded SQL end declaration) | Indicates the end of an embedded variable declaration section. | Y | Y | Y | C | C |
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. | Y | Y | N | N | N |
FREE CONNECTION HANDLE (Release connection handle) | Releases a connection handle that was allocated by ALLOCATE CONNECTION HANDLE. | Y | Y | N | N | N |
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. | Y | Y | N | N | N |
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. | Y | N | N | N | N |
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. | Y | Y | N | N | N |
COPY (Include library text) | Includes a library text into the source program. | N | Y | Y | C | C |
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. | N | N | N | N | N |
COMMAND EXECUTE (Execute command from UAP) | Executes HiRDB and OS commands from within a UAP. | Y | N | N | N | N |
SQL prefix | Indicates the beginning of an SQL. | Y | Y | Y | C | C |
SQL terminator | Indicates the end of an SQL. | Y | Y | Y | C | C |
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. | Y | Y | Y | C | C |
SQLCODE variable | Receives the return code issued by HiRDB following the execution of an SQL. | Y | Y | Y | C | C |
SQLSTATE variable | Receives the return code issued by HiRDB following the execution of SQL. | Y | Y | Y | C | C |
PDCNCTHDL-type variable declaration | Declares the handle that has the connection information to be used in an environment where a multi-connection function is used. | Y | N | N | N | N |
INSTALL JAR (Register JAR file) | Installs a JAR file on the HiRDB server. | Y | N | N | N | N |
REPLACE JAR (Re-register JAR file) | Installs a JAR file on the HiRDB server by overwriting it. | Y | N | N | N | N |
REMOVE JAR (Delete JAR file) | Uninstalls a JAR file from the HiRDB server. | Y | N | N | N | N |
Table B-5 SQL statements (routine control SQL)
Type | Function | Available SQL | ||||
---|---|---|---|---|---|---|
C | CO- BOL | Under OLTP | Distributed database (server type) | |||
HiRDB | Other than HiRDB | |||||
Compound statement (Execute multiple statements) | Executes a group of SQL statements as a single SQL statement. | Y | Y | N | N | N |
Assignment statement (Assign value) | Assigns a value to an SQL variable or an SQL parameter. | Y | Y | N | N | N |
IF statement (Execute by conditional branching) | Executes SQL statements under certain conditions. | Y | Y | N | N | N |
RETURN statement (Return function return value) | Returns a return value from a function. | Y1 | Y1 | N | N | N |
WHILE statement (Repeat a set of statements) | Executes repeatedly a set of SQL statements. | Y | Y | N | N | N |
FOR statement (Repeat a statement on rows) | Repeatedly executes SQL statements on rows in a table. | Y3 | Y3 | N | N | N |
LEAVE statement (Leave statement) | Exits from a compound statement or the WHILE statement and terminates the execution of the statement. | Y | Y | N | N | N |
WRITE LINE statement (Output of a character string to a file) | Outputs the character string of a specified value expression to a file. | Y | Y | N | N | N |
SIGNAL statement (Error signaling) | Generates and signals an error. | Y2 | Y2 | N | N | N |
RESIGNAL statement (Error resignaling) | Generates and resignals an error. | Y2 | Y2 | N | N | N |