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
 

 

Extracting the Primary key from a VSAM

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Extracting the Primary key from a VSAM
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    Post subject: Reply to: Extracting the Primary key from a VSAM
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    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Oct 17, 2011 11:28 pm    Post subject:
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:

http://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    Post subject:
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    Post subject:
Reply with quote

Anyone here... Waiting for your response.....
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Oct 18, 2011 7:38 pm    Post subject: Reply to: Extracting the Primary key from a VSAM
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Tue Oct 18, 2011 9:53 pm    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Oct 18, 2011 10:51 pm    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Oct 18, 2011 10:56 pm    Post subject:
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: 578
Location: USA

PostPosted: Wed Oct 19, 2011 12:09 am    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts VSAM define for large file jerryte JCL & VSAM 9 Wed Oct 05, 2016 1:51 am
No new posts CICS VSAM RLS READ akodakka CICS 3 Tue Oct 04, 2016 3:09 pm
No new posts SFTP to primary and secondary servers mallik4u JCL & VSAM 4 Tue Jun 07, 2016 12:52 pm
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am


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