Data Management

In this section:

As you manage your data, you may be required to modify your server and communications configuration files. The first step is understanding how and where data is described and the roles of the server and adapters in managing the processing flow.


Top of page

x
Describing Data Sources

In order to access a table or view, you must first describe it using two files: a Master File and an associated Access File.

Master Files and Access Files can represent an entire table or part of a table. Also, several pairs of Master and Access Files can define different subsets of columns for the same table, or one pair of Master and Access Files can describe several tables.

Note: In these topics, the term table refers to both base tables and views in data sources. The Master File describes the columns of the data source table using keywords in comma-delimited format. The Access File includes additional parameters that complete the definition of the data source table. Some adapters require both files to fulfill queries, and to build the DML to access the non-SQL data sources.


Top of page

x
Processing Requests

When requests are processed, control is passed from the server to an adapter and back. During the process, selected information is read from the Master and Access Files as described below.

The server processes a request as follows:

  1. The request is parsed to identify the table.
  2. The Master File for the table is read.
  3. The SUFFIX value in the Master File is checked (SUFFIX indicates the type of data source).
  4. Control is passed to the appropriate adapter.

The adapter then:

  1. Locates the corresponding Access File.
  2. Uses the information contained in the Master and Access Files to generate the DML statements (if necessary) required to accomplish the request.
  3. Passes the DML statements to the data source.
  4. Retrieves the answer set generated by the data source.
  5. Returns control to the server.

Depending on the requirements of the request, additional processing may be performed on the returned data.


Top of page

x
Master File

In this section:

How to:

A Master File describes a logical data source. A logical data source can be made up of one or more physical data sources of the same type. Each segment is a physical data source.

Master Files contain three types of declarations:

Declaration Type

Description

File

Names the file and describes the type of data source.

Segment

Identifies a table, file, view, or segment.

Field

Describes the columns of the table, view, or fields in the file.

The following guidelines apply:



x
Syntax: How to Specify a File Declaration in a Master File

A Master File begins with a file declaration, which has at least two attributes:

FILENAME (FILE)

Identifies the Master File.

SUFFIX

Identifies the adapter needed to interpret the request.

The syntax for a file declaration is

FILE[NAME]=file, SUFFIX=suffix [,$]

where:

file

Is the file name for the Master File. The file name should start with a letter and be representative of the table or view contents. The actual file must have a .mas extension, but the value for this attribute should not include the extension. The file name without the .mas extension can consist of a maximum of eight alphanumeric characters.

suffix

Identifies the adapter needed to interpret the request. For example, SQLORA is the value for the Adapter for Oracle.



x
Syntax: How to Specify a Segment Declaration in a Master File

Each table described in a Master File requires a segment declaration. The segment declaration consists of at least two attributes:

SEGNAME

Identifies one table.

SEGTYPE

Identifies the physical storage of rows and the uniqueness of column values.

The syntax for a segment declaration is

SEGNAME=segname, SEGTYPE=S0 [,$]

where:

segname

Is the segment name which serves as a link to the actual table name. It may be the same as the name chosen for FILENAME, the actual table name, or an arbitrary name. It can consist of a maximum of 8 alphanumeric characters.

The SEGNAME value in the Master File must be the same as the SEGNAME value specified in the Access File, where the TABLENAME portion of the segment declaration contains the fully-qualified name of the table.

S0

Indicates that the RDBMS is responsible for both physical storage of rows and the uniqueness of column values (if a unique index or constraint exists). It always has a value of S0 (S zero).



x
Syntax: How to Specify a Field Declaration in a Master File

Each row in a table may consist of one or more columns. These columns are described in the Master File as fields with the following primary field attributes:

FIELDNAME

Identifies the name of a field.

ALIAS

Identifies the full column name.

USAGE

Identifies how to display a field on reports.

ACTUAL

Identifies the data type and length in bytes for a field.

MISSING

Identifies whether a field supports null data.

You can obtain values for these attributes by using the system catalog table ALL_TAB_COLUMNS for the existing table or view you wish to describe.

The syntax for a field declaration is

FIELD[NAME]=fieldname, [ALIAS=]sqlcolumn, [USAGE=]display_format,
     [ACTUAL=]storage_format [,MISSING={ON|OFF}], $

where:

fieldname

Is the name of the field. This value must be unique within the Master File. The name can consist of a maximum of 48 alphanumeric characters including letters, digits, and underscores. The name must begin with a letter. Special characters and embedded blanks are not recommended. The order of field declarations in the Master File is significant with regard to the specification of key columns. For more information, see Primary Key.

Tip: Since the name appears as the default column title for reports, for client applications, or EDADESCRIBE, select a name that is representative of the data.

It is not necessary to describe all the columns of the table in your Master File.

sqlcolumn

Is the full column name (the adapter uses it to generate SQL statements). This value must comply with the naming conventions for identifiers, where a name should start with a letter and may be followed by any combination of letters, digits, or underscores. Embedded spaces are not allowed.

display_format

Is the display format. The value must include the field type and length and may contain edit options.

The data type of the display format must be identical to that of the ACTUAL format. For example, a field with an alphanumeric USAGE data type must have an alphanumeric ACTUAL data type.

Fields or columns with decimal or floating point data types must be described with the correct scale (s) and precision (p). Scale is the number of positions to the right of the decimal point. Precision is the total length of the field.

For the server, the total display length of the field or column includes the decimal point and negative sign. In SQL, the total length of the field or column excludes the decimal point and negative sign. For example, a column defined as DECIMAL(5,2) would have a USAGE attribute of P7.2 to allow for the decimal point and a possible negative sign.

storage_format

Is the storage format of the data type and length in bytes.

ON

Displays the character specified by the NODATA parameter for missing data. For related information, see MISSING Attribute.

OFF

Displays blanks or zeroes for fields having no value. This is the default. For more information, see MISSING Attribute.



x
MISSING Attribute

In a table, a null value represents a missing or unknown value; it is not the same as a blank or a zero. For example, a column specification that allows null values is used where a column need not have a value in every row (such as a raise amount in a table containing payroll data).

If the column allows null data but the corresponding field in the Master File is described with the MISSING attribute value OFF, null data values appear as zeroes or blanks.


Top of page

x
Access File

How to:

Each Master File may have a corresponding Access File. The name of the Access File must be identical to that of the Master File, but the extension will be .acx instead of .mas.

The Access File serves as a link between the server and the data source by providing the means to associate a segment in the Master File with the table it describes. The Access File minimally identifies 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.



x
Syntax: How to Specify a Segment Declaration in an Access File

The segment declaration in the Access File establishes the link between one segment of the Master File and the actual table or view. Attributes that constitute the segment declaration are:

SEGNAME

Identifies one table.

TABLENAME

Identifies the table or view. It may contain the owner ID as well as the table name.

KEYS

Identifies how many columns constitute the primary key.

KEYORDER

Identifies the logical sort sequence of data by the primary key.

The syntax for a segment declaration in an Access File is

SEGNAME=segname, TABLENAME=owner.tablename databaselink 
   [,KEYS={n|0}] [,KEYORDER={LOW|HIGH}] ,$

where:

segname

Is the same value as the SEGNAME value in the Master File.

owner

Is the user ID by default.

tablename

Is the name of the table or view.

databaselink

Is the DATABASE LINK name to be used in the currently connected database server.

n

Is the number of columns that constitute the primary key. It can be a value from 0 to 16. The default value is 0. For more information, see Primary Key.

LOW

Indicates an ascending primary key logical sort order. This value is the default.

HIGH

Indicates a descending primary key logical sort order.


Top of page

x
Primary Key

A primary key consists of the column or combination of columns whose values uniquely identify each row of the table. In the employee table, for example, every employee is assigned a unique employee identification number. Each employee is represented by one and only one row of the table, and is uniquely identified by that identification number.

The primary key definition must be defined partly in the Master File and partly in the Access File:

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


Top of page

x
Creating Virtual Fields

How to:

You use the DEFINE command to accomplish these tasks.



x
Syntax: How to Create Virtual Fields With the DEFINE Command
DEFINE fieldname/format [WITH fieldname]=expression ;$

where:

fieldname

Is a field name for the virtual field. It can consist of 1 to 48 characters. You must not qualify the field name.

format

Provides the display format for the field and follows the rules for USAGE formats. This operand is optional. If not specified, the default value is D12.2.

WITH fieldname

Must be coded when the expression is a constant. Any real field can be chosen from the same segment the DEFINE is associated with.

expression

Can be either a mathematical or a logical statement. It can consist of constants, database fields, and virtual fields. The expression must end with a semicolon followed by a dollar sign (;$).

Place your DEFINE statements after all of the field descriptions in the segment. If you are using the DESCRIPTION or TITLE attributes with virtual fields, you must place these attributes on a separate line.



Example: Defining a Virtual Field in a Master File

In the example that follows, the virtual field PROFIT is defined at the end of the segment named BODY.

SEGMENT=BODY, SEGTYPE=S0 , PARENT=CARREC,$
 FIELDNAME=BODYTYPE          ,ALIAS=BODYTYPE      ,A12,A12,$
FIELDNAME=DEALER_COST        ,ALIAS=DEALER_COST   ,D8, D8 ,$
FIELDNAME=RETAIL_COST        ,ALIAS=RETAIL_COST   ,D8, D8 ,$
DEFINE PROFIT/D8 = RETAIL_COST - DEALER_COST
 ;DESC=NET_COST, TITLE='NET,COST' ,$

As a result of this DEFINE statement, you can use PROFIT as a field name in reports. PROFIT is treated as a field with a value equal to the value of RETAIL_COST minus DEALER_COST.

Note:


Top of page

x
Cross-Century Dates

Many existing business applications use two digits to designate a year, instead of four digits. When they receive a value for a year, such as 00, they typically interpret it as 1900, assuming that the first two digits are 19, for the twentieth century. There is considerable risk that date-sensitive calculations in existing applications will be wrong unless an apparatus is provided for determining the century in question. This will impact almost every type of application, including those that process mortgages, insurance policies, anniversaries, bonds, inventory replenishment, contracts, leases, pensions, receivables, and customer records.

The cross-century dates feature enables you to solve this problem at the file and field level of your applications. You can retain your global settings while changing the file-level settings for greater flexibility.

You can enable this feature:


Top of page

x
Cross-Century Dates SET Commands

How to:

The server delivers SET commands that provide a means of interpreting the century if the first two digits of the year are not provided:

SET DEFCENT
SET YRTHRESH

If the first two digits are provided, they are simply accepted and validated.



x
Syntax: How to Implement a Cross-Century Date

The DEFCENT syntax is

SET DEFCENT=nn 

where:

nn

Is 19 unless otherwise specified.

The YRTHRESH syntax is

SET YRTHRESH=nn 

where:

nn

Is zero unless otherwise specified.

The combination of DEFCENT and YRTHRESH establishes a base year for a 100-year window. Any 2-digit year is assumed to fall within that window, and the first two digits are set accordingly. Years outside the declared window must be handled by user coding.

The default values for the two commands are SET DEFCENT=19, SET YRTHRESH=00. When you provide a year threshold, years greater than or equal to that value assume the value assigned by DEFCENT. Years lower than that threshold become DEFCENT plus 1.

To see how DEFCENT and YRTHRESH are applied to interpret 2-digit years, consider the following:

SET DEFCENT=19, SET YRTHRESH=80

This set of commands describes a range from 1980 to 2079. If a 2-digit year field contains the value 99, then the server interprets the year as 1999. If the year field is 79, then the year is interpreted as 2079. If the year field is 00, then the year is interpreted as 2000.


Top of page

x
Master File Syntax

How to:

Instead of using SET commands, you can include settings at the file level in a Master File, or at the field level in a Master File.



x
Syntax: How to Add Cross-Century Date Settings at the File Level

The FDEFCENT syntax is

{FDEFCENT|FDFC}=nn 

where:

nn

Is 19, unless otherwise specified.

The FYRTHRESH syntax is

{FYRTHRESH|FYRT}=nn 

where:

nn

Is zero, unless otherwise specified.



x
Syntax: How to Add Cross-Century Date Settings at the Field Level

At the field level, DEFCENT and YRTHRESH can be added. The DEFCENT syntax is

{DEFCENT|DFC}=nn 

where:

nn

Is 19, unless otherwise specified.

The YRTHRESH syntax is

{YRTHRESH|YRT}=nn 

where:

nn

Is zero, unless otherwise specified.



x
Syntax: How to Add Cross-Century Dates Using a DEFINE Command
DEFINE FILE EMPLOYEE
 fld/fmt [{DEFCENT|DFC} nn {YRTHRESH|YRT} nn] [MISSING...]=expression;
END

The DFC and YRT syntax must follow the field format information.



Example: Implementing Cross-Century Dates

The following example illustrates how century interpretation is implemented at both the file level and field level in a Master File.

FILENAME=EMPLOYEE, SUFFIX=FOC, FDEFCENT=20, FYRTHRESH=66,$
 SEGNAME=EMPINFO,  SEGTYPE=S1
  FIELDNAME=EMP_ID,     ALIAS=EID,  FORMAT=A9,       $
  FIELDNAME=LAST_NAME,  ALIAS=LN,   FORMAT=A15,      $
  FIELDNAME=FIRST_NAME, ALIAS=FN,   FORMAT=A10,      $
  FIELDNAME=HIRE_DATE,  ALIAS=HDT,  FORMAT=I6YMD, DEFCENT=19, 
YRTHRESH=75,$

The next example illustrates the conversion of a 2-digit year field with the DEFINE command:

DEFINE FILE EMPLOYEE
ESHIRE_DATE/YYMD = HIRE_DATE; (The format of HIRE_DATE is I6YM.)
ESHIRE DFC 19 YRT 80 = HIRE_DATE;
END

WebFOCUS