Access Files

Each Master File has a corresponding Access File. The name of the Access File (member name in the MVS partitioned data set allocated to DDNAME FOCSQL, or CMS file name with file type FOCSQL) must be identical to that of the Master File or, in the case of a remote segment description can be described in the cross-referenced Access File. The Access File associates a segment in the Master File with the table it describes.

In this section: Segment Declarations in the Access File Field Declarations (DB2 and DB2 for VM Only) How to: Describe a Table or View in a Master File Reference: TABLENAME DBSPACE WRITE KEYS KEYORDER FALLBACK (Teradata Only) CONNECTION (Oracle Only) PRECISION

The Access File must identify the table and primary key (if there is one). It may also indicate the logical sort order of data and identify storage areas for the table. Access File field declarations can define the precision of packed fields.

For multi-table structures, the Access File also contains KEYFLD and IXFLD attributes that implement embedded equijoins. See Multi-Table Structures, for details.

The following is an Access File for the DB2 table EMPINFO:

SEGNAME = EMPINFO, TABLENAME = "USER1"."EMPINFO", KEYS = 1,
 WRITE = YES,  DBSPACE = PUBLIC.SPACE0,$

Segment Declarations in the Access File

The segment declaration in the Access File establishes the link between the Master File and the RDBMS table or view. Attributes that constitute the segment declaration are: SEGNAME, TABLENAME, DBSPACE, WRITE, KEYS, and KEYORDER. Values for SEGNAME and TABLENAME are required; the remaining attributes acquire default values if they are omitted.

Teradata supports the FALLBACK attribute described in FALLBACK (Teradata Only).

Oracle supports the CONNECTION attribute described in CONNECTION (Oracle Only).

Syntax: How to Describe a Table or View in a Master File

SEGNAME=segname, TABLENAME= tableid [,DBSPACE=storage,]
  [,WRITE= {YES|NO}] [,KEYS= {0|n}] [,KEYORDER=sequence,]  ,$

where:

segname

Is the one- to eight-character SEGNAME value from the Master File.

tableid

Is the RDBMS-specific table name.

For DB2 and DB2 for VM is: [location.][creator.]table

For Teradata is: [databasename.]table

For Oracle is: userid.tablename

For IDMS/SQL is: [schema.]table

See TABLENAME for complete information.

DBSPACE = storage

Is an RDBMS-specific storage area for the table used by the CREATE FILE or HOLD FORMAT DB2, SQL, SQLDBC, SQLIDMS, or SQLORA commands:

For DB2 on MVS: databasename.tablespacename or DATABASE databasename

For DB2 for VM: owner.dbspace

For Teradata: N/A

For IDMS/SQL: segment.area

For Oracle: tablespacename

See DBSPACE for complete information.

WRITE = {YES|NO}

YES specifies read and write access using MODIFY and MAINTAIN. YES is the default.

NO specifies read-only access using FOCUS MODIFY and MAINTAIN.

KEYS = n

Indicates how many columns constitute the primary key. Is a value from 0 to 64. Zero is the default.

KEYORDER = sequence

Indicates the primary key sort sequence. Valid values are as follows:

LOW indicates ascending primary key sort sequence; LOW is the default.

ASC is a synonym for LOW.

HIGH indicates descending primary key sort sequence.

DESC is a synonym for HIGH.

Reference: TABLENAME

The TABLENAME attribute specifies the RDBMS table name. This name may have multiple parts depending on the specific RDBMS.

RDBMS	TABLENAME =
DB2 and DB2 for VM	[location.][creator.]table where: location Is the DB2 subsystem location name for the Distributed Data Facility; 16 characters maximum. creator Defaults to the current authorization ID if not specified. Eight characters maximum. table Is the name of the RDBMS table or view; 18 characters maximum. The TABLENAME attribute identifies the RDBMS table or view. It should contain both the creator ID and the table name. If not specified, the creator defaults to the current authorization ID. The maximum length for a fully-qualified table name is 44. For a discussion of FOCUS support for the DB2 Distributed Data Facility, see Additional Topics. All names must conform to the rules for identifiers stated in the appropriate RDBMS manual.
Teradata	[databasename.]table where: databasename Is the name of the database where the table resides; 30 characters maximum. The default is the database name assigned to your Teradata logon id, provided one exists. table Is the name of the table of view; 30 characters maximum. In Teradata, the maximum length of a fully-qualified table name is 61 characters including the required period (.). Database and table name may consist of uppercase letters (A through Z) and digits (0 through 9). Special characters, dollar sign ($), pound sign (#), and underscore (_) are also permitted.
IDMS/SQL	[schema.]table where: schema Is the IDMS SQL schema name for the table or view. Eight characters maximum. If not specified, IDMS searches for a temporary table definition for the named table. If that does not exist, IDMS uses the current schema in effect for the current user session. table Is the name of the table or view; 17 characters maximum. The maximum IDMS length for a fully qualified table name is 25. All names must conform to the CA-IDMS rules for identifiers.
Oracle	[creator.]tablename[@dblinkname] where: creator Is the Oracle userid, up to 30 characters in length. tablename Is the name of the Oracle table being described, up to 30 characters in length. dblinkname Is a valid DATABASE LINK defined in the currently connected Oracle database server.

Note:

• If any part of the TABLENAME begins with a number or special character or contains special characters, enclose it in double quotation marks.
• If any part of the TABLENAME includes a dollar sign ($), enclose that part in double quotation marks, and enclose the entire TABLENAME value in single quotation marks. For example:

TABLENAME = 'USER1."TABLE$1"'

Reference: DBSPACE

The DBSPACE attribute is an RDBMS-specific storage area for the table used by the CREATE FILE or HOLD FORMAT DB2, SQL, SQLDBC, SQLORA, or SQLIDMS commands:

RDBMS	DBSPACE =
DB2	databasename.tablespacename or DATABASE databasename The storage areas identified by the DBSPACE attribute are called tablespaces in DB2. The IBM default value is DSNDB04, a public database. (DB2 automatically generates a tablespace in DSNDB04.)
DB2 for VM	owner.dbspace The storage areas identified by the DBSPACE attribute are called dbspaces in DB2 for VM. The default value is the DB2 for VM authorization ID's private DBSPACE.
Teradata	N/A
IDMS/SQL	segment.area where: segment Is the IDMS SQL segment name to be used for the CREATE TABLE DDL resulting from a CREATE FILE or HOLD FORMAT SQLIDMS command. If not specified, IDMS uses the default area associated with the schema. area Is the IDMS SQL segment name to be used for the CREATE TABLE DDL resulting from a CREATE FILE or HOLD FORMAT SQLIDMS command. If not specified, IDMS uses the default area associated with the schema.
Oracle	tablespacename

Note:

• The DBSPACE attribute is ignored for all operations except FOCUS CREATE FILE or HOLD FORMAT SQLengine.
• The data adapter may have been installed with a default DBSPACE setting that is used if you do not either specify the DBSPACE attribute or issue a SET DBSPACE command.
• You can also declare storage areas by issuing the SET DBSPACE environmental command prior to CREATE FILE (see Environmental Commands).

The Access File DBSPACE attribute overrides both the SET command and the installation default.

Reference: WRITE

The read/write security attribute, WRITE, determines whether or not the data adapter allows FOCUS MODIFY and MAINTAIN operations (INCLUDE, UPDATE, or DELETE) on the table. The syntax is

WRITE = {YES|NO}

where:

YES

Specifies read and write access using FOCUS MODIFY and MAINTAIN. YES is the default.

NO

Specifies read-only access using FOCUS TABLE, MAINTAIN, and MODIFY. You can use MODIFY or MAINTAIN read-only functions, such as MATCH, NEXT, CRTFORM, or WINFORM, to display rows.

Note:

• The WRITE attribute has no effect on FOCUS reporting operations.
• Regardless of the WRITE value, RDBMS security must approve all operations and activities.
• The data adapter must have been installed to allow Read/Write operations. Contact your system support staff for installation options installed at your site.

Reference: KEYS

The KEYS attribute indicates how many columns constitute the primary key for the table. Acceptable values range from 0 to 64. Zero, the default, indicates that the table does not have a primary key. In the corresponding Master File, primary key columns must correspond to the first n fields described.

The syntax is

KEYS = {0|n} ,

where:

n

Is a value from 0 to 64. Zero is the default.

The KEYS value has the following significance in reporting operations:

• When you use FOCUS FST. or LST. direct operators, the data adapter instructs the RDBMS to sort the answer set by the primary key in KEYORDER sequence.

Note:

LST. processing is automatically invoked if you request SUM or WRITE of an alphanumeric field or use one in a report heading or footing.

• The data adapter uses the KEYS value to determine the relationship between two joined tables. For example, if the primary key of one table is joined to the primary key of another table, the data adapter can assume that a one-to-one relationship exists between the two tables. It then uses this assumption in conjunction with the JOIN specification and the current optimization setting to produce SQL statements. See The Data Adapter Optimizer, and Advanced Reporting Techniques, for a detailed explanation.

To provide consistent access to tables, you should specify the KEYS attribute whenever a primary key exists.

The KEYS value also has significance in MODIFY operations (see Maintaining Tables With FOCUS, for a detailed explanation).

Reference: KEYORDER

The KEYORDER attribute is optional. It specifies the logical sort sequence of data by the primary key; it does not affect the physical storage of data. The data adapter uses the KEYORDER value when you specify FST. and LST. direct operators in report requests. The syntax is

KEYORDER= sequence  ,

where:

sequence

Indicates the primary key sort sequence. Valid values are as follows:

LOW sorts the rows in ascending primary key sequence. LOW is the default.

ASC is a synonym for LOW.

HIGH sorts the rows in descending primary key sequence.

DESC is a synonym for HIGH.

For example, to retrieve the most recent pay dates first, specify KEYORDER = HIGH for the SALINFO table:

SEGNAME = SALINFO, TABLENAME = "USER1"."SALINFO", KEYS = 2,
 WRITE = YES,  KEYORDER = HIGH, DBSPACE = PUBLIC.SPACE0,$

The data adapter requests rows ordered by SALEID and PAY_DATE in descending order. FST.PAY_DATE retrieves the most recent salary data for each employee. See the Master File for SALINFO in File Descriptions and Tables.

KEYORDER also determines the logical sort order for MODIFY and MAINTAIN NEXT subcommands (see Maintaining Tables With FOCUS).

Reference: FALLBACK (Teradata Only)

The FALLBACK attribute indicates whether a secondary copy of data is maintained in addition to the primary table. The data adapter incorporates this backup operation while creating a table in response to the FOCUS CREATE FILE command. You can also specify the FALLBACK parameter in native SQL/DBC CREATE TABLE statements.

The syntax is

FALLBACK = {YES|NO}

where:

YES

Establishes a backup copy. The location is determined by Teradata and becomes available if the original copy becomes unavailable.

NO

Does not establish a backup copy. NO is the default value.

Reference: CONNECTION (Oracle Only)

The CONNECTION attribute selects a specific connection name from the list of Oracle database servers declared using the SET CONNECTION_ATTRIBUTES command. This setting supersedes the default connection in any request that references the Master and Access File pair. SERVER is a synonym for CONNECTION that is supported for compatibility with earlier releases.

CONNECTION=connection_name

where:

connection_name

Is the TNSNAME used to connect to an Oracle database server. This name must have been previously referenced in a SET CONNECTION_ATTRIBUTES command. See Connection, Authentication, and Security, for information about the SET CONNECTION_ATTRIBUTES command.

Field Declarations (DB2 and DB2 for VM Only)

Access File field declarations define the precision type for DECIMAL fields.

Reference: PRECISION

Static TABLE requests can contain SQL host variables (see Static SQL (DB2 and DB2 for VM)). When you use these variables in an SQL predicate, the RDBMS optimizer considers them for indexed access only if they have the same precision as the corresponding RDBMS columns.

The ACTUAL attribute from the Master File does not provide enough information for determining the RDBMS column precision of DECIMAL columns. For example, an ACTUAL attribute of P6 can correspond to a DECIMAL column of precision 10 or 11.

The PRECISION attribute in the Access File defines the precision for DECIMAL columns. The syntax is

FIELD = fieldname, PRECISION = {ODD|EVEN} , $

where:

fieldname

Is the name of an indexed packed decimal field that will be used either in screening conditions or as a join field in a cross-referenced file in a FOCUS-managed join.

ODD|EVEN

Is the precision-type. If n is the length specified for the ACTUAL attribute in the Master File:

ODD

the default, calculates the DECIMAL column precision for the host variable using the formula 2n-1.

EVEN

uses the formula 2n-2.

For example, an ACTUAL of P6 and a precision-type of ODD (the default) makes the host variable length 11; the RDBMS can consider index access if the RDBMS column precision is 11 (provided the column is indexed). With precision-type EVEN, the host variable length is 10, which enables index access for DECIMAL columns of precision 10.

Note: The DB2 for VM optimizer in Version 3 Release 4 and below does not allow index access from static SQL for packed decimal columns of even precision, regardless of the precision-type used.