Statements

CA IDMS SQL statements fall into the following categories:
idmscu
CA IDMS SQL statements fall into the following categories:
Category
Description
Control the creation and characteristics of access modules
Control access to and ownership of database entities
Define the flow of control in an SQL routine and assign values from expressions to routine parameters or local variables
Control the creation and characteristics of logical database entities
Retrieve and update data in the database
Diagnose the execution of SQL statements and return statistical information of the current transaction.
Control the run-time compilation and execution of SQL statements
Instruct the precompiler to include specified data structures and to generate specified error-processing code
Establish and control the characteristics of SQL sessions
Establish and control the characteristics of CA IDMS database transactions
Access Module Management Statements
Statement
Purpose
ALTER ACCESS MODULE
Modifies an access module in the dictionary
CREATE ACCESS MODULE
Creates an access module from one or more SQL statement modules (RCMs)
DROP ACCESS MODULE
Deletes an access module and its definition from the dictionary
EXPLAIN
Describes the strategy used to access data for a DELETE, INSERT, SELECT, or UPDATE statement
Authorization Statements
Statement
Purpose
GRANT definition privileges
Gives one or more users the privilege of performing selected actions on a specified schema, access module, table, or view
GRANT execution privilege
Gives one or more users the privilege of executing a specified access module
GRANT all privileges
Gives one or more users all definition and access privileges on a specified table or view
GRANT table access privileges
Gives one or more users the privilege of performing selected actions on a specified table or view
REVOKE definition privileges
Removes from one or more users the privilege of performing selected actions on a specified schema, access module, table, or view
REVOKE execution privilege
Removes from one or more users the privilege of executing a specified access module
REVOKE all privileges
Removes from one or more users all definition and access privileges on a specified table or view
REVOKE table access privileges
Removes from one or more users the privilege of performing selected actions on a specified table or view
TRANSFER OWNERSHIP
Passes ownership of a schema from one user or group of users to another
Control Statements
The CA IDMS SQL Control statements allow you to define the flow of control in an SQL routine and assign values to routine parameters or local variables. These statements are used primarily by the following:
  • Developers of SQL routines
  • Programmers developing SQL application programs
  • Users of interactive SQL tools
For more information about the individual Control statements, see SQL Control Statements.
Data Description Statements
Statement
Purpose
ALTER CATALOG
Supports the correct sorting of additional national characters for specific languages (used by the alternate character set feature)
ALTER FUNCTION
Modifies the definition of a function in the dictionary
ALTER INDEX
Enables the maximum number of entries to be changed without affecting the existing index structure
ALTER PROCEDURE
Modifies the definition of a procedure in the dictionary
ALTER TABLE PROCEDURE
Modifies the definition of a table procedure in the dictionary
ALTER SCHEMA
Modifies the definition of a schema in the dictionary
ALTER TABLE
Modifies the definition of a base table in the dictionary
CREATE CALC
Defines a CALC key on a base table
CREATE CONSTRAINT
Defines a constraint in the dictionary
CREATE FUNCTION
Defines a function in the dictionary
CREATE INDEX
Defines an index on a base table
CREATE PROCEDURE
Defines a procedure in the dictionary
CREATE SCHEMA
Defines a schema in the dictionary
CREATE TABLE
Defines a table in the dictionary
CREATE TABLE PROCEDURE
Defines a table procedure in the dictionary
CREATE TEMPORARY TABLE
Defines a temporary table
CREATE VIEW
Defines a view in the dictionary
DROP CONSTRAINT
Deletes the definition of a constraint from the dictionary
DROP CALC
Deletes the definition of a CALC key from the dictionary
DROP FUNCTION
Deletes the definition of a function from the dictionary
DROP INDEX
Deletes the definition of an index from the dictionary
DROP PROCEDURE
Deletes the definition of a procedure in the dictionary
DROP SCHEMA
Deletes the definition of a schema from the dictionary
DROP TABLE
Deletes the definition of a base table from the dictionary
DROP TABLE PROCEDURE
Deletes the definition of a table procedure in the dictionary
DROP VIEW
Deletes the definition of a view from the dictionary
Data Manipulation Statements
Statement
Purpose
CLOSE*
Places a specified cursor in the closed state
DECLARE CURSOR*
Defines a cursor for a specified result table
DECLARE EXTERNAL CURSOR*
Identifies an externally defined global cursor to be used by the application program
DELETE
Deletes one or more rows from a table
FETCH*
Retrieves values from the result table associated with a cursor
INSERT
Adds one or more new rows to a table
OPEN*
Places a specified cursor in the open state
SELECT
Retrieves values from one or more tables and views
UPDATE
Modifies the values in one or more rows of a table
*
Programmatic only
Diagnostics and Statistics Statements
The SQL Diagnostic statements category is used for diagnosing the execution of SQL statements and for returning statistical information for the current transaction.
These statements can be used as embedded SQL, including embedding in an SQL-invoked routine. The GET STATISTICS statement can also be used in the SQL command facility and the CA IDMS Visual DBA command console.
Statement
Purpose
GET DIAGNOSTICS*
Diagnoses the execution of the last executed SQL statement.
GET STATISTICS
Returns statistical information for the current transaction.
*
Programmatic only
Dynamic Compilation Statements
Statement
Purpose
ALLOCATE CURSOR*
Defines a cursor for a dynamically-prepared statement
DEALLOCATE PREPARE*
Destroys a dynamically-compiled statement and all other dynamically-compiled statements that directly or indirectly reference it.
DESCRIBE*
Directs CA IDMS to return information about a dynamically-compiled SQL statement in an SQL descriptor area
EXECUTE*
Executes a dynamically-compiled SQL statement
EXECUTE IMMEDIATE*
Dynamically compiles and executes an SQL statement
PREPARE*
Dynamically compiles an SQL statement for later execution in the application program
*
Programmatic only
Precompiler-directive Statements
Statement
Purpose
BEGIN DECLARE SECTION*
Notifies the precompiler that a host variable definition is beginning.
END DECLARE SECTION*
Notifies the precompiler that a host variable definition has ended.
INCLUDE*
Directs the precompiler to create host variable definitions for a specified structure or table in the application program
WHENEVER*
Specifies an action to be taken when the execution of an SQL statement results in a nonzero SQLCODE value
*
Programmatic only
Session Management Statements
Statement
Purpose
CONNECT
Establishes a connection to a CA IDMS dictionary and begins an SQL session
RELEASE
Releases a connection to a CA IDMS dictionary and ends the SQL session
RESUME SESSION
Resumes a suspended SQL session
SET SESSION
Establishes SQL session characteristics
SUSPEND SESSION
Suspends an SQL session and any transaction currently active within the session
Transaction Management Statements
Statement
Purpose
COMMIT
Makes permanent the changes to the database made during the current transaction and optionally ends the transaction
ROLLBACK
Cancels changes made to the database during the current transaction and ends the transaction
SET ACCESS MODULE*
Identifies the access module to be used by a transaction
SET TRANSACTION
Overrides access module defaults for conditions under which a transaction executes
*
Programmatic only