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
 

 

JCL - SORT JOINKEY or ICETool help need

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
a210955

New User


Joined: 07 Nov 2006
Posts: 4

PostPosted: Sat Nov 19, 2011 2:44 am    Post subject: JCL - SORT JOINKEY or ICETool help need
Reply with quote

I’m looking for a SORT Joinkey Jcl or ICETool JCL solution for the following scenario. Can anyone please help me.

I have 4 VB flat files:
File1 – RCfile – Parent File to FILE2,FILE3,FILE4
Key (7,5,CH,A,12,4,CH,A)

File2 – RDfile – Child of File1
Key (7,5,CH,A,32,4,CH,A)

File3 – RGfile – Child of File1
Key (7,5,CH,A,16,4,CH,A)

File4 – VRfile – Child of File1
Key (7,5,CH,A,12,4,CH,A)



I want to extract into separate out files on records from File2,File3 and File4 only if they have a matching Parent record on File1. I want to leave have all the files in the same format in/out. And I want to sort the new output files as follows:

Outfile - File2 sort: (7,29,CH,A)
Outfile - File3 sort: (7,13,CH,A,21,2,FI,A)
Outfile - File4 sort: (7,13,CH,A,20,2,FI,A)
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Nov 19, 2011 3:15 am    Post subject:
Reply with quote

Hi, i am looking for that dark-haired young women that just passed me in the hall.
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: Sat Nov 19, 2011 3:21 am    Post subject:
Reply with quote

a,

You need to do a better job of explaining what it is you want to do. Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If any file (e.g. file1) can have duplicates within it, show that in your example.
Back to top
View user's profile Send private message
a210955

New User


Joined: 07 Nov 2006
Posts: 4

PostPosted: Sat Nov 19, 2011 9:10 am    Post subject: Reply to: JCL - SORT JOINKEY or ICETool help need
Reply with quote

Thanks Dick, hopefully this is a bit more clear:
I have 4 QSAM files. Attributes are as follows :

File 1: LRECL 434 recfm VB
Match all other files to this:
Start pos: 7 len 5 + start pos 12 len 4

File 2 : LRECL 116 recfm VB
Start pos: 7 len 5 + start pos 32 len 4

File 3: LRECL 89 recfm VB
Start pos: 7 len 5 + start pos 16 len 4

File 4: LRECL – 196 recfm VB
Start pos: 7 len 5 + start pos 12 len 4

FILE1

111111AAAAABOBO110001XXXX2090000015XX
222222BBBBBCOCO100001XXXX7300500880XX
333333CCCCCDODO100001XXXX7300500886XX
444444DDDDDEOEO100001XXXX7390000010XX AAAAA1 XXXXXXXXX
555555EEEEEFOFO90000010XX000000000000 215220100001 EVERGREE
666666FFFFFGOGO90000010XX0000217026 215220100001 MIKE REE

FILE2
111111AAAAA11111111111111111111BOBO
444444DDDDD11111111111111111111EOEO
777777CCCCC11112222222222222222EOEO

FILE3
111111AAAAA1111BOBO
444444GDDDD1111EOEO
777777CCCCC1111DODO

FILE4
111111KKKKKBOBO110001XXXX2090000015XX
222222ZZZZZCOCO100001XXXX7300500880XX AAAAAAA01OTOMLP489
333333SSSSSDODO100001XXXX7300500886XX


NEW OUTFILES

FILE 2 Also sort: (7,29,CH,A)
111111AAAAA11111111111111111111BOBO
444444DDDDD11111111111111111111EOEO

FILE3 also sort (7,13,CH,A,21,2,FI,A)
111111AAAAA1111BOBO
777777CCCCC1111DODO

FILE 4 also sort: (7,13,CH,A,20,2,FI,A) - if there was data found
EMPTY FILE NOTHING MATCHED FILE 1
Back to top
View user's profile Send private message
kratos86

Active User


Joined: 17 Mar 2008
Posts: 148
Location: Anna NGR

PostPosted: Sat Nov 19, 2011 10:08 am    Post subject:
Reply with quote

Try JOINKEYS... we have plenty of examples in this forum.
Back to top
View user's profile Send private message
a210955

New User


Joined: 07 Nov 2006
Posts: 4

PostPosted: Sat Nov 19, 2011 6:40 pm    Post subject:
Reply with quote

I have looked at the examples and can't quite locate on that does what I need with multiple files in one step. I'm experimenting with some of the samples. If I get it, I'll be sure to post the sample for everyone to enjoy. Thanks..
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 Nov 22, 2011 12:21 am    Post subject:
Reply with quote

a,

I would use three JOINKEYS passes - it could be done in one ICETOOL step.

If you want me to show you how, I need some more information:

Are the records in input file1 already in sorted order as shown in your example? Can there be duplicates in file1?

Same question for input file2, input file3 and input file 4.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Nov 22, 2011 4:00 am    Post subject: Reply to: JCL - SORT JOINKEY or ICETool help need
Reply with quote

a210955,

The following DFSORT JCL will give you the desired results. It does the match and also sorts on the file 2, 3, 4 also.

Step0100 : Creates a VB delimiter file with delimiter $$$ in pos 5 (first 4 bytes of rdw).


Step0200 : We now use above created file and concatenate files 2,3,4 to perform the match. The $$$ ind will be used to distinguish file2, 3, 4. Using the WHEN=GROUP function, we pad an indicator at pos 5 and use that indicator to populate the key values from different places from the respective files. Since all the files are VB we manipulate the key right after RDW. Once we get the Matching records we then overlay the fields from different files to the sort main task based on the same ind.

Using OUTFIL we remove the indicator and temp fields and write out the actual file data from the respective files.


Code:


//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                             
//SORTOUT  DD DSN=&&HDR,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)
//SYSIN    DD *                                             
  SORT FIELDS=COPY                                         
  OUTFIL FTOV,REMOVECC,HEADER1=(3C'$')                     
//*
//STEP0200 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//INA      DD DSN=Your input VB 434 Lrecl file,DISP=SHR
//            DISP=SHR
//*                                   
//INB      DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT
//         DD DSN=Your input VB 116 Lrecl file,DISP=SHR
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT
//         DD DSN=Your input VB 89 Lrecl file,DISP=SHR
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT
//         DD DSN=Your input VB 196 Lrecl file,DISP=SHR
//*
//OUT1     DD DSN=Your matched out file2,
//            DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(X,Y),RLSE),LRECL=116
//*
//OUT2     DD DSN=Your matched out file3,
//            DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(X,Y),RLSE),LRECL=89
//*
//OUT3     DD DSN=Your matched out file4,
//            DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(X,Y),RLSE)
//*
//SYSIN    DD *                                               
  JOINKEYS F1=INA,FIELDS=(11,9,A)                             
  JOINKEYS F2=INB,FIELDS=(06,9,A)                             
  REFORMAT FIELDS=(F2:1,4,5)         
                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(6:31X)),                   
  IFTHEN=(WHEN=(5,1,ZD,EQ,1),OVERLAY=(6:43,29)),             
  IFTHEN=(WHEN=(5,1,ZD,EQ,2),OVERLAY=(6:43,13,35:57,2)),     
  IFTHEN=(WHEN=(5,1,ZD,EQ,3),OVERLAY=(6:43,13,35:56,2)) 
     
  SORT FIELDS=(5,1,CH,A,6,29,CH,A,35,2,FI,A)                 

  OUTFIL FNAMES=OUT1,INCLUDE=(5,1,ZD,EQ,1),BUILD=(1,4,37)     
  OUTFIL FNAMES=OUT2,INCLUDE=(5,1,ZD,EQ,2),BUILD=(1,4,37)     
  OUTFIL FNAMES=OUT3,INCLUDE=(5,1,ZD,EQ,3),BUILD=(1,4,37)     
//*                                                           
//JNF2CNTL DD *                                               
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,32X,5)),                 
  IFTHEN=(WHEN=GROUP,BEGIN=(37,3,CH,EQ,C'$$$'),PUSH=(5:ID=1)),
  IFTHEN=(WHEN=(5,1,ZD,EQ,1),OVERLAY=(6:43,5,68,4)),         
  IFTHEN=(WHEN=(5,1,ZD,EQ,2),OVERLAY=(6:43,5,52,4)),         
  IFTHEN=(WHEN=(5,1,ZD,EQ,3),OVERLAY=(6:43,5,48,4))           
//*
Back to top
View user's profile Send private message
a210955

New User


Joined: 07 Nov 2006
Posts: 4

PostPosted: Tue Nov 22, 2011 4:45 pm    Post subject:
Reply with quote

Thanks so much Kolusu!
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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