Access Files

Each Master File has a corresponding Access File. The name of the Access File (member name in the z/OS partitioned data set allocated to DDNAME 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.

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 Access Files.

Oracle supports the CONNECTION attribute described in Access Files.

Describe a Table or View in an Access 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, the format of the table name attribute is 

 [location.][creator.]table

For Teradata, the format of the table name attribute is

 [databasename.]table

For Oracle, the format of the table name attribute is

userid.tablename

For IDMS/SQL, the format of the table name attribute is

 [schema.]table

See Access Files for complete information.

DBSPACE = storage

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

For DB2 on z/OS

databasename.tablespacename  or DATABASEdatabasename

For Teradata, N/A.

For IDMS/SQL, the format of the DBSPACE attribute is

segment.area

For Oracle, the format of the DBSPACE attribute is

tablespacename

See Access Files for complete information.

WRITE = {YES|NO}

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

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.

TABLENAME

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

  • DB2 RDBMS
     TABLENAME = [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 RDBMS
     TABLENAME = [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 RDBMS
    TABLENAME = [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 RDBMS
    TABLENAME = [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"'

DBSPACE

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

  • DB2 RDBMS
    DBSPACE =  databasename.tablespacename

    or

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

  • Teradata RDBMS N/A
  • IDMS/SQL RDBMS
    DBSPACE = 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 RDBMS
    DBSPACE = tablespacename 
Note:
  • The DBSPACE attribute is ignored for all operations except FOCUS CREATE FILE or HOLD FORMAT SQLengine.
  • The 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 Adapter Commands).

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

WRITE

The read/write security attribute, WRITE, determines whether or not the 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 value.

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 adapter must have been installed to allow Read/Write operations. Contact your system support staff for installation options installed at your site.

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

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

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

FALLBACK (Teradata Only)

The FALLBACK attribute indicates whether a secondary copy of data is maintained in addition to the primary table. The 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.

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, and 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 Only)

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