Master Files

A table is an RDBMS object consisting of rows and columns. A Master File represents a table as a single segment.

In this section: File Attributes in the Master File Segment Attributes in the Master File Field Attributes in the Master File ACTUAL Format Conversion Charts Optional Field Attributes

A Master File contains three types of declarations:

• The file declaration indicates that the data is stored in a DB2, DB2 for VM, Teradata, Oracle, or CA-IDMS table or view.
• The segment declaration identifies a table.

Field declarations describe the columns of the table.

Each declaration must begin on a separate line. A declaration consists of keyword-value pairs (called attributes) separated by commas. A declaration can span as many lines as necessary, as long as no single keyword-value pair spans two lines. Certain attributes are required; the rest are optional (see Optional Field Attributes).

Do not use system or SQL reserved words as names for files, segments, fields, or aliases. Specifying a reserved word generates the SQL syntax errors -104, -105, or -106.

File Attributes in the Master File

Each Master File begins with a file declaration that names the file and describes the type of data source, in this case an RDBMS table or view. The file declaration has two attributes, FILENAME and SUFFIX. The syntax is FILE[NAME]=name, SUFFIX=suffix [,$]

How to: Implement a Long Master and Access File Name in OS/390 Example: Long and Short Master File Names Reference: FILENAME SUFFIX

where:

name

Is a one- to eight-character file name. For documentation purposes, it is recommended that the Master File name be used as the FILENAME attribute.

Note:

suffix

Identifies the data adapter needed for accessing the table. Valid values are:

SQLDS

Is the SUFFIX value for the DB2 and DB2 for VM Data Adapters.

SQLDBC

Is the SUFFIX value for the Teradata Data Adapter.

SQLIDMS

Is the SUFFIX value for the IDMS/SQL Data Adapter.

SQLORA

Is the SUFFIX value for the Oracle Data Adapter.

Reference: FILENAME

The FILENAME (or FILE) attribute names the Master File. On MVS, the name of the Master File is its membername in the PDS allocated to DDNAME MASTER. On VM, the Master File name is the filename whose filetype is MASTER.

The Master File name consists 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.

On VM Master File names are limited to eight characters. Master File names longer than eight characters are supported on MVS. 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.

Example: Long and Short Master File Names

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

Long Name	Short Name
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

Syntax: How to Implement a Long Master and Access File Name in OS/390

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.

Reference: SUFFIX

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

Segment Attributes in the Master File

Each table described in a Master File requires a segment declaration that consists of at least two attributes, SEGNAME and SEGTYPE.

Reference: SEGNAME in the Master File SEGTYPE CRFILE

If several Master Files (used only with TABLE requests) include the same table, you can avoid repeating the same description multiple times. Describe the table in one of the Master Files, and use the CRFILE attribute in the other Master Files to access the existing description. For a full explanation of remote segment descriptions, see Additional Topics.

The syntax for a segment declaration is

SEGNAME=segname, SEGTYPE={S0|KL} [,CRFILE=crfile] [,$]

where:

segname

Is a one- to eight-character name that identifies the segment. If this segment references a remote segment description, segname must be identical to the SEGNAME from the Master File that contains the full definition of the RDBMS table's columns (see Additional Topics).

S0

S zero indicates to the data adapter that the RDBMS handles the storage order of the data.

KL

References a remote segment description (see Additional Topics).

crfile

Is required only to reference a remote segment description. Indicates the name of the remote Master File that contains the full definition of the RDBMS table's columns (see Additional Topics).

Reference: SEGNAME in the Master File

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 RDBMS table's columns.

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.

Reference: SEGTYPE

In a single table Master File, SEGTYPE always has a value of 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.

Reference: CRFILE

Include the CRFILE attribute in a segment declaration if:

• The actual description of the table's columns 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 RDBMS table's columns. For a complete discussion of remote segment descriptions, see Additional Topics.
• You want to 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 Attributes in the Master File

Each table consists of one or more columns. In the Master File, you define each column as a field with the primary attributes FIELDNAME, ALIAS, USAGE, ACTUAL, and MISSING. The Describing Data manual explains additional attributes.

How to: Describe a Column in a Master File Reference: The Primary Key FIELDNAME ALIAS USAGE/FORMAT

You can get values for these attributes from the DB2 or DB2 for VM system catalog table SYSCOLUMNS, from the Oracle SYSTEM.COLUMNS data dictionary view, or from the IDMS SQL schema definition or standard IDMS dictionary reports. For a sample request for DB2, refer to Additional Topics.

Syntax: How to Describe a Column in a Master File

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

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. Although the qualifiers and qualification characters do not appear in the Master File, they count toward the 66-character maximum. Additional Topics, contains a discussion of long fieldnames. For more information, consult the Describing Data manual.

sqlcolumn

Is the RDBMS column name, up to:

• 18 characters long for DB2 and DB2 for VM.
• 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 ACTUAL Format Conversion Chart for DB2 and DB2 for VM.)

OFF|ON

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

Reference: The Primary Key

A table's primary key 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 data 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 data 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).

Reference: FIELDNAME

Field names must be unique within a single-table Master File and can consist of up to 66 alphanumeric characters (including any filename and segname qualifiers and qualification characters you may later prefix to them in requests). 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).

Reference: ALIAS

The ALIAS value for each field must be the full SQL column name (the data adapter uses it to generate SQL statements). The ALIAS name must be unique within the segment. DB2 and DB2 for VM permit a maximum of 18 alphanumeric characters, 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.

Reference: USAGE/FORMAT

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 ACTUAL Format Conversion Chart for DB2 and DB2 for VM 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.

ACTUAL Format Conversion Charts

The ACTUAL attribute indicates the Master File representation of RDBMS data types. The following conversion chart lists common data types and their Master File representations. Some aspects of ACTUAL = TX or ACTUAL = DATE, TIME, or TIMESTAMP are unique for the data adapters; these formats are discussed after the conversion charts.

Reference: ACTUAL Format Conversion Chart for DB2 and DB2 for VM ACTUAL Format Conversion Chart for Teradata ACTUAL Format Conversion Chart for IDMS/SQL ACTUAL Format Conversion Chart for Oracle ACTUAL = DATE, TIME, and TIMESTAMP ACTUAL = TX MISSING Comparing Fields With Null Values

Reference: ACTUAL Format Conversion Chart for DB2 and DB2 for VM

DB2 Data Type	ACTUAL Format	Description
CHAR(n)	An or TX	Fixed-length alphanumeric, not exceeding 4095 characters. You can use TX if you want text wrapping in reports.
VARCHAR(n)	An or TX	Variable-length character string where n £ 254 characters. Trailing blanks are truncated for efficient storage. You can use TX if you want text wrapping in reports.
VARCHAR(n)	An or TX	LONG VARCHAR where 254 < n < 4K characters; variable-length text. Trailing blanks are truncated for efficient storage. You can use TX if you want text wrapping in reports.
SMALLINT	I4	2-byte binary integer, ranges from -32767 to +32768. Note: To create SMALLINT NOT NULL columns with the CREATE FILE command or with the HOLD FORMAT DB2 or HOLD FORMAT SQL facilities, use an ACTUAL attribute of I2 in the CREATE FILE statement, but subsequently change it to I4. For all other purposes, use an ACTUAL attribute of I4.
INTEGER	I4	4-byte binary integer, ranges from -2147483648 to +2147483647.
DECIMAL(p,s)	P((p+1)/2	Packed decimal with format p.s where p excludes the decimal point and s is the scale of the stored value. p must be less than or equal to 31. The quantity (p+1)/2 is rounded down to the nearest integer number.
	P8	Required if p£15 and the column allows nulls (MISSING=ON).
REAL	F4	Single-precision floating-point number (4-byte).
FLOAT	D8	Double-precision floating-point number (8-byte).
DATE	DATE	Standard SQL storage for dates. For information about the default date ('1900-12-31'), see Additional Topics. You can use this format with USAGE values containing any combination of year, month, and day.
TIME	HHIS	Standard SQL storage for time.
TIMESTAMP	HYYMDm	Standard SQL storage for timestamps-date and time. The TIMESTAMP value may be updated or inserted if the input string is in the correct DB2 TIMESTAMP format. Consult the IBM DB2 or DB2 for VM SQL Reference for the correct input format.
GRAPHIC	Kn	DBCS Kanji character set. Fixed-length string of n 16-bit characters where 0 < n £ 127. The appropriate corresponding USAGE format is A(2n+2).
VARGRAPHIC	Kn	DBCS Kanji character set. Varying-length string of n 16-bit characters where 0 < n £ 127. The appropriate corresponding USAGE format is A(2n+2). Note: LONG VARGRAPHIC (or VARGRAPHIC (n) where n >127) is not supported.

Reference: ACTUAL Format Conversion Chart for Teradata

Teradata Data Type	ACTUAL Format	Description
CHAR(n)	An or TX	Fixed-length alphanumeric, not exceeding 4095 characters. You can use TX if you want text wrapping in reports.
VARCHAR(n)	An or TX	Variable-length character string where n is less than or equal to 256 characters. You can use TX if you want text wrapping in reports.
VARCHAR(n)	An or TX	Variable-length character string where n is greater than 256 and less than 4096 characters. You can use TX if you want text wrapping in reports.
BYTEINT	I2	1-byte binary integer, ranges from -128 to +127.
	I4	Required if column allows nulls (MISSING=ON).
SMALLINT	I2	2-byte binary integer, ranges from -32768 to +32767.
	I4	Required if column allows nulls (MISSING=ON).
INTEGER	I4	4-byte binary integer, ranges from -2.147G to +2.147G, approximately.
DECIMAL(p,s)	P((p+1)/2)	Packed decimal with format p.s where p excludes decimal point and s is the scale of stored value. P may not be greater than 15.
	P8	Required if column allows nulls (MISSING=ON).
FLOAT	D8	Double-precision floating-point number (8-byte), ranges from 2.226E-308 to 1.797E+308.
DATE	DATE	Standard Teradata storage for dates.
BYTE	An or TX	Fixed-length binary string where n is less than 4096 characters.
VARBYTE	An or TX	Variable-length binary string where n is less than 4096 characters.
GRAPHIC	Kn	DBCS Kanji character set. Fixed-length string of n 16-bit characters where 0 < n £ 127. The appropriate corresponding USAGE format is A(2n+2).
VARGRAPHIC	Kn	DBCS Kanji character set. Varying-length string of n 16-bit characters where 0 < n £ 127. The appropriate corresponding USAGE format is A(2n+2). Note: LONG VARGRAPHIC (or VARGRAPHIC (n) where n >127) is not supported.
TIMESTAMP	A26	TIMESTAMP is treated as a record composed of six fields. The record is not treated as a numeric value, though it is composed of numeric fields.

Reference: ACTUAL Format Conversion Chart for IDMS/SQL

IDMS Data Type	ACTUAL Format	Description
CHAR(n)	An or TX	Fixed-length alphanumeric, not exceeding 4095 characters. You can use TX if you want text wrapping in reports.
VARCHAR(n)	An or TX	Variable-length character string where n £ 254 characters. Trailing blanks are truncated for efficient storage. You can use TX if you want text wrapping in reports.
VARCHAR(n)	An or TX	LONG VARCHAR where 254 < n < 4K characters; variable-length text. Trailing blanks are truncated for efficient storage. You can use TX if you want text wrapping in reports.
SMALLINT	I4	2-byte binary integer, ranges from -32767 to +32768. Note: To create SMALLINT NOT NULL columns with the CREATE FILE command or with the HOLD FORMAT SQLIDMS facility, use an ACTUAL attribute of I2 in the CREATE FILE statement, but subsequently change it to I4. For all other purposes, use an ACTUAL attribute of I4. If the field has the MISSING attribute, the ACTUAL attribute must be I4.
INTEGER	I4	4-byte binary integer, ranges from -2147483648 to +2147483647.
DECIMAL(p,s)	P((p+1)/2)	Packed decimal with format p.s where p excludes the decimal point and s is the scale of the stored value. p must be less than or equal to 31.
	P8	Required if p£15 and the column allows nulls (MISSING=ON).
REAL	F4	Single-precision floating-point number (4-byte).
FLOAT	D8	Double-precision floating-point number (8-byte).
DATE	DATE	Standard SQL storage for dates. You can use this format with USAGE values containing any combination of year, month, and day.
GRAPHIC	Kn	DBCS Kanji character set. Fixed-length string of n 16-bit characters where 0 < n £ 127. The appropriate corresponding USAGE format is A(2n+2).
VARGRAPHIC	Kn	DBCS Kanji character set. Varying-length string of n 16-bit characters where 0 < n £ 127. The appropriate corresponding USAGE format is A(2n+2). Note: LONG VARGRAPHIC (or VARGRAPHIC(n) where n >127) is not supported.

Reference: ACTUAL Format Conversion Chart for Oracle

Oracle Data Type	Actual Format	Description
CHAR(n)	An or TX	Fixed length alphanumeric where n may not exceed 2000 characters. You can use TX if you want text wrapping in reports.
NUMBER	P16 (USAGE=P32)	Number column with space for 40 digits.
NUMBER(n)	P(n+1)/2 (USAGE= P(n+1))	Number column of specified size n. FOCUS limit is 31. First, n is reduced to 31, if it is higher.
NUMBER(n,m)	P(n+1)/2 (USAGE= P((n+1).m))	Number column of specified size n with m digits following the decimal point. First, n and m are reduced to 31, if they are higher.
DATE	HYYMDS	Standard Oracle storage for dates.
DATE	DATE	Must conform to default format 'DD MON YY' (for example, '24 APR 57')
LONG	An or TX	Character data up to 65,535 characters. FOCUS limit is 4095.
RAW(n)	A(2n) or TX	Variable length binary string where n<4K
LONGRAW		No FOCUS equivalent

Certain DB2 for VM data types are acceptable keywords for use in creating Oracle tables. These data types are SMALLINT, INTEGER, DECIMAL(n,m), and FLOAT. You may notice that FOCUS uses these data types when you create a new Oracle table using the FOCUS CREATE FILE command. Oracle responds by creating full size NUMERIC or NUMERIC(n,m) columns for each of these data types. DB2 for VM data types are used by certain data adapter functions. There is no need for you to be familiar with them or to use them when creating your own tables.

Reference: ACTUAL = DATE, TIME, and TIMESTAMP

The data 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 datatypes. FOCUS date formats containing any combination of the components year, month, and day can display dates stored with the RDBMS DATE datatype. 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 data 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 non-relational 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 TABLE or MODIFY requests.
• AUTODB2, AUTOSQL, and CREATE FILE support these data types.
• Date-time manipulation handled by the FOCUS date-time functions is not converted to SQL.

Reference: ACTUAL = TX

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.
• In a DEFINE or COMPUTE.

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 4K in size. (Text fields greater than 4K are truncated at the 4K boundary.)

For DB2 for VM, a LONG VARCHAR column may contain up to 32,767 bytes of text.

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

For DB2 on MVS, 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.

For DB2 on MVS, 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.

Reference: MISSING

The data 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.

Reference: Comparing Fields With Null Values

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

The following table summarizes the results produced by FOCUS and the RDBMS:

Condition	Field1 Value	Field2 Value	FOCUS Result	RDBMS Result
EQ	Null	Null	True	False
	Null	Not Null	False	False
NE	Null	Null	False	False
	Null	Not Null	True	False

In most cases, the data adapter translates the FOCUS WHERE clause to an SQL WHERE predicate and passes it to the RDBMS for processing. (This translation process is called data adapter optimization.) If the data adapter does not translate the FOCUS WHERE test to an SQL WHERE predicate, FOCUS applies the selection test and may produce a different answer set.

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 data adapter when FOCUS handled the request. See The Data Adapter Optimizer, for a discussion of optimization.

Optional Field Attributes

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

Note: The data adapter does not support the use of FIND in the ACCEPT attribute, nor does it support the attributes GROUP and FIELDTYPE.