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
 

 

Merge selective columns from two files into one file

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

New User


Joined: 18 Apr 2013
Posts: 10
Location: India

PostPosted: Wed May 08, 2013 9:52 am    Post subject: Merge selective columns from two files into one file
Reply with quote

Hi,

I have two files. I have to merge two files into one file. But I have to select only few but different columns from both the files. For eg:
File1: 111AAA222BBB333CCC
444DDD555EEE666FFF
File2: ZZZ999YYY888XXX777
WWW666VVV555UUU444

I need output file containing
111BBB
444EEE
999XXX
666UUU

Can anybody tell me if this is possible using ICETOOL or DFSORT utilities. If yes, which command can be used. Thanks!
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed May 08, 2013 10:32 am    Post subject:
Reply with quote

Is there a method or value could be used to identify if the data is from File1 or File 2?
Back to top
View user's profile Send private message
santoshks0611

New User


Joined: 18 Apr 2013
Posts: 10
Location: India

PostPosted: Wed May 08, 2013 10:35 am    Post subject:
Reply with quote

Hi, Thanks for the reply.

There is no specific method actually. But if you see the above example. I have to copy column 1 to 3 and 10 to 12 from file1 and column 4 to 6 and 13 to 15 from file2. I dont have to check for any keys. Just I have to select these columns and write in a new file.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed May 08, 2013 10:39 am    Post subject:
Reply with quote

Yes this can be achieved through ICETOOL
you could achieve the solution through two passes of of COPY with CNTL
Search forum for aamples
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed May 08, 2013 11:50 am    Post subject:
Reply with quote

Is it possible that your two input files may have same values in those specific columns.

For example:
Code:
File1:
111AAA222BBB333CCC
333AAA222DDD333CCC
444DDD555EEE666FFF

File2:
ZZZ999YYY888XXX777
ZZZ333YYY888DDD777
WWW666VVV555UUU444

What should be the output in the above case?
Back to top
View user's profile Send private message
santoshks0611

New User


Joined: 18 Apr 2013
Posts: 10
Location: India

PostPosted: Wed May 08, 2013 12:20 pm    Post subject:
Reply with quote

Hi mistah kurtz, Thanks for the reply.
Yes it is possible to have same data. the output should be,
Code:

Output File:
111BBB
333DDD
444EEE
999XXX
333DDD
666UUU

First 3 rows from file1 and next 3 rows from file2.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed May 08, 2013 12:35 pm    Post subject:
Reply with quote

ok..In that case you cna try below SORT job(Assuming that all files are FB and LRECL=80):

Code:
//SRT01    EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTJNF1 DD *                                         
111AAA222BBB333CCC                                       
333AAA222DDD333CCC                                       
444DDD555EEE666FFF                                       
//SORTJNF2 DD *                                         
ZZZ999YYY888XXX777                                       
ZZZ333YYY888DDD777                                       
WWW666VVV555UUU444                                       
//*                                                     
//SORTOUT  DD SYSOUT=*                                   
//*                                                     
//SYSIN    DD *                                         
    JOINKEYS FILE=F1,FIELDS=(1,3,A,10,3,A),SORTED,NOSEQCK
    JOINKEYS FILE=F2,FIELDS=(4,3,A,13,3,A),SORTED,NOSEQCK
    JOIN     UNPAIRED,F1,F2                             
    OPTION   COPY                                       
    REFORMAT FIELDS=(?,F1:1,3,10,3,F2:4,3,13,3)         
    OUTFIL   IFTHEN=(WHEN=(1,1,CH,EQ,C'1'),BUILD=(2,6)),
             IFTHEN=(WHEN=(1,1,CH,EQ,C'2'),BUILD=(8,6)) 
/*   


Output will be SORTOUT:
Code:
111BBB
333DDD
444EEE
999XXX
333DDD
666UUU
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed May 08, 2013 5:11 pm    Post subject: Reply to: Merge selective columns from two files into one fi
Reply with quote

mistah kurtz,

Having established that there may be equal values on the "keys" in the files, you will lose data if the last record of the first file has the same value as the first record of the second file.

You are only getting the correct output because the 999 value in the first record of the second file is "protecting" the other records on the second file from being selected "early". If the 999 appear last, the 333 record will appear in an inconvenient order.

santoshks0611,

What are the RECM and LRECL of your two input files?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed May 08, 2013 5:22 pm    Post subject:
Reply with quote

Bill,

Why not use ICETOOL for this requirement?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Wed May 08, 2013 5:26 pm    Post subject: Reply to: Merge selective columns from two files into one fi
Reply with quote

Yes, two copies to same output DD with DISP=MOD, USING(xxxx) and USING(yyyy) to do the data selection for the separate input DDs.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed May 08, 2013 6:49 pm    Post subject:
Reply with quote

Thanks Bill for pointing out the error..I was getting the feeling that something is wrong..my apologies for posting it as solution..

Hi Santosh,
Please try similar to this job:

Code:
//STEP01   EXEC PGM=ICETOOL                                   
//DFSMSG   DD SYSOUT=*                                       
//TOOLMSG  DD SYSOUT=*                                       
//IN1      DD *                                               
111AAA222BBB333CCC                                           
333AAA222DDD333CCC                                           
444DDD555EEE666FFF                                           
//IN2      DD *                                               
ZZZ999YYY888XXX777                                           
ZZZ333YYY888DDD777                                           
WWW666VVV555UUU444                                           
//*                                                           
//OUT      DD DSN=XXXXX.OUTPUT,DISP=(MOD,CATLG,DELETE),
//            SPACE=(TRK,(1,1),RLSE),UNIT=DASD,               
//            DCB=(RECFM=FB,LRECL=80,BLKSIZE=0,DSORG=PS)     
//*                                                           
//TOOLIN   DD *                                 
    COPY FROM(IN1) TO(OUT) USING(CTL1)         
    COPY FROM(IN2) TO(OUT) USING(CTL2)         
/*                                             
//CTL1CNTL DD *                                 
    OUTFIL FNAMES=OUT,BUILD=(1,3,4:10,3,80:X)   
/*                                             
//CTL2CNTL DD *                                 
    OUTFIL FNAMES=OUT,BUILD=(4,3,4:13,3,80:X)   
/*                                             


Hope this time it's fine :-)
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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