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
 

 

Merging two datasets horizontally using DFSORT

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

New User


Joined: 12 Mar 2008
Posts: 53
Location: Chennai

PostPosted: Tue Jul 07, 2009 11:43 pm    Post subject: Merging two datasets horizontally using DFSORT
Reply with quote

Hi,

Can anyone please help me out to merge two datasets horizontally using DFSORT as shown below.
For Example,
Data - 1
ID Name
001 Ashok
002 Bharath
002 Sami
003 Anand

Data - 2
DOB ID
07MAR1984 001
02JAN1982 002
04APR1981 002
17NOV1985 003

The output is,
Data - 3
ID Name DOB
001 Ashok 07MAR1984
002 Bharath 02JAN1982
002 Sami 04APR1981
003 Anand 17NOV1985

Is there a DFSORT to achieve this output.
Note:-
Please make note, I want this to be done in DFSORT not in ICETOOL.

Tnaks in Advance

Regards
Ashok Kumar.K
Back to top
View user's profile Send private message

Skolusu

Senior Member


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

PostPosted: Wed Jul 08, 2009 12:00 am    Post subject:
Reply with quote

ashok4u_it,

1. What is the LRECL and RECFM of the input files?
2. What is the position and format of the key to be matched?
3. what are the positions and lengths of other fields to be merged?

Quote:

Is there a DFSORT to achieve this output.
Note:-
Please make note, I want this to be done in DFSORT not in ICETOOL.


ICETOOL is a part of DFSORT since 1991. I just don't understand the restriction. who is making these stupid rules?
Back to top
View user's profile Send private message
ashok4u_it

New User


Joined: 12 Mar 2008
Posts: 53
Location: Chennai

PostPosted: Wed Jul 08, 2009 12:14 am    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

Hi Skolusu.

Consider this as a VB dataset having same LRECL as 20.
The key field to be matched is ID.

As per our production environment we are not supposed to use ICETOOLS commands as a part of DFSORT. I am not sure about the reason behind this mystery.

Regards
Ashok Kumar.K
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Jul 08, 2009 12:29 am    Post subject:
Reply with quote

ashok4u_it,

Answer ALL of the questions I asked. In addition to that does any of the file have duplicates on key?
Back to top
View user's profile Send private message
ashok4u_it

New User


Joined: 12 Mar 2008
Posts: 53
Location: Chennai

PostPosted: Wed Jul 08, 2009 12:49 am    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

SKolusu,

1) LRECL = 20 and RECFM = VB

2)
In Data - 1, the Key field is in position - 1 (ID) and format is numeric
In Data - 2, the Key field is in position - 11(ID) and format is numeric

3)
The position and other fields to be merged are,
Fields Position
Name - 5 Numeric (Data - 1)
DOB - 1 Numeric (Data - 2)

4) Both the files has duplicate records.

Please find a solution for this problem. I had a real time scenario, which resembles the same.

Thanks in Advance
Ashok Kumar.K
Back to top
View user's profile Send private message
ashok4u_it

New User


Joined: 12 Mar 2008
Posts: 53
Location: Chennai

PostPosted: Wed Jul 08, 2009 1:14 am    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

Hi Frank,

Can you please help me out to resolve this problem.

Regards
Ashok Kumar.K
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: Wed Jul 08, 2009 1:32 am    Post subject:
Reply with quote

Hi Ashok,

Quote:
Hi Dick,

Sorry, there it was mistakenly posted. Please forgive for my mistake done accidently. I am working on this scenario for the past 3 days and it is very critical now.
Not to worry icon_smile.gif

I removed several of our exchanges to get the topic back to your question icon_smile.gif

d
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 Jul 08, 2009 1:45 am    Post subject:
Reply with quote

Quote:
Can you please help me out to resolve this problem.


We'd like to, but you haven't actually described the "rules" for what you want to do clearly, and your restriction against using ICETOOL is problematical.

As for the rules, you show the two files both with these "keys":

001
002
002
003

The "keys" match up in this case. But I suspect there are other possible variations such as:

File1
001
002
002
002
003

File2
001
001
002
002
003

Here the "keys" don't match up and I don't know what rules you want to use for that situation or other possible situations, or even what the other possible situations can be. Your example doesn't tell me much.

We can't give you a solution when we don't understand what you want to do and Kolusu and I really don't have time for guessing games as we have "day jobs" developing DFSORT.

Quote:
I am working on this scenario for the past 3 days and it is very critical now.


Wouldn't that have been enough time to write a program in the language of your choice (or are all languages disallowed at your site as well?).
Back to top
View user's profile Send private message
ashok4u_it

New User


Joined: 12 Mar 2008
Posts: 53
Location: Chennai

PostPosted: Wed Jul 08, 2009 6:21 pm    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

Sorry Frank. The requirement is like this. File-1 has duplicates records and File-2 is unique. We should merge both the files based upon key fields.
I.e. For each occurance of File-1 should merge with file-2 based upon unique key field.

Example.

Data - 1
DOB ID
07MAR1984 001
02JAN1982 002
04APR1981 002
17NOV1985 003
20SEP1987 004

Data - 2
ID Name
001 Ashok
002 Bharath
003 Anand

The output is,
Data - 3
ID Name DOB
001 Ashok 07MAR1984
002 Bharath 02JAN1982
002 Bharath 04APR1981
003 Anand 17NOV1985

I think now it is pretty much clear. Thanks for your response.
Please do the needful.

Thanks
Ashok Kumar.K
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 Jul 08, 2009 9:00 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed the name field can be a maximum of 12 characters. Be sure to use your Data 1 file (DOB,ID) as IN1 and your Data 2 file (ID,Name) as IN2.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/20)  DOB/ID
//IN2 DD DSN=...  input file2 (VB/20)  ID/NAME
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=32
//OUT DD DSN=...  output file (VB/30)  ID/NAME/DOB
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(7,3,ZD) VLENOVLY WITHALL -
  WITH(5,1) WITH(24,9) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,4,5:C'BB',7:5)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,5:C'VV',7:15,3,24:5,9)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(5,2,CH,EQ,C'VB'),
    BUILD=(1,4,5:7)
/*
Back to top
View user's profile Send private message
ashok4u_it

New User


Joined: 12 Mar 2008
Posts: 53
Location: Chennai

PostPosted: Wed Jul 08, 2009 9:10 pm    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

Frank, the statements SPLICE, COPY those sort of ICETOOL commands wont work in our production servers. Thats the challenge in this scenario. Please dont say that "ICETOOL is a part of DFSORT". I am not sure about the reason behind this mystery.

But, here we have to go only with DFSORT commands.
Anyway, thank you very much for your prompt response.

Regards
Ashok Kumar.K
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: Wed Jul 08, 2009 9:50 pm    Post subject:
Reply with quote

Hello,

Quote:
But, here we have to go only with DFSORT commands.
And knowing this from the beginning, you couldn't be bothered to mention this rule when you posted the request. . .

Quote:
I am not sure about the reason behind this mystery.
Your management most likely is concerned about supporting application "code" that no one in your organization is capable of writing/maintaining.
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 Jul 08, 2009 11:11 pm    Post subject:
Reply with quote

Quote:
And knowing this from the beginning, you couldn't be bothered to mention this rule when you posted the request. . .


Actually he did. I just forgot it. Kolusu is working on a non-ICETOOL solution.
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: Wed Jul 08, 2009 11:37 pm    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

Oops, my bad - i should have re-read from the beginning before posting icon_redface.gif

Most of the time, this isn't mentioned until considerable effort has already been spent. . . icon_wink.gif

d
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Jul 09, 2009 3:12 am    Post subject: Reply to: Merging two datasets horizontally using DFSORT
Reply with quote

ashok4u_it,

The following DFSORT JCL will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=your unique data file,DISP=SHR     
//SORTOUT  DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)         
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                                 
  OUTFIL VTOF,BUILD=(5,16,11X,C'U'),VLFILL=C'$'
//*                   
//STEP0200 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=your duplicate data file,DISP=SHR
//SORTOUT  DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)     
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                                 
  OUTFIL VTOF,BUILD=(15,3,X,18:5,10,C'D')                         
//*                                                               
//STEP0300 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=&&T1,DISP=SHR                                   
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *                                                   
  SORT FIELDS=(1,3,CH,A),EQUALS                                   
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(29:SEQNUM,8,ZD,RESTART=(1,3))),
  IFTHEN=(WHEN=GROUP,BEGIN=(29,8,ZD,EQ,1),PUSH=(1:1,16))
                                                 
  OUTFIL FTOV,VLTRIM=C' ',                       
  INCLUDE=(28,1,CH,EQ,C'D',AND,4,13,CH,GT,C' '),
  IFTHEN=(WHEN=INIT,BUILD=(1,27)),               
  IFTHEN=(WHEN=INIT,FINDREP=(IN=C'$',OUT=C''))   
//*
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 DUMP DATASETS VS TRACE DATASETS! lind sh CICS 4 Tue Dec 06, 2016 11:31 pm
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts Single COPY CICS TS datasets and when... Kyle Carroll CICS 2 Tue Oct 11, 2016 9:47 pm
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts DFSORT sorting only specific records akodakka DFSORT/ICETOOL 4 Thu Aug 04, 2016 11:24 am


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