SQL Column Report from Datadictionary for Legacy Data Access

Document ID : KB000027831
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

CA Datacom Database Datadictionary provides a report format that allows you to easily determine whether a particular column is accessible directly through native SQL, or if direct access would require the use of the Datacom View feature. The SQL Column Report is included as a standard report format that can be generated in either DDUTILTY or DDUPDATE.

Instructions:

The report can be generated in any of four formats. The format is determined by two options provided on the -RPT Header Transaction. The options allow the user to select:

  1. Whether the report uses the Datadictionary Occurrence Name or the SQL Name in the body of the report.
  2. Whether the report is generated in alphabetic sequence by the name option selected or in physical sequence (displacement in the physical table), which is referred to as "Copybook Order".

The four formats are shown in the following illustrations:

  1. Copybook order, Datadictionary Occurrence Name
    -RPT COLUMN,TABLE,CBLTEST-TBL(T010),COPYBOOK,OCCURRENCE

  2. Copybook order, SQL Name
    -RPT COLUMN,TABLE,CBLTEST-TBL(T010),COPYBOOK,SQL

  3. Alphabetic order, Datadictionary Occurrence Name
    -RPT COLUMN,TABLE,CBLTEST-TBL(T010),NAME,OCCURRENCE

  4. Alphabetic order, SQL Name
    -RPT COLUMN,TABLE,CBLTEST-TBL(PROD),NAME,SQL

The key to these reports is the report column headed "SQL ACCESS." This column is further segmented under the headings "STD" and "DATACOM." The column labeled "STD." reports the accessibility of the column using standard SQL access. The column labeled "DATACOM" reports the same information if SQL access is done using Datacom Views.

Under each of these headings you will find either "YES" or "NO" for each column listed. "YES" indicates the column can be accessed in the given manner. "NO" indicates the column is not accessible using the method indicated by the header. In addition, when "NO" is displayed, additional information is provided to indicate the reason the column is not accessible. This reason is given by means of a three-character code in parenthesis following the "NO". The reason codes are:

CMP:The column is compound and it is not a repeating group.
FIL:The column is filler.
RDF:The column is a redefinition of another or is a child of another column that is a redefinition.
RPC:The column is the child of a repeating column.
RPT:The column is a repeating column.
VAL:The column is a value definition.

You will notice from the examples above, that the use of Datacom Views makes most columns accessible that were not accessible using standard SQL. The only reason codes you should see for Datacom View access are "FIL", "RPC" and "VAL". The "RPC" reason code indicates that the column participates in a multi-dimensional array which is not supported by Datacom View access.

Additional Information:

The transactions used to request these reports and a detail description of the report content is provided in section "SQL Column Report of the CA Datacom Database Datadictionary Batch Reference Guide at

https://support.ca.com/cadocs/7/CA%20Datacom%20V15%200%20Customer%20Access%20Only-ENU/Bookshelf_Files/PDF/DatacomDD15--Batch_Ref_ENU.pdf