Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using IDCAMS for creating Alternate index

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

New User


Joined: 18 Aug 2006
Posts: 43

PostPosted: Sun Oct 05, 2008 3:33 pm    Post subject: Using IDCAMS for creating Alternate index
Reply with quote

Hi All,
I have a requirement where i need to create a alternate index which has 2 variables included with it.This is the first time i am working on alternate index,so i am pretty confused as to how i need to create and code so that i can read the records from vsam file.Can anyone pls help me out in using IDCAMS for creating the Althernate index and the JCL used to run the program.Because i came to know that we need to have a Path file specified so can any one give me a model jcl.

Also i need to know how i can specify the following two variables as a part of my alternate index while using IDCAMs.
Variable name start pos Length
R86-CUSID 243 15
R86-CORP 278 5.

The other query i have is can we read the vsam file by using partial key(Alternate key i mean to say).Example:Suppose i have 2 variables part of alternate index and i know the value of one can i partially read it as we do with primary key using START and READ NEXT command.

Please help me out.
Back to top
View user's profile Send private message

genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Sun Oct 05, 2008 6:08 pm    Post subject:
Reply with quote

As far as i know.. you need to have adjacent positions of the file to make them as key. You can have one AIX for pos 243 length 15 and another AIX for pos 278 length 5. But they cannot be part of one AIX. They need to be in adjacent positions!

As per the syntax.. u can try like this.. there are other ways also..

First you get ur KSDS...

Code:

//STEP010 EXEC PGM=IDCAMS,REGION=5M               
//SYSPRINT DD  SYSOUT=*                           
//SYSIN    DD *                                   
  DELETE (TGE.RK.DEMOFILE.KSDS) CLUSTER PURGE     
    IF LASTCC > 8 THEN SET MAXCC = 16             
    ELSE SET MAXCC = 0                           
  DEFINE CLUSTER ( -                             
    NAME (TGE.RK.DEMOFILE.KSDS) -                 
    INDEXED) -                                   
    DATA ( -                                     
    NAME (TGE.RK.DEMOFILE.KSDS.D) -               
    CISZ (512) -                                 
    TRK (2 0) -                                   
    KEYS (4 0) -                                 
    RECORDSIZE (500 500)) -                       
    INDEX ( -                                     
    NAME (TGE.RK.DEMOFILE.KSDS.I) -               
    CISZ (512) -                                 
    TRK (1 0))                     
  IF LASTCC > 0 THEN SET MAXCC = 16
/*


Then define AIX and PATH for the KSDS

Code:

//STEP020 EXEC PGM=IDCAMS,COND=(0,NE)             
//SYSPRINT DD SYSOUT=*                             
//SYSOUT DD SYSOUT=*                               
//SYSIN DD *                                       
  DELETE TGE.RK.DEMOFILE.AIX ALTERNATEINDEX PURGE 
  IF LASTCC > 8 THEN SET MAXCC = 16               
   ELSE SET MAXCC = 0                             
  DEFINE ALTERNATEINDEX ( -                       
    NAME (TGE.RK.DEMOFILE.AIX) -                   
    RELATE (TGE.RK.DEMOFILE.KSDS) -               
    UPGRADE )-                                     
    DATA ( -                                       
    NAME (TGE.RK.DEMOFILE.AIX.D) -                 
    CISZ (4096) -                                 
    TRK (3 1) -                                   
    KEY (1 5) -                                   
    RECORDSIZE (500 500) -                         
    NUNQK ) -                                     
    INDEX ( -                           
    NAME (TGE.RK.DEMOFILE.AIX.I) -       
    CISZ (512) -                         
    TRK (3 1) -                         
    NOIMBED )                           
  DEFINE PATH ( -                       
    NAME (TGE.RK.DEMOFILE.PATH) -       
    PATHENTRY (TGE.RK.DEMOFILE.AIX) -   
    UPDATE )                             
/*                                       


Then just build the AIX

Code:

//STEP030 EXEC PGM=IDCAMS,COND=(0,NE)       
//SYSPRINT DD SYSOUT=*                       
//SYSOUT DD SYSOUT=*                         
//INDD DD DSN=TGE.RK.DEMOFILE.KSDS,DISP=SHR 
//OUTDD DD DSN=TGE.RK.DEMOFILE.AIX,DISP=OLD 
//SYSIN DD *                                 
  BLDINDEX INFILE(INDD) OUTFILE(OUTDD)       
/*                                           
Back to top
View user's profile Send private message
akshathan

New User


Joined: 18 Aug 2006
Posts: 43

PostPosted: Sun Oct 05, 2008 6:48 pm    Post subject: Reply to: Alternate index
Reply with quote

Hi Genesis786,
Thanks for your reply.

Assume that i need to read a record with R86-CORP=10 and R86-CUSID=456, Then by building two AIX's is it possible,if yes can you please tell me the way.Also can you tell me how you will refer to the Vsam file with alternate index in your JCL(used to execute the program).

Regards,
Akshatha
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sun Oct 05, 2008 8:19 pm    Post subject: Re: Reply to: Alternate index
Reply with quote

akshathan wrote:
Also can you tell me how you will refer to the Vsam file with alternate index in your JCL(used to execute the program).
Via the PATH....
Code:
  DEFINE PATH ( -                       
    NAME (TGE.RK.DEMOFILE.PATH) -       
    PATHENTRY (TGE.RK.DEMOFILE.AIX) -   
    UPDATE )                             
//filename DD TGE.RK.DEMOFILE.PATH....
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Sun Oct 05, 2008 8:22 pm    Post subject:
Reply with quote

Your program logic would have to read one of the AIX and then sequentially read until you find a match on the other key. It would be a lot easier to create a single field to do what you want.

One point I don't see mentioned in the genesis post: alternate indexes cannot be defined until the base file has at least one record in it. You must repro in a record before doing the DEFINE AIX.

Also, alternate index record sizes are calculated differently than base record sizes and you need consider how many duplicates you can have in setting the AIX record size. Do not just copy the base file record size as it's not likely to be right.

In JCL, each AIX is referenced by the same DD name as the base file but with a 1 appended to the DD name for the first AIX, 2 for the second AIX, and so forth. The DSN for an AIX is the PATH name for the AIX, not AIX cluster name. So for example you could have:
Code:
//VSAMIN  DD DISP=SHR,DSN=<BASE>
//VSAMIN1 DD DISP=SHR,DSN=<PATH1>
//VSAMIN2 DD DISP=SHR,DSN=<PATH2>
Back to top
View user's profile Send private message
akshathan

New User


Joined: 18 Aug 2006
Posts: 43

PostPosted: Sun Oct 05, 2008 10:18 pm    Post subject: Reply to: Alternate index
Reply with quote

Hi Robert,
In the program logic,can i move two variables for which i am having 2 AIX and then read by using Read command.Will it work?.Because these two variables for which i want AIX are not adjacent hence i need to define two AIX.So can i do like populating two AIX with the values and read from Vsam.Please let me know whether its correct.

Also in the JCL you have specified we have to use that base cluster ,and the Paths of AIX.My doubt here is whether we will have only one SELECT statement for base cluster in cobol program or for all the three.

Apart from the above doubts when i am executing the JCL to create AIX its giving the error
3561 EXCESS PRIME KEY VALUE VALUES FOR AIX KEY F0F0F0F0F24040404040 while building the index.
My base cluster Record size is (750 782)
I am not aware of the criteria on which i need to increase the size of the record in the alternate index(definig AIX).

Please help me.

Regards,
Akshatha
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Oct 05, 2008 10:43 pm    Post subject:
Reply with quote

Hello,

Quote:
In the program logic,can i move two variables for which i am having 2 AIX and then read by using Read command.Will it work?.
I believe not. . .

If you post the define for the file and how you created the alternate index entries it may help us help you. When posting these, use the copy/paste and "Code" tag (at the top of the Reply panel). Do not try to use a "screenshoot" as they are deleted. Once you have created your post, use Preview to see your post as it will appear to the forum. When you are satisfied with the way your post looks, click Submit.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Sun Oct 05, 2008 11:44 pm    Post subject:
Reply with quote

You'll have one SELECT with multiple ALTERNATE INDEX phrases. When you do the READ, you specify a key value -- ONE key value -- so you cannot put values in both AIX fields and read based on both record keys.

An alternate index record size is defined in terms of how many duplicate base cluster keys can exist. The manual formula for calculating is:
Quote:
A key sequenced base cluster



RECSZ = 5 + AIXKL + (n x BCKL)
so take 5 plus the alternate index key length plus the total base cluster key lengths. So if your alternate index is 20 bytes and the base cluster key length is 12, and you expect 150 duplciates, you'd take 5 + 20 + 150 * 12 or 1825 as the maximum AIX record length; the average would be whatever you think the average number of duplicates is, plugged into this formula.
Back to top
View user's profile Send private message
akshathan

New User


Joined: 18 Aug 2006
Posts: 43

PostPosted: Sun Oct 05, 2008 11:57 pm    Post subject: Reply to: Alternate index
Reply with quote

Hi Robert,
If we are populating the value to only one variable(1 AIX) then wat is the use of defining multiple ALTERNATE INDEX clause in the SELECT statement.Can we use Two AIX's at the same time while doing the read.
You mean to say i should read the vsam record using one AIX and search for the other variable values by making a sequential read.

Plese help me.
Back to top
View user's profile Send private message
akshathan

New User


Joined: 18 Aug 2006
Posts: 43

PostPosted: Mon Oct 06, 2008 12:02 am    Post subject: Reply to: Alternate index
Reply with quote

Hi Dick,
I have copied the code.Please correct me if i am worng.
Code:

DEFINE CLUSTER -                                   
    (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -     
      VOL(SYSDA) -                                 
      INDEXED -                                   
      SPEED -                                     
      NOIMBED -                                   
      NOREUSE -                                   
      NOREPLICATE -                               
      NOWRITECHECK-                               
      SHR(2 3)-                                   
      CYL(150 25) -                               
      RECSZ (750 782) -                           
      FSPC (9 08) -                               
      KEYS (56 0) -                               
    ) -                                           
 DATA -                                           
    (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.DATA) -
/*-------------------------------------------------------*/
/*    REPRO   FROM BACKUP COPY TO NEW DATASET            */
/*-------------------------------------------------------*/
      REPRO   INFILE(SYSUT1)                    -         
          OUTDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410)   
/*-------------------------------------------------------*/
/*    DEFINE  AIX #1 (KEY  IS  CUSID)                    */
/*-------------------------------------------------------*/
      DELETE  XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI1 ALTERNATEINDEX -
           PURGE                                                   
      DEFINE  ALTERNATEINDEX                    -                   
        (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI1) -             
              CONTROLINTERVALSIZE(6144)         -                   
              KEYS(15 243)                      -                   
              RECORDS(500000 50000)             -                   
              RECORDSIZE(750 782)               -                   
              RELATE(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -           
              SHAREOPTIONS(2 3))                                   
      IF LASTCC GT 4                            -                   
         THEN SET MAXCC = 16;                                       
/*-------------------------------------------------------*/         
/*    BUILD   INDEX #1                                   */         
/*-------------------------------------------------------*/         
      BLDINDEX                                  -                   
             INDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -     
             OUTDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI1);
      IF LASTCC GT 4                            -               
         THEN SET MAXCC = 16;                                   
/*-------------------------------------------------------*/     
/*    DEFINE  PATH FROM BASE TO AIX #1                   */     
/*-------------------------------------------------------*/     
      DEFINE PATH                               -               
             (NAME(XX1.SMS.XAR.CONTROL.AR3002.DT0410.PATH1)     
              PATHENTRY(XX1.SMS.XAR.CONTROL.AR3002.DT0410.AI1));

Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Mon Oct 06, 2008 1:05 am    Post subject:
Reply with quote

Primary key is 56 bytes; AIX key is 15 bytes. The AIX maximum record size needs to be 5 + 56 + n times 15 bytes, where n is how many duplicates you expect. From the earlier error message, 750 782 isn't big enough. I would try a record size of (811 7561) for the alternate index to see how that fits.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Mon Oct 06, 2008 1:07 am    Post subject:
Reply with quote

My earlier response was based on
Quote:
for which i am having 2 AIX
; if you only have one AIX you only need one ALTERNATE KEY phrase in the COBOL program.
Back to top
View user's profile Send private message
akshathan

New User


Joined: 18 Aug 2006
Posts: 43

PostPosted: Mon Oct 06, 2008 1:48 am    Post subject: Reply to: Alternate index
Reply with quote

Hi Robert,
Please disregard my previous code post.Do consider this new code.
In this i have increased the record size for AIX1 and its working fine,but for AIX2 even if i am giving 699436 its giving excess prime key error.If i am increasing the recordsize morethan this then its giving vsam catalog error.Please look in to the code and help me so that a correctly build index for AIX2.
Code:

/*-------------------------------------------------------*/
/*    DELETE  CLUSTER AND ALL ASSOCIATED FILES           */
/*-------------------------------------------------------*/
  DELETE   XX1.SMS.XAR.CASHFIL.AR3001.DT0410 CLUSTER PURGE 
  DEFINE CLUSTER -                                         
      (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -           
        VOL(SYSDA) -                                       
        INDEXED -                                           
        SPEED -                                             
        NOIMBED -                                           
        NOREUSE -                                           
        NOREPLICATE -                                       
        NOWRITECHECK-                                       
        SHR(2 3)-                                           
        CYL(150 25) -                                       
        RECSZ (750 782) -                                   
        FSPC (9 08) -                                     
        KEYS (56 0) -                                     
      ) -                                                 
   DATA -                                                 
      (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.DATA) -     
       CISZ(2048) -                                       
      ) -                                                 
   INDEX -                                                 
      (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.INDEX) -     
       CISZ(2048) -                                       
      )                                                   
      IF LASTCC GT 8                            -         
         THEN SET MAXCC = 16;                             
/*-------------------------------------------------------*/
/*    REPRO   FROM BACKUP COPY TO NEW DATASET            */
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
      REPRO   INFILE(SYSUT1)                    -         
          OUTDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410)   
/*-------------------------------------------------------*/
/*    DEFINE  AIX #1 (KEY  IS  CUSID)                    */
/*-------------------------------------------------------*/
      DEFINE  ALTERNATEINDEX                    -         
        (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI1) -     
              CONTROLINTERVALSIZE(6144)         -         
              KEYS(15 243)                      -         
              RECORDS(500000 50000)             -         
              RECORDSIZE(3000 399436)        -             
              UPGRADE                        -             
              RELATE(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) - 
              SHAREOPTIONS(2 3))                           
      IF LASTCC GT 4                            -         
         THEN SET MAXCC = 16;                             
/*-------------------------------------------------------*/       
/*    BUILD   INDEX #1                                   */       
/*-------------------------------------------------------*/       
      BLDINDEX                                  -                 
             INDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -       
             OUTDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI1);   
      IF LASTCC GT 4                            -                 
         THEN SET MAXCC = 16;                                     
/*-------------------------------------------------------*/       
/*    DEFINE  PATH FROM BASE TO AIX #1                   */       
/*-------------------------------------------------------*/       
      DEFINE PATH                               -                 
             (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.PATH1) -     
              PATHENTRY(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI1))   
/*-------------------------------------------------------*/       
/*    DEFINE  AIX #2 (KEY  IS  CORP)                     */       
/*-------------------------------------------------------*/   
      DEFINE  ALTERNATEINDEX                    -             
        (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2) -       
              CONTROLINTERVALSIZE(6144)         -             
              KEYS(5 278)                      -             
              UPGRADE                          -             
              RECORDS(500000 50000)             -             
              RECORDSIZE(4000 699436)           -             
              RELATE(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -     
              SHAREOPTIONS(2 3));                             
      IF LASTCC GT 4                            -             
         THEN SET MAXCC = 16;                                 
/*-------------------------------------------------------*/   
/*    BUILD   INDEX #2                                   */   
/*-------------------------------------------------------*/   
      BLDINDEX                                  -             
             INDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -     
             OUTDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2); 
      IF LASTCC GT 4                            -               
         THEN SET MAXCC = 16;                                   
/*-------------------------------------------------------*/     
/*    DEFINE  PATH FROM BASE TO AIX #2                   */     
/*-------------------------------------------------------*/     
      DEFINE PATH                               -               
             (NAME(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.PATH2) -   
              PATHENTRY(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2));



The excess primary key error message
Code:

 /*-------------------------------------------------------*/         
 /*    BUILD   INDEX #2                                   */         
 /*-------------------------------------------------------*/         
       BLDINDEX                                  -                   
              INDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410) -         
              OUTDATASET(XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2);     
IDC1646I 94091 EXCESS PRIME KEY VALUES FOR AIX KEY F040404040       
IDC1646I 63477 EXCESS PRIME KEY VALUES FOR AIX KEY F540404040       
IDC1653I XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2 BUILT WITH ERRORS     
IDC0001I FUNCTION COMPLETED, HIGHEST CONDITION CODE WAS 4           


The vsam catalog error message
Code:

IGD17103I CATALOG ERROR WHILE DEFINING VSAM DATA SET                 
XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2                                 
RETURN CODE IS 96 REASON CODE IS 4 IGG0CLEQ                           
IGD17219I UNABLE TO CONTINUE DEFINE OF DATA SET                       
XX1.SMS.XAR.CASHFIL.AR3001.DT0410.AI2                                 
IDC3014I CATALOG ERROR                                               
IDC3009I ** VSAM CATALOG RETURN CODE IS 96 - REASON CODE IS IGG0CLEQ-4
IDC3003I FUNCTION TERMINATED. CONDITION CODE IS 12                   


The two fields which i am referring two are ver much needed for reading the required record.Since they are not placed adjacently i am creating two AIX so that i can read the required record at once.So i just want to know how i can read an exact record by using the two AIX.

Regards,
Akshatha
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Oct 06, 2008 2:21 am    Post subject:
Reply with quote

Hello,

Quote:
Since they are not placed adjacently i am creating two AIX so that i can read the required record at once.So i just want to know how i can read an exact record by using the two AIX.

Robert has already explained that you cannot do that.
Quote:
When you do the READ, you specify a key value -- ONE key value -- so you cannot put values in both AIX fields and read based on both record keys.


You will have to use the file as it is and code around the issue or you will have to go thru the (probably large) amount of work to change the vsam file so the fields are adjacent.
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 Creating an 'Enter' event with IMS MF... dileep chirasani Java & MQSeries 1 Sat Aug 06, 2016 2:23 am
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts creating a physical sequential file i... Shaheen Shaik JCL & VSAM 8 Wed Jun 29, 2016 3:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us