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
 

 

Compare the two files with different columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
srajanbose
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Nov 13, 2008 1:56 pm    Post subject: Compare the two files with different columns
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: 2238
Location: @my desk

PostPosted: Thu Nov 13, 2008 2:02 pm    Post subject:
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
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Nov 13, 2008 2:10 pm    Post subject: Reply to: Query in sort
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: 2238
Location: @my desk

PostPosted: Thu Nov 13, 2008 2:35 pm    Post subject:
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
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Nov 13, 2008 2:47 pm    Post subject: Reply to: Query in sort
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
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Nov 13, 2008 2:48 pm    Post subject: Reply to: Query in sort
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    Post subject:
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    Post subject:
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: 500
Location: USA

PostPosted: Thu Nov 13, 2008 9:17 pm    Post subject:
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: 2238
Location: @my desk

PostPosted: Thu Nov 13, 2008 10:58 pm    Post subject:
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: 500
Location: USA

PostPosted: Thu Nov 13, 2008 11:38 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 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
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm


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