Master Files

In this section:

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

A Master File contains three types of declarations.

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.


Top of page

x
File Attributes in the Master File

How to:

Reference:

FILE[NAME]=name, SUFFIX=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: Master File names longer than eight characters are supported on z/OS, as described in FILENAME.

suffix

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

DB2

Is the SUFFIX value for the Adapter for DB2.

Note: The suffix value SQLDS is also available for backward compatibility.

SQLDBC

Is the SUFFIX value for the Adapter for Teradata.

SQLIDMS

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

SQLORA

Is the SUFFIX value for the Adapter for Oracle.



x
Reference: FILENAME

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.

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 z/OS.

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


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

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.



x
Reference: SUFFIX

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


Top of page

x
Segment Attributes in the Master File

Reference:

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

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 columns of the RDBMS table (see Additional Topics).

S0

S zero indicates to the 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 columns of the RDBMS table (see Additional Topics).



x
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 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.



x
Reference: SEGTYPE

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.



x
Reference: CRFILE

Include the CRFILE attribute in a segment declaration if:


Top of page

x
Field Attributes in the Master File

How to:

Reference:

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.

You can get values for these attributes from the DB2 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.



x
Syntax: How to Describe a Column in a Master File
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.



x
Reference: The Primary Key

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).



x
Reference: FIELDNAME

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:

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).



x
Reference: ALIAS

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.



x
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:


Top of page

x
Data Type Support

In this section:

The ACTUAL attribute indicates the Master File representation of RDBMS data types.



x
DB2 Data Type Support
x

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

 

DB2 Data Type

FOCUS Data Type

 

Remarks

USAGE

ACTUAL

Date-Time Data Types

DATE

YYMD

DATE

 

TIME

HHIS

HHIS

 

TIMESTAMP

HYYMDm

HYYMDm

 

Numeric Data Types

SMALLINT

I6

I4

 

INTEGER

I11

I4

Maximum precision is 11.

BIGINT

P20

P10

Available on UNIX and Windows only.

DECIMAL (p,s)

P6

P8

p is an integer between 1 and 31.
s is an integer between 0 and p.

REAL

F9.2

F4

Maximum precision is 9.

FLOAT

D20.2

D8

Maximum precision is 20.

LOB Data Types

BLOB

BLOB

BLOB

Up to 2 gigabytes.

CLOB

TX50

TX

Up to 2 gigabytes.

Other Data Types

CHAR (n)

An

An

n is the number of bytes, and is an integer between 1 and 254.

LONG VARCHAR (n) in (1...32700)

 

 

Not supported

GRAPHIC (n)

Am

Kn

m = (n * 2) + 2

m is the number of bytes, and n is the number of characters.

VARGRAPHIC (n)

Am

Kn

m = (n * 2) + 2

VARGRAPHIC is assumed to be GRAPHIC until n = 127 for non-Unicode.

The maximum length of m is 256.

LONG VARGRAPHIC

 

 

Not supported

DATALINK

 

 

Not supported

XML

TX50

TX

Supported with DB2 Version 9 on UNIX, Windows, and z/OS; not supported on IBM i.

The following table describes how the adapter maps non-Unicode Character data types.

 

DB2 Data Type

 

Remarks

LONGCHAR ALPHA

LONGCHAR TEXT

USAGE

ACTUAL

USAGE

ACTUAL

VARCHAR (n)

n is an integer between 1 and 256.

AnV

AnV

AnV

AnV

n is an integer between 257and 32768.

AnV

AnV

TX50

TX





x
Teradata Data Type Support

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

 

Teradata Data Type

FOCUS Data Type

 

Remarks

USAGE

ACTUAL

Date-Time Data Types

DATE

YYMD

A8

 

TIME

HHISsm

HHISsm

The operation with different Teradata TIME formats depends on DateTimeFormat setting in the data source part of the $HOME/.odbc.ini file. The adapter requires DateTimeFormat=IAI.

Neither the ODBC nor the CLI interface supports the integer format of TIME (I). Only the ANSI format of TIME (AT) is supported.

TIMESTAMP

HYYMdm

HYYMdm

 

INTERVAL

An

An

INTERVAL identifies a period of time in different ranges (YEAR, MONTH, DAY, HOUR, MIN, SEC).

The Teradata external (client) representation of INTERVAL is always CHAR (n) where n = p + x. Precision p from 1 to 4 and x from 1 to 11 depend on range.

Numeric Data Types

SMALLINT

I6

I4

A 2-byte signed binary integer.

Range: -215 to 215 - 1.

INTEGER

I11

I4

A 4-byte binary integer

Range: -2.147G to +2.147G.

BYTEINT

I6

I4

A 1-byte signed binary integer

Range: -128 to +127.

BIGINT

P20

P10

Teradata representation of a signed, binary integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

DECIMAL(n,m)

P31.31

P16

n, the precision, is an integer between 1 and 31.

m, the scale, is an integer between 0 and n.

Also referred to as NUMERIC.

FLOAT

D20.2

D8

8 bytes.

Range: -2 * 10307 to 2 * 10308.

Same as REAL or DOUBLE PRECISION.

LOB Data Types

GRAPHIC

 

 

Not supported.

VARGRAPHIC

 

 

Not supported.

LONG GRAPHIC

 

 

Not supported.

VARBYTE

 

 

Not supported.

BLOB

 

 

Not supported.

The following table lists how the adapter maps non-Unicode Character data types.

 

Teradata Data Type

 

Remarks

LONGCHAR ALPHA

LONGCHAR TEXT

USAGE

ACTUAL

USAGE

ACTUAL

CHAR

n is an integer between 1 and 256.

An

An

An

An

n is an integer between 257 and 32000.

An

An

TX50

TX

VARCHAR (n)

n is an integer between 1 and 256.

AnV

AnV

AnV

AnV

n is an integer between 257 and 32000.

AnV

AnV

TX50

TX

LONG VARCHAR (n)

n is an integer between 1 and 256.

AnV

AnV

AnV

AnV

n is an integer between 257 and 64000.

FOCUS supports up to 32767 bytes. All data exceeding 32K will be truncated.

AnV

AnV

TX50

TX



x
Reference: Data Type Support for Unicode

The following table describes how the adapter maps Unicode Character data types. The adapter operates in character semantic when configured for Unicode. The LONGCHAR does not affect mapping in this case.

 

Teradata Data Type

FOCUS Data Type

 

Remarks

USAGE

ACTUAL

CHAR(n)

AnV

AnV

n is an integer between 1 and 4000.

VARCHAR (n)

AnV

AnV

n is an integer between 1 and 4000.



x
Oracle Data Type Support
x

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

 

Oracle Data Type

FOCUS Data Type

 

Remarks

USAGE

ACTUAL

Date-Time Data Types

DATE

HYYMDS

HYYMDS

Stores point-in-time values (dates and times) ranging from January 1, 4712 BCE through December 31, 9999 CE.

TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE

HYYMDS

HYYMDs

HYYMDm

HYYMDS

HYYMDs

HYYMDm

fractional_seconds_precision in (0,1,2).

fractional_seconds_precision in (3,4,5).

fractional_seconds_precision in (6,7,8,9).

The adapter supports TIMESTAMP without the TIME ZONE portion.

INTERVAL YEAR (year_precision) TO MONTH

Not supported

Not supported

Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.

INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)

Not supported

Not supported

Stores a period of time in days, hours, minutes, and seconds.

Numeric Data Types

NUMBER (p, s)

Pp,s

P1,...,8

p is an integer between 1 and 31.

s is an integer between 0 and p.

Note: If the MISSING=ON attribute is present in the Master File, the ACTUAL attribute must be P8 or longer. If MISSING=OFF, the ACTUAL attribute will be generated with the length equal to the precision of a NUMERIC field in an Oracle table.

Pp,s

P9,...,16

p is an integer between 16 and 31.

s is an integer between 0 and p.

D20.2

D8

p is an integer between 32 and 37.

s is an integer between 0 and p.

I11

I4

p is 38. This value is the Oracle default.

s is an integer between 0 and p.

INTEGER

See Number

See Number

Oracle converts and stores this type as NUMBER.

DECIMAL

See Number

See Number

Oracle converts and stores this type as NUMBER.

FLOAT

D20.2

D8

An approximate numeric type.

BINARY_DOUBLE

D20.2

D8

A 32-bit, single-precision floating-point approximate numeric type.

BINARY_FLOAT

D20.2

D8

A 64-bit, double-precision floating-point approximate numeric type.

LOB Data Types

BLOB

BLOB

BLOB

The adapter supports BLOB through the OCI interface, with ora_oci=y set in edaserve.cfg.

CLOB

TX50

TX

Adapter supports CLOB through the OCI interface, with ora_oci=y set in edaserve.cfg.

NCLOB

TX50

TX

Adapter supports NCLOB through the OCI interface, with ora_oci=y set in edaserve.cfg.

LONG

TX50

TX

Character data of variable length up to 2 gigabytes, or 231 – 1 bytes. Provided for backward compatibility.

The LONG field must be the last position in the report. This field is the subject of all limitations as documented by Oracle.

As of Oracle 8i, this data type is deprecated. You can migrate to CLOB whenever possible.

LONG RAW

BLOB

BLOB

As of Oracle 8i, this data type is deprecated. You can migrate to BLOB whenever possible.

BFILE

Not supported

Not supported

Stores unstructured binary data in operating system files outside the database.

Other Data Types

ROWID

A18

A18

The pseudo-column data types that store the physical address of a row.

UROWID

Not supported

Not supported

The pseudo-column data types that store both the physical and logical address of a row.

MLSLABEL

Not supported

Not supported

Stores variable-length, binary operating system labels.

The following table lists how the adapter maps non-Unicode Character data types.

Oracle Data Type

 

Remarks

LONGCHAR ALPHA

LONGCHAR TEXT

USAGE

ACTUAL

USAGE

ACTUAL

CHAR (n)

n is an integer between 1 and 256.

An

An

An

An

n is an integer between 257 and 2000.

An

An

TX50

TX

NCHAR (n)

n is an integer between 1 and 256.

An

An

An

An

n is an integer between 257 and 2000.

An

An

TX50

TX

VARCHAR2 (n)

or

VARCHAR (n)

n is an integer between 1 and 256.

AnV

AnV

AnV

AnV

n is an integer between 257 and 4000.

AnV

AnV

TX50

TX

NVARCHAR2 (n)

n is an integer between 1 and 256.

An

An

An

An

n is an integer between 257 and 4000.

An

An

TX50

TX

RAW (n)

n is an integer between 1 and 128, m = 2 * n

Am

Am

Am

Am

 

n is an integer between 129 and 2000, m = 2 * n

Am

Am

TX50

TX





x
IDMS/SQL Data Type Support
x

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

 

IDMS/SQL Data Type

FOCUS Data Type

 

Remarks

USAGE

ACTUAL

Date-Time Data Types

Date

YYMD

DATE

 

Time

A8

A8

 

Timestamp

A26

A26

 

Numeric Data Types

Smallint

I6

I4

 

Integer

I11

I4

 

Longint/BIGINT

I11

I4

 

Numeric/Decimal(n,m)

P(n+2,m)

P8

n is an integer between 1 and 14, m is between 0 and 31.

P(n+2,m)

P16

n is an integer between 14 and 31, m is between 0 and 31.

Double Precision

D20.2

D8

 

Float(n)

F9.2

F4

n is an integer between 1 and 24.

D20.2

D8

n is an integer between 25 and 56.

Real

F9.2

F4

 

LOB Data Types

Vargraphic (1..16379)

TX

TX

 

Other Data Types

Binary(1..32,760)

An

An

n is less than 257.

Graphic (1..16380)(n)

A(2*n)

A(2*n)

2*n is less than or equal to 32,000.

The following table lists how the adapter maps Character data types.

 

IDMS/SQL Data Type

FOCUS Data Type

 

Remarks

USAGE

ACTUAL

CHARACTER(1...32,760)

An

An

n is less than or equal to 32,000.

VARCHAR (1...32,760)

AnV

AnV

n is less than or equal to 32,000.



x
Additional Attributes

Reference:

Some aspects of ACTUAL = TX or ACTUAL = DATE, TIME, or TIMESTAMP are unique for the adapters.



x
Reference: ACTUAL = DATE, TIME, and TIMESTAMP

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:

Note:

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:



x
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:

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

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

The following HOLD formats do not support text fields:

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.



x
Reference: MISSING

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:

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.



x
Reference: Comparing Fields With Null Values

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

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 adapter translates the FOCUS WHERE clause to an SQL WHERE predicate and passes it to the RDBMS for processing. (This translation process is called adapter optimization.) If the 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 adapter when FOCUS handled the request. See The Adapter Optimizer, for a discussion of optimization.



x
Reference: Optional Field Attributes

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.


Top of page

x
FIELDTYPE=R

A field with the FIELTYPE=R (read-only) attribute represents a DB2 TIMESTAMP column. The RDBMS automatically increments this type of field. If you supply a value, it is ignored.



Example: Using FIELDTYPE=R

SALTIME is a DB2 table with a TIMESTAMP column (field ENTRY_DATE in the Master File):

FILENAME=SALINFO,  SUFFIX=DB2,$
 
SEGNAME=SALINFO  ,SEGTYPE=S0,$
 FIELDNAME=SALEID    ,ALIAS=EID     ,USAGE=A9      ,ACTUAL=A9,$
 FIELDNAME=PAY_DATE  ,ALIAS=PD      ,USAGE=YYMD    ,ACTUAL=DATE,$
 FIELDNAME=GROSS     ,ALIAS=MO_PAY  ,USAGE=D12.2M  ,ACTUAL=D8,$
 FIELDNAME=ENTRY_DATE,ALIAS=EDATE   ,USAGE=HYYMDm  ,ACTUAL=HYYMDm,
 FIELDTYPE=R,$

The following MODIFY procedure asks for an employee ID and pay date. If the employee ID exists in the table, but the pay date does not, the procedure asks for a monthly salary and adds a new row to the table:

MODIFY FILE SALINFO
CRTFORM LINE 1
" PLEASE ENTER A VALID EMPLOYEE ID AND PAY_DATE </1"
" EMPLOYEE ID: <SALEID   PAY DATE: <PAY_DATE "
MATCH SALEID
ON NOMATCH GOTO EXIT
ON MATCH GOTO ADDCASE
CASE ADDCASE
MATCH PAY_DATE
ON MATCH GOTO EXIT
ON NOMATCH CRTFORM LINE 5
" ENTER NEW MONTHLY SALARY <T.GROSS>  "
ON NOMATCH INCLUDE
ENDCASE
DATA
END

The following rows exist in the table for employee ID 071382660:

SALEID     PAY_DATE            GROSS  ENTRY_DATE                
------     --------            -----  ----------                
071382660  11/11/30        $5,000.33  2011/12/25 01:05:50.595295
071382660  11/12/30        $5,000.33  2012/01/24 01:27:48.680248
071382660  12/01/27        $5,083.67  2012/02/21 03:12:38.689755
071382660  12/02/24        $5,083.67  2012/03/20 06:11:40.965379
071382660  12/03/30        $5,083.67  2012/04/24 04:54:56.465970
071382660  12/04/30        $5,083.67  2012/05/25 01:33:58.063954
071382660  12/05/28        $5,083.67  2012/06/22 04:43:38.063858
071382660  12/06/29        $5,083.67  2012/07/24 03:12:22.609716
071382660  12/07/30        $5,083.67  2012/08/24 01:43:42.648030
071382660  12/08/31        $5,083.67  2012/09/25 06:15:38.112955

The following example executes the MODIFY procedure to add a salary of $6,000 for the pay date 13/04/30 for this employee:

 PLEASE ENTER A VALID EMPLOYEE ID AND PAY_DATE
 
 EMPLOYEE ID:  071382660    PAY DATE:  130430
 
 ENTER NEW MONTHLY SALARY  6000

The RDBMS automatically generates a timestamp value for the ENTRY_DATE field. If the procedure had supplied a value, it would have been ignored:

SALEID     PAY_DATE              GROSS  ENTRY_DATE
------     --------              -----  ----------
071382660  13/04/30        $6,000.00  2013/05/12 14:43:17.852050

It is up to the developer to query the data source to find out what value was generated by the RDBMS for the auto-increment field.


Information Builders