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 files

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

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal

PostPosted: Wed Sep 15, 2010 5:49 pm    Post subject: Merging two files
Reply with quote

Could someone help me?

I have two files with the following atributes

File1 LREC (12 + 7)
Code:
ID          Client
AAAAAAAAAAAA1111111
AAAAAAAAAAAA2222222
BBBBBBBBBBBB3333333
CCCCCCCCCCCC4444444
CCCCCCCCCCCC5555555


File 2 LREC (12 + 14 + 12)
Code:
ID          CTA           APO
AAAAAAAAAAAAaaaaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAAbbbbbbbbbbbbbbssssssssssss
BBBBBBBBBBBBccccccccccccccoooooooooooo
BBBBBBBBBBBBddddddddddddddwwwwwwwwwwww
CCCCCCCCCCCCeeeeeeeeeeeeeetttttttttttt


Output LREC (14 + 07 + 12 + 12)
Code:
ID          Client CTA           APO
AAAAAAAAAAAA1111111aaaaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAA1111111bbbbbbbbbbbbbbssssssssssss
AAAAAAAAAAAA2222222aaaaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAA2222222bbbbbbbbbbbbbbssssssssssss
BBBBBBBBBBBB3333333ccccccccccccccoooooooooooo
BBBBBBBBBBBB3333333ddddddddddddddwwwwwwwwwwww
CCCCCCCCCCCC4444444eeeeeeeeeeeeeetttttttttttt
CCCCCCCCCCCC5555555eeeeeeeeeeeeeetttttttttttt
Back to top
View user's profile Send private message

smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 15, 2010 5:54 pm    Post subject:
Reply with quote

yes you can ...

read for JOINKEYS in DFSORT ...
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 15, 2010 8:27 pm    Post subject:
Reply with quote

rafaelantunes,
Please find below jcl to get what you asked for. I created output of 12+7+12+12 = 43 because this what you show in the expected output.
Code:

//STEP01 EXEC PGM=SORT                             
//SYSOUT DD SYSOUT=*                               
//SORTJNF1 DD *                                     
AAAAAAAAAAAA1111111                                 
AAAAAAAAAAAA2222222                                 
BBBBBBBBBBBB3333333                                 
CCCCCCCCCCCC4444444                                 
CCCCCCCCCCCC5555555                                 
/*                                                 
//SORTJNF2 DD *                                     
AAAAAAAAAAAAaaaaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAAbbbbbbbbbbbbbbssssssssssss
BBBBBBBBBBBBccccccccccccccoooooooooooo
BBBBBBBBBBBBddddddddddddddwwwwwwwwwwww
CCCCCCCCCCCCeeeeeeeeeeeeeetttttttttttt             
/*                                                 
//SORTOUT DD SYSOUT=*                               
//SYSIN DD *                                       
  JOINKEYS FILE=F1,FIELDS=(01,12,A)                 
  JOINKEYS FILE=F2,FIELDS=(01,12,A)                 
  REFORMAT FIELDS=(F1:01,12,13,07,                 
                   F2:13,12,27,12)                 
  SORT FIELDS=COPY                                 
/*                                                 

OUTPUT
Code:
AAAAAAAAAAAA1111111aaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAA1111111bbbbbbbbbbbbssssssssssss
AAAAAAAAAAAA2222222aaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAA2222222bbbbbbbbbbbbssssssssssss
BBBBBBBBBBBB3333333ccccccccccccoooooooooooo
BBBBBBBBBBBB3333333ddddddddddddwwwwwwwwwwww
CCCCCCCCCCCC4444444eeeeeeeeeeeetttttttttttt
CCCCCCCCCCCC5555555eeeeeeeeeeeetttttttttttt


Thanks,
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal

PostPosted: Wed Sep 15, 2010 8:49 pm    Post subject:
Reply with quote

Unfortunatly the JOINKEYS is not available to use...
Is there any other way?
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 Sep 15, 2010 9:10 pm    Post subject:
Reply with quote

Hello,

Quote:
Unfortunatly the JOINKEYS is not available to use...
Why not?

Please post the informational messages generated by any successful execution of the sort - including message id's.
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal

PostPosted: Wed Sep 15, 2010 9:16 pm    Post subject:
Reply with quote

Here it goes:

Code:
********************************* TOP OF DATA **********************************
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 16:14 ON WED SEP 15, 2010 -
            JOINKEYS FILE=F1,FIELDS=(01,12,A)                                   
            $                                                                   
ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY           
            JOINKEYS FILE=F2,FIELDS=(01,12,A)                                   
            $                                                                   
ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY           
            REFORMAT FIELDS=(F1:01,12,13,07,                                     
            $                                                                   
ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY           
                             F2:13,12,27,12)                                     
                               $                                                 
ICE007A 1 SYNTAX ERROR                                                           
            SORT FIELDS=COPY                                                     
ICE056A 0 SORTIN   NOT DEFINED                                                   
ICE751I 0 C5-K48846 C6-K90014 C7-K45047 C8-K46331 E7-K48846                     
ICE052I 3 END OF DFSORT                                                         
******************************** BOTTOM OF DATA ********************************


The job was executed exactly as showned in the previous post.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 15, 2010 9:34 pm    Post subject:
Reply with quote

rafaelantunes,
You can achieve the same with SPLICE but you need tell if there is a limit to maximum number of duplicates for any given key in both the files?

Thanks,
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 15, 2010 9:40 pm    Post subject:
Reply with quote

rafaelantunes,
Please run below job as is and provide SYSOUT.
Code:
//S1 EXEC PGM=ICEMAN
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*


Thanks,
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal

PostPosted: Wed Sep 15, 2010 9:49 pm    Post subject:
Reply with quote

There is no specific limit number, although we can say no more than 10 entries with the same key in both files.

The majority is 3 or 4 entries on file 1, and 2 entries on file 2 with the same key.

Below is the SYSOUT from the code above:

Code:
********************************* TOP OF DATA **********************************
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                                 
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 17:13 ON WED SEP 15,2010 -
            OPTION COPY                                                         
ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1                           
ICE751I 0 C5-K48846 C6-K90014 C7-K45047 C8-K46331 E9-K47759 C9-BASE   E5-K48846 E7-K48846
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS         
ICE088I 0 DBB071AA.S1      .        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,6291456,6278238)                                   
ICE156I 0 MAIN STORAGE ABOVE 16MB = (6200798,6200798)                           
ICE127I 0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=256
ICE128I 0 OPTIONS: SIZE=6291456,MAXLIM=2097152,MINLIM=450560,EQUALS=N,LIST=Y,ERET=RC16 ,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=N,RESDNT=ALL ,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=MSG
ICE130I 0 OPTIONS: RESALL=61440,RESINV=0,SVC=109 ,CHECK=N,WRKREL=Y,OUTREL=N,CKPT=N,COBEXIT=COB2             
ICE131I 0 OPTIONS: TMAXLIM=6291456,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=Y,DSA=0     
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=S,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=400    ,DSPSIZE=MAX ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0                                                   
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT                                   
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN                                     
ICE751I 1 EF-K49535 F0-K49038 E8-K48846                                         
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB                             
ICE055I 0 INSERT 0, DELETE 0                                                     
ICE054I 0 RECORDS - IN: 1, OUT: 1                                               
ICE052I 0 END OF DFSORT                                                         
******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Sep 15, 2010 9:52 pm    Post subject:
Reply with quote

ICE005A 0 BLANK NEEDED IN COLUMN 1


I vote to send this to the rookie forum.
Back to top
View user's profile Send private message
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal

PostPosted: Wed Sep 15, 2010 10:07 pm    Post subject:
Reply with quote

WOW take it easy dbzTHEdinosauer the job was submited like this:

Code:
//STEP01 EXEC PGM=SORT                             
//SYSOUT DD SYSOUT=*                               
//SORTJNF1 DD *                                     
AAAAAAAAAAAA1111111                                 
AAAAAAAAAAAA2222222                                 
BBBBBBBBBBBB3333333                                 
CCCCCCCCCCCC4444444                                 
CCCCCCCCCCCC5555555                                 
/*                                                 
//SORTJNF2 DD *                                     
AAAAAAAAAAAAaaaaaaaaaaaaaammmmmmmmmmmm
AAAAAAAAAAAAbbbbbbbbbbbbbbssssssssssss
BBBBBBBBBBBBccccccccccccccoooooooooooo
BBBBBBBBBBBBddddddddddddddwwwwwwwwwwww
CCCCCCCCCCCCeeeeeeeeeeeeeetttttttttttt             
/*                                                 
//SORTOUT DD SYSOUT=*                               
//SYSIN DD *                                       
  JOINKEYS FILE=F1,FIELDS=(01,12,A)                 
  JOINKEYS FILE=F2,FIELDS=(01,12,A)                 
  REFORMAT FIELDS=(F1:01,12,13,07,                 
                   F2:13,12,27,12)                 
  SORT FIELDS=COPY                                 
/*


and as you can see there are blanks in column 1.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 15, 2010 10:52 pm    Post subject:
Reply with quote

rafaelantunes,
See if this works for you. Below JCL is coded for maximum of 3 duplicate keys but you can modify to match your requirement of 10. Logic is to convert to each duplicate keys in both the files to unique key (convert rows to column) before final splicing and then when you create unique record for each key, splice both the files and use OUTFIL to create desired output. I tried to reduce number of Pass but this is the best I could come up with.

Personally speaking, I would request my System Admin to install new DFSort PTF and wouldn't move this to Production.

Let me know if this works for you...

Code:
//STEP01   EXEC  PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN1      DD YOUR INPUT FILE1                                           
//IN2      DD YOUR INPUT FILE2                                           
//T1       DD DSN=&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)   
//T2       DD DSN=&T2,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)   
//T3       DD DSN=&T3,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE)   
//CON      DD DSN=&T1,VOL=REF=*.T1,DISP=OLD                             
//         DD DSN=&T2,VOL=REF=*.T2,DISP=OLD                             
//OUT    DD SYSOUT=*                                                   
//TOOLIN DD *                                                           
 SPLICE FROM(IN1) TO(T1)  ON(01,12,CH) KEEPNODUPS  WITHEACH -           
               WITH(20,07) WITH(27,07) USING(CTL1)                     
 SPLICE FROM(IN2) TO(T2)  ON(01,12,CH) KEEPNODUPS  WITHEACH -           
               WITH(60,26) WITH(86,26) USING(CTL2)                     
 SPLICE FROM(CON) TO(T3)  ON(01,12,CH) KEEPNODUPS  WITHALL -           
   WITH(34,26) WITH(60,26) WITH(86,26) USING(CTL3)                     
 COPY FROM(T3) TO(OUT)                 USING(CTL4)                     
/*                                                                     
//CTL1CNTL DD *                                                         
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(121:SEQNUM,8,ZD,RESTART=(01,12))),   
       IFTHEN=(WHEN=(121,8,ZD,EQ,2),OVERLAY=(20:13,07)),               
       IFTHEN=(WHEN=(121,8,ZD,EQ,3),OVERLAY=(27:13,07))                 
/*                                                                     
//CTL2CNTL DD *                                                         
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(121:SEQNUM,8,ZD,RESTART=(01,12))),   
       IFTHEN=(WHEN=(121,8,ZD,EQ,1),OVERLAY=(34:13,26)),               
       IFTHEN=(WHEN=(121,8,ZD,EQ,2),OVERLAY=(60:13,26)),               
       IFTHEN=(WHEN=(121,8,ZD,EQ,3),OVERLAY=(86:13,26))                 
/*                                                                     
//CTL3CNTL DD *
 SORT FIELDS=(01,12,CH,A)                                                         
 OUTFIL FNAMES=T3,BUILD=(01,12,13,07,34,26,/,                           
                         01,12,13,07,60,26,/,                           
                         01,12,13,07,86,26,/,                           
                         01,12,20,07,34,26,/,                           
                         01,12,20,07,60,26,/,                           
                         01,12,20,07,86,26,/,                           
                         01,12,27,07,34,26,/,                           
                         01,12,27,07,60,26,/,                           
                         01,12,27,07,86,26)                             
/*                                                                     
//CTL4CNTL DD *                                                         
 OMIT COND=(13,07,CH,EQ,C' ',OR,20,26,CH,EQ,C' ')                       
 SORT FIELDS=COPY                                                       
/*                                                                     

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: Wed Sep 15, 2010 11:01 pm    Post subject:
Reply with quote

Code:

ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY           


Dick,

This message is also issued if the function (JOINKEYS) is not supported because the PTF has not been applied. That's what the OPERATION NOT DEFINED CORRECTLY part means.

rafaelantunes,

Ask your System Programmer to install z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009) so you can use JOINKEYS. For complete details on JOINKEYS 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
rafaelantunes

New User


Joined: 10 May 2007
Posts: 32
Location: Portugal

PostPosted: Thu Sep 16, 2010 9:04 pm    Post subject:
Reply with quote

Thanks sqlcode1. It worked.

I will make some changes to see if I can get the results I am expecting.

Thanks to all.

best regards
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 Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am


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