Tables 1-1 to 1-5 list the SQL statements that can be used in HiRDB. In the table headings, OLTP refers to an application program that complies with X/Open in the OLTP environment.

For details about the following items, refer to the indicated manuals or locations:

Details about the SQL coding formats

HiRDB Version 8 SQL Reference

Database definitions

HiRDB Version 8 Installation and Design Guide

Database operations

2. Database Operations

Database management

3. UAP Design

Embedded language

HiRDB Version 8 SQL Reference

Table 1-1 List of SQL statements (definition SQL)

SQL	Function	Usability
		C	COBOL	OLTP	Distributed database (server type)
					HiRDB	Other than HiRDB
ALTER PROCEDURE (re-create SQL object for procedure)	Re-creates an SQL object for a procedure.	U	U
ALTER ROUTINE (re-create SQL object for function, procedure, or trigger)	Re-creates an SQL object for a function or procedure.	U	U
ALTER TABLE (alter table definition)	Adds a new column to end of a base table. Changes a data type. Increases the maximum length of an existing column of the variable-length data type. Deletes a base table column that contains no data. Changes the uniqueness constraint for cluster keys for a base table containing no data. Renames table and columns.	U	U
ALTER TRIGGER (re-create SQL object for trigger)	Re-creates an SQL object for a trigger.	U	U
COMMENT (add comment)	Provides a comment in a table or column.	U	U
CREATE ALIAS (define alias)	Assigns an alias for a table.	U	U
CREATE AUDIT (define audit event)	Defines an audit event to be recorded as an audit trace and its target.	U	U
CREATE CONNECTION SECURITY (define connection security facility)	Defines the security item related to the connection security facility.	U	U
CREATE FOREIGN INDEX (define foreign index)	Defines a foreign index.	U	U
CREATE FOREIGN TABLE (define foreign table)	Defines a foreign table.	U	U
CREATE FUNCTION (define function)	Defines a function.	U	U
CREATE INDEX (define index)	Defines an index (ascending or descending order) for columns in a base table.	U	U
CREATE PROCEDURE (define procedure)	Defines a procedure.	U	U
CREATE SCHEMA (define schema)	Defines a schema.	U	U
CREATE SERVER (define foreign server)	Defines a foreign server.	U	U
CREATE TABLE (define base table)	Defines a base table.	U	U
CREATE TRIGGER (define trigger)	Defines a trigger.	U	U
CREATE TYPE (define type)	Defines an abstract data type.	U	U
CREATE USER MAPPING (define user mapping)	Defines a user mapping.	U	U
CREATE VIEW (define view table)	Defines a view table.	U	U
DROP ALIAS (delete alias)	Deletes a table alias.	U	U
DROP AUDIT (delete audit event)	Deletes the definition that matches the audit event and contents defined by CREATE AUDIT from the audit targets.	U	U
DROP CONNECTION SECURITY (delete connection security facility)	Deletes the security item related to the connection security facility.	U	U
DROP DATA TYPE (delete user-defined type)	Deletes a user-defined type.	U	U
DROP FOREIGN INDEX (delete foreign index)	Deletes the definition of a foreign index.	U	U
DROP FOREIGN TABLE (delete foreign table)	Deletes the definition of a foreign table.	U	U
DROP FUNCTION (delete function)	Deletes a function.	U	U
DROP INDEX (delete index)	Deletes an index.	U	U
DROP PROCEDURE (delete procedure)	Deletes a procedure.	U	U
DROP SCHEMA (delete schema)	Deletes a schema.	U	U
DROP SERVER (delete foreign server)	Deletes the definition of a foreign server.	U	U
DROP TABLE (delete table)	Deletes a base table, as well as any indexes, comments, access privileges, view tables, and trigger associated with the base table.	U	U
DROP TRIGGER (delete trigger)	Deletes a trigger.	U	U
DROP USER MAPPING (delete user mapping)	Deletes a user mapping.	U	U
DROP VIEW (delete view table)	Deletes a view table.	U	U
GRANT AUDIT (change auditor's password)	Changes the auditor's password.	U	U
GRANT CONNECT (grant CONNECT privilege)	Grants the CONNECT privilege to users.	U	U
GRANT DBA (grant DBA privilege)	Grants the DBA privilege to users.	U	U
GRANT RDAREA (grant RDAREA usage privilege)	Grants the RDAREA usage privilege to users.	U	U
GRANT SCHEMA (grant schema definition privilege)	Grants the schema definition privilege to users.	U	U
GRANT access-privilege (grant access privileges)	Grants access privileges to users.	U	U
REVOKE CONNECT (revoke CONNECT privilege)	Revokes previously granted CONNECT privileges.	U	U
REVOKE DBA (revoke DBA privilege)	Revokes previously granted DBA privileges.	U	U
REVOKE RDAREA (revoke RDAREA usage privilege)	Revokes previously granted RDAREA usage privileges.	U	U
REVOKE SCHEMA (revoke schema definition privilege)	Revokes previously granted schema definition privileges.	U	U
REVOKE access-privilege (revoke access privilege)	Revokes previously granted access privileges.	U	U

U: Can be used.

: Cannot be used.

Table 1-2 List of SQL statements (data manipulation SQL)

SQL	Function	Usability
		C	COBOL	OLTP	Distributed database (server type)
					HiRDB	Other than HiRDB
ALLOCATE CURSOR statement (allocate cursor)	Allocates a cursor for a SELECT statement preprocessed by the PREPARE statement or for a group of result sets returned by a procedure.	U	U	U
ASSIGN LIST statement (create list)	Creates a list from a base table.	U	U	U
CALL statement* (call procedure)	Calls a procedure.	U	U	U	U
CLOSE statement (close cursor)	Closes a cursor.	U	U	U	U	U
DEALLOCATE PREPARE statement (release preprocessing)	Releases the allocation of an SQL statement preprocessed by the PREPARE statement.	U	U	U
DECLARE CURSOR (declare cursor)	Declares a cursor that the results of a retrieval by the SELECT statement can be fetched row by row with the FETCH statement.	U	U	U	U	U
DELETE statement (delete rows)	Deletes either the rows that satisfy specified search conditions or the row indicated by the cursor.	U	U	U	U	U
Preparable dynamic DELETE statement: locating (delete row that uses preprocessable cursor)	Deletes the row indicated by the specified cursor. This statement is used for dynamic execution.	U	U	U
DESCRIBE statement (receive retrieval and I/O information)	Returns to the SQL Descriptor Area SQL retrieval information, output information, or input information that was preprocessed by the PREPARE statement.	U	U	U	U	U
DESCRIBE CURSOR statement (receive retrieval information for cursor)	Returns to the SQL Descriptor Area retrieval information for a cursor that can reference a result set returned by a procedure.	U	U	U
DESCRIBE TYPE statement (receive definition information for user-defined type)	Receives in the SQL Descriptor Area the definition information (including data codes for all attributes and data length) for a user-defined type. The user-defined type has been directly or indirectly included in the SQL retrieval item information that was preprocessed by the PREPARE statement.	U	U	U
DROP LIST statement (delete list)	Deletes a list.	U	U	U
EXECUTE statement (execute SQL)	Executes an SQL statement preprocessed by the PREPARE statement.	U	U	U	U	U
EXECUTE IMMEDIATE statement (preprocess and execute SQL)	Preprocesses and executes an SQL statement provided in a character string.	U	U	U	U	U
FETCH statement (fetch data)	Advances the cursor to the next row to be fetched, and reads column values in that row into the embedded variable specified in the INTO clause.	U	U	U	U	U
FREE LOCATOR statement (invalidate locator)	Invalidates a locator.	U	U	U
INSERT statement (insert rows)	Inserts rows into a table. A single row can be inserted by direct specification of values; one or more rows can be inserted by using the SELECT statement.	U	U	U	U	U
OPEN statement (open cursor)	Opens a cursor. The cursor declared by DECLARE CURSOR or allocated by ALLOCATE CURSOR is positioned immediately preceding the first line of the retrieval results so that the retrieval results can be fetched.	U	U	U	U	U
PREPARE statement (preprocess SQL statement)	Preprocesses the SQL statement provided in a character string so that the statement can be executed and assigns a name (SQL statement identifier or extended statement name) to that SQL statement.	U	U	U	U	U
PURGE TABLE statement (delete all rows)	Deletes all rows in a base table.	U	U		U
Single-row SELECT statement (retrieve one row)	Searches table data. To fetch only one row of data from a table, the single-row SELECT statement can be used without having to declare a cursor.	U	U	U	U	U
Dynamic SELECT statement (retrieve dynamically)	Searches table data. The dynamic SELECT statement is preprocessed by the PREPARE statement. During the search, a cursor declared by DECLARE CURSOR or allocated by ALLOCATE CURSOR is used to fetch the retrieval results row by row.	U	U	U	U	U
UPDATE statement (update data)	Updates the values of columns in the rows that satisfy specified search conditions or in the row indicated by the cursor.	U	U	U	U	U
Preparable dynamic UPDATE statement: locating (update data that uses preprocessable cursor)	Updates the value of the specified column in the row indicated by the specified cursor. This statement is used for dynamic execution.	U	U	U
Assignment statement (assign value)	Assigns a value to an SQL variable or SQL parameter.	U	U	U

U: Can be used.

: Cannot be used.

^* If a procedure is called under OLTP, or if a procedure defined on a distributed server is called when a distributed database facility is used, that procedure cannot be executed if it contains a PURGE TABLE, COMMIT, or ROLLBACK statement.

Table 1-3 List of SQL statements (control SQL)

SQL	Function	Usability
		C	COBOL	OLTP	Distributed database (server type)
					HiRDB	Other than HiRDB
COMMIT statement (terminate transaction normally)	Terminates the current transaction normally, sets synchronization points, generates one unit of commitment, and effects the database updates performed by the transaction.	U	U		U	U
CONNECT statement (connect UAP to HiRDB)	Passes the authorization identifier and password to HiRDB, and enables the UAP to use HiRDB.	U	U		U*	U*
DISCONNECT statement (disconnect UAP from HiRDB)	Terminates the current transaction normally, sets synchronization points, and generates one unit of commitment, then disconnects the UAP from HiRDB.	U	U		U	U
LOCK statement (lock control on tables)	Performs exclusive locks on specified tables.	U	U	U	U
CONNECT statement with RD-node specification (connect to distributed RD-node)	Relays an authorization identifier and a password to a distributed RD-node so that a UAP can use that distributed RD-node.	U	U	U	U	U
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.	U	U		U	U
ROLLBACK statement (cancel transaction)	Cancels the current transaction and nullifies the database updating performed by the transaction.	U	U		U	U
SET CONNECTION statement (set current RD-node)	Sets the current RD-node.	U	U	U	U	U
SET SESSION AUTHORIZATION statement (change execution user)	Changes the user who is currently connected.	U	U	U

U: Can be used.

: Cannot be used.

^* The system automatically connects to the distributed server DBMS when the first data manipulation SQL that accesses a distributed server database is executed, rather than when the CONNECT statement is executed. After the CONNECT statement is executed, the system also connects to the distributed server DBMS, if the CONNECT statement is executed again with the RD node specified.

Table 1-4 List of SQL statements (embedded language)

SQL	Function	Usability
		C	COBOL	OLTP	Distributed database (servertype)
					HiRDB	Other than HiRDB
BEGIN DECLARE SECTION (declare beginning embedded SQL)	Indicates the beginning of an embedded SQL declare section, that specifies the embedded variables and indicator variables used in the SQL.	U	U	U	DDF	DDF
END DECLARE SECTION (declare end of embedded SQL)	Indicates the end of an embedded SQL declare section.	U	U	U	DDF	DDF
ALLOCATE CONNECTION HANDLE (allocate connection handle)	Allocates a connection handle to be used by the UAP in an environment that uses multiple connection functions.	U	U
FREE CONNECTION HANDLE (free connection handle)	Frees a connection handle that was allocated by ALLOCATE CONNECTION HANDLE.	U	U
DECLARE CONNECTION HANDLE SET (declare connection handle to be used)	Declares a connection handle to be used by the UAP SQL in an environment that uses the multi-connection facility.	U	U	U*
DECLARE CONNECTION HANDLE UNSET (cancel all connection handles being used)	Cancels all declarations of connection handle use specified with DECLARE CONNECTION HANDLE SET statements before this statement.	U
GET CONNECTION HANDLE (get connection handle)	Allocates the connection handle to be used by the UAP when the multi-connection facility is to be used in an X/Open XA interface environment.	U	U	U*
COPY (copy cataloged text)	Copies cataloged text into a source program.		U	U	DDF	DDF
GET DIAGNOSTICS (get diagnostic information)	If the preceding SQL statement is CREATE PROCEDURE or CALL, obtains error information and diagnostic information from the diagnostics area.	U	U	U	U	U
COMMAND EXECUTE (execute commands from UAP)	Executes HiRDB and OS commands from inside the UAP.	U
SQL prefix	Indicates the beginning of SQL statements.	U	U	U	DDF	DDF
SQL terminator	Indicates the end of SQL statements.	U	U	U	DDF	DDF
WHENEVER (declare embedded exception)	Declares UAP processing, based on the return code set by HiRDB in the SQL Communications Areas after SQL statements have been executed.	U	U	U	DDF	DDF
SQLCODE variable	Receives the return code issued by HiRDB after an SQL statement has been executed.	U	U	U	DDF	DDF
SQLSTATE variable	Receives the return code issued by HiRDB after an SQL statement has been executed.	U	U	U	DDF	DDF
Declaration of PDCNCTHDL-type variable	Declares a connection handle type variable to be used in an environment that uses the multi-connection facility.	U
INSTALL JAR (register JAR file)	Installs the JAR file in the HiRDB server.	U
REPLACE JAR (re-register JAR file)	Replaces the JAR file in the HiRDB server.	U
REMOVE JAR (delete JAR file)	Uninstalls the JAR file from the HiRDB server.	U

U: Can be used.

: Cannot be used.

DDF: Cannot be executed on a distributed server; however, it can be used in UAPs that use the distributed database function.

^* The statement can be used if a connection handle was allocated with the GET CONNECTION HANDLE statement.

Table 1-5 List of SQL statements (routine control SQL)

SQL	Function	Usability
		C	COBOL	OLTP	Distributed database (servertype)
					HiRDB	Other than HiRDB
Compound statement (execute multiple statements)	Executes a group of SQL statements as a single SQL statement.	PFT	PFT
IF statement (execute by conditional branching)	Executes the SQL statement that satisfies a set of specified conditions.	PFT	PFT
RETURN statement (return return value)	Returns the return value of a function.	PFT1	PFT1
WHILE statement (repeat statements)	Executes a set of SQL statements repetitively.	PFT	PFT
FOR statement (repeat execution of each row)	Repeats execution of an SQL statement for each row in a table.	PFT3	PFT3
LEAVE statement (leave statement)	Exits from a compound statement or the WHILE statement and terminates execution of the statement.	PFT	PFT
WRITE LINE statement (output character string to file)	Outputs a character string of the specified value expression to a file.	PFT	PFT
SIGNAL statement (report error)	Triggers an error and reports it.	PFT2	PFT2
RESIGNAL statement (re-report error)	Triggers an error and reports it again.	PFT2	PFT2

PFT: The statement cannot be used directly in the UAP. However, the statement can be used to define an SQL procedure, SQL function, or trigger operation in the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement.

: Cannot be used.

Note

In procedure definitions, the SQL statements that can be specified in addition to the routine control SQL statements are the CALL, CLOSE, DECLARE CURSOR, DELETE, FETCH, INSERT, OPEN, PURGE TABLE, single-row SELECT, UPDATE, COMMIT, LOCK, and ROLLBACK statements. In functions, SQL statements other than routine control SQL statements cannot be specified.

¹ This statement cannot be used if an SQL procedure or a trigger operation is defined in the CREATE PROCEDURE or CREATE TRIGGER statement.

² This statement cannot be used if an SQL function is defined in the CREATE FUNCTION statement.

³ This statement cannot be used in the CREATE FUNCTION statement.