By default, tables created by the native CA Datacom/DB Datadictionary are not immediately available for SQL processing. Tables created using the SQL DDL (Data Definition Language) are accessible to both SQL and the CA Datacom/DB native APIs (RAAT and SAAT). The process to make a table SQL accessible is relatively straightforward and is discussed in detail below.
One thing to note, while all Datacom tables can be made SQL accessible, the flexibility of the native data definition (Datadictionary) supports the ability to define tables with non-relational attributes such as group fields, repeating groups, and redefines. For these non-relational field types, SQL access is limited or not available.
Procedure for Making a Table SQL Accessible
Copy the existing Datadictionary table definition from PRODuction into TEST status. Using DDOL or DDUPDATE (3152 transaction), set the table attribute SQL-INTENT to "Y". Copy the table definition from TEST to PRODuction status, and catalog the definition to the CXX (Directory).
During the CXX catalog, the SQL-INTENT=Y attribute triggers the automatic population of the table and column information (from this table) to the Data Definition Database (DDD DBID 015) which is used to house data definition information for SQL. The DDD is an extension of the Datadictionary database (DBID 002).
Prior to doing the CXX catalog, you should consider updating the other SQL attributes for the table and column entity occurrences where possible, otherwise the Datadictionary will assign default values for these SQL attributes.
Some Guidelines on SQL Attributes to Remember:
- Datadictionary generates default values for the SQLNAME attribute for the column (field) entity occurrences when SQL-INTENT is set to Y.
- Any SQLNAME for a table must be unique within the AUTHID and conform to the selected SQL mode (ANSI, FIPS, or DATACOM).
- Any SQLNAME for a column must be unique within the table and conform to the selected SQL mode.
- Any SQLNAME for a key (index) must be unique within the AUTHID and conform to the selected SQL mode.
- Any SQLNAME for an area must be unique within the CA-Datacom environment.
SQL Attributes for Database Model Entities
SQL-INTENT = Y
As stated above, is required.
AUTHID = aaaaaa
SQL AUTHID to uniquely identify this table. No default value is assigned, you must enter one. You should use one unique AUTHID per database to allow for multiple occurrences of the same SQL table name. ANSI and FIPS mode support a 1- to 18-character AUTHID while Datacom mode SQL supports up to a 32-character AUTHID. For consistency with ANSI SQL, the 1- to 18-character is suggested.
SQLNAME = sssssss
Name that is used to reference this table in SQL. No default value is assigned. Depending on site requirements, a unique SQLNAME should be assigned to the table. A simple conversion of the imbedded dashes "-" to underscores "_" may be considered. Table SQLNAMEs need to be assigned according to SQL mode naming standards. ANSI and FIPS mode support a 1- to 18-character SQLNAME while Datacom mode SQL supports up to a 32-character SQLNAME.
For fields (columns):
SQLNAME = sssssss
Name that is used to reference this column (field) in the table by SQL. A default value is created by using the field’s entity-name with all dashes (-) being replaced by (_). Depending on site requirements, it may or may not be beneficial to assign alternate SQL names (not the defaults) for fields. ANSI and FIPS mode support a 1- to 18-character SQLNAME while Datacom mode SQL supports up to a 32-character SQLNAME.
Other database model entities:
No SQL attributes are used.
SQLNAME is supported. It would only be used for issuing SQL DDL statements such as DROP INDEX.
SQLNAME is supported. It would only be used/needed when issuing the SQL DDL statement CREATE TABLE IN AREA area-sqlname. For normal production native systems, it may be advisable to "not" assign an SQLNAME for areas as it would prevent the inadvertent adding of tables to production data areas.
SQLNAME attribute is supported, but is not used in any CA-Datacom processing at this time. It is strictly a "comment".
SQL-SECURITY selects whether this database (and its tables) can be secured using SQL DSL (Grant/Revoke) commands. The selection of this option is only enforced if the appropriate Multi-User security options are selected.
Now the Catalog is Complete, You Can Try Your SQL Access
Changing the SQL-INTENT and other SQL attribute information do not require the data or index to be re-loaded. Once the catalog has completed, the tables and columns are accessible to SQL.
Dataquery provides an easy way to determine what tables or columns are accessible to SQL. After signing-on to Dataquery (DQRY), issue the command "SQL" at the top command line. This switches your DQRY session to SQL mode. Once there, you can use the "LIST TABLES" commands to list the SQL accessible tables.
To display the columns that are SQL accessible in a given table, use the "DRAW table-sqlname" to produce a sample SQL SELECT statement that contains all of the SQL accessible columns.
From here, it is just a simple edit of the SQL select statement to do your first SQL query against this newly defined table.
Other SQL Tools Available with the Base SQL Product
Datacom DBSQLPR utility is a powerful and simple means to test both SQL and dynamic SQL commands in batch. For customers who are not licensed for COBOL, this tool provides a way. Refer to CA Datacom IVPs for sample execution of DBSQLPR.
The CA-Datacom/DB Datadictionary Online facility (DDOL) also provides an interactive SQL mode. After signing on to DDOL, choose the option for ISQL to create and execute SQL statements from a DDOL panel.
Both the DQRY SQL facility and the DDOL ISQL facility provide the user with ways of storing SQL statements for later re-use. DBSQLPR program accepts SYSIN from various sources like PDS members which could also be saved for later re-use.