AMS COMMANDS FOR ALTERNATE INDEX CREATION - IBM Mainframe

There are three distinct AMS commands to create an alternate index:

  • DEFINE ALTERNATEINDEX (abbreviated DEFINE A1X) is used to allocate space for alternate index cluster
  • BLD1NDEX is used load an alternate index with alternate key-pointer pair records. Pointers are the prime key values of the base cluster in a KSDS, while in an ESDS they are Relative Byte Addresses of the records.
  • DEFINE PATH is used to develop a bridge (or path) between the alternate index cluster and the base cluster.

Upgrade Set

To ensure that the access of records through an alternate index is accurate, the alternate index has to be kept in sync with the base cluster, i.e. whenever a record is added, deleted or modified in the base cluster, the appropriate changes have to be made for the alternate index.

Now, the responsibility of maintaining the alternate index can be done manually be deleting the alternate index, allocating them and reloading them with the BLDINDEX command. The drawback of this approach is that there will still be times when the alternate index and the base cluster are not in sync. Another alternative is to have the alternate index automatically updated to reflect the changes in the base cluster. This can be accomplished by specifying an UPGRADE parameter while creating the alternate index. Alternate indexes thus created are said to belong to the upgrade set of the base cluster.

The disadvantage of the latter option is the extra overhead involved each time an update is made to the base cluster. Such overheads could prove to be crucial in a response-critical online application. However, there could be applications that need the alternate index to be always in sync with the base cluster. The trade¬off has to be made depending on the nature of the individual applications.

DEFINE AIX Command

As mentioned before DEFINE AIX allocates space for an alternate index cluster. Before defining an alternate index it should be noted that the base cluster has to be defined first, though not necessarily loaded.

We will use our CUSTOMER.KSDS.CLUSTER for which the first 6 positions indicate the customer number, which is the prime key, and the next 10 positions indicate the customer name on which we will define the alternate index.

DEFINE AIX Command

  • DEFINE AIX is the functional command used to allocate an alternate index
  • The NAME parameter indicates the name of the alternate index. Note that one of the parentheses is left open
  • RELATE gives the name of the base cluster to which the alternate index is related
  • The VOLUMES parameter indicates the volume serial on which the space for the AIX cluster will be allocated
  • The CYLINDERS parameter indicates that 1 cylinder of primary space will be allocated. When this space is used up secondary space will be allocated in increments of 1 cylinder each
  • The CONTROUNTERVALSIZE (CISZ) for the AIX cluster will be 2048 bytes
  • The FREESPACE parameter means that at the time of initial loading, each control interval will have 10 percent of its space left free and that 20 percent of control intervals in each control area will be left unused
  • The KEYS parameter indicates that the alternate key is 10 bytes long and starts at position 6 (seventh byte) of the record
  • NONUNIQUEKEY indicates that the alternate index can have duplicate keys. Since multiple customers can have the same name, we are using this option. The opposite is UNIQUEKEY
  • UPGRADE option makes the alternate index part of the upgrade set of the base cluster
  • RECORDSIZE parameters indicate the average and maximum record lengths in that order. Suppose there are at the most 10 customers with the same name. The maximum record size figure is arrived at by the following formula: Record length = 5 (control fields) + length of alternate key + n * length of prime key, where n is the number of occurrences of the non-unique alternate key, i.e. 5 + 10 + 10 * 6. The first 5 bytes, it can be recalled, stores control information about the alternate index record.
  • DATA indicates the beginning of the data component parameters
  • NAME indicates the name of the data component of the cluster
  • INDEX indicates the beginning of the index component parameters
  • NAME indicates the name of the index component of the cluster
  • Note that the in-stream data is terminated with a "/*" delimiter

If this JCL were executed after being customized to your installation standards, an AIX cluster CUSTOMER.KSDS.AIX1.CLUSTER would be allocated. The allocation messages would be in the SYSPRINT device. Note that we have just created the AIX cluster and it will still be empty. You would have to load records into it by using the BLDINDEX command. This will be discussed in the next section. Note that there are more extensive options for allocation of an AIX cluster. Refer to an IBM manual for details.

BLDINDEX Command

This command loads records into an AIX cluster. Prior to executing this command, the base cluster must be defined, loaded with records and the AIX cluster must be defined. When the BLDINDEX command is executed, three distinct functions occur in the following order.

Records of the base cluster are read and alternate key - pointer pair records are extracted

These key - pointer pair records are sorted in ascending order of the alternate key.

The alternate index is loaded with these sorted pair records.

alternate index is loaded with these sorted pair records

The sample JCL given above loads the CUSTOMER.KSDS.AIX1 .CLUSTER.

  • IDCUT1 and IDCUT2 are two sort work files. These are default DD names in the BLDINDEX command.
  • INTERNALSORT indicates that AMS will perform an internal sort of the alternate-key pointer pair if possible. Otherwise, it will perform an external sort by using the sort work files.
  • CATALOG gives the VSAM catalog where the sort work files are defined. This catalog does not have to be the same as those in which the base cluster and alternate index are defined.

DEFINE PATH

This command establishes a relation between the base cluster and the alternate index. A path is not a data set. It does not occupy any space, nor does it have any records. It is just an entry in the catalog, which establishes a link between the base cluster and the alternate index cluster. Below is a sample JCL for creating a PATH

DEFINE PATH

PATHENTRY specifies the alternate index cluster to which the path is related.

  • The UPDATE parameter indicates that when the path is opened for processing, both the base cluster and its upgrade set (alternate index associated with it) is opened for processing. When a path defined with the NOUPDATE parameter is opened for processing, only the base cluster is opened. UPDATE is the default.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics