Optimizing IMS search with partial key with VISION:Inquiry.

Products

• Vision:Inquiry

Issue/Introduction

When a complete key is not available, sequentially searching an IMS database uses a lot of resources. Can the required cycles be reduced?

 

Resolution

As each new database was created, it was defined to VISION:Inquiry for use by the Applications area for extracting data for reporting and a number of end-users that used the online features as a means for quick displays of data that may not be covered by an online application.

Two major parts databases contained a part number key that was a composite key made up of various interest areas. You can pick similar scenarios of multiple field keys from any industry. Take a database with a root key of Customer Number, Region ID, Product Group for example. Thee part number scenario shows it can be done on a single field key that has portions of the number with built-in meaning in basically the same way as using individual fields of a multi-field key.

The part number used is a 12 digit numeric field logically broken into the following meanings
(think fields for multi-field keys) : aaa-mmss-t-nnnn.
Dashes not carried in the numeric field - just for readability.

aaa  = aircraft type (747, 737, etc) 
mm   = major assembly (engine, main landing gear, wing, etc) 
ss   = sub assembly (engine turbine shaft, engine fan blade, wing front slats, wing rear flaps, etc) 
t    = type of part (repairable, disposable, etc) 
nnnn = 4-digit sequence number within a combination.

An example part number could then be something like 747-5510-3-0034. To an end user wanting to get a report on parts; this could mean it is a 747 part. The 55 meaning an engine part, the 10 meaning the compressor portion of the engine, the 3 meaning it is a repairable part, and the 0034 means the 34^th part number within this portion of the engine sub-assembly. And let's say many of these engine parts may be interchanged between 747 and 777 engines.

For a multi-field key you've got a similar Customer Number, Region ID, and Product Group (maybe 239785 003 FAB).

For the above part number example, a user in the engine shop wants a report of all of the engine compressors, regardless of aircraft type. In VISION:Inquiry they may enter a query similar to this:

        Display from Part-DB Part-Nbr, Curr-Location, MFG-Name, MFG-Part-Nbr
             IF  Major-Asmbly = 55 and Sub-Asmbly = 10;
or
 
       Display from Cust-DB Cust-Nbr, Cust-Name, Cust-Phone, Cust-Contact-Name
            IF  Region-ID = 003 and Prod-Grp = 'FAB';

For HIDAM Databases:

Let's assume that both of these databases (Part-DB and Cust-DB) are HIDAM databases with a Primary Index.
Later we'll assume they are HDAM with randomly placed roots/records.

In both of these query examples IMS will be unable to retrieve the records by use of a qualified key call. Only a portion of the key is supplied and in both cases it does not include any of the high-order portion of the key to even try to position with a GU type of call. IMS will need to sequentially read each record and then see if the comparison values supplied in the queries match the fields in the current record. If not, IMS must simply move on to the next record and repeat the check of the fields. Depending on the size of the database / number of records, this could be relatively quick, or take a long time. One of the part databases had about 3 million records and this type of query could run hours, using a lot of I/O as well.

The customer came up with the solution of using the ability of VISION:Inquiry to join multiple databases, or VSAM files to an IMS database in this case. When running DL/I Batch or BMP extract or report jobs, they made use of the underlying VSAM file of the HIDAM Primary Index to drive the query.

Define the Primary Index to VISION:Inquiry as if it were a basic VSAM KSDS file and define the various fields of the Cust-DB example, or the parts of the part number field for the Part-DB example.

For the Cust-DB example you may have fields of the Primary Index VSAM file defined like:
VS-Cust-DB-Key          X(12)  Group level
K-Cust-Nbr              9(6)
K-Region-ID             9(3)
K-Prod-Grp              X(3)
 
For the Part-DB:
VS-Part-DB-Key          9(12)  Group level
K-Aircraft-Type         9(3)
K-Major-Asmbly          9(2)
K-Sub-Asmbly            9(2)
K-Part-Type             9(1)
K-Part-Seq              9(4)

Besides the individual key fields, also define a single field that encompasses the full 12 bytes of each of these keys. Now you can reference the individual fields and you can also reference the entire key. The basic Group and Fields format within COBOL.

Now modify the original queries with a join to the index VSAM dataset.

Find  VS-Part-Index  %Part-Key = VS-Part-Key 
        IF  K-Major-Asmbly = 55 and K-Sub-Asmbly = 10; 
Display from Part-DB Part-Nbr, Curr-Location, MFG-Name, MFG-Part-Nbr 
    IF Part-DB-Key = %Part-Key; 

Find VS-Cust-Index %Cust-Key = VS-Cust-Key 
   IF K-Region-ID = 003 and K-Prod-Grp = 'FAB'; 
Display from Cust-DB Cust-Nbr, Cust-Name, Cust-Phone, Cust-Contact-Name 
   IF Cust-DB-Key = %Cust-Key;

The end result is that while a sequential scan is still needed to find the matching part number fields, it is done against the Primary Index as a VSAM file read rather than IMS sequential Get Next processing through the main database. The index VSAM file is normally a slight fraction the size of the main database and its blocks are loaded with key values. If you run this as a PRINT or EXTRACT query just against the VSAM file, you'll see it runs through the entire file in less than a minute for even a million key entries. It is just doing VSAM sequential processing at this point to find the subset of keys that contain the values in the desired key fields.

Using the FIND against the VSAM file, each time a match is found in the VSAM file, the full key becomes known and you can join to the main database with a full-key read. You may have skipped 30,000 VSAM records that did not match and now issue only one database call with a full key. Then go on to skip another 10,000 VSAM records. But this VSAM searching, in place of IMS database reads, is only taking seconds. Then another match is found and the full-key used for the second IMS read. These are 40,000 skipped records that didn't require IMS to read the database once to determine they didn't match.

For HDAM Databases:

The process against an HDAM is done in a similar fashion by using a VSAM KSDS dataset. Since HDAM does not have a Primary Key Index dataset like HIDAM, a couple of different methods can be used.

Many HDAM databases are old enough that there have been at least one, and probably several, secondary index datasets added over time. If one of these secondary indexes contains the root key fields necessary to support the partial key access needed, that index's VSAM file could then be defined to VISION:Inquiry the same way as the Primary Key Index in the HIDAM example.

Where an existing secondary index does not contain the necessary root key fields, there is the option to build your own VSAM KSDS file of root key field data from the HDAM database. An example would be to run a VISION:Inquiry Extract job against the HDAM database retrieving only the key fields of the root segments. The time to extract these keys is weighed against the time saved for any subsequent runs against the database. This is relatively fast in that IMS will only read the root segments and will read from one to the next with internal pointers, ignoring all other segment types in the database records.

Once this extracted file of root keys is produced, there are a couple of options for building the VSAM KSDS. Remember that the keys have been read sequentially from the HDAM database and are in no particular key order. If you write these keys to the VSAM KSDS file they will be put into key sequence. What then happens, if this VSAM file is used to do the FIND against the HDAM database, the calls are processed in key sequence.

One option is to create the VSAM KSDS file with a key field that is simply a sequence number and the root keys as the data. Then as the HDAM root keys are written to the VSAM file, a simple program can generate a sequence number for the VSAM key, and the HDAM root keys were kept in the same order found by the Extract. Now, when using the VSAM dataset to get the key for the HDAM record, you would be processing in basically sequential order through the database. If the resulting data is needed in a particular order, a SORT can always be used.