mainframevsam

Calculating the record size of alternate index for a VSAM ksds file

In the IBM documentation, the formula to calculate the record size of AIX for a KSDS file is given as

5 + alternate-key length + (n * base cluster's prime-key length)

In my case, the alternate-key is non-unique to the value of n, according to the documentation, would be the number of records that contain the same alternate-key value. So my question is, How to find this value of n when there are thousands of records?

Also, I don't understand what it means by "the number of records that contain the same alternate key value". For example, if the alternate-key for a file with 6 records contains the values 10,20,20,30,30,30 then what would be the value of n in this case?

Solution

  • In the DEFINE AIX command you are basically defining the record for the Alternate Index. If they are UNIQUE then the relationship is 1:1 and so n = 1.

    When the relationship is non-unique then each alternate-index record will have as many entries as there are records that contain the non-unique key. This means, that DFSMS will create an alternate-key record and that record will contain a reference to the PK of all those records.

    In your example let me add a PK Value.

    PK1 - 10
    PK2 - 20
    PK3 - 20
    PK4 - 30
    PK5 - 30
    PK6 - 30

    The AIX will have the following records:

    10 - PK1
    20 - PK2, PK3
    30 - PK4, PK5, PK6

    Each of those records will be updated when a new PK is added that has the alternate-key. What the manual is asking you for is an estimate of how many PKs will be associated with the non-unique alternate-key.

    Assuming that

    Using IBM's formula your record size would be:

    5 + 2 + (10000 * 8) = 80007 as the max value.

    If you have millions of records then you would use a large number. Note that these values allow Access Method Services to manage storage based on your estimates of what the file will contain.

    There is no crystal ball and these are best guess estimates.

    The next question is what happens if you underestimate? You will get a logical error when trying to add a record / update it. Here is the error:

    Here is a reference to the error in IBM's Documentation enter image description here