HP 3000 Manuals HP 3000 Manuals
SQL Statement Summary
SQL statements fall into four groups. General-purpose statements are
used programmatically, interactively, and in procedures. Application
programming statements are used in application programs. Database
administration statements are usually used interactively. Procedure,
control flow, and status statements are used only in procedures. Within
each of these groups, the SQL statements fall into categories, as shown
in Table 10-1.
Table 10-1. SQL Statement Summary
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| General-purpose | DBEnvironment | CONNECT | Begins a DBEnvironment |
| statements | session management | | session. |
| | | | |
| | | DISCONNECT | Terminates a connection to a |
| | | | DBEnvironment, or all |
| | | | connections. |
| | | | |
| | | SET CONNECTION | Sets the current connection |
| | | | within the currently |
| | | | connected set of |
| | | | DBEnvironments. |
| | | | |
| | | SET | Switches between |
| | | MULTITRANSACTION | single-transaction mode and |
| | | | multi-transaction mode. |
| | | | |
| | | RELEASE | Terminates a DBEnvironment |
| | | | session. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| General-purpose | Data definition | | |
| statements | | | |
| (continued) | | | |
| | | | |
| | Databases | CREATE SCHEMA | Defines a database and |
| | | | associates it with an |
| | | | authorization name. |
| | | | |
| | Indexes | CREATE INDEX | Defines an index for a table |
| | | | based on one or more of its |
| | | | columns. |
| | | | |
| | | DROP INDEX | Deletes an index. |
| | | | |
| | Tables | ALTER TABLE | Adds to a table new columns |
| | | | and constraints, or drops |
| | | | constraints from a table, and |
| | | | assigns a table to a |
| | | | partition or removes it from |
| | | | a partition.[REV BEG] |
| | | | |
| | | RENAME COLUMN | Defines a new name for an |
| | | | existing column. |
| | | | |
| | | RENAME TABLE | Defines a new name for an |
| | | | existing table.[REV END] |
| | | | |
| | | CREATE TABLE | Defines a table and assigns |
| | | | it to a partition. |
| | | | |
| | | TRUNCATE TABLE | Deletes all rows from a |
| | | | table. |
| | | | |
| | | DROP TABLE | Deletes a table and any |
| | | | authorities, indexes, rules, |
| | | | and views based on it. |
| | | | |
| | Views | CREATE VIEW | Defines a view based on a |
| | | | table, another view, or a |
| | | | combination of tables and |
| | | | views. |
| | | | |
| | | DROP VIEW | Deletes the definition of a |
| | | | view as well as authorities |
| | | | or views based on the view. |
| | | | |
| | Rules | CREATE RULE | Defines a rule for a table |
| | | | and associates it with |
| | | | INSERTS, UPDATES, and/or |
| | | | DELETES. |
| | | | |
| | | DROP RULE | Deletes a rule. |
| | | | |
| | | Refer to the | |
| | Groups, | database | |
| | DBEFileSets, | administration | |
| | | statements. | |
| | DBEFiles | | |
| | | | |
| | Procedures | CREATE PROCEDURE | Defines a procedure for |
| | | | storage in the DBEnvironment. |
| | | | |
| | | DROP PROCEDURE | Deletes a procedure. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| General-purpose | Partitions | CREATE PARTITION | Defines a partition for audit |
| statements | | | logging in the DBEnvironment. |
| (continued) | | | |
| | | | |
| | | DROP PARTITION | Deletes a partition. |
| | | | |
| | Data manipulation | DELETE | Deletes one or more rows from |
| | | | a single table or view. |
| | | | |
| | | INSERT | Adds a row to a single table |
| | | | or view. |
| | | | |
| | | SELECT | Retrieves data from one or |
| | | | more tables or views. |
| | | | |
| | | UPDATE | Changes the values of one or |
| | | | more columns in all rows of a |
| | | | specific table or view that |
| | | | satisfy a search condition. |
| | | | |
| | | DROP MODULE | Deletes a preprocessed |
| | | | module. |
| | | | |
| | | EXECUTE | Executes dynamically |
| | | | preprocessed statements. |
| | | | |
| | | EXECUTE IMMEDIATE | Defines and executes dynamic |
| | | | statements. |
| | | | |
| | | PREPARE | Dynamically preprocesses |
| | | | statements, storing them as a |
| | | | module if issued |
| | | | interactively. |
| | | | |
| | Transaction | BEGIN WORK | Begins a transaction and |
| | management | | optionally sets its isolation |
| | | | level and priority. |
| | | | |
| | | COMMIT WORK | Ends a transaction and makes |
| | | | permanent any changes it made |
| | | | to the DBEnvironment. |
| | | | |
| | | ROLLBACK WORK | Ends a transaction and undoes |
| | | | changes made to the |
| | | | DBEnvironment during the |
| | | | whole transaction or back to |
| | | | a savepoint within the |
| | | | transaction. |
| | | | |
| | | SAVEPOINT | Defines a point within a |
| | | | transaction back to which you |
| | | | can roll back work. |
| | | | |
| | | SET DML ATOMICITY | Sets the general error |
| | | | checking level. |
| | | | |
| | | SET CONSTRAINTS | Sets the level of constraint |
| | | | error checking. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| General-purpose | | SET SESSION | Sets transaction attributes |
| statements | | | for a session. |
| (continued) | | | |
| | | | |
| | | SET TRANSACTION | Sets execution attributes for |
| | | | a transaction. |
| | | | |
| | Executing | EXECUTE PROCEDURE | Invokes a procedure. |
| | procedures | | |
| | | | |
| | Other | RAISE ERROR | Causes a user-defined error |
| | | | to occur and specifies the |
| | | | error number and text to be |
| | | | raised. |
| | | | |
| | Concurrency | CREATE TABLE | Defines the automatic locking |
| | | | strategy and implicit |
| | | | authority grants used for a |
| | | | table. |
| | | | |
| | | LOCK TABLE | Locks a table, explicitly |
| | | | overriding ALLBASE/SQL's |
| | | | automatic locking strategy. |
| | | | |
| | | START DBE | Defines the maximum number of |
| | | | transactions that can execute |
| | | | concurrently, when used with |
| | | | the TRANSACTION= parameter. |
| | | | |
| | Module maintenance | DROP MODULE | Deletes a module from the |
| | | | system catalog, optionally |
| | | | retaining authorization |
| | | | information. |
| | | | |
| | | GENPLAN | Places optimizer's access |
| | | | plan in SYSTEM.PLAN (from |
| | | | ISQL only). |
| | | | |
| | | SETOPT | Modifies access optimization |
| | | | plan used by queries. |
| | | | |
| | | VALIDATE | Validates modules and |
| | | | procedures. |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Application | Single row data | FETCH | Retrieves a single row from |
| programming | manipulations | | an active set associated with |
| statements | | | a cursor. |
| | | | |
| | | INSERT | Inserts a single row into a |
| | | | table. |
| | | | |
| | | SELECT | Retrieves a single row not |
| | | | associated with a cursor. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
------------------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
------------------------------------------------------------------------------------------------------------
| [REV BEG] | | | |
| | | | |
| Application | Bulk manipulations | BULK FETCH | Retrieves multiple rows from |
| programming | | | an active set associated with |
| statements | | | a cursor. (See FETCH.) |
| (continued) | | | |
| | | | |
| | | BULK INSERT | Inserts multiple rows into a |
| | | | single table. (See INSERT.) |
| | | | |
| | | BULK SELECT | Retrieves multiple rows not |
| | | | associated with a cursor. |
| | | | (See SELECT.)[REV END] |
| | | | |
| | Cursor management | ADVANCE | Advances a procedure cursor. |
| | | | |
| | | CLOSE | Closes a cursor currently in |
| | | | the open state. |
| | | | |
| | | DECLARE CURSOR | Associates a cursor with a |
| | | | specific SELECT or EXECUTE |
| | | | PROCEDURE statement. |
| | | | |
| | | DELETE WHERE | Deletes the current row of an |
| | | CURRENT | active set. |
| | | | |
| | | FETCH | Advances the position of an |
| | | | open cursor to the next row |
| | | | of the active set and copies |
| | | | columns into host variables. |
| | | | |
| | | REFETCH | Copies columns from the |
| | | | current cursor position in |
| | | | the active set into host |
| | | | variables. Used with the RU |
| | | | and RC isolation levels to |
| | | | verify the continued |
| | | | existence of data and to |
| | | | obtain stronger locks prior |
| | | | to updating. |
| | | | |
| | | OPEN | Makes an active set available |
| | | | to manipulation statements. |
| | | | |
| | | UPDATE WHERE | Changes columns in the |
| | | CURRENT | current row of the active |
| | | | set. |
| | | | |
| | Preprocessor | BEGIN DECLARE | Indicates the beginning of |
| | directives | SECTION | the host variable |
| | | | declarations in an |
| | | | application program. |
| | | | |
| | | END DECLARE SECTION | Indicates the end of the host |
| | | | variable declarations in an |
| | | | application program. |
| | | | |
| | | INCLUDE | Includes declarations for |
| | | | structures used to pass |
| | | | information between |
| | | | ALLBASE/SQL and a program. |
| | | | |
------------------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Application | | | |
| programming | | | |
| statements | | | |
| (continued) | | | |
| | | | |
| | | WHENEVER | Specifies an action to be |
| | | | taken depending on the |
| | | | outcome of an SQL statement. |
| | | | |
| | Dynamically | DESCRIBE | Obtains information about the |
| | preprocessed | | results of a dynamic |
| | queries | | statement. |
| | | | |
| | | EXECUTE EXECUTE | Refer to general-purpose |
| | | IMME- DIATE | statements. |
| | | PREPARE | |
| | | | |
| | Status messages | SQLEXPLAIN | Retrieves a message |
| | | | describing the status of SQL |
| | | | statement execution. |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Database | Authorization | GRANT | Grants authorities to all |
| administration | | | users, specific users, or |
| statements | | | groups. |
| | | | |
| | | REVOKE | Revokes authorities from all |
| | | | users, specific users, or |
| | | | groups. |
| | | | |
| | | TRANSFER OWNERSHIP | Makes a different user or |
| | | | authorization group the owner |
| | | | of a table, view, |
| | | | authorization group, or |
| | | | procedure. |
| | | | |
| | Authorization | ADD TO GROUP | Adds one or more users or |
| | groups | | groups to an authorization |
| | | | group. |
| | | | |
| | | CREATE GROUP | Defines an authorization |
| | | | group. |
| | | | |
| | | DROP GROUP | Removes the definition of an |
| | | | authorization group from the |
| | | | system catalog. |
| | | | |
| | | REMOVE FROM GROUP | Removes one or more users or |
| | | | groups from an authorization |
| | | | group. |
| | | | |
| | DBEnvironment | START DBE NEW | Configures a new |
| | configuration and | | DBEnvironment. |
| | use | | |
| | | | |
| | | START DBE | Makes a DBEnvironment |
| | | | available in a mode different |
| | | | from that defined in the |
| | | | DBECon file; also starts up a |
| | | | DBEnvironment when the |
| | | | autostart flag is off. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Database | STOP DBE | Terminates all DBE | |
| administration | | sessions and causes | |
| statements | | a checkpoint to be | |
| (continued) | | taken, recovering | |
| | | log file space if | |
| | | nonarchive logging | |
| | | is in effect.[REV | |
| | | BEG] | |
| | | | |
| | | TERMINATE QUERY | Terminates a running Query. |
| | | | |
| | | TERMINATE | Stops the transaction. |
| | | TRANSACTION | |
| | | | |
| | | TERMINATE USER | Stops the DBE session for a |
| | | | specific user.[REV END] |
| | | | |
| | DBEnvironment | ENABLE RULES | Turns rule checking on for |
| | settings | | the current DBEnvironment |
| | | | session. |
| | | | |
| | | DISABLE RULES | Turns rule checking off for |
| | | | the current DBEnvironment |
| | | | session. |
| | | | |
| | | SET PRINTRULES | Specifies whether rule names |
| | | | and statement types are to be |
| | | | issued as messages when the |
| | | | rules are fired during a |
| | | | DBEnvironment session. |
| | | | |
| | | SET USER TIMEOUT | Specifies the amount of time |
| | | | the user waits if requested |
| | | | database resource is |
| | | | unavailable. |
| | | | |
| | Space management | | |
| | | | |
| | DBEFiles | ADD DBEFILE | Associates a DBEFile with a |
| | | | DBEFileSet. |
| | | | |
| | | ALTER DBEFILE | Changes the type attribute of |
| | | | a DBEFile. |
| | | | |
| | | CREATE DBEFILE | Defines and creates a |
| | | | DBEFile, optionally |
| | | | specifying a group name and |
| | | | volume. |
| | | | |
| | | DROP DBEFILE | Removes the definition of an |
| | | | empty DBEFile not associated |
| | | | with a DBEFileSet. |
| | | | |
| | | REMOVE DBEFILE | Disassociates a DBEFile from |
| | | | a DBEFileSet. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Database | DBEFileSets | CREATE DBEFILESET | Defines a DBEFileSet. |
| administration | | | |
| statements | | | |
| (continued) | | | |
| | | | |
| | DBEFileSets | SET DEFAULT | Sets a default DBEFileSet. |
| | | DBEFILESET | |
| | | | |
| | | DROP DBEFILESET | Removes the definition of a |
| | | | DBEFileSet from the system |
| | | | catalog. |
| | | | |
| | Temporary sort | CREATE TEMPSPACE | Defines and creates a |
| | space | | temporary storage space. |
| | | | |
| | | DROP TEMPSPACE | Removes the definition of a |
| | | | temporary storage space from |
| | | | the system catalog. |
| | | | |
| | Logging | | |
| | | | |
| | | BEGIN ARCHIVE | Starts a new archive log file |
| | Recovery of | COMMIT ARCHIVE | before a DBEnvironment is |
| | log | | back up. |
| | space | | |
| | | | |
| | | CHECKPOINT | Causes an ALLBASE/SQL system |
| | | | checkpoint to be taken. A |
| | | | system checkpoint causes data |
| | | | and log buffers to be written |
| | | | to disk and makes old log |
| | | | space, occupied by completed |
| | | | transactions, available for |
| | | | reuse if nonarchive logging |
| | | | is in effect. Returns values |
| | | | in host variable. |
| | | | |
| | | START DBE NEWLOG | Reinitializes log file(s) |
| | | | when you need to change the |
| | | | size. Makes audit logging |
| | | | effective when used with |
| | | | AUDIT LOG option. |
| | | | |
| | | START DBE | Initiates the first DBE |
| | | | session if the DBE is not in |
| | | | autostart mode and causes a |
| | | | checkpoint to be taken, |
| | | | recovering log file space if |
| | | | nonarchive logging is in |
| | | | effect. |
| | | | |
| | | STOP DBE | Terminates all DBE sessions |
| | | | and causes a checkpoint to be |
| | | | taken, recovering log file |
| | | | space if nonarchive logging |
| | | | is in effect. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Database | Dual logging | START DBE NEW | Causes ALLBASE/SQL to |
| administration | | | maintain two separate, |
| statements | | | identical logs, when used |
| (continued) | | | with the DUAL LOG option. |
| | | | Makes audit logging effective |
| | | | when used with AUDIT LOG |
| | | | option. |
| | | | |
| | Audit logging | DISABLE AUDIT | Disables current audit |
| | | LOGGING | logging for a session. |
| | | | |
| | Log comment | LOG COMMENT | Enters a user comment in the |
| | | | log file. |
| | | | |
| | | ENABLE AUDIT | Enables audit logging for a |
| | | LOGGING | session after being disabled. |
| | | | |
| | Recovery | START DBE | Rolls back transactions that |
| | Rollback | | were incomplete the last time |
| | | | the DBEnvironment was shut |
| | | | down. |
| | | | |
| | | TERMINATE USER | Ends a user's transactions, |
| | | | backing out any work not |
| | | | committed. |
| | | | |
| | | STOP DBE | Terminates all DBE sessions |
| | | | and causes a checkpoint to be |
| | | | taken, |
| | | | |
| | Rollforward | BEGIN ARCHIVECOMMIT | Creates an archive record in |
| | | ARCHIVE | the rollforward log(s) and |
| | | | initiates archive mode |
| | | | logging. |
| | | | |
| | DBEnvironment | RESET | Resets ALLBASE/SQL accounting |
| | statistics | | and statistical data activity |
| | | | management. |
| | | | |
| | | UPDATE STATISTICS | Updates system catalog |
| | | | information used to optimize |
| | | | data access operations on a |
| | | | per table basis. |
| | | | |
---------------------------------------------------------------------------------------------------
Table 10-1. SQL Statement Summary (cont.)
---------------------------------------------------------------------------------------------------
| | | | |
| Group | Category | Statement | Statement Use |
| | | | |
---------------------------------------------------------------------------------------------------
| | | | |
| Procedure | General statements | Assignment (=) | Assigns a value to a local |
| statements | | | variable or parameter in a |
| | | | procedure. |
| | | | |
| | | DECLARE Variable | Defines a local variable |
| | | | within a procedure. |
| | | | |
| | | PRINT | Stores information to be |
| | | | displayed by ISQL or an |
| | | | application program. |
| | | | |
| | Control flow | BEGIN | Begins a single statement or |
| | statements | | group of statements within a |
| | | | procedure. |
| | | | |
| | | GOTO | Permits a jump to a labeled |
| | | | statement within a procedure. |
| | | | |
| | | Label | Labels a statement in a |
| | | | procedure. |
| | | | |
| | | IF | Allows conditional execution |
| | | | of one or more statements |
| | | | within a procedure. |
| | | | |
| | | RETURN | Permits an exit from a |
| | | | procedure with an optional |
| | | | return code. |
| | | | |
| | | WHILE | Allows looping within a |
| | | | procedure. |
| | | | |
---------------------------------------------------------------------------------------------------