SQL Statements

Before You Start › What You Should Know About SQL › SQL Statements

SQL Statements

You embed SQL statements in a host program written in a host language such as COBOL or PL/I. Variables defined in the host program that are referenced by the SQL statements are called host variables.

You can also submit certain SQL statements through the CA Datacom Datadictionary Interactive SQL Service Facility or interactively through CA Dataquery. See the Statement Execution Table.

CA Datacom/DB supports the dynamic preparation and execution of SQL statements under the control of an application program. See Dynamic SQL.

The SQL sub-language consists of the following:

Data Definition Language (DDL)

DDL statements define the SQL objects, such as tables and views.

Note: Because DDL statements are not recorded to the Log Area (LXX), they are not recoverable using the RECOVERY function of the CA Datacom/DB Utility (DBUTLTY). In the case of DDL statements, it is therefore your responsibility to ensure the existence of the Directory (CXX) definitions necessary for recovery.

Data Manipulation Language (DML)

DML statements let you access and manipulate the data in your SQL tables.

Note: You cannot use SQL DML statements to do maintenance on the DATA-DICT database, that is, no maintenance can be done to any tables in the DATA-DICT database using SQL. For details about DATA-DICT, see the CA Datacom/DB Database and System Administration Guide.

SQL Control Statements

Includes the CALL and EXECUTE PROCEDURE statements that supports the implementation of procedures and triggers beginning in r10.

The following table lists the SQL statements in the categories of DDL, DML, and SQL Control Statements:

Data Definition Language (DDL)	Data Manipulation Language (DML)	SQL Control Statements
ALTER TABLE COMMENT ON CREATE INDEX CREATE PROCEDURE CREATE RULE CREATE SCHEMA CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE VIEW DROP GRANT REVOKE	Cursor operations: CLOSE DECLARE CURSOR DELETE...CURRENT (positioned DELETE) FETCH OPEN UPDATE...CURRENT (positioned UPDATE) Non-cursor operations: DELETE (searched DELETE) INSERT SELECT UPDATE (searched UPDATE) Exception handling operations: WHENEVER	CALL EXECUTE PROCEDURE

Data Definition Language (DDL)

Data Manipulation Language (DML)

SQL Control Statements

ALTER TABLE
COMMENT ON
CREATE INDEX
CREATE PROCEDURE
CREATE RULE
CREATE SCHEMA
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DROP
GRANT
REVOKE

Cursor operations:

CLOSE
DECLARE CURSOR
DELETE...CURRENT (positioned DELETE)
FETCH
OPEN
UPDATE...CURRENT (positioned UPDATE)

Non-cursor operations:

DELETE (searched DELETE)
INSERT
SELECT
UPDATE (searched UPDATE)

Exception handling operations:

WHENEVER

CALL
EXECUTE PROCEDURE

The following table lists the dynamic SQL and SQL session statements:

Dynamic SQL Statements	SQL Session Statement
DESCRIBE dynamic DECLARE dynamic FETCH dynamic OPEN EXECUTE EXECUTE IMMEDIATE PREPARE	SET CURRENT SQLID

See the descriptions of the SQL statements beginning with ALTER TABLE for information on how to use these statements.