MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

comparing 2 files and picking fields with values

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
geetha001
Beginner


Joined: 22 Jun 2005
Posts: 41
Topics: 14
PostPosted: Wed Jun 22, 2005 9:53 pm    Post subject: comparing 2 files and picking fields with values Reply with quote
Hi,

I searched the forum before posting but could not find a correct match.

I have 2 input files with the same layout. Example :
Input file 1:
Fields and records :

Code:

100  AB     130    10     1       27.20    AAAAA   11110                 
101  AB     230    20     1       33.33    BBBBB   11101
101  AB     330    23     2       55.50    BBBBB   11101
101  AB     430    30     1       101.10  BBBBB   11101
101  AB     530    10     1       15.10    BBBBB   11101
102  AB     330    25     1       20.10    CCCCC  11201
102  AB     630    15     1       10.10    CCCCC  11201



input file 2:
Fields and records:

Code:

100   AB       130    10      1       27.20                            0.09      10
101   AB       230    20      1       33.33                            0.50      20
101   AB       330    23      2       55.50                            0.43      25
101   AB       530    32      1       15.10                            0.01      10
102   AB       330    15      1       20.10                            0.90      30



Output file:
Fields and records:

Code:

100   AB       130    10      1       27.20     AAAAA     11110   0.09    10       
101   AB       230    20      1       33.33     BBBBB     11101   0.50    20
101   AB       330    23      2       55.50     BBBBB     11101   0.43    25
101   AB       430    30      1       101.10   BBBBB     11101 
101   AB       530    10      1       15.10     BBBBB     11101   0.01    10
102   AB       330    25      1       20.10    CCCCC     11201   0.90    30
102   AB       630    15      1       10.10    CCCCC     11201 



I am expecting the output as above. If I can get the above output as two different files, where one file will have all the matching records and the other file has non matching records should also be fine.

The AID, ATYP, APD, ASZ, AQT, AAMT are key fields on which the 2 input files are sorted. We are using Syncsort in our shop. I am sure there might be options with DFSORT, but I would like the solution in Syncsort.

I did see in this forum the basic syncsort JCL used for comparing 2 files and producing a matching and non-matching output.
I am more looking closer into merging the fields of the matching records. I hope I have made myself clear. Please let me know what approach I could use to get an output as above in Syncsort.

Thanks & Regards,
Geetha001
Back to top
View user's profile Send private message
geetha001
Beginner


Joined: 22 Jun 2005
Posts: 41
Topics: 14
PostPosted: Thu Jun 23, 2005 5:52 am    Post subject: Reply with quote
Referring to the output file above the names of the fields are as follows

AID, ATYP, APD, ASZ, AQT, AAMT AADDR AZIP APCT AUNIT.

As mentioned above , the AID, ATYP, APD, ASZ, AQT, AAMT are the key fields for both the input files.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
PostPosted: Thu Jun 23, 2005 6:02 am    Post subject: Reply with quote
Quote:

As mentioned above , the AID, ATYP, APD, ASZ, AQT, AAMT are the key fields for both the input files.


geetha001,

I Don't see any AID, ATYP, APD, ASZ, AQT, AAMT headings in your input sample.please post a layout of the files

Also can there be duplicates in any of the files?

what is the LRECL, RECFM of the files?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
geetha001
Beginner


Joined: 22 Jun 2005
Posts: 41
Topics: 14
PostPosted: Thu Jun 23, 2005 7:47 am    Post subject: Reply with quote
Kolusu,

I did see that I did not provide the field values. Therefore I replied to the message indicating the fields.
As mentioned previously,
the input file 1 has the following fields

AID, ATYP, APD, ASZ, AQT, AAMT AADDR AZIP APCT AUNIT.

I have mentioned the values in this order for the input file1. But Input file1 will have blanks/zeroes for the APCT and AUNIT fields.

the input file 2 has the following fields

AID, ATYP, APD, ASZ, AQT, AAMT AADDR AZIP APCT AUNIT.

In the case of input file 2 the AADDR and AZIP files will have blanks.

The output file layout has the following fields

AID, ATYP, APD, ASZ, AQT, AAMT AADDR AZIP APCT AUNIT.


Please note again, I have mentioned the records in the field order as above for all the files.

The LRECL is actually 318 and RECFM is FB. I have given only certain field values, which are of importance and required for the solution.

There will be no duplicates in any input record.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
PostPosted: Thu Jun 23, 2005 7:59 am    Post subject: Reply with quote
geetha,

Your request is similar to the solution posted here. Since there are no duplicates involved you can customize the below solution to your needs

http://mvsforums.com/helpboards/viewtopic.php?t=10&highlight=merge

Hope this helps..

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
geetha001
Beginner


Joined: 22 Jun 2005
Posts: 41
Topics: 14
PostPosted: Thu Jun 23, 2005 6:40 pm    Post subject: Reply with quote
Kolusu,

Thanks a lot for your quick response. The link you gave me was very helpful. This is a great Site

I did use your example and tried to run my sort. But I got some errors. Please find below the JCL

Code:

//STEP003 EXEC PGM=SYNCTOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN1      DD DSN=TEST1.INPUT,DISP=SHR
//IN2      DD DSN=TEST2.INPUT,DISP=SHR
//T1       DD DSN=T1.INPUT,DISP=SHR
//T2       DD DSN=T2.INPUT,DISP=SHR
//INT      DD DSN=T1.INPUT,DISP=SHR
//         DD DSN=T2.INPUT,DISP=SHR
//OUT      DD DSN=T1T2.OUTPUT,
//            DISP=(NEW,CATLG,DELETE),UNIT=3390,
//            SPACE=(CYL,(200,100),RLSE),
//            DCB=(RECFM=FB,LRECL=318,BLKSIZE=0)
//TOOLIN   DD *
  COPY FROM(IN1) USING(CTL1)
  COPY FROM(IN2) USING(CTL2)
  SORT FROM(INT) USING(CTL3)
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,OUTREC=(1,51,7Z,59,260,SEQNUM,8,ZD)
//CTL2CNTL DD *
  OUTFIL FNAMES=T2,OUTREC=(1,11,40Z,52,7,179Z,238,18,3Z,
                           259,35,25Z,SEQNUM,8,ZD)
//CTL3CNTL DD *
  OPTION EQUALS
  SORT FIELDS=(1,11,CH,A,                   
               290,4,PD,A,                 
               238,8,CH,A,                 
               246,10,CH,A,                 
               259,3,PD,A,                   
               262,2,PD,A,                   
               264,2,PD,A,                   
               266,6,PD,A,                   
               272,7,PD,A)                   
  SUM FIELDS=(12,40,52,3,55,4,59,33,92,35,127,35,162,35,
              197,30,227,2,229,5,234,4,256,3,294,25),FORMAT=BI
  OUTFIL FNAMES=OUT,OUTREC=(1,318)
/*
//


Here is the error I got. I am not sure if I formulated the sum fields correctly, but based on my output requirement I wrote it. Any ideas ?


[code:1:e457499fd3]
CTL1CNTL :
OUTFIL FNAMES=T1,OUTREC=(1,51,7Z,59,260,SEQNUM,8,ZD)
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=IN1,SORTDD=CTL1,COPY
WER428I CALLER-PROVIDED IDENTIFIER IS "0001"
WER164B 8,492K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 613K BYTES USED
WER146B 20K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I IN1 : RECFM=FB ; LRECL= 318; BLKSIZE= 27984
WER110I T1 : RECFM=FB ; LRECL= 326; BLKSIZE= 27710
WER405I T1 : DATA RECORDS OUT 20; TOTAL RECORDS OUT 20
WER416B IN1: EXCP'S=1,UNIT=3390,DEV=C22D,CHP=393A3B404142434A,VOL=WORKZF
WER416B OUTFIL WAS USED FOR CTL1OUT
WER054I RCD IN 20, OUT 20
WER169I RELEASE 1.1D BATCH 0426 TPF LEVEL 3A
WER052I END SYNCSORT
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
PostPosted: Fri Jun 24, 2005 5:23 am    Post subject: Reply with quote
geetha001,

There are a couple of errors in your posts.

1. You can only have 2,4,8 bytes for binary fields on SUM FIELDS. You coded 40 as length which is invalid. you need to split them in the multiples of 2/4/8 bytes.

2. You really don't need a Seqnum since you have the key fields.

so change your control cards to the following.

Code:

//CTL1CNTL DD *
  OUTFIL FNAMES=T1,
  OUTREC=(1,51,
          7Z,
          59,260)
//CTL2CNTL DD *
  OUTFIL FNAMES=T2,
  OUTREC=(1,11,
          40Z,
          52,7,
          179Z,
          238,18,
          3Z,
          259,35,
          25Z)
//CTL3CNTL DD *
  OPTION EQUALS
  SORT FIELDS=(1,11,CH,A,                   
               290,4,PD,A,                 
               238,8,CH,A,                 
               246,10,CH,A,                 
               260,2,PD,A,                   
               262,2,PD,A,                   
               264,2,PD,A,                   
               266,6,PD,A,                   
               272,7,PD,A)                   

   SUM FIELDS=(012,8,
               020,8,
               028,8,
               036,8,
               044,8,
               052,8,
               060,8,
               068,8,
               076,8,
               084,8,
               092,8,
               100,8,
               108,8,
               116,8,
               124,8,
               132,8,
               140,8,
               148,8,
               156,8,
               164,8,             
               172,8,             
               180,8,             
               188,8,             
               196,8,             
               204,8,             
               212,8,             
               220,8,             
               228,8,             
               236,2,             
               256,2,             
               258,2,             
               294,8,             
               302,8,             
               310,8),FORMAT=BI   
   OUTFIL FNAMES=OUT
/*



Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
geetha001
Beginner


Joined: 22 Jun 2005
Posts: 41
Topics: 14
PostPosted: Fri Jun 24, 2005 5:49 am    Post subject: Reply with quote
Thank a lot Kolusu. I will try that.

Thanks & Regards,
Geetha
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group