|
View previous topic :: View next topic
|
| Author |
Message |
akshathan
New User
Joined: 18 Aug 2006
Posts: 45
|
|
|
|
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 |
|
 |
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
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 |
|
|
akshathan
New User
Joined: 18 Aug 2006
Posts: 45
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| 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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
akshathan
New User
Joined: 18 Aug 2006
Posts: 45
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
akshathan
New User
Joined: 18 Aug 2006
Posts: 45
|
|
|
|
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 |
|
|
akshathan
New User
Joined: 18 Aug 2006
Posts: 45
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
akshathan
New User
Joined: 18 Aug 2006
Posts: 45
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
