How to make an existing Datacom Table accessible to SQL (SQL-able)
Article ID: 13738
Updated On:
Products
Issue/Introduction
This article defines the steps necessary to make an existing Datacom/DB table accessible using the Datacom/SQL Option.
Environment
Release: 15.1
Resolution
By default, tables created by the native 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 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 or 1500 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.
Sample DDUPDATE JCL to Make a Table SQL Accessible
Note that to run this job first close the base in the MUF with DBUTLTY COMM CLOSE,DBID=nnn and close any online CICS URTs with DBOC CLOSE=nnn.
//UPDATE EXEC PGM=DDUPDATE,REGION=4M
//STEPLIB DD DISP=SHR,DSN=<datacom.hlq>.CUSLIB
// DD DISP=SHR,DSN=<datacom.hlq>.CABDLOAD << for Datacom/AD CAAXLOAD)
// DD DISP=SHR,DSN=<ipc.hlq>.CAVQLOAD << Not needed for Datacom/AD)
//SYSOUT DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSPRINT DD SYSOUT=*
//SNAPER DD SYSOUT=*
//SYSIN DD *
-USR DATACOM-INSTALL,NEWUSER
-UPD DATABASE,<db_name>(PROD)
1000 RESTORE,T999
-END
-UPD TABLE,<table_name>(T999)
1500 SQL-INTENT,Y
1500 AUTHID,SYSUSR
1500 SQLNAME,<SQL_table_name>
-END
-CPY DATABASE,<db_name>(T999),PROD
-END
-UPD DATABASE,<db_name>(PROD)
1000 CATALOG
1000 ENABLE
-END
-DEL DATABASE,<db_name>(T999)
-END
/*
<SQL_table_name> - SQL doesn't support the dash "-" character so in the table SQLNAME these have to be replaced by an underscore "_".
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 Datacom environment.
SQL Attributes for Database Model Entities
For tables:
|
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:
|
Elements: |
No SQL attributes are used. |
|
|
|
|
Keys (Indexes): |
SQLNAME is supported. It would only be used for issuing SQL DDL statements such as DROP INDEX. |
|
|
|
|
Areas: |
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. |
|
|
|
|
Database: |
SQLNAME attribute is supported, but is not used in any 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 Datacom IVPs for sample execution of DBSQLPR.
The 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.
Additional Information
Feedback
