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 FETCH.) |
| | 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. |