Creating an External Index: The EXTERNAL INDEX Subcommand

In this section:

Concatenating Index Databases
Positioning Indexed Fields
Activating an External Index

How to:

Use the EXTERNAL INDEX Subcommand

Reference:

Special Considerations for REBUILD EXTERNAL INDEX

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:

Whether you want to add new records from a concatenated database to the index database.
The name of the external index database that you want to build.
The name of the data source from which the index information is obtained.
The name of the field from which the index is to be created.
Whether you want to position the index field within a particular segment.
Any valid WHERE or IF record selection tests.

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

Procedure: How to Use the EXTERNAL INDEX Subcommand

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

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.

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)

Select the EXTERNAL INDEX subcommand by entering:
```
EXTERNAL INDEX or 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
```
Specify the name of the external index database:
```
EMPIDX
```
Specify the name of the data source from which the index records are obtained:
```
EMPLOYEE
```
Specify the name of the field to index:
```
CURR_JOBCODE
```
Specify whether the index should be associated with a particular field by entering YES or NO. For this example, enter:
```
NO
```
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.

Reference: Special Considerations for REBUILD EXTERNAL INDEX

Consider the following when working with external indexes:

Up to eight indexes can be activated at one time in a USE list using the WITH statement. More than eight indexes may be activated in a session if you issue the USE CLEAR command and issue new USE statements.
Up to 256 concatenated files may be indexed. However, only eight indexes may be activated at one time.
Verification of the component files is now done for both the date and time stamp of file creation. Files with the same date and time stamp that are copied display the following message:
```
(FOC995) ERROR. EXTERNAL INDEX DUPLICATE COMPONENT: fn REBUILD ABORTED
```
MODIFY may only use the external index with the FIND or LOOKUP functions. The external index cannot be used as an entry point, such as:
```
MODIFY FILE filename.indexfld
```
Indexes may not be created on field names longer than twelve characters.
Text fields may not be used as indexed fields.
The USE options NEW, READ, ON, LOCAL, and AS master ON userid READ are not supported for the external index database.
The external index database need not be allocated since CREATE FILE automatically performs a temporary allocation. If a permanent database is required, then an allocation for the index database must be in place prior to the REBUILD EXTERNAL INDEX command.
SORTIN and SORTOUT, work files that the REBUILD EXTERNAL INDEX process creates, must be allocated with adequate space. In order to estimate the space needed, the following formula may be used:
```
bytes = (field_length + 20) * number_of_occurrences
```

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

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.

Activating an External Index

How to:

Activate an External Index

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.

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.

On z/OS, enter the ddname.

On UNIX, Windows, and OpenVMS, enter filename. The data source to be rebuilt will be referenced by a USE command. If no USE command is in effect, the data source will be searched for using the EDAPATH variable.

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.

On z/OS, enter the ddname.

On UNIX, Windows, and OpenVMS, enter [pathname]filename.foc. The data source to be rebuilt will be referenced by a USE command. If no USE command is in effect, the data source will be searched for using the EDAPATH variable.

WITH|INDEX

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