General Purpose
Statements |
| DBEnvironment session management |
| | | CONNECT | Begins a DBEnvironment
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 MULTI
TRANSACTION | Switches between single-transaction
mode and multi-transaction mode. |
| | | RELEASE | Terminates a DBEnvironment session. |
| | Data
definition |
| | 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. |
| | | RENAME COLUMN | Defines a new name
for an existing column. |
| | | RENAME TABLE | Defines a new name
for an existing table. |
| | | 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. |
| | Groups, DBEFileSet, DBEFiles | Refer to the database administration statements. | |
| | Procedures | CREATE PROCEDURE | Defines a procedure
for storage in the DBEnvironment. |
| | | DROP PROCEDURE | Deletes a procedure. |
| | Partitions | CREATE PARTITION | Defines a partition
for audit logging in the DBEnvironment. |
| | | 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 management |
| | | BEGIN WORK | Begins a transaction
and 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. |
| | | SET SESSION | Sets transaction
attributes for a session. |
| | | SET TRANSACTION | Sets execution attributes for
a transaction. |
| | Executing procedures | EXECUTE PROCEDURE | Invokes a procedure. |
| | 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 Programming
Statements |
| Single row data manipulations |
| | | FETCH | Retrieves a single
row from an active set associated with a cursor. |
| | | INSERT | Inserts a single
row into a table. |
| | | SELECT | Retrieves a single row not associated
with a cursor. |
| Bulk manipulations |
| | | BULK FETCH | Retrieves multiple
rows from an active set associated with a cursor. (See .) |
| | | BULK INSERT | Inserts multiple
rows into a single table. (See INSERT.) |
| | | BULK SELECT | Retrieves multiple rows not associated
with a cursor. (See SELECT.) |
| 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 CURRENT | Deletes the current
row of an 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 CURRENT | Changes columns in the current
row of the active set. |
| Preprocessor directives |
| | | BEGIN DECLARE SECTION | Indicates the beginning
of 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. |
| | | WHENEVER | Specifies an action to be taken depending
on the outcome of an SQL statement. |
| Dynamically preprocessed
queries |
| | | DESCRIBE | Obtains information
about the results of a dynamic statement. |
| | | EXECUTE
EXECUTE IMMEDIATE PREPARE | Refer to general-purpose statements. |
| | Status messages | SQLEXPLAIN | Retrieves a message describing
the status of SQL statement execution. |
Database Administration
Statements |
| Authorization |
| | | GRANT | Grants authorities to all users, specific
users, or 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 groups |
| | | ADD TO GROUP | Adds one or more
users or 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 configuration
and use |
| | | START DBE NEW | Configures a new
DBEnvironment. |
| | | 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. |
| STOP DBE |
| | | Terminates all
DBE sessions and causes a checkpoint to be taken, recovering log
file space if nonarchive logging is in effect. | |
| | | TERMINATE QUERY | Terminates a running
Query. |
| | | TERMINATE TRANSACTION | Stops the transaction. |
| | | TERMINATE USER | Stops the DBE session for a
specific user. |
| DBEnvironment settings |
| | | ENABLE RULES | Turns rule checking
on for 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. |
| | | DROP DBEFILE | Removes the definition
of an empty DBEFile not associated with a DBEFileSet. |
| | | REMOVE DBEFILE | Disassociates a
DBEFile from a DBEFileSet. |
| | DBEFileSets | CREATE DBEFILESET | Defines a DBEFileSet. |
| | | SET DEFAULT DBEFILESET | Sets a default
DBEFileSet. |
| | | DROP DBEFILESET | Removes the definition of a DBEFileSet
from the system catalog. |
| Temporary sort space |
| | | CREATE TEMPSPACE | Defines and creates
a temporary storage space. |
| | | DROP TEMPSPACE | Removes the definition of a temporary
storage space from the system catalog. |
| | Logging |
| | Recovery of log
space | BEGIN ARCHIVE COMMIT ARCHIVE | Starts a new archive
log file before a DBEnvironment is back up. |
| | | 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. |
| | Dual logging | START DBE NEW | Causes ALLBASE/SQL to maintain two
separate, identical logs, when used with the DUAL LOG option. Makes
audit logging effective when used with AUDIT LOG option. |
| | Audit logging | DISABLE AUDIT LOGGING | Disables current audit logging
for a session. |
| | Log comment | LOG COMMENT | Enters a user comment
in the log file. |
| | | ENABLE AUDIT LOGGING | Enables audit logging for a
session after being disabled. |
| Recovery Rollback |
| | | START DBE | Rolls back transactions
that 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 ARCHIVE COMMIT ARCHIVE | Creates an archive record in
the rollforward log(s) and initiates archive mode logging. |
| DBEnvironment statistics |
| | | RESET | Resets ALLBASE/SQL
accounting and statistical data activity management. |
| | | UPDATE STATISTICS | Updates system catalog information used
to optimize data access operations on a per table basis. |
Procedure Statements |
| General statements |
| | | Assignment (=) | Assigns a value
to a local 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 statements |
| | | BEGIN | Begins a single
statement or 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. |
