Skip to content
CA Datacom Core - 15.1
Documentation powered by DocOps

Get Started with SQL in CA Datacom

Last update January 2, 2019

SQL is a database sub-language which you can use to define, manipulate, and control data in your relational databases. As part of our ongoing commitment to protect your investments in application software resources, CA Datacom®/DB offers SQL support as a fully integrated part of CA Datacom®/DB. We intend CA Datacom®/DB SQL to provide support that offers a broad scope of facilities for the development of applications while minimizing the amount of effort required to port those applications from one DBMS to another.

SQL allows you to perform powerful relational functions such as projection, restriction, joining, and union.

In performing tasks using SQL, you can draw on support provided by other DATACOM products such as CA Datacom® Datadictionary™ and CA Dataquery™ for CA Datacom®.

The following topics are discussed on this page:

What You Should Know About SQL

While a database must satisfy many requirements to be classified as a relational database, one of the requirements is that the data appears to you as a collection of tables.

Tables

SQL allows you to access tables as sets of data. A base table is the table as it is defined and contained in the database. You can form result tables by accessing only part of the data stored in a base table. Each table consists of a specific number of columns and an unordered collection of rows.

Columns

Columns are the vertical components of the table. A column describes an indivisible unit of data. Each column has a name and a particular data type, such as character or integer. While the order of columns in a table is fixed, there is no conceptual significance to this order.

Rows

The horizontal components of tables are named rows. A row is a sequence of values, one for each column of the table. Each row contains the same number of columns. You insert and delete rows, whereas you update individual columns. A table, by the way, can exist without any rows.

Views

Using SQL, you can define views, which are alternative representations of data from one or more tables.

A view is a derived table or a subset of the columns and rows of the table on which it is defined. A view can also be defined on another view.

The capability of joining two or more tables easily is a major advantage that distinguishes relational systems from nonrelational systems. The ability to create views, or derived tables, allows you to access and manipulate only that data which is significant for your purposes.

Table and View Examples

Following is a conceptual diagram of a table named PERSONNEL:


EMPNO LNAME FNAME MI CITY ST
ROW 1 010900 Duparis Jean C Houston TX
ROW 2 008206 Santana Juan M Dallas TX
ROW 3 002105 MacBond Sean D El Paso TX
ROW 4 010043 Odinsson Jon L Dallas TX

Following is a conceptual diagram of a table named PAY:


EMPNO SALARY YTDCOM
ROW 1 010043 03560000 00120000
ROW 2 008206 04530000 00290000
ROW 3 010900 02970000 00075000
ROW 4 002105 03280000 00107500

The two previously shown tables contain information about the same four people (match the EMPNO columns), but the order of the rows in each table is not significant.

However, the columns appear in the same order in each row. For example, in the PERSONNEL table, EMPNO is always first, LNAME is always second, FNAME is always third, and so on.

The values which appear in a column fall within the same type, that is to say, LNAME, FNAME, and MI each contain character data, while SALARY contains numeric data.

The values which appear in LNAME all fall within the range of valid values, or domain, of "last name," the values in FNAME are within the domain of "first name," and the values in SALARY are within the domain of "salary" which is $999,999.99 to 0.00 for this example.

Some columns, such as ST (for "state"), might contain duplicate values (in this case, TX), but that does not mean that TX is the only value in the domain for the column ST.

Other columns contain only unique values, such as EMPNO, because no two employees of this company have the same employee number. In the previous example, the employee number is used to uniquely identify information about each employee no matter which table contains the information.

Using the tables in the previous example, you could define a view that allows you to see the name of each employee (columns LNAME, FNAME and MI from the PERSONNEL table) and the salary of that employee (column SALARY from the PAY table). In your "view," the information you requested would look like a table and actually joins specified data from two different tables.

Following is a conceptual diagram of a view which you have named WAGES:


LNAME FNAME MI SALARY
ROW 1 Duparis Jean C 02970000
ROW 2 Santana Juan M 04530000
ROW 3 MacBond Sean D 03280000
ROW 4 Odinsson Jon L 03560000

The view WAGES, derived from the tables PERSONNEL and PAY, thus shows a "view" of only the columns that you want to see.

Indexes

Tables are often accessed by the data values contained in one or more columns. To make such accesses efficient, the tables can be indexed by one or more columns. Such an index supports direct access to the table's rows by their data value content. A given table can support multiple indexes. CA Datacom®/DB automatically maintains the index as the table's content changes.

Indexes are a performance-only consideration for you, the SQL user. The presence or absence of an index does not enhance or restrict the logical operations supported for a table. CA Datacom®/DB also supports a special type of index to control the physical placement of rows to enhance performance. This "clustering index" is automatically the lowest level, no locks are acquired for rows accessed "read only" created by the system if your Database Administrator has selected this space management option.

Cursors

You can control the row to which an application program points by manipulating a control structure named the cursor. You can use the cursor to retrieve rows from an ordered set of rows, possibly updating or deleting. The SQL statements FETCH, UPDATE, and DELETE support the concept of positioned operations.

Units of Work

A unit of work contains one or more units of recovery. In a batch environment, a unit of work corresponds to the execution of an application program. Within that program, there may be many units of recovery as COMMIT or ROLLBACK statements are executed.

Units of Recovery (Logical Unit of Work)

A unit of recovery, also known as a Logical Unit of Work (LUW), is a sequence of operations within a unit of work and includes the data and control information needed to enable CA Datacom®/DB to back out or reapply all an application's changes to recoverable resources since the last commit point. A unit of recovery is initiated when a unit of work starts or by the termination of a previous unit of recovery. A unit of recovery is terminated by a commit or rollback operation or the termination of a unit of work. The commit or rollback operation affects only the results of SQL statements and CA Datacom®/DB commands executed within a single unit of recovery.

Isolation Levels

Units of recovery can be isolated from the updating operations of other units of recovery. This is called isolation level. When making changes to data, you can control access to SQL tables through the isolation level Preprocessor option, or with the LOCK TABLE statement.

The "uncommitted data" isolation level allows you to access rows that have been updated by another unit of recovery, but the changes have not been committed, or written to the base table.

The isolation level that provides a higher degree of integrity is the "cursor stability" isolation level. With cursor stability, a unit of recovery holds locks only on its uncommitted changes and the current row of each of its cursors.

The "repeatable read" isolation level provides maximum protection from other executing application programs. When your program executes with repeatable read protection, rows referenced by your program cannot be changed by other programs until your program reaches a commit point.

Note: In a Data Sharing environment, an isolation level of repeatable read is not supported across the MUFplex.

Repeatable Read Interlocks

The repeatable read transaction isolation level provides the highest level of isolation between transactions because it acquires a share or exclusive scan range intent lock before beginning a scan (all rows are accessed with the scan operation). This lock is released when the transaction ends, guaranteeing that other transactions cannot update, delete or insert rows within the scan range until the transaction ends. If another transaction attempts to do so, it waits until the transaction has ended, or one of the transactions is aborted if an exclusive control interlock occurs. As the name implies, a repeatable read transaction is therefore guaranteed to reread the exact same set of rows if it reopens a cursor or re-executes a SELECT INTO statement (any changes made by the transaction itself would of course be visible).

Although repeatable read isolation provides a convenient way to isolate transactions, it does so at the cost of possible lower throughput and more exclusive control interlocks, as described in the following:

  • Lower Throughput:
    Because more rows remain locked for a longer timeframe, repeatable read isolation might lower total throughput (transactions wait longer for locks to be released).
  • Mixed Repeatable Read and Cursor Stability Transactions:
    Repeatable read might cause more exclusive control interlocks, especially if concurrent transactions are not using repeatable read. For example, if cursor stability transaction CS updates row R1, and then repeatable read transaction RR acquires a scan range intent lock that includes row R1, CS waits if it attempts to read a row in RR's scan range with exclusive control. While CS is waiting, unless row R1 has already been read by RR's scan, RR eventually attempts to read row R1 with a row-level share lock. But because CS is waiting on RR, neither transaction can continue. So, the deadlock condition is resolved by abnormally terminating RR, which releases its locks and allows CS to continue. In this case, if transaction CS is changed to repeatable read isolation, it acquires an exclusive scan range intent lock before updating row R1. Transaction RR then waits when it attempts to acquire its scan range intent lock.
  • Scan Range May be Entire Table:
    A deadlock can still occur if concurrent repeatable read transactions acquire multiple scan range intent locks. The same conditions exist as with row-level locking of cursor stability transactions, except that with repeatable read a larger number of rows might be locked with the scan ranges, and these locks are held for a longer timeframe. This is especially true when the first column of the scan index is not restricted, or multiple indexes are merged. In these cases, the scan range is the entire table.
  • Avoiding Deadlocks:
    If deadlock avoidance is critical, it can be avoided if all concurrent transactions execute LOCK TABLE statements in the same sequence before executing any other statements in a transaction. If the transaction might insert, update or delete rows of a table, the lock must be exclusive, and this causes all other transactions attempting to execute a LOCK TABLE statement for the table, or tables, to wait. Because the LOCK TABLE statements are executed in the same sequence, perhaps by table name, no deadlock can occur.

Schemas

A schema is a collection of tables, views, synonyms, and plans which make up an SQL environment. Schemas might be created so that each user has a personalized SQL environment by creating a schema for each user. Schemas might also be created that reflect some other organization of data, such as by department or project. Or a combination of both approaches might be used.

Authorization ID

The name of a schema is known as its authorization ID. A fully-qualified table, view, synonym, or plan name consists of the name of the object and the authorization ID of the schema to which the object belongs. If an authorization ID is not explicitly specified, the default authorization ID in effect is assumed.

Note: For application programs, the default authorization ID is the one named in the AUTHID= Preprocessor option.

Accessor ID

An accessor ID designates a user. This ID is a user ID, not a schema authorization ID.

Privileges

Security is typically handled using the CA Datacom®/DB External Security Model. With external security, access rights to the underlying data are controlled through table, plans, or view rights, defined in the external security product.

Optionally, you might secure access using the SQL Security Model. With the SQL Security Model, privileges are automatically granted to the owner when a table or view is created. The owner might then grant and revoke those privileges to others by issuing GRANT and REVOKE statements. With external security, there is no automatic granting of privileges.

Note: Privileges in CA Datacom®/DB are granted to users, not to schema IDs. For example, when a table is created the table is defined to be in a particular schema. But the privileges which are automatically granted are given to the accessor ID of the user who executed the CREATE TABLE statements. Similarly, when privileges are granted, they are granted to users, not schemas.

Synonym

Synonyms are alternative names for tables and views. The full name of a table or view is qualified by the authorization ID. You can avoid using the full name by defining a synonym for a specific table or view. These short names are especially useful if accessing a table or view owned by another schema.

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™ for CA Datacom®. See the SQL Statements section.

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.
  • 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 AREA
ALTER DATABASE
ALTER TABLE
COMMENT ON
CREATE AREA
CREATE DATABASE
CREATE INDEX
CREATE PROCEDURE
CREATE RULE
CREATE SCHEMA
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DROP
DROP AREA
DROP DATABASE
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.

Binding

SQL statements must be prepared during the program preparation process before the program is executed. This process is named binding. The SQL Preprocessor prepares the SQL portions of a source program for execution.

CA Datacom®/DB delays some decisions which impact the method used to execute an SQL statement until execution time if information required to make the best decision is not available until execution time. This technique is named phased binding. In effect, the binding process is performed in discrete phases and one of those phases does not occur until execution time.

For SQL statements embedded in a host language, such as COBOL, binding is performed when the program is preprocessed. For SQL statements executed through CA Dataquery™ for CA Datacom®, binding occurs during the validation step. For the CA Datacom® Datadictionary™, binding occurs automatically when SQL statements are executed.

When a statement is prepared, any dependencies of that statement on table or view definitions are recorded in the CA Datacom® Datadictionary™. If any dependent objects are changed, the related statement is marked invalid and must be rebound before it can be executed again.

The SQL Manager automatically attempts a rebind when an invalid statement is executed. Rebinding can also be requested in advance. For more information, see SQL Preprocessors.

Plan

A product of the binding process is the CA Datacom®/DB access plan. The plan is required by CA Datacom®/DB to process SQL statements encountered during execution. The preparation phase builds the plan for the application and binds a statement to table, view and synonym definitions stored in the CA Datacom® Datadictionary™. This eliminates the cost of binding at each execution of a statement.

Because SQL plans are stored in the CA Datacom® Datadictionary™, the CA Datacom® Datadictionary™ must be available to execute previously prepared SQL statements.

SQL plans are securable. With plan security you can create a plan such that, in order to execute the plan, an accessor ID must have the plan EXECUTE privilege for that plan. The plan EXECUTE privilege can be granted with the GRANT statement and revoked with the REVOKE statement. For other plan security information, see GRANT and REVOKE, and see CHECKPLAN=, CHECKWHEN=, CHECKWHO=, and SAVEPLANSEC= options, in the SQL Preprocessor Options.

More Information:

Was this helpful?

Please log in to post comments.