Need to keep only the last records for a certain group
Moderators: Frank Yaeger, Moderator Group
Need to keep only the last records for a certain group
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
# 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
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
That does make a difference. Here's a DFSORT/ICETOOL for that requirement:I should have mentioned my input file is VB and the records are not always in order.
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
SPLICE Question
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.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page