I am trying to build an alternative index for ESDS but I am getting incorrect values after build index. The alternate key has to be built as a non-unique key
unless You post the content of the AIX in hex nothing we can do ...
but just looking at the record pattern it looks good to me
each record contains a <prefix> <the key> a list of RBA
two RBA's for keys 66660 and 66668
one RBA for the other keys
As per my understanding, build index will extract the RBA's for ESDS with the key value defined in the alternative key.
What I am not able to understand is. How the output is getting displayed for build index with different RBA values.
I am not able to figure out how the RBA or 2nd record is 14 and the 3rd record has 14 as RBA and so on. It is also not clear to me why the alternative keys are getting displayed from 6th Position.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
How many occurrences of a single key do you expect? The RECSZ of the alternate index has ABSOLUTELY NO relationship to the RECSZ of the underlying cluster. The RECSZ of the alternate index should be set to hold the maximum number of duplicates expected (up to the maximum allowed).
Your confusion comes from the fact that the alternate index is built by VSAM, maintained by VSAM, and has nothing to do with the underlying cluster. Where did you get "14" from in your post -- I don't see any X'0E' anywhere in what you posted? And you should not have 14 anywhere -- the RBA of the record is what is used, so you should be looking for X'00', X'50', X'A0', X'F0', and so forth -- multiples of 80. Furthermore, the first alternate index you display has primary key of '66660' with records at X'00000000' and X'00000050' -- which is PRECISELY what it should have (in an alternate index, the primary key is followed by the RBA of each record with that primary key).
I mean the RBA of 0,18,32,46 and 64 respectively are not very clear to me.
My understanding is -
a) As 66660 appears 2 times so how the RBA is 18. I am getting confused on how this value of 18 has come?
b) For 66661, RBA is 18 which has come from above but length as 14. I am not able to get how this length is 14?
c) For 66662, RBA is 32 which is understandable(18+14) but I am not able to understand again, why the length is showing as 14 corresponding to this.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
The alternate index record length is defined in the Redbook VSAM Demystified (among other places) as 5 (overhead) + length of primary key + n times length of alternate key (4 for ESDS since RBA has length of 4), where n is the number of occurrences of that primary key in the records.
To deconstruct this: you have 2 bytes of something (and you don't have to know what it is), followed by a 2-byte occurrence count followed by 1 byte of something (again, you don't have to know what it is) followed by your primary key '66660' followed by 2 bytes of RBA followed by another 2 bytes of RBA. 5 + 5 + 4 + 4 is 18 bytes -- as I said, PRECISELY what you should have. For the 66661 record, you have 5 bytes of overhead plus the 5 bytes of primary key plus the 4 bytes for 1 occurrence of RBA, or a total of 14 bytes.
The RECSZ for the alternate index (assuming an average of 1 and a maximum of 32767 occurrences of the primary key) would be RECSZ(14,131078) -- as I said, the alternate index RECSZ has no relationship to the RECSZ(80,80) of the underlying cluster.
Thanks, Robert.
It is clear to me now. Basically, the formulae are-
AIX for a KSDS RECSZ = 5 + AIXKL + (n x BCKL)
AIX for ESDS RECSZ = 5 + AIXKL + (n x 4)
Where:
AIXKL is the alternate-key length
BCKL is the base cluster's prime-key length
n = 1 when UNIQUE KEY is specified
n = the number of data records in the base cluster that contain the same alternate-key value when NONUNIQUEKEY is specified.
Please correct me if I am wrong.
So, the below rule can be applied for ESDS.
Quote:
To deconstruct this: you have 2 bytes of something (and you don't have to know what it is), followed by a 2-byte occurrence count followed by 1 byte of something (again, you don't have to know what it is) followed by your primary key '66660' followed by 2 bytes of RBA followed by another 2 bytes of RBA. 5 + 5 + 4 + 4 is 18 bytes -- as I said, PRECISELY what you should have. For the 66661 record, you have 5 bytes of overhead plus the 5 bytes of primary key plus the 4 bytes for 1 occurrence of RBA, or a total of 14 bytes.
The RECSZ for the alternate index (assuming an average of 1 and a maximum of 32767 occurrences of the primary key) would be RECSZ(14,131078) -- as I said, the alternate index RECSZ has no relationship to the RECSZ(80,80) of the underlying cluster.
What will be the rule for KSDS in terms of the explanation which you showed -
Quote:
To deconstruct this: you have 2 bytes of something (and you don't have to know what it is), followed by a 2-byte occurrence count followed by 1 byte of something (again, you don't have to know what it is) followed by your primary key '66660' followed by 2 bytes of RBA followed by another 2 bytes of RBA. 5 + 5 + 4 + 4 is 18 bytes -- as I said, PRECISELY what you should have. For the 66661 record, you have 5 bytes of overhead plus the 5 bytes of primary key plus the 4 bytes for 1 occurrence of RBA, or a total of 14 bytes.
The RECSZ for the alternate index (assuming an average of 1 and a maximum of 32767 occurrences of the primary key) would be RECSZ(14,131078) -- as I said, the alternate index RECSZ has no relationship to the RECSZ(80,80) of the underlying cluster
From the formula, if the file is KSDS-
For 66660 - recsz 20
For 66661 - recsz 15
For 66662 - recsz 20
For 66668 - recsz 20
For 66669 - recsz 15
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
You have the formulas correct now.
For KSDS, let's make up some numbers: the base cluster key length (that is, the primary key length) is 17 bytes. The alternate index can have up to 40 occurrences and it is 29 bytes long. So the minimum alternate index record length will be 5 + 29 + 17, or 51 bytes while the maximum alternate index record length will be 5 + 29 + 17 * 40, or 714 bytes. The definition for the alternate index would have RECSZ(51,714) (assuming you expect most alternate indexes to be unique) and the alternate index record lengths would vary from 51 to 714 bytes depending on the number of repeats of the key that exist.
Quote:
From the formula, if the file is KSDS-
the first record - 20
the second record - 20
the third record - 20
the fourth record - 15
the fifth record - 15
the sixth record - 15
Please correct me if I am wrong.
I have no idea what you are trying to say here. The alternate index record lengths are based entirely upon the alternate index key length and the number of repetitions of the key in the various base cluster records, period. For a KSDS, you MUST know the length of the primary key and the length of the alternate key in order to calculate the record lengths of the alternate index records.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
For full information, the layout of the 5 overhead bytes is:
Code:
Byte 1 Flag byte (X'00' for ESDS, X'01' for KSDS)
2 Pointer length (X'04' for ESDS, length of primary key for KSDS)
3-4 Count of number of pointers
5 Key length of alternate index
so for my KSDS example earlier, the overhead bytes would be X'0111????1D' where ???? depends upon the number of repetitions.
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
Quote:
From the formula, if the file is KSDS-
RBA Len
0 20 66660
20 15 66661
35 15 66662
50 20 66668
70 85 66669
Again, what you posted makes absolutely no sense. For a KSDS, you MUST know the primary key length in order to determine where the alternate index records will start. They CERTAINLY would not have the lengths here!