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 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 MULTITRANSACTION	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, DBEFileSets, 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, 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.
	DBEFileSets	CREATE DBEFILESET	Defines a DBEFileSet.
	DBEFileSets	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 ARCHIVECOMMIT 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.

SQL Statement Summary

Technical documentation

» Table of Contents

» Index