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 with more than one criteria

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

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Fri Jan 18, 2008 10:38 am    Post subject: Merging two files with more than one criteria
Reply with quote

Hi
i have the following requirement

there are 2 flat files (both are of same lrecl)
each file contains records like this
1) control record (record 1 of file)
2) datarecords (from record 2 to last but one record)
3) summary record (last record of a file)

For Eg:-
File 1
weekly report
data1
data2
data3
306(summary record which says total count of records and sum of field5)

File 2
weekly report
data4
data5
data6
315 (summary record which says total count of records and sum of field5)

Expected output
Weekly report
data1
data2
data3
data4
data5
data6
621


i.e output file should be in such a way that it should contain one control record, merge all the data record and one summary record which says total records and sum of fieldposition5

can any one help me with a sort card for this?
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: Fri Jan 18, 2008 10:58 pm    Post subject:
Reply with quote

This is actually quite tricky. But here's a DFSORT/ICETOOL job that will do it:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB)
//IN2 DD DSN=...  input file2 (FB)
//CTL2CNTL DD DSN=&&C2,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//CTL4CNTL DD DSN=&&C4,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//***> USE MOD FOR OUT FILE
//OUT DD DISP=MOD,DSN=...  output file (FB)
//TOOLIN   DD    *
COPY FROM(IN1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
COPY FROM(IN2) USING(CTL3)
COPY FROM(IN2) TO(T1) USING(CTL4)
COPY FROM(T1) USING(CTL5)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=OUT,ENDREC=1
  OUTFIL FNAMES=CTL2CNTL,REMOVECC,NODETAIL,
    BUILD=(80X),
    TRAILER1=('  OPTION SKIPREC=1,STOPAFT=',
     COUNT-2=(M11,LENGTH=8))
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=CTL4CNTL,REMOVECC,NODETAIL,
    BUILD=(80X),
    TRAILER1=('  OPTION SKIPREC=1,STOPAFT=',
     COUNT-2=(M11,LENGTH=8))
/*
//CTL5CNTL DD *
  OUTFIL FNAMES=OUT,REMOVECC,
    TRAILER1=(COUNT=(M11,LENGTH=1),
      TOTAL=(5,1,ZD,M11,LENGTH=2))
/*
Back to top
View user's profile Send private message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Wed Jan 23, 2008 4:08 pm    Post subject:
Reply with quote

Thank you Frank. but the requirement has changed now. and the new requuirement is

File 1 (LRECL=70):-
AAA 07365 12345
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
CCC 07365 12345 00000012129 000000011111111 .....

File 2 (LRECL 70):-
AAA 07365 12345
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
CCC 07365 12345 00000021219 000000011111111 .....

Expected output:-
AAA 07365 12345
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
CCC 07365 12345 00000033348 000000022222222 .....

00000033348 --> This is sum of trailer records (from position 16 to 26)

000000022222222 --> This is sum of trailer records (from position 28 to 42)
Back to top
View user's profile Send private message
raam_kumar

New User


Joined: 25 Apr 2007
Posts: 44
Location: chennai, India

PostPosted: Wed Jan 23, 2008 4:16 pm    Post subject:
Reply with quote

hi

i have also tried by myself to create a sort card for this requirement. But i was unable to do the summing functionality. the sort card which i used is as follows

//STEP0001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//INPUT DD DISP=SHR,DSN=<dsn1>
// DD DISP=SHR,DSN=<dsn2>
//TEMP1 DD SYSOUT=*
//TEMP DD DSN=&&TEMPDSN,
// DISP=(NEW,PASS),
// SPACE=(TRK,(5,5))
//OUTPUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(INPUT) TO(OUTPUT) USING(CTL1)
SORT FROM(INPUT) TO(OUTPUT) USING(CTL2)
COPY FROM(INPUT) TO(TEMP) USING(CTL3)
SORT FROM(TEMP) TO(TEMP1) USING(CTL5)
/*
//CTL1CNTL DD *
SORT FIELDS=COPY,
STOPAFT=1
//CTL2CNTL DD *
SORT FIELDS=COPY
OMIT COND=(1,3,CH,EQ,C'AAA',OR,1,3,CH,EQ,C'CCC')
/*
//CTL3CNTL DD *
SORT FIELDS=COPY
INCLUDE COND=(1,3,CH,EQ,C'CCC')
/*
//CTL5CNTL DD *
SORT FIELDS=COPY
SUM FIELDS=(16,11,ZD,28,15,ZD)

The problem is i am not able to produce the trailer record (i.e CCC) with the sum. i was able to get the trailer seperately, but could not find the sum values. could you please help me
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 Jan 23, 2008 11:21 pm    Post subject:
Reply with quote

For this new requirement, we can take advantage of the ids in the record (AAA for header, BBB for detail and CCC for trailer) to do what you want with DFSORT in one pass as follows:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT DD  SYSOUT=*
//SORTIN DD DSN=...  input file1 (FB/70)
//       DD DSN=...  input file2 (FB/70)
//SORTOUT DD DSN=...  output file (FB/70)
//SYSIN DD    *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(71:SEQNUM,8,ZD,79:26C'0')),
    IFTHEN=(WHEN=(1,3,CH,EQ,C'AAA'),OVERLAY=(71:8C'0')),
    IFTHEN=(WHEN=(1,3,CH,EQ,C'CCC'),
      OVERLAY=(71:8C'9',79:16,11,90:28,15))
  OPTION EQUALS,ZDPRINT
  SORT FIELDS=(1,3,CH,A,71,8,ZD,A)
  SUM FIELDS=(79,11,ZD,90,15,ZD)
  OUTREC IFOUTLEN=70,
    IFTHEN=(WHEN=(1,3,CH,EQ,C'CCC'),
      OVERLAY=(16:79,11,28:90,15))
/*
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 Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 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


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