Creating an External Index: The EXTERNAL INDEX Subcommand

In this section:

How to:

Reference:

Users with READ access to a local FOCUS data source can create an index database that facilitates indexed retrieval when joining or locating records. An external index is a FOCUS data source that contains index, field, and segment information for one or more specified FOCUS data sources. The external index is independent of its associated FOCUS data source. External indexes offer equivalent performance to permanent indexes for retrieval and analysis operations.

External indexes enable indexing on concatenated FOCUS data sources, indexing on real and defined fields, and indexing selected records from WHERE/IF tests. External indexes are created as temporary data sets unless preallocated to a permanent data set. They are not updated as the indexed data changes.

You create an external index with the REBUILD command. Internally, REBUILD begins a process which reads the databases that make up the index, gathers the index information, and creates an index database containing all field, format, segment, and location information.

You provide information about:

Sort libraries and work space must be available. The REBUILD allocates default sort work space if you have not already. DDNAMEs SORTIN and SORTOUT must be allocated prior to issuing a REBUILD.


Top of page

x
Procedure: How to Use the EXTERNAL INDEX Subcommand

To create an external index from a concatenated database, follow these steps:

  1. Assume that you have the following USE in effect:
    USE CLEAR *
    USE
    EMPLOYEE
    EMP2 AS EMPLOYEE
    JOBFILE
    EDUCFILE
    END

    Note that EMPLOYEE and EMP2 are concatenated and can be described by the EMPLOYEE Master File.

  2. Initiate the REBUILD facility by entering:
    REBUILD

    The following options are available:

    1. REBUILD        (Optimize the database structure)
    2. REORG          (Alter the database structure)
    3. INDEX          (Build/modify the database index)
    4. EXTERNAL INDEX (Build/modify an external index database)
    5. CHECK          (Check the database structure)
    6. TIMESTAMP      (Change the database timestamp)
    7. DATE NEW       (Convert old date formats to smartdate formats)
    8. MDINDEX        (Build/modify a multidimensional index)
  3. Select the EXTERNAL INDEX subcommand by entering:
    EXTERNAL INDEX or 4
  4. Specify whether to create a new index data source or add to an existing one by entering one of the following choices:
    NEW
    ADD

    For this example, assume you are creating a new index database and respond by entering:

    NEW
  5. Specify the name of the external index database:
    EMPIDX
  6. Specify the name of the data source from which the index records are obtained:
    EMPLOYEE
  7. Specify the name of the field to index:
    CURR_JOBCODE
  8. Specify whether the index should be associated with a particular field by entering YES or NO. For this example, enter:
    NO
  9. Indicate whether you require any record selection tests by entering YES or NO.

    For this example, enter:

    NO

    If you responded YES, you would next enter the record selection tests, ending them with the END command on a separate line.

    For example:

    IF DEPARTMENT EQ 'MIS'
    END

You will see statistics (output of the ? FDT query) about the index data source when the REBUILD EXTERNAL INDEX procedure is complete. This query is automatically issued at the end of the REBUILD EXTERNAL INDEX process in order to validate the contents of the index database.



Example: External Index Statistics

The following illustrates external index statistics.

EXTERNAL INDEX FILE:               EMPIDX
FULL NAME:                         EMPIDX.FOCUS
VERSION :
DATE/TIME OF LAST CHANGE:          05/13/99 15.40.46
 
   EXTERNAL INDEX DATABASE PAGES:  00000001
   DATABASE INDEXED:               EMPLOYEE
   FIELD NAME:                     EMPINFO.CURR
   FIELD FORMAT:                   A3
   SEGMENT NAME:                   EMPINFO
   SEGMENT LOCATION:               EMPLOYEE
 
EXTERNAL INDEX DATA COMPONENTS:
   EMPLOYEE.FOCUS
   EMP2.FOCUS

Top of page

x
Reference: Special Considerations for REBUILD EXTERNAL INDEX

Consider the following when working with external indexes:


Top of page

x
Concatenating Index Databases

The external index feature enables indexed retrieval from concatenated FOCUS data sources. If you wish to concatenate databases that comprise the index, you must issue the appropriate USE command prior to the REBUILD. The USE must include all cross-referenced and LOCATION files. REBUILD EXTERNAL INDEX contains an add function that enables you to append only new index records from a concatenated database to the index database, eliminating the need to recreate the index database.

The original data source from which the index was built may not be in the USE list when you add index records. If it is, REBUILD EXTERNAL INDEX generates the following message:

(FOC999) WARNING. EXTERNAL INDEX COMPONENT REUSED: ddname

Top of page

x
Positioning Indexed Fields

The external index feature is useful for positioning retrieval of indexed values for defined fields within a particular segment in order to enhance retrieval performance. By entering at a lower segment within the hierarchy, data retrieved for the indexed field is affected, as the index field is associated with data outside its source segment. This enables the creation of a relationship between the source and target segments. The source segment is defined as the segment that contains the indexed field. The target segment is defined as any segment above or below the source segment within its path.

If the target segment is not within the same path, the following message is generated:

(FOC974) EXTERNAL INDEX ERROR. INVALID TARGET SEGMENT

A defined field may not be positioned at a higher segment.

While the source segment can be a cross-referenced or LOCATION segment, the target segment cannot be a cross-referenced segment. If an attempt is made to place the target on a cross-referenced segment, the following message is generated:

(FOC1000) INVALID USE OF CROSS REFERENCE FIELD

If you choose not to associate your index with a particular field, the source and target segments will be the same.


Top of page

x
Activating an External Index

How to:

After building an external index database, you must associate it with the data sources from which it was created. This is accomplished with the USE command. The syntax is the same as when USE is issued prior to building the external index database, except the WITH or INDEX option is required.



x
Syntax: How to Activate an External Index
USE  [ADD|REPLACE] 
database_name [AS mastername] 
index_database_name  [WITH|INDEX]  mastername 
   .
   .
   .
END

where:

ADD

Appends one or more new databases to the present USE list. Without the ADD option, the existing USE list is cleared and replaced by the current list of USE databases.

REPLACE

Replaces an existing database_name in the USE list.

database_name

Is the name of the data source.

Enter the ddname.

You must include a data source name in the USE list for all cross-referenced and LOCATION files that are specified in the Master File.

AS

Is used with a Master File name to concatenate data sources.

mastername

Specifies the Master File.

index_database_name

Is the name of the external index database.

Enter the ddname.

WITH|INDEX

Is a keyword that creates the relationship between the component data sources and the index database. INDEX is a synonym for WITH.


Information Builders