Describing a Partitioned FOCUS Data Source

Intelligent Partitioning

The FOCUS data source supports intelligent partitioning, which means that each vertical partition contains the complete data source structure for specific data values or ranges of values. Intelligent partitioning not only lets you separate the data into up to 1022 physical files, it allows you to create an Access File in which you describe the actual data values in each partition using WHERE criteria. When processing a report request, the selection criteria in the request are compared to the WHERE criteria in the Access File to determine which partitions are required for retrieval.

To select applications that can benefit most from partitioning, look for ones that employ USE commands to concatenate data sources, or for data that lends itself to separation based on data values or ranges of values, such as data stored by month or department. Intelligent partitioning functions like an intelligent USE command. It looks at the Access File when processing a report request to determine which partitions to read, whereas the USE command reads all of the files on the list. This intelligence decreases I/O and improves performance.

To take advantage of the partitioning feature, you must:

Edit the Master File and add the ACCESSFILE attribute.
Create the Access File using a text editor.

Concatenation of multiple partitions is supported for reporting only. You must load or rebuild each physical partition separately. You can either create a separate Master File for each partition to reference in the load procedure, or you can use the single Master File created for reporting against the partitioned data source, if you:

Issue an explicit allocation command to link the Master File to each partition in turn.
Run the load procedure for each partition in turn.

Note: Report requests automatically read all required partitions without user intervention.

Specifying an Access File in a FOCUS Master File

How to:

Specify an Access File for a FOCUS Data Source

Reference:

Usage Notes for Partitioned FOCUS Data Sources

To take advantage of the partitioning feature, you must edit the Master File and add the ACCESSFILE attribute to identify the name of the Access File.

In prior releases, the Access File for a FOCUS data source was space delimited and could describe the files associated with several Master Files. The name of the Access File was arbitrary and was specified with the ACCESS = attribute in the Master File.The new syntax is comma-delimited, similar to the Master File syntax. It can describe the files needed for accessing only one Master File. The name of the Access File must be the same as the Master File name, and it must be specified with the ACCESS = attribute in the Master File.Both types of syntax are supported in the FOCUS 7.6 track, but starting with FOCUS 7.7, only the comma-delimited syntax will be supported.

Both types of Access File syntax follow with examples relevant to each.

Top of page

Syntax: How to Specify an Access File for a FOCUS Data Source

FILENAME=filename, SUFFIX=FOC, ACCESS[FILE]=accessfile,
.
.
.

where:

filename: Is the file name of the partitioned data source.
accessfile: Is the name of the Access File. For a space-delimied Access File, this can be any valid name. For a comma-delimited Access File, this must be the same as the Master File name.

Top of page

Example: Master File for the VIDEOTR2 Partitioned Data Source

FILENAME=VIDEOTR2,  SUFFIX=FOC,
 ACCESS=VIDEOACX,  $
  SEGNAME=CUST,       SEGTYPE=S1
   FIELDNAME=CUSTID,     ALIAS=CIN,         FORMAT=A4,          $
   FIELDNAME=LASTNAME,   ALIAS=LN,          FORMAT=A15,         $
   FIELDNAME=FIRSTNAME,  ALIAS=FN,          FORMAT=A10,         $
   FIELDNAME=EXPDATE,    ALIAS=EXDAT,       FORMAT=YMD,         $
   FIELDNAME=PHONE,      ALIAS=TEL,         FORMAT=A10,         $
   FIELDNAME=STREET,     ALIAS=STR,         FORMAT=A20,         $
   FIELDNAME=CITY,       ALIAS=CITY,        FORMAT=A20,         $
   FIELDNAME=STATE,      ALIAS=PROV,        FORMAT=A4,          $
   FIELDNAME=ZIP,        ALIAS=POSTAL_CODE, FORMAT=A9,          $
  SEGNAME=TRANSDAT, SEGTYPE=SH1,  PARENT=CUST
   FIELDNAME=TRANSDATE,  ALIAS=OUTDATE,     FORMAT=HYYMDI,      $
  SEGNAME=SALES,    SEGTYPE=S2,   PARENT=TRANSDAT
   FIELDNAME=TRANSCODE,  ALIAS=TCOD,        FORMAT=I3,          $
   FIELDNAME=QUANTITY,   ALIAS=NO,          FORMAT=I3S,         $
   FIELDNAME=TRANSTOT,   ALIAS=TTOT,        FORMAT=F7.2S,       $
  SEGNAME=RENTALS,  SEGTYPE=S2,   PARENT=TRANSDAT
   FIELDNAME=MOVIECODE,  ALIAS=MCOD,        FORMAT=A6, INDEX=I, $
   FIELDNAME=COPY,       ALIAS=COPY,        FORMAT=I2,          $
   FIELDNAME=RETURNDATE, ALIAS=INDATE,      FORMAT=YMD,         $
   FIELDNAME=FEE,        ALIAS=FEE,         FORMAT=F5.2S,       $
 DEFINE DATE/I4 = HPART(TRANSDATE, 'YEAR', 'I4');

Top of page

Reference: Using a Partitioned Data Source

The following illustrates how to use an intellgently partitioned data source. The Access File for the VIDEOTR2 data source describes three partitions based on DATE:

TABLE FILE VIDEOTR2
PRINT LASTNAME FIRSTNAME DATE
WHERE DATE FROM 1996 TO 1997
END

The output is:

LASTNAME         FIRSTNAME   DATE
--------         ---------   ----
HANDLER          EVAN        1996
JOSEPH           JAMES       1997
HARRIS           JESSICA     1997
HARRIS           JESSICA     1996
MCMAHON          JOHN        1996
WU               MARTHA      1997
CHANG            ROBERT      1996

There is nothing in the request or output that signifies that a partitioned data source is used. However, only the second partition is retrieved, reducing I/O and enhancing performance.

Top of page

Reference: Usage Notes for Partitioned FOCUS Data Sources

Concatenation of multiple partitions in one request is only valid for reporting. To MODIFY or REBUILD a partitioned data source, you must explicitly allocate and MODIFY, Maintain, or REBUILD one partition at a time.
The order of precedence for allocating data sources is:
- A USE command that is in effect has the highest precedence. It overrides an Access File or an explicit allocation for a data source.
- An Access File overrides an explicit allocation for a data source.
A DATASET attribute cannot be used in the same Master File as an ACCESSFILE attribute.
Commands that alter a data source (for example, MODIFY, Maintain, and REBUILD) do not use the Access File. If you use a Master File that contains an ACCESSFILE attribute with a command that alters the data source, the following warning message appears:
```
(FOC1968)ACCESS FILE INFORMATION IN MASTER %1 WILL NOT BE CONSIDERED
```
The CREATE FILE command automatically issues a dynamic allocation for the data source it creates, and this allocation takes precedence over the ACCESSFILE attribute. In order to use the ACCESSFILE attribute after issuing a CREATE FILE command, you must first free this automatic allocation.
When the type of command changes from reading a data source to writing to a data source, or vice versa (for example, Maintain to TABLE), the Master File is reparsed.
When a cross-referenced Master File includes an ACCESSFILE attribute, the host Master File cannot rename the cross-referenced fields.

The Space-Delimited FOCUS Access File

The Access File provides comprehensive metadata management for all FOCUS data sources. It shields end users from the complex file storage and configuration details used for efficient and transparent access to partitioned and distributed data sources.

The Access File describes how to locate, concatenate, join, and select the appropriate physical data files for retrieval requests against one or more FOCUS data sources. Access Files are optional, except for retrieval requests using intelligent partitioning.

Every request supplies the name of a Master File. The Master File is read and the declarations in it are used to access the data source. If the Master File includes an ACCESSFILE attribute, FOCUS reads the named Access File and uses it to locate the correct data sources. Each Master File can point to its own separate Access File, or several Master Files can point to the same Access File. This flexibility makes it possible to create one Access File that manages data access for an entire application. If the Master File does not contain an ACCESSFILE attribute, FOCUS attempts to satisfy the request with the Master File alone.

Use an Access File to take advantage of the following data source features:

Horizontal and vertical partitioning. A data source can consist of several separate files, or partitions, each of which contains the data source records for a specific time period, region, or other element. It can also have LOCATION files for individual segments (horizontal partitions). The Access File describes how to concatenate the separate data sources.
Joins. If joined data sources are partitioned, the Access File describes how to concatenate the separate data sources in the join.

An Access File is required to take advantage of intelligent partitioning. Intelligent partitioning places specific data values in each physical partition, and uses the Access File to describe the values in each partition. With this information, FOCUS optimizes data access by retrieving only those partitions whose values are consistent with the selection criteria in the request.

Note: On z/OS, the Access File must be a member of a data set concatenated in the allocation for ddname ACCESS. On VM/ESA, the Access File must have the file type ACCESS. FOCSQL cannot be used as the file type. The Access File has the same DCB attributes as the Master File.

FOCUS Space-Delimited Access File Attributes

How to:

Create a Space-Delimited FOCUS Access File

The Access File can include the following attributes:

Attribute	Synonyms	Description
MASTERNAME	MASTER	Master File entry.
DATANAME	DATA	Name of the physical file.
WHERE		WHERE criteria.
LOCATION		Segment location.

Each Access File declaration begins with a MASTERNAME attribute that identifies the Master File to which it applies. By including multiple MASTERNAME declarations, you can use one Access File for multiple Master Files, and possibly for an entire application.

Top of page

Syntax: How to Create a Space-Delimited FOCUS Access File

MASTERNAME filename1 
 DATANAME dataname1 [WHERE test1 ;]
  [LOCATION locationnamea  DATANAME datanamea]
   .
   .
   .
 DATANAME dataname2 [WHERE test2;]
  [LOCATION locationnameb  DATANAME datanameb]
   .
   .
   .
MASTERNAME filename2 
   .
   .
   .

where:

MASTERNAME

Is the attribute that identifies the Master File name. MASTER is a synonym for MASTERNAME.

filename1, filename2

Are names of Master Files. You can describe unrelated Master Files in one Access File.

DATANAME

Is the attribute that identifies a physical file. DATA is a synonym for DATANAME.

dataname1, dataname2

Are the fully qualified physical file names of physical partition files, in the syntax native to your operating environment.

test

Is a valid WHERE test. The following types of expressions are supported. You can also combine any number of these expressions with the AND operator:

 fieldname relational_operator value1 [OR value2 OR value3 ... ]

 fieldname FROM value1 TO value2 [OR value3 TO value4 ...]

 fieldname1 FROM value1 TO value2  
    [OR fieldname2 FROM value3 TO value4 ...]

where:

fieldname, fieldname1, fieldname2 are field names in the Master File.

relational_operator can be one of the following: EQ, NE, GT, GE, LT, LE.

value1, value2, value3, value4 are valid values for their corresponding fields.

Note: If the test conditions do not accurately reflect the contents of the data sources, you may get incorrect results from requests.

LOCATION

Is the attribute that identifies a separately stored segment.

locationnamea, locationnameb

Are the values of the LOCATION attributes from the Master File. Segment locations must map one-to-one to horizontal partitions.

datanamea, datanameb

Are the fully qualified physical file names of the LOCATION files, in the syntax native to your operating environment.

Top of page

Example: Describing an Intelligent Partition in a Space-Delimited FOCUS Access File

The following Access File illustrates how to define intelligent partitions for the VIDEOTR2 data source, in which data is grouped by date.

For z/OS:

MASTERNAME VIDEOTR2
  DATANAME USER1.VIDPART1.FOCUS
     WHERE DATE EQ 1991;
 
  DATANAME USER1.VIDPART2.FOCUS
     WHERE DATE FROM 1996 TO 1998;
 
  DATANAME USER1.VIDPART3.FOCUS
     WHERE DATE FROM 1999 TO 2000;

For CMS:

MASTERNAME VIDEOTR2
  DATANAME 'VIDPART1 FOCUS A'
     WHERE DATE EQ 1991;
 
  DATANAME 'VIDPART2 FOCUS A'
     WHERE DATE FROM 1996 TO 1998;
 
  DATANAME 'VIDPART3 FOCUS A'
     WHERE DATE FROM 1999 TO 2000;

Top of page

Example: Describing a Partition With a LOCATION File in a Space-Delimited Access File

Consider the following version of a SALES Master File. The CUSTDATA segment is stored in a separate LOCATION file named MORECUST:

FILENAME=SALES, ACCESSFILE=XYZ,$
  SEGNAME=SALEDATA
  .
  .
  .
  SEGNAME=CUSTDATA, LOCATION=MORECUST,$

The corresponding Access File (XYZ) describes one partition for 1994 data, and another partition for the 1993 data. Each partition has its corresponding MORECUST LOCATION file.

For z/OS:

MASTERNAME SALES
 DATANAME USER1.SALES94.FOCUS
  WHERE SDATE FROM '19940101' TO '19941231';
   LOCATION MORECUST
    DATANAME USER1.MORE1994.FOCUS
 
 DATANAME USER1.SALES93.FOCUS
  WHERE SDATE FROM '19930101' TO '19931231';
   LOCATION MORECUST
    DATANAME USER1.MORE1993.FOCUS

For CMS:

MASTERNAME SALES
 DATANAME 'SALES94 FOCUS A'
  WHERE SDATE FROM '19940101' TO '19941231';
   LOCATION MORECUST
    DATANAME 'MORE1994 FOCUS A'
 
 DATANAME 'SALES93 FOCUS A'
  WHERE SDATE FROM '19930101' TO '19931231';
   LOCATION MORECUST
    DATANAME 'MORE1993 FOCUS A'

Describing Joined Data Sources In a Space-Delimited Access File

The Master File can describe cross-references to other Master Files. In simple cases, the Master File alone may be sufficient for describing the cross-reference.

If one of the joined data sources is horizontally partitioned, only that data source needs an Access File to implement the join.

However, when both of the joined data sources are horizontally partitioned, they can both be described in one Access File, or they can each be described in a separate Access File in order to implement the join. Only the host data source is allowed to have WHERE criteria in the Access File. If both the host and cross-referenced data sources have WHERE criteria, a join may produce unexpected results.

Top of page

Example: Joining Two Partitioned Data Sources in a Space-Delimited Access File

The cross-referenced field in a join must be indexed. If the host data source is partitioned, the cross-referenced data source must either contain the same number of partitions as the host data source, or only one partition.

For z/OS:

MASTERNAME SALES
 DATANAME USER1.NESALES.FOCUS
 DATANAME USER1.MIDSALES.FOCUS
 DATANAME USER1.SOSALES.FOCUS
 DATANAME USER1.WESALES.FOCUS
 
MASTERNAME CUSTOMER
 DATANAME USER1.NECUST.FOCUS
 DATANAME USER1.MIDCUST.FOCUS
 DATANAME USER1.SOCUST.FOCUS
 DATANAME USER1.WECUST.FOCUS

For CMS:

MASTERNAME SALES
 DATANAME 'NESALES  FOCUS A'
 DATANAME 'MIDSALES FOCUS A'
 DATANAME 'SOSALES  FOCUS A'
 DATANAME 'WESALES  FOCUS A'
 
MASTERNAME CUSTOMER
 DATANAME 'NECUST  FOCUS A'
 DATANAME 'MIDCUST FOCUS A'
 DATANAME 'SOCUST  FOCUS A'
 DATANAME 'WECUST  FOCUS A'

FOCUS Comma-Delimited Access File Attributes

How to:

Create a Comma-Delimited FOCUS Access File

Reference:

Access File Attributes for a Comma-Delimited Access File

Every request supplies the name of a Master File. The Master File is read and the declarations in it are used to access the data source. If the Master File contains an ACCESS= attribute that references an Access File, the Access File is read and used to locate the correct data sources. With the comma-delimited syntax, the Access File must have the same name as the Master File. With the space-delimited syntax, the Access File can have any name. If there is no Access File with the same name as the ACCESS= attribute in the Master File, the request is processed with the Master File alone.

Top of page

Reference: Access File Attributes for a Comma-Delimited Access File

Each comma-delimited Access File describes the files and MDIs for one Master File, and that Master File must have the same file name as the Access File.

All attribute/value pairs are separated by an equal sign (=), and each pair in a declaration is delimited with a comma (,). Each declaration is terminated with the comma dollar sign (,$).

Each Access File starts with a declaration that names its corresponding Master File.
Next comes the DATA declaration that describes the location of the physical file. If the file is partitioned, it has multiple DATA declarations.
If the file is intelligently partitioned so that an expression describes which data values reside in each partition, the DATA declaration has a WHERE phrase that specifies this expression.
If the data source has LOCATION segments the LOCATION declaration names a location segment. Its corresponding DATA declaration points to the physical LOCATION file.
If the data source has an MDI, the Access File has an MDI declaration that names the MDI and its target segment, followed by declarations that name the dimensions of the MDI, followed by the MDIDATA declaration that points to the physical MDI file. If the MDI is partitioned, there are multiple MDIDATA declarations for the MDI.

Top of page

Syntax: How to Create a Comma-Delimited FOCUS Access File

Master File declaration:

MASTER=mastername,$

where:

mastername

Indicates the name of the Master File with which this Access File is associated. It is the same value included in the Master File ACCESS=filename attribute, used to indicate both the existence of the Access File and its name.

DATA=file_specification, 
   [WHERE= expression; ,]$
[DATA=file_specification, 
   [WHERE= expression; ,]$ ...]

where:

file_specification

Points to the file location. This is a complete file specification. There are can be up to 1022 DATA declarations (partitions) in a single Access File. With XFOCUS data sources, this supports creation of a 4 Terabtye database. Using FOCUS data sources, a 500 GB database can be constructed.The WHERE clause is the basis of the Intelligent Partitioning feature. The expression is terminated with the semi-colon and the entire declaration with the comma/dollar sign. WHERE expressions of the following type are supported:

WHERE = field  operator value1 [ OR  value2...]; ,$

WHERE = field FROM value1 TO value2 [AND FROM value3 TO value4];,$

Expressions can be combined with the AND operator.

Location File declarations:

LOCATION=location_segment_name,
  DATA=location_segment_file_specification,$

where:

location_segment_name: Is the name of the segment stored in the location file.
location_segment_file_specification: Is the full file specification for the physical file the segment is located in.

MDI declarations:

MDI=mdiname, TARGET_OF = segname,$
   DIM = [filename.]fieldname [, MAXVALUES = n] [, WITHIN = dimname1],$
   [DIM = [filename.]fieldname [, MAXVALUES = n] [, WITHIN = dimname1] ,$ ...]
 MDIDATA=mdi_file_specification,$
 [MDIDATA=mdi_file_specification,$ ...]

where:

mdiname: Is the name of the MDI.
segname: Is the name of the target segment
filename: Is the name of the file where an MDI dimension resides.
fieldname: Is the name of a field that is a dimension of the MDI.
n: Is the number of distinct values in the dimension. When the MDI is created, the actual dimension value will be converted to an integer of length 1, 2, or 4 bytes, and this number will be stored in the index leaf.
mdi_file_specification: Is the fully-qualified specification of the physical MDI file. If the MDI is partitioned, it is the specification for one partition of the MDI. An MDI can have up to 250 MDIDATA declarations (partitions). An Access File can have an unlimited number of MDIs.
dimname: Defines a hierarchy of dimensions. This dimension is defined within the dimname dimension. For example, CITY WITHIN STATE.

Top of page

Example: Comma-Delimited Access File for the VIDEOTR2 Data Source

VIDEOTR2 is an intelligently partitioned FOCUS data source. The Master File has an ACCESS=VIDEOTR2 attribute:

FILENAME=VIDEOTR2,  SUFFIX=FOC,    ACCESS=VIDEOTR2             
SEGNAME=CUST,       SEGTYPE=S1                                 
 FIELDNAME=CUSTID,       ALIAS=CIN,          FORMAT=A4,       $
 FIELDNAME=LASTNAME,     ALIAS=LN,           FORMAT=A15,      $
 FIELDNAME=FIRSTNAME,    ALIAS=FN,           FORMAT=A10,      $
 FIELDNAME=EXPDATE,      ALIAS=EXDAT,        FORMAT=YMD,      $
 FIELDNAME=PHONE,        ALIAS=TEL,          FORMAT=A10,      $
 FIELDNAME=STREET,       ALIAS=STR,          FORMAT=A20,      $
 FIELDNAME=CITY,         ALIAS=CITY,         FORMAT=A20,      $
 FIELDNAME=STATE,        ALIAS=PROV,         FORMAT=A4,       $
 FIELDNAME=ZIP,          ALIAS=POSTAL_CODE,  FORMAT=A9,       $
 FIELDNAME=EMAIL,        ALIAS=EMAIL,        FORMAT=A18,      $
SEGNAME=TRANSDAT, SEGTYPE=SH1,  PARENT=CUST                    
 FIELDNAME=TRANSDATE,    ALIAS=OUTDATE,   FORMAT=HYYMDI,       
   MISSING=ON, $                                               
SEGNAME=SALES,    SEGTYPE=S2,   PARENT=TRANSDAT                
 FIELDNAME=TRANSCODE,    ALIAS=TCOD,    FORMAT=I3,            $
 FIELDNAME=QUANTITY,     ALIAS=NO,      FORMAT=I3S,           $
 FIELDNAME=TRANSTOT,     ALIAS=TTOT,    FORMAT=F7.2S,         $
SEGNAME=RENTALS,  SEGTYPE=S2,   PARENT=TRANSDAT                
 FIELDNAME=MOVIECODE,    ALIAS=MCOD,      FORMAT=A6, INDEX=I, $
 FIELDNAME=COPY,         ALIAS=COPY,      FORMAT=I2,          $
 FIELDNAME=RETURNDATE,   ALIAS=INDATE,    FORMAT=YMD,         $
 FIELDNAME=FEE,          ALIAS=FEE,       FORMAT=F5.2S,       $
 DEFINE DATE/I4 = HPART(TRANSDATE, 'YEAR', 'I4');

The following shows the Access File, named VIDEOTR2, on z/OS:

MASTER=VIDEOTR2 ,$            
  DATA=USER1.VIDPART1.FOCUS,    
    WHERE=DATE EQ 1991;,$           
                                  
  DATA=USER1.VIDPART2.FOCUS,    
    WHERE=DATE FROM 1996 TO 1998; ,$
                                  
  DATA=USER1.VIDPART3.FOCUS,    
   WHERE=DATE FROM 1999 TO 2000;,$

The following shows the Access File, named VIDEOTR2, on CMS:

MASTER=VIDEOTR2 ,$            
  DATA='VIDPART1 FOCUS A',    
    WHERE=DATE EQ 1991;,$           
                                  
  DATA='VIDPART2 FOCUS A',    
    WHERE=DATE FROM 1996 TO 1998; ,$
                                  
  DATA='VIDPART3 FOCUS A',    
   WHERE=DATE FROM 1999 TO 2000;,$