CA-Datacom/DB Essentials

CA-Datacom/DB Databases

A CA-Datacom/DB database is a collection of CA-Datacom/DB tables, organized within certain CA-Datacom/DB areas and files. Each table consists of records that have one or more FIELD entity-occurrences.

Typically, a database is organized according to the types of data and how you want to use the data. You must understand and be familiar with your database's organization in order to retrieve and update information accurately and efficiently. And you must be familiar with the organization and contents of the database to create descriptor files for the SAS/ACCESS interface.

You need to know about several CA-Datacom/DB entity-types to use the SAS/ACCESS interface to CA-Datacom/DB. The most important entity-types are databases, records, and fields. Fields contain the actual data values, which are either character or numeric type.

You can define a field as a simple field or a compound field. Fields can also become keys. Two special keys, the Native Key and the Master Key, are required for each table. CA-Datacom/DB generates an index for each key field. Knowing about the Native Key and the indexes can help you minimize CA-Datacom/DB processing time for your view descriptors. In addition, fields can repeat. For more information about fields, see FIELD Entity-type.

The following sections describe the various CA-Datacom/DB entity-types that pertain to the SAS/ACCESS interface to CA-Datacom/DB.

DATABASE Entity-type

Each DATABASE entity-occurrence in the CA-DATADICTIONARY database has a unique name, from 1 to 32 characters long. A database also has a status (TEST or PROD) and version associated with it.

RECORD Entity-type

A table consists of some number of records, each having one or more fields. The table name is the name of a RECORD entity-occurrence, up to 32 characters long. Data records in the table are ordered by the values for an assigned field called the Native Key. CA-Datacom/DB permits up to 240 tables in a database. The tables can be spread across one or more CA-Datacom/DB areas. When you define a record for a table, you must define at least one field, one key, and one element for that record.

To create descriptor files for the SAS/ACCESS interface, you must know the name of the RECORD entity-occurrence (table) and the user ID and optional password for the CA-DATADICTIONARY. An access descriptor and its associated view descriptors pertain to only one table.

A Sample CA-Datacom/DB Table illustrates four fields from the table CUSTOMERS. Field names are shown at the top of the columns. Each row represents the values in a record. The first field, CUSTOMER, is the Native Key in this table, which causes the records to be maintained in order by customer number.

A Sample CA-Datacom/DB Table

        CUSTOMER     CITY                STATE     COUNTRY
        14324742     San Jose            CA        USA
        14569877     Memphis             TN        USA
        14898029     Rockville           MD        USA
        24589689     Belgrade                      Yugoslavia
        26422096     La Rochelle                   France
        38763919     Buenos Aires                  Argentina
        46783280     Singapore                     Singapore

FIELD Entity-type

Each FIELD entity-occurrence has a name (of up to 32 characters) and specific attributes, such as the data type. For more information about data types, see Data Types in CA-Datacom/DB Fields.

You can define several kinds of fields, as described briefly here.

A simple field is a single field.
A compound field consists of two or more simple or compound fields. The fields can be of different data types and lengths; they can also repeat or be within repeating fields. The fields making up a compound field must be contiguous.
A key field enables you to quickly select and sequence data records. A key field can be any combination of simple and compound fields, up to 180 characters. The fields in a key do not have to be contiguous.
The Native Key is the field that determines the order of the records in a CA-Datacom/DB table. Each table must have one Native Key. It can be the same as the Master Key.
The Master Key enables you to prevent duplicate values in a key field and to prevent changing values in that key. Each record must have one Master Key. It can be the same as the Native Key.
A repeating field is a simple field or a compound field that can occur more than once. Repeating fields can also be nested within other repeating fields.
An element is a unit of transfer between application programs and CA-Datacom/DB. It consists of one or more contiguous fields. An element should contain only those fields that an application program uses at execution time. When defining an element, group together fields that are frequently accessed together in applications.

Data Types in CA-Datacom/DB Fields

A CA-Datacom/DB field can be any one of a variety of data types; they are mostly type character or type numeric, as discussed below.

When you create a view descriptor, the ACCESS procedure assigns SAS formats, informats, and so on, in addition to SAS column names from the CA-Datacom/DB field names. See ACCESS Procedure Data Conversions for the default SAS column formats and informats for each CA-Datacom/DB data type. You can change the default formats and informats.

Numeric Data Types

Here are some of the numeric types available for CA-Datacom/DB fields:

B	binary
D	packed decimal
E	extended floating-point
L	long floating-point
N	numeric (zoned decimal)
S	short floating-point
2	halfword binary (aligned)
4	fullword binary (aligned)
8	doubleword binary (aligned).

Character Data Types

Here are some of the character types available for CA-Datacom/DB fields:

C	character
G	graphics data
H	hexadecimal character
K	kanji (same as type Y)
T	PL/I bit representation
Y	double-byte character set (DBCS)
Z	mixed DBCS and single byte.

Date Types

CA-Datacom/DB supports the CA-Datacom/DB SQL types SQL-DATE, SQL-TIME, and SQL-STMP as binary data.

SQL-DATE: specifies date values in the format CCYYMMDD, where CC=century, YY=year, MM=month, and DD=day.
SQL-TIME: specifies the time values in the format HHMMSS, where HH=hours, MM=minutes, and SS=seconds.
SQL-STMP: specifies a date and a time and adds microseconds in the format CCYYMMDDHHMMSSNNNNNN.

See ACCESS Procedure Data Conversions for information about the default formats that the ACCESS procedure assigns to the DBMS data types. To specify a different representation, you can change the default SAS format in your descriptor files.

Missing Values

Missing values in a CA-Datacom/DB table are referred to as nil values or simply nils. Nil values for both character and numeric type data are blanks, that is, HEX (40)s. All fields of a key must contain blanks for a value to be nil. There are no valid packed decimal or zoned decimal nil values. You can specify binary zeros for nils (see System Options for the CA-Datacom/DB Interface).

In SAS, nils are referred to as missing values. CA-Datacom/DB and SAS handle missing values differently, but the interface view engine takes care of the differences. See Missing Values (Nils) in CA-Datacom/DB Tables for a discussion of the differences.

Top of Page