Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Incorrect output after build alternate index

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sandeep kumar302

New User


Joined: 14 Mar 2012
Posts: 81
Location: India

PostPosted: Wed May 02, 2018 2:35 pm    Post subject: Incorrect output after build alternate index
Reply with quote

Hi,

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

Step1: I created ESDS cluster as below -
Code:

//STEP01   EXEC    PGM=IDCAMS                 
//SYSPRINT  DD SYSOUT=*                       
//SYSOUT   DD SYSOUT=*                         
//SYSIN    DD *                               
  DEFINE CLUSTER(NAME(HKTPTS.ESDS.CLUSTER)    -
  FREESPACE(0,0)        -                     
  TRACKS(2,4)             -                   
  RECSZ(80,80)            -                   
  CISZ(4096)              -                   
  VOLUMES(ZASYS1)         -                   
  NONINDEXED)             -                   
  DATA(NAME(HKTPTS.ESDS.DATA))                 
/*                                             


Step2- I loaded the cluster with REPRO command and the records are populated as below -

Code:

 RBA        Len     1...5...10....5...20....5...30....5...40..
 0            80    12345 990 66660                           
 80           80    12346 991 66660                           
 160          80    12347 992 66661                           
 240          80    12348 993 66662                           
 320          80    12349 994 66668                           
 400          80    12350 995 66668                           
 480          80    12351 996 66669                           
 ****  End of data  ****                                     

Step3: I create alternate index as below. The keys are of length 5 and offset 10. Example - 66660, 66661, 66662 and so on
Code:

//STEP01   EXEC    PGM=IDCAMS           
//SYSPRINT  DD SYSOUT=*                 
//SYSOUT   DD SYSOUT=*                 
//SYSIN    DD *                         
  DEFINE AIX(NAME(HKTPTS.ESDS.AIX)    -
  RELATE(HKTPTS.ESDS.CLUSTER) -         
  NONUNIQUEKEY            -             
  KEYS(5,10)              -             
  UPGRADE                 -             
  FREESPACE(10,20)        -             
  RECSZ(80,80)            -             
  TRACKS(2,1)             -             
  CISZ(4096)              -             
  VOLUMES(ZASYS1))                     
/*                                     


Step4: I created the Path using -

Code:

   DEFINE PATH                         -
   (NAME(HKTPTS.ESDS.AIX.PATH) -       
   PATHENTRY(HKTPTS.ESDS.AIX))         


Step5: I try to build index using-
Code:

   BLDINDEX  INFILE(DD1)    OUTFILE(DD2)                           


All the steps are successful with MAXCC=0 but the build index gives me wrong output:

Code:

RBA        Len     1...5<===>....5...20....5...30....5...40..
0            18    .....66660.......&                       
18           14    .....66661....                           
32           14    .....66662...0                           
46           18    .....66668... ....                       
64           14    .....66669...\                           
****  End of data  ****                                     


I am not able to figure out where I am doing a mistake?
Back to top
View user's profile Send private message

enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10487
Location: italy

PostPosted: Wed May 02, 2018 2:58 pm    Post subject: Reply to: Incorrect output after build alternate index
Reply with quote

why do You think so icon_question.gif

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

see here for a full test of building an aix over an ESDS
http://ibmmainframes.com/viewtopic.php?t=29269&postdays=0&postorder=asc&start=15
Back to top
View user's profile Send private message
sandeep kumar302

New User


Joined: 14 Mar 2012
Posts: 81
Location: India

PostPosted: Wed May 02, 2018 3:36 pm    Post subject:
Reply with quote

Hi Enrico,

Thanks for the prompt reply.

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.

The hex value of the build index is -
Code:

000001 .....66660.......&
       00000FFFFF00000005
       040256666000000000
       ----+<===>----+---
                         
000002 .....66661....     
       00000FFFFF000A     
       04015666610000     
       ----+<===>----     
                         
000003 .....66662...0     
       00000FFFFF000F     
       04015666620000     
       ----+<===>----     
                         
000004 .....66668... ....
       00000FFFFF00040009
       040256666800100010     
       ----+<===>----+---     
                               
000005 .....66669...\         
       00000FFFFF000E         
       04015666690010         
       ----+<===>----         
                               
****** ****  End of data  ****
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8359
Location: Dubuque, Iowa, USA

PostPosted: Wed May 02, 2018 5:08 pm    Post subject: Reply to: Incorrect output after build alternate index
Reply with quote

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).
Back to top
View user's profile Send private message
sandeep kumar302

New User


Joined: 14 Mar 2012
Posts: 81
Location: India

PostPosted: Wed May 02, 2018 6:01 pm    Post subject:
Reply with quote

Hi Robert,

My bad. 14 against the length, not RBA.

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8359
Location: Dubuque, Iowa, USA

PostPosted: Wed May 02, 2018 6:35 pm    Post subject:
Reply with quote

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.
Code:
000001  .....66660.......&
        00000FFFFF00000005
        040256666000000000
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.
Back to top
View user's profile Send private message
sandeep kumar302

New User


Joined: 14 Mar 2012
Posts: 81
Location: India

PostPosted: Wed May 02, 2018 8:14 pm    Post subject:
Reply with quote

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

Please correct me if I am wrong.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8359
Location: Dubuque, Iowa, USA

PostPosted: Wed May 02, 2018 8:26 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
sandeep kumar302

New User


Joined: 14 Mar 2012
Posts: 81
Location: India

PostPosted: Wed May 02, 2018 8:30 pm    Post subject:
Reply with quote

Ok. I got it.

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
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8359
Location: Dubuque, Iowa, USA

PostPosted: Wed May 02, 2018 8:37 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8359
Location: Dubuque, Iowa, USA

PostPosted: Wed May 02, 2018 8:40 pm    Post subject:
Reply with quote

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!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Help to Create 4 Output Records ... satish.ms10 DFSORT/ICETOOL 4 Thu Jul 19, 2018 1:55 am
No new posts INCLUDE 2 conditions to copy in the o... Chiku kumar DFSORT/ICETOOL 10 Tue Jul 03, 2018 7:11 pm
No new posts Index related queries Poha Eater DB2 5 Thu Jun 28, 2018 8:36 pm
No new posts Can we use a file both as input and o... Chiku kumar COBOL Programming 11 Thu Jun 28, 2018 3:30 pm
No new posts Formating output file Niki DFSORT/ICETOOL 3 Fri Jun 15, 2018 3:27 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us