SQL in CA Datacom
SQL is a database sub-language that 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.
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,
Datacom/DBoffers SQL support as a fully integrated part of
Datacom/DB. We intend
Datacom/DBSQL 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 that is provided by other DATACOM products such as
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.
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 that is stored in a base table. Each table consists of a specific number of columns and an unordered collection of rows.
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.
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.
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
The following example is a conceptual diagram of a table named PERSONNEL:
The following example is a conceptual diagram of a table named PAY:
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. Therefore, 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." 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). However, 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.
The following example is a conceptual diagram of a view which you have named WAGES:
The view WAGES, derived from the tables PERSONNEL and PAY, thus shows a "view" of only the columns that you want to see.
Tables are often accessed by the data values that are 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 rows of the table by their data value content. A given table can support multiple indexes.
Datacom/DBautomatically maintains the index as the content of the table 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 supported operations for a table.
Datacom/DBalso 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 that are accessed "read only" created by the system if your Database Administrator has selected this space management option.
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. The LUW includes the data and control information that is needed to enable
Datacom/DBto back out or reapply all an changes of an application 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
Datacom/DBcommands that are executed within a single unit of recovery.
Units of recovery can be isolated from the updating operations of other units of recovery. This is called isolation level. When you are 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. However, 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 that are referenced by your program cannot be changed by other programs until your program reaches a commit point.
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 lockbefore 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 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 would 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:If concurrent transactions are not using repeatable read, repeatable read might cause more exclusive control interlocks. 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 scan range with exclusive control. While CS is waiting, unless row R1 has already been read by the RR 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.
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
personalizedSQL 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.
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.
For application programs, the default authorization ID is the one named in the AUTHID= Preprocessor option.
An accessor ID designates a user. This ID is a user ID, not a schema authorization ID.
Security is typically handled using the
Datacom/DBExternal Security Model. With external security, access rights to the underlying data are controlled through table, plans, or view rights, which are 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.
Datacom/DBare granted to users, not to schema IDs. For example, when a table is created the table is defined to be in a particular schema. 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.
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 when accessing a table or view that is owned by another schema.
You embed SQL statements in a host program that is 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
Datacom DatadictionaryInteractive SQL Service Facility or interactively through
Dataquery. For more information, see the SQL Statements section.
Datacom/DBsupports the dynamic preparation and execution of SQL statements under the control of an application program. For more information, see Dynamic SQL.
The SQL sub-language consists of the following statements:
- Data Definition Language (DDL)DDL statements define the SQL objects, such as tables and views.Because DDL statements are not recorded to the Log Area (LXX), they are not recoverable using the RECOVERY function of theDatacom/DBUtility (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.You cannot use SQL DML statements to do maintenance on the DATA-DICT database. Therefore, no maintenance can be done to any tables in the DATA-DICT database using SQL.
- SQL Control StatementsIncludes the CALL and EXECUTE PROCEDURE statements that support 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
DELETE...CURRENT (positioned DELETE)
UPDATE...CURRENT (positioned UPDATE)
DELETE (searched DELETE)
UPDATE (searched UPDATE)
Exception handling operations:
The following table lists the dynamic SQL and SQL session statements:
Dynamic SQL Statements
SQL Session Statement
SET CURRENT SQLID
See the descriptions of the SQL statements beginning with ALTER TABLE for information on how to use these statements.
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.
Datacom/DBdelays some decisions which impact the method that is used to execute an SQL statement until execution time if information that is 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
Dataquery, binding occurs during the validation step. For the
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
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.
A product of the binding process is the
Datacom/DBaccess plan. The plan is required by
Datacom/DBto 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
Datacom Datadictionary. The cost of binding is eliminated at each execution of a statement.
Because SQL plans are stored in the
Datacom Datadictionary, the
Datacom Datadictionarymust be available to execute previously prepared SQL statements.
SQL plans are securable. With plan security you can create a plan such that, 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.