Advantages of Multi-Dataset Indexes & Index Buffers
Article ID: 31349
Updated On:
Products
Issue/Introduction
What are the advantages of using Multi-Dataset Indexes & Index Buffers?
Resolution
The standard process when creating a database index in Datacom/DB or Datacom/AD is to create the entire index (IXX) in a single file. This is a very effective and efficient means of accessing the data for most databases. However, when an index becomes very large, or when there are special requirements for a part of an index, it might be advantageous to use the Multi-Dataset Index (MDI) capability of Datacom/DB.
When implementing an MDI, it may be of interest to determine the impact of this change, and any potential performance changes in doing so. One way clients have tried to do this is by comparing MUF EOJ report and any use of Dynamic System Tables for I/O. These reports in version 12.0, 14.0 and 15.0 are highly accurate.
However, the accounting field EXCIX in 12.0 was defective. It excluded all I/O to an index partition MDI. So, if reviewing that statistic gives the impression that there were I/O savings because of implementing an MDI, that in fact was not actually happening. In 14.0 this accounting was fixed inadvertently as part of another correction that was implemented; so it was never noticed (until now) that the 12.0 code was wrong.
The fact that the MDIs did not deliver the perceived I/O improvement does not mean that they were not helping system performance. In fact, there are several benefits to MDIs:
1 - In an index structure, the DXX (low level index blocks) are exactly the same for every key whether stored in the IXX area or a MDI (I00 – I99 or Inn) area, so you cannot see a difference in accessing them; the performance will be the same at that DXX level. However, the IXX part of the index is used to find the DXX. With MDI, in the IXX you may have one less level in the index structure; that will result in a CPU savings because that level can be skipped. This would NOT be seen as I/O EXCP savings because most of the IXX blocks stay in memory. Instead, what you are getting is CPU savings by being able to get to the DXX with one less level to process.
2 - On multi-dataset indexes, some DBUTLTY functions still work at a group level (AREA=IXX) while other functions work at an individual data set level (AREA=IXX,IXXAREA=Inn). To rebuild an MDI index you only need to process the specific index area (Inn) desired.
3 - Another major benefit to MDI is that you get to cover a single key with the COVERED MUF Startup Option. The way this works is that blocks of an area can be privately saved in a buffer pool dedicated to that specific area. This is helpful when all needed blocks cannot fit into the common index block buffer pools. For example, if a table has 5 keys for various reasons but one key is used by in a critical situation, e.g. where safety is an issue, then you want access to that key to be super-fast. Having that key COVERed will provide that faster level of access.
4 - In addition to covered, with 14.0, there are changes to buffer pools for indexes. Index buffer pools can still be specified with the SYSPOOL and COVERED MUF Startup Options; but in addition you can specify alternate buffer pools. Using these alternate buffer pools allows you to define the pools and to specify which areas and database IDs (DBIDs) use these alternate pools. This facility can be used to increase memory resources for selected areas and to restrict memory for other selected areas. That will improve performance when accessing the selected databases, which amounts to prioritizing access of one index over another.
To summarize, in some circumstances, using an MDI can produce performance benefits to your applications, and as seen, there are several ways to take advantage of these.
Additional Information
For further information, please consult the Datacom/DB Database and System Administration Guide, in the section titled, “Multi-Dataset Index.”
Feedback
