IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Extracting the Primary key from a VSAM
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Fri Oct 14, 2011 8:24 pm
 Reply with quote
I need to extract the Primary key of the VSAM file1, using a Flat file2 data.

VSAM file1 has a Variable Record Format with Max. Length of 1120.

VSAM file1 Record Layout--
Code:
Filler ------ (Start Byte from 1 to 30)
********VSAM PRIMARY KEY STARTS........********
INSTITUTION               9(9) COMP-3        (Start Byte from 31 to 35)
VCUSTOMER                 9(10)              (Start Byte from 36 to 45)
VRECORD-TYPE              X                  (Start Byte from 46 to 46)
VRECORD-NUMBER            9(4)               (Start Byte from 47 to 50)
********VSAM PRIMARY KEY ENDS........ ********
Filler ------ (Start Byte from 51 to 233)
V-POOL-NO                       9(3)               (Start Byte from 234 to 236)
Filler ------ (Start Byte from 233 to END)



Flat file2 has a Fixed Record Format with Record Length of 80.

Flat file2 Record Layout--
Code:
FCUSTOMER                     9(10)              (Start Byte from 1 to 10)
Filler                        X(06)              (Start Byte from 11 to 16)
F-POOL-NO                     9(3)               (Start Byte from 17 to 19)
Filler ------ (Start Byte from 20 to 80)


Extract VSAM file1 Primary key into output Flat file3 into readable format with below Layout---
Flat file3 Record Layout--
Code:
INSTITUTION                9(9)              (Start Byte from 1 to 09)
CUSTOMER                   9(10)             (Start Byte from 10 to 19)
RECORD-TYPE                X(01)             (Start Byte from 19 to 19)
RECORD-NUMBER              9(4)              (Start Byte from 20 to 23)
Filler ------ (Start Byte from 20 to 80)


Matching Conditions----

Code:
VCUSTOMER == FCUSTOMER
V-POOL-NO  == F-POOL-NO


Let me know if you have any concern.
Thanks for your support as always.

Regards,
Deepak
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Oct 14, 2011 10:21 pm
 Reply with quote
deepak_munjal wrote:
Let me know if you have any concern.


I don't have any concern but have a question.

You have overlapping fields in your output.Record-type field overlays the Customer field's last byte. I am assuming that it is a typo.

The following DFSORT JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//INA      DD DISP=SHR,DSN=your input vsam cluster
//INB      DD DISP=SHR,DSN=Your input FB 80 file
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                               
  OPTION COPY                                 
  JOINKEYS F1=INA,FIELDS=(36,10,A,234,3,A)   
  JOINKEYS F2=INB,FIELDS=(01,10,A,017,3,A)   
  REFORMAT FIELDS=(F1:31,24)                 
  INREC BUILD=(1,5,PD,ZD,LENGTH=9,6,15,80:X)
//*
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Mon Oct 17, 2011 3:25 pm
 Reply with quote
Thanks Kolusu! Working as expected! icon_smile.gif

But one more things comes up... Just wonder if we have any option in the same Sort step to have all duplicates rectified in a seperate file.

Like....

In Above out put data is looks like--

Code:
0000003010000893057V0001
0000003010000893057V0001
0000003010000893057V0001
0000003010000893057V0002
0000003010000893057V0002
0000003010000893057V0002
0000003010000893057V0003
0000003010000893057V0003
.
.
.



Filetering Duplicates means Output looks like---

Code:
0000003010000893057V0001
0000003010000893057V0002
0000003010000893057V0003


Output file having Duplicate records copied looks like----

Code:
0000003010000893057V0001
0000003010000893057V0001
0000003010000893057V0002
0000003010000893057V0002
0000003010000893057V0003
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Mon Oct 17, 2011 11:28 pm
 Reply with quote
You can use the SELECT operator of DFSORT's ICETOOL to do that. See the "Keep dropped duplicate records (XSUM)" Smart DFSORT Trick at:

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000094
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Tue Oct 18, 2011 3:45 pm
 Reply with quote
Thanks Kolusu & Frank for your assistance...
I am sorry, I missed one more condition in the above---

Matching Conditions---- Should be

Code:
VCUSTOMER == FCUSTOMER
V-POOL-NO  == F-POOL-NO
VRECORD-TYPE  == V       



Please let me know how i can include this condition "VRECORD-TYPE == V" in the above SORT solution....

Many Thanks...
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Tue Oct 18, 2011 7:32 pm
 Reply with quote
Anyone here... Waiting for your response.....
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10873
Location: italy
PostPosted: Tue Oct 18, 2011 7:38 pm
 Reply with quote
Quote:
Anyone here... Waiting for your response.....


DO NOT PESTER

You should have realized for a while that Frank and Kolusu live and work in a different time zone

if You have time constraint You might want to peruse a different type of consultancy ( FEE ) icon_evil.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
PostPosted: Tue Oct 18, 2011 7:38 pm
 Reply with quote
well I am sorry that I took four hours to respond.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
PostPosted: Tue Oct 18, 2011 7:50 pm
 Reply with quote
I looked to make sure that you could have multiple keys,
(can never tell, 2 may have been the maximum)
and sure enough, the manual had an
explanation of FIELDs in the JOINKEYS statement

and incase you are incapable of reading a syntax diagram,
here is the link for the 'repeat symbol'
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Tue Oct 18, 2011 8:41 pm
 Reply with quote
Sorry Enrico.... for to be impatient.... icon_redface.gif
Thanks Dick.. You made my day.... Tutorial link is very informational....
I have included the third condition now... Working fine..

Last thing... As frank said above.. to remove duplicates we can use SELECT OPERATOR under ICETOOL utility.. But I think it will take all the dups out of the main Output file.. For Example....

If input files looks like----
Code:
11
12
12
14
14
14
16


Output file would looks like----

Code:
16


But acc. to my requirement it should looks like----

Code:
11
12
14
16


Which I think can be done using XSUM .... Which it seems is not supported in my mainframe environment as shown below---

Code:
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 15:59 ON T
               SORT FIELDS=(1,24,CH,A)                                     
               SUM FIELDS=NONE,XSUM                                       
                               £                                           
ICE172A E XSUM IS NOT SUPPORTED - USE ICETOOL SELECT IF APPROPRIATE       
ICE751I 0 C5-K51707 C6-K51707 C7-K54603 C8-K51707 E7-K51707               
ICE052I 3 END OF DFSORT                                                   
******************************** BOTTOM OF DATA ***************************
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Tue Oct 18, 2011 9:23 pm
 Reply with quote
Let me know if using an other way can give the desired result...
Also, I will wonder if we can do this within the 1st Sort Step(Sulotion given above by Skolusu)..... !!!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Tue Oct 18, 2011 9:53 pm
 Reply with quote
Hello,

Quote:
I will wonder if we can do this within the 1st Sort Step(Sulotion given above by Skolusu)
What have you tried while you are sitting and waiting?

Trying things is free and will cause no damage. . .
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Oct 18, 2011 10:51 pm
 Reply with quote
Quote:
Last thing... As frank said above.. to remove duplicates we can use SELECT OPERATOR under ICETOOL utility.. But I think it will take all the dups out of the main Output file.. For Example....


Either you didn't bother to read the Smart DFSORT Trick I pointed you to or you didn't understand it. It shows this example:

Code:

//XSUM JOB ...
//DOIT EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=... input data set
//OUT DD DSN=... first record with each key
//SORTXSUM DD DSN=... subsequent records with each key
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,3,CH) FIRST DISCARD(SORTXSUM)
/*


Which will do what you asked for if you use the correct ON field.

Please look at that Smart DFSORT Trick as it explains what this does.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Oct 18, 2011 10:56 pm
 Reply with quote
Quote:
Also, I will wonder if we can do this within the 1st Sort Step(Sulotion given above by Skolusu)..... !!!


Probably, but I'm not clear on exactly what it is you're trying to do so you'd have to explain it more clearly and show a good example of input and expected output.

Note: Kolusu is out of the office on personal business for a while.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Oct 19, 2011 12:09 am
 Reply with quote
deepak_munjal,
See if below UNTESTED sort card helps...

If I understand your requirement clearly, after matching both the files, you are trying to select unique records for key fields and would also like to retain dups in a separate file. If you don't want dups be retained in a separate file take off second OUTFIL FNAMES condition from below.

FYI... I don't see a sample input data to test below and hence UNTESTED...
Code:
//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//INA      DD DISP=SHR,DSN=your input vsam cluster
//INB      DD DISP=SHR,DSN=Your input FB 80 file
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                               
  OPTION COPY                                 
  JOINKEYS F1=INA,FIELDS=(36,10,A,234,3,A)   
  JOINKEYS F2=INB,FIELDS=(01,10,A,017,3,A)   
  REFORMAT FIELDS=(F1:31,24)                 
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,5,PD,ZD,LENGTH=9,6,15,80:X))
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,24),PUSH=(81:SEQ=8))
  OUTFIL FNAMES=UNQ,INCLUDE=(81,8,ZD,EQ,1),BUILD=(1,80)
  OUTFIL FNAMES=DUP,SAVE,BUILD=(1,80)
//*


Thanks,
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai
PostPosted: Wed Oct 19, 2011 4:07 pm
 Reply with quote
Thanks a lot guys!!

I have implemented the XSUM Sort trick(As Frank suggested).. and get the desired result now..

Thanks as always...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us