Appendix B. List of SQLs

The following table lists the SQL statements that can be used by type. The Under OLTP column indicates whether the SQL statement can be used in an X/Open-compliant UAP running under OLTP.

Table B-1 SQL statements (definition SQL)

TypeFunctionAvailable SQL
CCOBOLUnder OLTP
ALTER INDEX (Change index definition)Changes the name of an index.YYN
ALTER PROCEDURE (Recreate SQL object of procedure)Recreates an SQL object of a procedure.YYN
ALTER ROUTINE (Recreate SQL objects for functions, procedures, and triggers)Recreates SQL objects for functions, procedures, and triggers.YYN
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.
YYN
ALTER TRIGGER
(Recreate trigger SQL object)
Recreates a trigger SQL object.YYN
COMMENT (Comment)Adds a comment in a table or a column.YYN
CREATE AUDIT
(Define audit target event)
Defines the audit event to be recorded as an audit trail, and its target.YYN
CREATE CONNECTION SECURITY
(Define CONNECT security facility)
Defines security items related to the CONNECT security facility.YYN
CREATE [PUBLIC] FUNCTION (Define function, define public function)Defines a function or a public function.YYN
CREATE INDEX (Define index)Defines an index (ascending or descending order) on the columns in a base table.YYN
CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure)Defines a procedure or a public procedure.YYN
CREATE SCHEMA (Define schema)Defines a schema.YYN
CREATE SEQUENCE (Define sequence generator)Defines a sequence generator.YYN
CREATE TABLE (Define base table)Defines a base table.YYN
CREATE TRIGGER
(Define trigger)
Defines a trigger.YYN
CREATE TYPE (Define type)Defines an abstract data type.YYN
CREATE [PUBLIC] VIEW (Define view, define public view)Defines a view table or a public view table.YYN
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.YYN
DROP CONNECTION SECURITY
(Delete CONNECT security facility)
Deletes security items related to the CONNECT security facility.YYN
DROP DATA TYPE (Delete user-defined data type)Deletes a user-defined data type.YYN
DROP [PUBLIC] FUNCTION (Delete function, delete public function)Deletes a function or a public function.YYN
DROP INDEX (Delete index)Deletes an index.YYN
DROP [PUBLIC] PROCEDURE (Delete procedure, delete public procedure)Deletes a procedure or a public procedure.YYN
DROP SCHEMA (Delete schema)Deletes a schema.YYN
DROP SEQUENCE (Delete sequence generator)Deletes a sequence generator.YYN
DROP TABLE (Delete table)Deletes a base table and any indexes, comments, access privilege, view tables, and triggers that are associated with it.YYN
DROP TRIGGER
(Delete trigger)
Deletes a trigger.YYN
DROP [PUBLIC] VIEW (Delete view table, public view table)Deletes a view table or a public view table.YYN
GRANT AUDIT
(Change auditor's password)
Changes the auditor's password.YYN
GRANT CONNECT (Grant connect privilege)Grants the connect privilege to a user.YYN
GRANT DBA (Grant DBA privilege)Grants the DBA privilege to a user.YYN
GRANT RDAREA (Grant RDAREA utilization privilege)Grants the RDAREA utilization privilege to a user.YYN
GRANT SCHEMA (Grant schema definition privilege)Grants the schema definition privilege to a user.YYN
GRANT access privilege (Grant access privileges)Grants access privilege to a user.YYN
REVOKE CONNECT (Revoke connect privilege)Revokes a user's connect privilege.YYN
REVOKE DBA (Revoke DBA privilege)Revokes a user's DBA privilege.YYN
REVOKE RDAREA (Revoke RDAREA utilization privilege)Revokes a user's RDAREA utilization privilege.YYN
REVOKE SCHEMA (Revoke schema definition privilege)Revokes a user's schema definition privilege.YYN
REVOKE access privilege (Revoke access privileges)Revokes a user's access privilege.YYN
Y: Can be used.
N: Cannot be used.

Table B-2 SQL statements (data manipulation SQL)

TypeFunctionAvailable SQL
CCOBOLUnder OLTP
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.YYY
ASSIGN LIST statement (Create list)Creates a list from a base table.YYY
CALL statement* (Call procedure)Calls a procedure.YYY
CLOSE statement (Close cursor)Closes a cursor.YYY
DEALLOCATE PREPARE statement
(Nullify the preprocessing)
Releases the allocation of the SQL statement preprocessed by the PREPARE statement.YYY
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.YYY
DELETE statement (Delete row)Deletes a row that satisfies a set of specified search conditions or a row that is indicated by the cursor.YYY
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.YYY
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.YYY
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.YYY
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.YYY
DROP LIST statement (Delete list)Deletes a list.YYY
EXECUTE statement (Execute SQL)Executes SQL preprocessed by the PREPARE statement.YYY
EXECUTE IMMEDIATE statement (Preprocess and execute SQL)Preprocesses and executes the SQL specified in a character string.YYY
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.YYY
FREE LOCATOR statement
(Invalidate locator)
Nullifies the locator.YYY
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.YYY
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.YYY
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.YYY
PURGE TABLE statement (Delete all rows)Deletes all rows in a base table.YYN
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.YYY
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.YYY
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.YYY
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.YYY
Assignment statement
(Assign value)
Assigns a value.YYY
Y: Can be used.
N: Cannot be used.
Note
If a procedure is called under OLTP, 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
CCOBOLUnder OLTP
CALL COMMAND statement (Execute command or utility)Executes a HiRDB command or utility.YYY
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.YYN
CONNECT statement (Connect to HiRDB)Posts an authorization identifier and password to HiRDB so that the UAP can use HiRDB.YYN
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.YYN
LOCK statement (Lock table)Locks a specified table.YYY
ROLLBACK statement (Cancel transaction)Cancels the current transaction and any database updates performed within the transaction.YYN
SET SESSION AUTHORIZATION statement (Change executing user)Changes the currently connected user.YYY
Y: Can be used.
N: Cannot be used.

Table B-4 SQL statements (embedded language)

TypeFunctionAvailable SQL
CCOBOLUnder OLTP
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.YYY
END DECLARE SECTION (Embedded SQL end declaration)Indicates the end of an embedded variable declaration section.YYY
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.YYN
FREE CONNECTION HANDLE (Release connection handle)Releases a connection handle that was allocated by ALLOCATE CONNECTION HANDLE.YYN
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.YYN
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.YNN
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.YYN
COPY (Include library text)Includes a library text into the source program.NYY
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.NNN
COMMAND EXECUTE (Execute command from UAP)Executes HiRDB and OS commands from within a UAP.YNN
SQL prefixIndicates the beginning of an SQL.YYY
SQL terminatorIndicates the end of an SQL.YYY
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.YYY
SQLCODE variableReceives the return code issued by HiRDB following the execution of an SQL.YYY
SQLSTATE variableReceives the return code issued by HiRDB following the execution of SQL.YYY
PDCNCTHDL-type variable declarationDeclares the handle that has the connection information to be used in an environment where a multi-connection function is used.YNN
INSTALL JAR (Register JAR file)Installs a JAR file on the HiRDB server.YNN
REPLACE JAR (Re-register JAR file)Installs a JAR file on the HiRDB server by overwriting it.YNN
REMOVE JAR (Delete JAR file)Uninstalls a JAR file from the HiRDB server.YNN
DECLARE AUDIT INFO SET (Set user connection information)Sets account information and other user connection information for applications that access a HiRDB server.YYY
Y: Can be used.
N: Cannot be used.

Table B-5 SQL statements (routine control SQL)

TypeFunctionAvailable SQL
CCOBOLUnder OLTP
Compound statement (Execute multiple statements)Executes a group of SQL statements as a single SQL statement.YYN
Assignment statement (Assign value)Assigns a value to an SQL variable or an SQL parameter.YYN
IF statement (Execute by conditional branching)Executes SQL statements under certain conditions.YYN
RETURN statement (Return function return value)Returns a return value from a function.Y#1Y#1N
WHILE statement (Repeat a set of statements)Executes repeatedly a set of SQL statements.YYN
FOR statement (Repeat a statement on rows)Repeatedly executes SQL statements on rows in a table.Y#3Y#3N
LEAVE statement (Leave statement)Exits from a compound statement or the WHILE statement and terminates the execution of the statement.YYN
WRITE LINE statement
(Output of a character string to a file)
Outputs the character string of a specified value expression to a file.YYN
SIGNAL statement
(Error signaling)
Generates and signals an error.Y#2Y#2N
RESIGNAL statement
(Error resignaling)
Generates and resignals an error.Y#2Y#2N
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.