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
 

 

Record Splicing Using DFSORT

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

New User


Joined: 01 Dec 2006
Posts: 5
Location: Houston, TX

PostPosted: Sun Jun 24, 2007 1:58 pm    Post subject: Record Splicing Using DFSORT
Reply with quote

Hi,

I have two input files, namely, employee and department -

Structure of Employee File:

Emp# Emp Name
1 Harris
2 Chris
2 Frank
3 Borris

Structure of department File:

Emp# Department
1 marketing
2 purchasing
4 merchandise

The output file should be like

Emp# Emp Name Department
1 Harris Marketing
2 Chris Purchasing
2 Frank Purchasing
3 Borris ------------
4 ------- Merchandise

Can somebody please give me a DFSORT code snippet to achieve this?
Back to top
View user's profile Send private message

ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Mon Jun 25, 2007 4:54 pm    Post subject:
Reply with quote

LRECL,RECFM of both the files?
Field size of the all the fields?
Back to top
View user's profile Send private message
pritamdebnath

New User


Joined: 01 Dec 2006
Posts: 5
Location: Houston, TX

PostPosted: Mon Jun 25, 2007 5:50 pm    Post subject: Re: Record Splicing Using DFSORT
Reply with quote

LRECL = 80
RECFM = FB

Record Structure -

Input File 1:

Position 1,3: Emp No
Postion 4,20:Employee Name
Position 24,56: Filler

Input File 2:

Position 1,3: Emp No
Position 4,20: Department
Position 24,56: Filler
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 Jun 25, 2007 9:22 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/80)
//IN2 DD DSN=... input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN   DD    *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) KEEPNODUPS -
  WITHALL WITH(1,23)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,3,24:4,20,80:X)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,23,80:X)
/*
Back to top
View user's profile Send private message
pritamdebnath

New User


Joined: 01 Dec 2006
Posts: 5
Location: Houston, TX

PostPosted: Wed Jun 27, 2007 8:39 am    Post subject: Re: Record Splicing Using DFSORT
Reply with quote

Thanks Frank - the solution is working. But there is a slight problem here -
Let me give a scenario -

File1 - Employee

Emp# Employee
1 Frank
1 Harris
2 Chris
3 Raju
4 Nelson

File2 - Department

Emp# Department
1 Marketing
2 Purchasing
2 AP
4 Buying
5 Merchandizing

Now I want the output file to come like this -

Emp# Name Department
1 Frank Marketing
1 Harris Marketing
2 Chris Purchasing
2 Chris AP
3 Raju
4 Nelson Buying
5 Merchandizing

But when I executed the job given, I got the following output -

Emp# Name Department
1 Frank Marketing
1 Harris Marketing
2 Purchasing
2 Chris Purchasing
3 Raju
4 Nelson Buying
5 Merchandizing

So basically the n-n mapping is not coming, but n-1 or 1-n mapping is comming. Can you please help?
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: Wed Jun 27, 2007 8:07 pm    Post subject:
Reply with quote

In your original example, there were duplicates in the Employee file but no duplicates in the Department file. My solution was based on that setup and works for that setup. Now you've changed the requirements so that you have duplicates in both files. That's a more complicated situation and for all I know the data is even more complicated than you've shown, so I can't give you a solution.
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 24, 2009 2:17 am    Post subject:
Reply with quote

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), you can now use JOINKEYS to do an n-n join. For example:

Code:

//S1    EXEC  PGM=SORT                                 
//SYSOUT    DD  SYSOUT=*                               
//IN1 DD *                                             
  1Frank                                               
  1Harris                                             
  2Chris                                               
  3Raju                                               
  4Nelson                                             
//IN2 DD *                                             
  1Marketing                                           
  2Purchasing                                         
  2AP                                                 
  4Buying                                             
  5Merchandizing                                       
//SORTOUT DD SYSOUT=*                                 
//SYSIN    DD    *
  JOINKEYS F1=IN1,FIELDS=(1,3,A)                   
  JOINKEYS F2=IN2,FIELDS=(1,3,A)                   
  JOIN UNPAIRED,F1,F2                             
  REFORMAT FIELDS=(F1:1,3,4,20,F2:1,3,4,20,?)     
  OUTREC IFTHEN=(WHEN=(47,1,CH,EQ,C'B'),           
    BUILD=(1,3,4,20,27,20)),                       
   IFTHEN=(WHEN=(47,1,CH,EQ,C'1'),                 
    BUILD=(1,3,4,20)),                             
   IFTHEN=(WHEN=(47,1,CH,EQ,C'2'),                 
    BUILD=(24,3,24:27,20))                         
  OPTION COPY                                     
/*


SORTOUT would have:

Code:

  1Frank               Marketing
  1Harris              Marketing
  2Chris               Purchasing
  2Chris               AP
  3Raju
  4Nelson              Buying
  5                    Merchandizing


For complete details on date conversion functions and the other new functions available with the Nov, 2009 DFSORT PTF, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
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 Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Sort Large record length cmsmoon DFSORT/ICETOOL 14 Tue Apr 11, 2017 5:49 pm
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm


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