IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Compare the two files with different columns
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Thu Nov 13, 2008 1:56 pm
 Reply with quote
Quote:
hi,

I have 2 input files.The first have the format as below
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7
********************************* Top of Data ************************
APV;GAR;AGR;$AGAGR  ;COP;RLAGR0D ;   93;20000529;20031112;20050728;Y;
APV;GAR;AGR;$AGANNUL;COP;RLAGR0D ;   37;20010206;20030227;20060704;Y;
APV;GAR;AGR;$AGCATAL;COP;RLAGR0D ;   42;20000530;20010222;20030113;Y;
APV;GAR;AGR;$AGCONNX;COP;RLAGR0D ;   48;20010327;20061113;20061113;Y;
APV;GAR;AGR;$AGDEM  ;COP;RLAGR0D ;   42;20010417;20051111;20051111;Y;
APV;GAR;AGR;$AGDOCA ;COP;RLAGR0D ;   43;20010720;20010720;20010720;Y;
APV;GAR;AGR;$AGDOTS ;COP;RLAGR0D ;   63;20000918;20010905;20061212;Y;
APV;GAR;AGR;$AGDOUB ;COP;RLAGR0D ;   68;20000919;20030227;20030227;Y;
[quote]The 2nd have the format as below

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+
********************************* Top of Data *****************************
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGAGR               
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGANNUL             
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGCATAL             
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGCONNX             
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDEM               
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDOCA               
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDOTS               
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR1D;  $AGDOUB               
Quote:
I want to compare the two files with different columns.I want to compare the column 4 to 7 from file1 to column 4 to 7 from file2.
Then compare the column 13 t0 20 from file1 to column 54 to 61 from file2.Then compare column 26-32 with column 44-50 and i want the output file as the rows which is not get satisfied with the condition.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Thu Nov 13, 2008 2:02 pm
 Reply with quote
Quote:
i want the output file as the rows which is not get satisfied with the condition.

What all fields you want in output and from which file? It would be better if you post the output for the above input files. Also what about input/output LRECL/RECFM
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Thu Nov 13, 2008 2:10 pm
 Reply with quote
Hi Arun,

The rec format is FB and lrecl is 80. i want the output from both the files and write it in an output file with lrecl 160.

Thanks
Rajan BOSE.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Thu Nov 13, 2008 2:35 pm
 Reply with quote
Do you need an output like this
Code:
AAA
BBB
CCC


Code:
XXX
YYY
ZZZ

Code:
   XXX
   YYY
   ZZZ
AAA   
BBB     
CCC

arcvns wrote:
It would be better if you post the output for the above input files.
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Thu Nov 13, 2008 2:47 pm
 Reply with quote
XXX;AAA
YYY; YYY
ZZZ;ZZZ

OR

XXX
YYY
ZZZ


Above two is ok for me.
Back to top
View user's profile Send private message
srajanbose

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai
PostPosted: Thu Nov 13, 2008 2:48 pm
 Reply with quote
XXX;AAA
YYY; BBB
ZZZ ;CCC


OR


XXX
YYY
ZZZ
AAA
BBB
CCC
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
PostPosted: Thu Nov 13, 2008 4:14 pm
 Reply with quote
Try this code... Hope this will give you desire answer

Code:
//STEP10A  EXEC PGM=ICETOOL                       
//*                                               
//SYSOUT       DD  SYSOUT=*                       
//TOOLMSG      DD  SYSOUT=*                       
//DFSMSG       DD  SYSOUT=*                       
//*                                               
//I1           DD  DSN=(INFILE1 --> 80Length),     
//            DISP=SHR                           
//*                                               
//I2           DD  DSN=(INFILE2 --> 80Length),     
//            DISP=SHR                           
//*                                               
//T1           DD  DSN=&&T1,                     
//            DISP=(MOD,PASS),                   
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//*                                               
//T2           DD  DSN=&&T2,                     
//            DISP=(MOD,PASS),                   
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//*                                               
//O1           DD  DSN=(OUTFILE1 --> 160Length),                 
//            DISP=(NEW,CATLG,DELETE),                       
//            UNIT=(SYSDA,5),                               
//            SPACE=(TRK,(200,200),RLSE),                   
//            DCB=(RECFM=FB,LRECL=160,BLKSIZE=0)             
//*                                                         
//TOOLIN   DD  *    *** CONSTANT CONTROL CARDS ***           
  COPY FROM(I2) TO(T1) USING(CTL1)                           
  COPY FROM(I1) TO(T1) USING(CTL2)                           
  SPLICE FROM(T1) TO(T2) ON(83,19,CH) KEEPNODUPS KEEPBASE - 
    WITHALL WITH(1,81) USING(CTL3)                           
  SORT FROM(T2) TO(O1) USING(CTL4)                           
/*                                                           
//CTL1CNTL DD *                                             
  INREC OVERLAY=(81:C'11',83:4,4,87:54,8,95:44,7)           
/*                                                           
//CTL2CNTL DD *                                             
  INREC OVERLAY=(81:C'22',83:4,4,87:13,8,95:26,7)           
/*                                                           
//CTL3CNTL DD *                           
  OUTFIL FNAMES=T2,OMIT=(81,2,CH,EQ,C'21')
/*                                         
//CTL4CNTL DD *                           
  SORT FIELDS=(81,2,CH,A)                 
  OUTREC IFOUTLEN=160,                     
    IFTHEN=(WHEN=INIT,OVERLAY=(81:80X))   
/*
Back to top
View user's profile Send private message
nelson.pandian

Active User


Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
PostPosted: Thu Nov 13, 2008 4:15 pm
 Reply with quote
output for that code is
Code:
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGAGR           
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGANNUL         
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGCATAL         
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGCONNX         
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDEM           
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDOCA           
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDOTS           
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR1D;  $AGDOUB           
APV;GAR;AGR;$AGDOUB ;COP;RLAGR0D ;   68;20000919;20030227;20030227;Y; 
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 496
Location: USA
PostPosted: Thu Nov 13, 2008 9:17 pm
 Reply with quote
The following SyncSort application will produce the matched records based on the JOIN keys. The output will have the data from File 2 (80 bytes) appended to the end of their corresponding records in File 1 (80 bytes), creating LRECL=160.
Code:

//STEP1 EXEC PGM=SORT                                               
//SORTJNF1 DD *                                                     
APV;GAR;AGR;$AGAGR  ;COP;RLAGR0D ;   93;20000529;20031112;20050728;Y;
APV;GAR;AGR;$AGANNUL;COP;RLAGR0D ;   37;20010206;20030227;20060704;Y;
APV;GAR;AGR;$AGCATAL;COP;RLAGR0D ;   42;20000530;20010222;20030113;Y;
APV;GAR;AGR;$AGCONNX;COP;RLAGR0D ;   48;20010327;20061113;20061113;Y;
APV;GAR;AGR;$AGDEM  ;COP;RLAGR0D ;   42;20010417;20051111;20051111;Y;
APV;GAR;AGR;$AGDOCA ;COP;RLAGR0D ;   43;20010720;20010720;20010720;Y;
APV;GAR;AGR;$AGDOTS ;COP;RLAGR0D ;   63;20000918;20010905;20061212;Y;
APV;GAR;AGR;$AGDOUB ;COP;RLAGR0D ;   68;20000919;20030227;20030227;Y;
//SORTJNF2 DD *                                               
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGAGR   
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGANNUL
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGCATAL
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGCONNX
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDEM   
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDOCA 
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR0D;  $AGDOTS 
APV;GAR;OR7SQE.PFI.L10.D100908.GAR.GAR.RPL.RLAGR1D;  $AGDOUB 
//SORTOUT DD DSN=OUTPUT.DATASET
//SYSOUT DD SYSOUT=*                             
//SYSIN DD *                                     
  JOINKEYS FILES=F1,FIELDS=(4,4,A,13,8,A,26,7,A)
  JOINKEYS FILES=F2,FIELDS=(4,4,A,54,8,A,44,7,A)
  REFORMAT FIELDS=(F1:1,80,F2:1,80)             
  SORT FIELDS=COPY                               
/*
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Thu Nov 13, 2008 10:58 pm
 Reply with quote
srajanbose wrote:
i want the output file as the rows which is not get satisfied with the condition.

Alissa,

I doubt if he is asking for a "JOIN UNPAIRED" version of the above job.

srajanbose,

To repeat: Post the expected output for the sample input in your first post
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 496
Location: USA
PostPosted: Thu Nov 13, 2008 11:38 pm
 Reply with quote
srajanbose wrote:
i want the output file as the rows which is not get satisfied with the condition.
arcvns wrote:
I doubt if he is asking for a "JOIN UNPAIRED" version of the above job.

Well, since we don't yet have an example of the expected output from the OP, here are the modified cards if only the UNPAIRED records are desired:
Code:
//SYSIN DD *                                     
  JOINKEYS FILES=F1,FIELDS=(4,4,A,13,8,A,26,7,A)
  JOINKEYS FILES=F2,FIELDS=(4,4,A,54,8,A,44,7,A)
  JOIN UNPAIRED,ONLY
  SORT FIELDS=COPY                               
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Increase the number of columns in the... IBM Tools 3
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us