![[Contents]](FIGURE/CONTENT.GIF)
Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
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:
| SQL | Function | Usability | ||
|---|---|---|---|---|
| C | COBOL | OLTP | ||
| ALLOCATE MEMORY TABLE (specify a table to be expanded to the memory database) |
Specifies a table defined by CREATE TABLE as a table to be expanded to the memory database. | U | U | -- |
| ALTER INDEX (change index definition) |
Renames an index. | U | U | -- |
| 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) |
|
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 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 FUNCTION (define function) | Defines a function. | U | U | -- |
| CREATE PUBLIC FUNCTION (define public function) |
Defines a public 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 PUBLIC PROCEDURE (define public procedure) |
Defines a public procedure. | U | U | -- |
| CREATE SCHEMA (define schema) | Defines a schema. | U | U | -- |
| CREATE SEQUENCE (define sequence generator) |
Defines a sequence generator. | 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 VIEW (define view table) | Defines a view table. | U | U | -- |
| CREATE PUBLIC VIEW (define public view) |
Defines a public view table. | U | U | -- |
| DEALLOCATE MEMORY TABLE (release table expanded to memory database) |
Releases a table expanded to the memory database. | 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 FUNCTION (delete function) | Deletes a function. | U | U | -- |
| DROP PUBLIC FUNCTION (delete public function) |
Deletes a public function. | U | U | -- |
| DROP INDEX (delete index) | Deletes an index. | U | U | -- |
| DROP PROCEDURE (delete procedure) | Deletes a procedure. | U | U | -- |
| DROP PUBLIC PROCEDURE (delete public procedure) |
Deletes a public procedure. | U | U | -- |
| DROP SCHEMA (delete schema) | Deletes a schema. | U | U | -- |
| DROP SEQUENCE (delete sequence generator) |
Deletes a sequence generator. | 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 VIEW (delete view table) | Deletes a view table. | U | U | -- |
| DROP PUBLIC VIEW (delete public view table) |
Deletes a public 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 | -- |
| SQL | Function | Usability | ||
|---|---|---|---|---|
| C | COBOL | OLTP | ||
| 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 |
| CLOSE statement (close cursor) |
Closes a cursor. | 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 |
| DELETE statement (delete rows) |
Deletes either the rows that satisfy specified search conditions or the row indicated by the cursor. | 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 |
| 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 |
| EXECUTE IMMEDIATE statement (preprocess and execute SQL) |
Preprocesses and executes an SQL statement provided in a character string. | 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 |
| 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 |
| 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 |
| 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 |
| PURGE TABLE statement (delete all rows) |
Deletes all rows in a base table. | 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 |
| 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 |
| 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 |
| 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 |
| SQL | Function | Usability | ||
|---|---|---|---|---|
| C | COBOL | OLTP | ||
| CALL COMMAND statement (execute command or utility) |
Executes a HiRDB command or utility. | U | U | U |
| 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 | -- |
| CONNECT statement (connect UAP to HiRDB) | Passes the authorization identifier and password to HiRDB, and enables the UAP to use HiRDB. | 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 | -- |
| LOCK statement (lock control on tables) | Performs exclusive locks on specified tables. | U | U | U |
| ROLLBACK statement (cancel transaction) | Cancels the current transaction and nullifies the database updating performed by the transaction. | U | U | -- |
| SET SESSION AUTHORIZATION statement (change execution user) | Changes the user who is currently connected. | U | U | U |
| SQL | Function | Usability | ||
|---|---|---|---|---|
| C | COBOL | OLTP | ||
| 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 |
| END DECLARE SECTION (declare end of embedded SQL) | Indicates the end of an embedded SQL declare section. | U | U | U |
| 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 |
| 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 |
| 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 |
| SQL terminator | Indicates the end of SQL statements. | U | U | U |
| 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 |
| SQLCODE variable | Receives the return code issued by HiRDB after an SQL statement has been executed. | U | U | U |
| SQLSTATE variable | Receives the return code issued by HiRDB after an SQL statement has been executed. | U | U | U |
| 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 | -- | -- |
| SQL | Function | Usability | ||
|---|---|---|---|---|
| C | COBOL | OLTP | ||
| 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. | PFT#1 | PFT#1 | -- |
| 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. | PFT#3 | PFT#3 | -- |
| 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. | PFT#2 | PFT#2 | -- |
| RESIGNAL statement (re-report error) | Triggers an error and reports it again. | PFT#2 | PFT#2 | -- |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.