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:
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 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.
Following is a conceptual diagram of a table named PERSONNEL:
|ROW 3||002105||MacBond||Sean||D||El Paso||TX|
Following 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, 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:
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 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.
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.
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.
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.
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.
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:
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.
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.
An accessor ID designates a user. This ID is a user ID, not a schema authorization ID.
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.
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.
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.
Data Manipulation Language (DML)
DML statements let you access and manipulate the data in your SQL tables.
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 (searched DELETE)
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.
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.
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.