The FILENAME (or FILE) attribute names the Master File. On z/OS, the name of the Master File is its member name in the PDS allocated to DDNAME MASTER.

The Master File name consists of alphanumeric characters and must contain at least one letter. You should make the name representative of the table or view contents. It can have the same name as the RDBMS table if the table name complies with FOCUS naming conventions. It must have the same name as its corresponding Access File.

Master File names longer than eight characters are supported on z/OS. Because file and member names are limited to eight characters, longer Master File names are assigned eight-character names to be used when interacting with the operating system. The short name consists of three parts, a prefix consisting of the leftmost characters from the long name, followed by a left brace character ({), followed by an index number.

The length of the prefix depends on how many long names have a common set of leftmost characters.

• The first ten names that share six or more leftmost characters have a six-character prefix and a one-character index number, starting from zero.
• Starting with the eleventh long name that shares the same leftmost six characters, the prefix becomes five characters, and the index number becomes two characters, starting from 00.

This process can continue until the prefix is one character and the index number is six characters. If you delete one of these members from the PDS, the member name will be reused for the next long name.

The following table lists sample long names with the corresponding short names that will be assigned under z/OS.

EMPLOYEES_ACCOUNTING

EMPLOY{0

EMPLOYEES_DEVELOPMENT

EMPLOY{1

EMPLOYEES_DISTRIBUTION

EMPLOY{2

EMPLOYEES_FINANCE

EMPLOY{3

EMPLOYEES_INTERNATIONAL

EMPLOY{4

EMPLOYEES_MARKETING 

EMPLOY{5

EMPLOYEES_OPERATIONS

EMPLOY{6

EMPLOYEES_PERSONNEL 

EMPLOY{7

EMPLOYEES_PUBLICATIONS

EMPLOY{8

EMPLOYEES_RESEARCH 

EMPLOY{9

EMPLOYEES_SALES 

EMPLO{00

EMPLOYEES_SUPPORT 

EMPLO{01

To relate the short name to its corresponding long name, the first line of the Master and Access File must contain the following comment:

$ VIRT=complete_long_file_name 

where:

complete_long_file_name

Is the long name, up to 64 characters.

The SUFFIX attribute indicates which adapter is required for interpreting requests. Valid values are DB2, SQLDS, SQLDBC, SQLIDMS, and SQLORA.

The SEGNAME attribute identifies or links one table or view. The one- to eight-character SEGNAME value may be the same as the name chosen for FILENAME, the actual table name, or an arbitrary name. To reference a remote segment description, the SEGNAME value must be identical to the SEGNAME in the Master File that contains the full definition of the columns of the RDBMS table.

The corresponding Access File must contain a segment declaration with the same SEGNAME value as the Master File. The segment declaration in the Access File specifies the name of the RDBMS table. In this manner, the SEGNAME value serves as a link to the actual table name.

In a single table Master File, SEGTYPE always has the value S0 (or KL for a remote segment description). The RDBMS assumes responsibility for both physical storage of rows and the uniqueness of column values (if a unique index exists). SEGTYPE values for multi-table Master Files are discussed in Multi-Table Structures.

Include the CRFILE attribute in a segment declaration if:

• The actual description of the columns of the table is stored in another (remote) Master File. The CRFILE value must be the name of the Master File that contains the full definition of the columns of the RDBMS table. For a complete discussion of remote segment descriptions, see Additional Topics.
• You want to implement a conditional join to another RDBMS table. For information, see Multi-Table Structures. (You can also join tables using the JOIN command described in Advanced Reporting Techniques).

FIELD[NAME]=name, [ALIAS=]sqlcolumn, [{USAGE|FORMAT}=]display 
                  [,ACTUAL=]sqlfmt,  [, MISSING= {OFF|ON}] [,FIELDTYPE=R]  ,$

where:

name

Is a 1- to 66-character unqualified name. In requests, you can qualify a fieldname with its Master File and/or segment name. Additional Topics, contains a discussion of long fieldnames. For more information, consult the Describing Data manual.

sqlcolumn

Is the RDBMS column name, up to:

• 30 characters long for DB2.
• 30 characters for Oracle and Teradata.
• 32 characters for IDMS SQL.

display

Is the FOCUS display format for the field.

sqlfmt

Is the FOCUS definition of the RDBMS data type and length, in bytes, for the field. (See Data Type Support.)

OFF|ON

Indicates whether the field can contain null values. OFF, the default, does not permit null values.

FIELDTYPE=R

Indicates that the field is read-only. Any number of fields can have this attribute. A field with this attribute must represent a DB2 TIMESTAMP column.

Note: AUTODB2 does not add the FIELDTYPE=R attribute to the FIELD declaration in the generated Master File. You must edit the Master File to add this attribute.

A primary key for a table is the column or combination of columns whose values uniquely identify each row of the table. In the EMPINFO table, every employee is assigned a unique employee identification number. This identification number, and its corresponding employee, are represented by one (and only one) row of the table.

Note: The terms primary key and foreign key refer to columns that relate two tables. In this manual, they do not refer to primary and foreign keys defined in SQL CREATE TABLE statements (RDBMS referential integrity) unless explicitly stated.

The adapter uses information from both the Master File and the Access File to identify the primary key. In the Access File, the KEYS=n attribute specifies the number of key fields, n. In the Master File, the first n fields described immediately after the segment declaration constitute the primary key. Therefore, the order of field declarations in the Master File is significant.

To define the primary key in a Master File, describe its component fields first after the segment declaration. You can specify the remaining fields (those that do not participate in the primary key) in any order.

The KEYS attribute in the Access File completes the process of defining the primary key.

Typically, the primary key is supported by an SQL unique index to prevent the insertion of duplicate key values. The adapter itself does not require any index or IDMS calc key on columns comprising the primary key (although a unique index is certainly desirable for both data integrity and performance reasons).

Field names must be unique within a single-table Master File and can consist of up to 66 alphanumeric characters. Within the Master File, field names cannot include qualifiers. Column names are acceptable values if they meet the following naming conventions:

• A name can consist of letters, digits, and underscore characters. Special characters and embedded blanks are not advised.
• The name must contain at least one letter.

Since the field name displays as the default column title on reports, select a name that is representative of the data. In TABLE, GRAPH, and MODIFY requests, you can specify field names, aliases, or a unique truncation of either. Maintain does not support alias names or truncated names. In all requests, you can qualify a field name with its file name and/or segment name (see your FOCUS documentation).

The ALIAS value for each field must be the full SQL column name (the adapter uses it to generate SQL statements). The ALIAS name must be unique within the segment. DB2, Oracle and Teradata permit 30 characters, and IDMS permits 32 characters. The ALIAS name must comply with the same naming conventions described for field names.

The USAGE attribute indicates the display format of the field. An acceptable value must include the field type and length and may contain display options. The USAGE format is used for data display on reports and CRTFORMs. All standard FOCUS USAGE formats are available, except that Oracle does not support F format. For a complete list of USAGE formats appropriate for non-FOCUS data sources, see the Describing Data manual.

Note:

• The field type described by the USAGE format must be identical to that of the ACTUAL format. For example, a field with an alphanumeric USAGE field type must have an alphanumeric ACTUAL field type.
• Field type text (TX) varies for each RDBMS in terms of syntax and storage limitations. See Data Type Support for a complete discussion.
• Fields with decimal and floating-point data types must be described with the correct scale and precision. Scale (s) is the number of positions to the right of the decimal point. Precision (p) is the total length of the field. For FOCUS field formats, the field length includes the decimal point and negative sign. RDBMS data types exclude positions for the decimal point and negative sign.

  For example, a column defined as DECIMAL(5,2) in DB2 would have a USAGE attribute of P7.2 to allow for the decimal point and a possible negative sign.

The following tables describe how the adapter maps DB2 data types.

The following tables describe how the adapter maps Teradata data types.

The following tables describe how the adapter maps Oracle data types.

The following tables describe how the adapter maps IDMS/SQL data types.

The adapters support a comprehensive set of date and time formats.

ACTUAL = DATE, in conjunction with USAGE date formats (such as YDM, DMY, MDY), describes columns with DATE data types. FOCUS date formats containing any combination of the components year, month, and day can display dates stored with the RDBMS DATE data type. This feature makes it easier to manipulate dates, and the storage format is compatible with both FOCUS and non-FOCUS programs.

The FOCUS default date is '1900-12-31'. For information about setting the default value, see Additional Topics.

ACTUAL = DATE makes it possible to:

• Sort by date into date sequence, regardless of the USAGE display format.
• Define date components such as year, month, and day, and extract them from the date field.
• Perform date arithmetic and date comparisons without using special date-handling functions.
• Refer to dates in a natural way (JAN 1 1999, for example) regardless of formats.
• Automatically validate dates in transactions.
• Easily convert dates from one USAGE format to another (YMD to DMY, for example).

Note:

• In report requests, a DATE literal in a WHERE clause must conform to its USAGE format. For example, the field DATE_FLD, with a display format of YMD and an ACTUAL format of DATE, is specified as:

  WHERE DATE_FLD EQ '990224'

• When using MODIFY to update a field with ACTUAL = DATE, be aware that if the USAGE format lacks a day and/or month component (YY, for example), the adapter assigns a default day and/or month of '01' to the incoming transaction value (for example, 1999 becomes 19990101).
• RDBMS date fields are century aware. However, if you join an RDBMS table to a nonrelational data source, use a non-aware date literal, or use a MODIFY transaction file with non-aware dates, you may need to invoke FOCUS Year 2000 solutions such as the DEFCENT and YRTHRESH parameters. See your FOCUS documentation for details.
• Teradata users who choose to use native DBC/SQL in creating tables with dates must use the Teradata default date format (YY/MM/DD).
• Oracle users who choose to use native Oracle/SQL in creating tables with dates must use the Oracle default date format (DD MON YY).

ACTUAL formats for RDBMS TIME and TIMESTAMP columns are represented by FOCUS date-time data types, which use the format code H, as described in the ACTUAL format conversion charts:

• These data types are supported for Direct SQL Passthru, TABLE, MODIFY, Maintain, and SQL Translator requests. They are not supported for Static MODIFY requests.
• AUTODB and CREATE FILE support these data types.
• Date-time manipulation handled by the FOCUS date-time functions is not converted to SQL.

The ACTUAL = TX field type can be used to describe CHAR, VARCHAR, and LONG VARCHAR data types (LONG for Oracle). Text fields contain variable-length text, usually lengthy descriptions or explanations. They also support text wrapping in reports. The syntax is

FIELDNAME=name, ALIAS=sqlcolumn, USAGE=TXnn, ACTUAL=TX,$

where:

nn

Defines the length of an output line for display. (Maximum line length is 254 characters.)

You can specify text fields in report and MODIFY requests. SQL restrictions do not permit them to be:

• Indexed.
• Defined as primary key fields.
• Used in joins.

The following are additional limitations on the use of text fields in MODIFY requests:

• MODIFY can reference up to 95 text fields.
• You cannot use text fields in the Scratch Pad Area (HOLD).
• You cannot use text fields in a CRTFORM or with TYPE.
• You can include at most one text field per FIXFORM subcommand. The text field must be the last field in the FIXFORM statement.

In report requests (TABLE), you cannot use text fields:

• In an IF test or WHERE clause.
• As a BY clause or an ACROSS clause.

The following HOLD formats do not support text fields:

• DIF
• IFPS
• CALC
• LOTUS
• COM, COMMA, COMT, TABT

Text fields cannot take advantage of the standard display options, nor can you use them in a DEFINE. Trailing blanks are truncated for efficient storage.

Limit: FOCUS can handle text fields up to 32K in size. (Text fields greater than 32K are truncated at the 32K boundary.)

For Oracle, a RAW column may contain up to 32,767 bytes of text.

For DB2 on z/OS, the maximum physical size of a text value is also 32,767, but the entire row must fit into the database buffer pool. A buffer pool is a main storage area reserved to hold data pages or index pages. The buffer pool size is either 4K or 32K, although 4K is generally assumed. The size of the text field is limited by the total row length and the buffer pool size. Therefore, the actual space available may be less than the size of the buffer pool. Calculate the maximum size available for TX fields using the formula

TXsize= (BPsize - nontext_bytes)

where:

BPsize

Is approximately 4000 bytes for 4K buffers or 32,000 bytes for 32K buffers.

nontext_bytes

Is the total number of bytes for field types other than text (TX).

To load text field data, use a FIXFORM or PROMPT command, or use TED in a MODIFY request. For a complete discussion of text fields, consult the Describing Data and Maintaining Databases manuals.

The adapter supports RDBMS null data. In a table, a null value represents a missing or unknown value. It is not the same as a blank or zero. For example, you can use a column specification that allows nulls for a column that does not have to contain a value in every row (such as a raise amount in a table containing payroll data).

When a MODIFY or MAINTAIN procedure enters data into a table, it represents missing data in the table as RDBMS standard null data for columns that allow nulls. At retrieval, null data is translated into the FOCUS missing data display value. The default NODATA display value is the period (.).

The MISSING attribute in the Master File indicates whether the RDBMS column accepts null data. When a field declaration omits the MISSING attribute, it defaults to OFF. The syntax is

MISSING = {OFF|ON} ,$

where:

OFF

Is the default. In the RDBMS, columns should be created with the NOT NULL attribute (or NOT NULL WITH DEFAULT—DB2 only).

ON

FOCUS displays the NODATA value for null data. The column should be created without the NOT NULL attribute.

For null data support:

• The RDBMS table definition must describe a column that allows null data (without the clause NOT NULL).
• The Master File for that table must describe a column that allows null data as a field with the attribute MISSING=ON.

Note: If the column allows null data but the corresponding field in the Master File uses the attribute MISSING=OFF, null data appears as a zero or blank. In MODIFY or MAINTAIN, incoming null values for these fields are stored as zero or blank. This practice is not recommended since it can affect the results of SUM or COUNT aggregate operations, as well as allowing the (perhaps unintentional) storage of real values for fields that, in fact, should be null.

FOCUS and the RDBMS differ slightly in how they compare null field values:

• When two fields contain null values, FOCUS considers them equal. The RDBMS considers the result of any comparison between them to be UNDEFINED and does not return any rows.
• When one field contains a null value and another field does not, FOCUS considers them not equal. The RDBMS considers the result of any comparison between them to be undefined and returns the value FALSE for any such selection condition.

Consider two examples:

TABLE FILE X
PRINT *
WHERE (FIELD1 EQ FIELD2)
END

TABLE FILE X
PRINT *
WHERE (FIELD1 NE FIELD2)
END

Because of optimization enhancements introduced in recent releases, certain requests (outer joins, for example) no longer disable optimization as they did in prior releases. Therefore, the answer set returned may differ from that produced by prior versions of the adapter when FOCUS handled the request. See The Adapter Optimizer, for a discussion of optimization.

The optional field attributes DESCRIPTION, TITLE, DEFINE, and ACCEPT are supported for use with the adapter. Refer to your FOCUS documentation for information about these attributes.