Getting Started › SQL Tables › Using Existing Tables

Using Existing Tables

Existing tables which were not created by the SQL Manager can be accessed by SQL if they adhere to the following rules:

1. The table definition must exist in the CA Datacom Datadictionary.
2. The value in the table's SQLNAME attribute must be a valid SQL table name. The valid character set includes A-Z, 0-9, $, #, @, and _ (underscore). The area and database in which the table resides must also have valid SQLNAMEs.
3. The value in the table's AUTHID attribute must be a valid authorization ID.
4. Field names must have valid SQL column names specified for the SQLNAME attribute.
5. If the table includes group fields and you are not using DATACOM VIEWs, only the lowest-level, simple fields may be accessed using SQL. For information about DATACOM VIEWs, see DATACOM VIEWs.
6. Data types other than the following types are treated as character fields by SQL. See SQL Data Type Support for All CA Datacom/DB Tables for more information.
   • C (character)
   • B 2 (small integer, 2-byte binary, signed)
   • B 4 (long integer, 4-byte binary, signed)
   • L (float, signed)
   • D (packed decimal, signed; decimal, unsigned; decimal, positive)
   • N (zoned decimal, signed; numeric, unsigned; numeric, positive)
   • DATE (B 4 (binary, length=4) SEMANTIC-TYPE=SQL-DATE)
   • TIME (B 3 (binary, length=3) SEMANTIC-TYPE=SQL-TIME)
   • TIMESTAMP (B 10 (binary length=10) SEMANTIC-TYPE=SQL-STMP)

   Note: DATE, TIME, and TIMESTAMP are stored as binary data but are automatically converted to character strings in SQL. See Character String Literals for more information. See the CA Datacom/DB Database and System Administration Guide for an explanation of how DATE, TIME, and TIMESTAMP data types are stored in CA Datacom/DB. If you are accessing an SQL DATE, TIME, or TIMESTAMP with a non-SQL command, you must perform the conversion from the internal format yourself.

   See Data Types for more information about SQL data types.

7. If the repeat factor of a group field is greater than one (for example, let the repeat factor be represented by the letter R), the entire field (all R elements) is treated as one-character column by SQL. Arrays on group fields are not supported. If the table includes arrays and you are not using DATACOM VIEWs, the entire array is treated as one-character field by SQL.

   Note: To process this as group field using a DATACOM VIEW (see DATACOM VIEWs), you would need to create a "redefine" that either defines each subfield R times or redefines the group field as a CHAR column with a repeat factor of R. In the latter case, the SQL SUBSTR (substring) and CAST WITHOUT CONVERSION functions can be used to extract the desired sub-fields. Parallel arrays of simple repeating fields are supported.

8. No variable-length fields can exist in the table definition.
9. Redefined fields can exist in the table definition, but SQL ignores the REDEFINES attribute.
10. The value of the table's SQL-INTENT attribute must be set to Y to mark that the table is to be accessed by SQL. The table must successfully pass the verification for SQL access during the catalog operation before it can be accessed by SQL.
11. After you complete any modifications to make the tables SQL accessible, the table must be copied to PROD status and cataloged to the CA Datacom/DB Directory (CXX).

The SQL CREATE TABLE statement cannot create a remote, partitioned or replicated table. For remote tables, SQL access requires a complete duplicate definition of a remote table in CA Datacom Datadictionary, and version control enforcement is not available (version control enforcement helps ensure that remote definitions are synchronized with the local active definition).