Data Management

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.

Describing Data Sources

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.

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.

Master File

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:

The following guidelines apply:

• A declaration consists of attribute-value pairs separated by commas.
• Each declaration must begin on a separate line. A declaration can span as many lines as necessary, as long as no single keyword-value pair spans two lines.
• Do not use system or reserved words as names for files, segments, fields, or aliases. Specifying a reserved word generates syntax errors.

Specify a File Declaration in a Master File

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

Specify a Segment Declaration in a Master File

SEGNAME=segname, SEGTYPE=S0 [,$]

Specify a Field Declaration in a Master File

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

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

• The default NODATA character is a period.
• A column in a table that allows null data does not need to include the NULL clause in its table definition, since that is the default.
• In the Master File for that table, the column that allows null data must be described with the MISSING attribute value ON. The default for this attribute is OFF, which corresponds to the NOT NULL attribute in the table definition.

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.

Access File

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.

Specify a Segment Declaration in an Access File

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

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:

• The order of field declarations in the Master File is significant to the specification of key columns. To define the primary key in a Master File, describe its component fields immediately after the segment declaration. You can specify the remaining fields in any order. In the Access File, the KEYS attribute completes the process of defining the primary key.
• To identify the primary key, the adapter uses the number of columns (n) indicated by the KEYS attribute in the Access File and the first n fields described in the Master 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 columns comprising the primary key (although a unique index is certainly desirable for both data integrity and performance reasons).

Creating Virtual Fields

You use the DEFINE command to accomplish these tasks.

Create Virtual Fields With the DEFINE Command

DEFINE fieldname/format [WITH fieldname]=expression ;$

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' ,$

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 are wrong unless an apparatus is provided for determining the century in question. This impacts 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:

• Using SET commands.
• At the file level in a Master File.
• At the field level in a Master File.

Cross-Century Dates SET Commands

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.

Implement a Cross-Century Date

SET DEFCENT=nn 

SET YRTHRESH=nn 

SET DEFCENT=19, SET YRTHRESH=80

Master File Syntax

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.

Add Cross-Century Date Settings at the File Level

{FDEFCENT|FDFC}=nn 

{FYRTHRESH|FYRT}=nn 

Add Cross-Century Date Settings at the Field Level

{DEFCENT|DFC}=nn 

{YRTHRESH|YRT}=nn 

Add Cross-Century Dates Using a DEFINE Command

DEFINE FILE EMPLOYEE
 fld/fmt [{DEFCENT|DFC} nn {YRTHRESH|YRT} nn] [MISSING...]=expression;
END

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,$

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

Synonym Management Options

Data Type Support Report

SQL Data Type mapping options are available in a report available from the WebFOCUS Reporting Server browser interface.

Access the Data Type Report

Procedure
1. Select Get Data from the main menu.
2. From the side menu, click the Data Types button.

   The Filter Data Types Report page opens, as shown in the following image.

   

3. Select an Adapter Subcategory from the corresponding list box.
4. Select an Adapter from the corresponding list box.
5. Select a Server Data Type from the corresponding list box.
6. Click Show Report.

Changing the Precision and Scale of Numeric Columns

You can alter the length and scale of numeric columns returned by a SELECT request to the server by creating different specifications in your login profile or in a stored procedure. The conversion settings are reflected in the Master File in the USAGE and ACTUAL formats of the fields generated by CREATE SYNONYM. This affects how the fields are processed and formatted by the server.

Override the Default Precision and Scale

ENGINE ADAPTER_ID SET CONVERSION RESET
ENGINE ADAPTER_ID SET CONVERSION format RESET
ENGINE ADAPTER_ID SET CONVERSION format [PRECISION precision [scale]]
ENGINE ADAPTER_ID SET CONVERSION format [PRECISION MAX]

dbsqlism

dbdb2

dbsqlexc

dbsqlvrt

dbsqlhyp

dbsqliia

dbidmsr

dbsqlinf

dbsqljdb

dbmariadb

dbsqlmac

dbsqlmss

dbmsodbc

dbsqlmys

dbsqlnez

dbsqlodb

dbsqlora

dbsqlpst

dbsqlpro

dbsqlpsq

dbsqlhan

dbeda

dbsqlsyb

dbsqldbc

dbsqluv

dbsqlund

dbxml

ENGINE ADAPTER_ID SET CONVERSION INTEGER PRECISION 7

ENGINE ADAPTER_ID SET CONVERSION FLOAT PRECISION 14 3

ENGINE ADAPTER_ID SET CONVERSION INTEGER RESET

ENGINE ADAPTER_ID SET CONVERSION RESET