Need to keep only the last records for a certain group

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
rbowers
Member
Posts: 4
Joined: Sat Jan 16, 2010 3:09 am

Need to keep only the last records for a certain group

Post by rbowers » Sat Jan 16, 2010 3:21 am

Data Example
# date seq
1234567 2009-06-18 1
1234567 2009-06-18 2
1234567 2009-06-25 1
1234567 2009-06-25 2
1234567 2009-06-26 1
1234567 2009-06-26 2
8888888 2009-06-05 1
8888888 2009-06-05 2
8888888 2009-06-05 3
8888888 2009-06-12 1
8888888 2009-06-12 2
8888888 2009-06-12 3

I want to keep only the latest dated records for each #
1234567 2009-06-26 1
1234567 2009-06-26 2
8888888 2009-06-12 1
8888888 2009-06-12 2
8888888 2009-06-12 3

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Sat Jan 16, 2010 4:19 am

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes. I assumed your input records are already in order by the # and date as shown in your example.

Code: Select all

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
COPY FROM(IN) USING(CTL1)
COPY FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,7,CH) ON(9,10,CH) WITHALL -
  WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:C'BB')
  OUTFIL FNAMES=T1,REMOVECC,NODETAIL,

    SECTIONS=(1,7,
     TRAILER3=(1,82))
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'VB'),
    BUILD=(1,80)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

rbowers
Member
Posts: 4
Joined: Sat Jan 16, 2010 3:09 am

Post by rbowers » Mon Jan 18, 2010 7:52 pm

Thank you for your quick response. I should have mentioned my input file is VB and the records are not always in order.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Jan 18, 2010 10:35 pm

I should have mentioned my input file is VB and the records are not always in order.
That does make a difference. Here's a DFSORT/ICETOOL for that requirement:

Code: Select all

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file (VB/24)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=... output file (VB/24)
//TOOLIN DD *
SELECT FROM(IN) TO(T1) ON(5,7,CH) LAST USING(CTL1)
COPY FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(7,7,CH) ON(15,10,CH) WITHALL VLENOVLY-
  WITH(5,1) WITH(7,20) USING(CTL3)
/*
//CTL1CNTL DD *
  SORT FIELDS=(5,7,CH,A,13,10,CH,A)
  OUTFIL FNAMES=T1,BUILD=(1,4,5:C'BB',7:5)
/*
//CTL2CNTL DD *
  OUTFIL FNAMES=T1,BUILD=(1,4,5:C'VV',7:5)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(5,2,CH,EQ,C'VB'),
    BUILD=(1,4,5:7)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

rbowers
Member
Posts: 4
Joined: Sat Jan 16, 2010 3:09 am

Post by rbowers » Tue Jan 19, 2010 2:07 am

Thank you again! This works great. Can you point me to link where I can read what this code means so I can understand what it's doing? Or can you explain it? I don't want to implement this until I can understand it completely and document it well.

Thank you so much for your time!

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Jan 19, 2010 2:41 am

For explanations of how this kind of DFSORT/ICETOOL code works, see the "Create files with matching and non-matching records " Smart DFSORT Trick at:

http://www.ibm.com/support/docview.wss? ... g3T7000094

In your case, we only have one input file, but we break it into two input files before we do the splice. The first input file will have the "last" records with the 'BB' id in positions 5-6. The second input will have all of the records with the 'VV' id in positions 5-6.

For complete information on DFSORT's ICETOOL, see:

http://publibz.boulder.ibm.com/cgi-bin/ ... 0527161936
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

rbowers
Member
Posts: 4
Joined: Sat Jan 16, 2010 3:09 am

SPLICE Question

Post by rbowers » Mon Feb 01, 2010 8:37 pm

On the SPLICE WITH(7,20)...I have MANY files that have the same first 2 records and then have different data for the rest of the file. The LRECL varies for each file. Is there any negative to using WITH(7,2000) or some extra large # that greatly exceeds the longest LRECL of all my files so I can use this one sort card for all the files without needing to maintain individual files. Your advice is greatly appreciated.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Feb 01, 2010 10:26 pm

Yes, you should be able to use the longest LRECL. There might be a performance degradation, but it probably won't be anything to worry about. The only way to find out for sure is to try it and evaluate the results by your own criteria.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

academyindia4

Topic deleted by Admin

Post by academyindia4 » Mon Jan 25, 2016 10:15 pm

<< Content deleted By Admin >>

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others