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.

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.

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.

        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

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 types of fields, as described briefly here.

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.

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

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

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

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 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 in order 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.