SYNCSORT to Add Fields and Create Summary Records
Moderators: Frank Yaeger, Moderator Group
SYNCSORT to Add Fields and Create Summary Records
I have an analyst on my team that is trying to use syncsort to sum the values from 3 data columns from a file, then add them to create a summary level record when a control break is reached (dealer, part or model changes). Here's a simplified version of the file layout:
dealer
part
model
demand1
demand2
demand3
Add demand 1, 2 and 3 for each line. Add the sum into a summary record that is created for each unique dealer-part-model combination.
Is this possible with syncsort or will it require several syncsoft steps (add columns in 1 step, sum into summary record in a second step)?
JCL to Add Fields and Create Summary Records
Hi rcticejr,
Its possible with ICETOOL,
First let me give my understanding of your requirement and the way i coded based on it,if my understanding is wrong please correct me.
Say we have a input FILEA with the following records
FILEA -> RECS
dealer part model demand1 demand2 demand3
D1 P1 M1 15 15 15
D1 P1 M1 14 14 14
D2 P2 M2 15 15 15
D2 P2 M2 14 14 14
Final Output Desired as per your requirement
********************************************
dealer part model Sum(demand1+ demand2+ demand3) per each key
D1 P1 M1 87
D2 P2 M2 87
Find below the JCL
//TESTSUMX JOB (AAAA,04,T,0000),'SORTT',CLASS=C,MSGCLASS=T,
// NOTIFY=AAAA
/*JOBPARM L=9999
//*
//JOBLIB DD DSN=AAAA.LOAD,DISP=SHR
//*
//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//*
//IN1 DD *
D1 P1 M1 15 15 15
D1 P1 M1 14 14 14
D2 P2 M2 15 15 15
D2 P2 M2 14 14 14
//*
//TMP1 DD DSN=AAAA.TEST.TMP1,
// DISP=(MOD,PASS),
// UNIT=SYSDA,
// SPACE=(CYL,(1,2),RLSE),
// DCB=(RECFM=FB,LRECL=11,BLKSIZE=110)
//*
//OUT DD DSN=AAAA.TEST.SPLOUTF,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,2),RLSE),
// DCB=(RECFM=FB,LRECL=11,BLKSIZE=110)
//*
//TOOLIN DD *
COPY FROM(IN1) TO(TMP1) USING(CPY1)
SORT FROM(TMP1) TO(OUT) USING(CPY2)
//CPY1CNTL DD *
OUTFIL FNAMES=TMP1,
BUILD=(1,9,(10,2,ZD,ADD,13,2,ZD,ADD,16,2,ZD),EDIT=(TT),SIGNS=(+,-))
//CPY2CNTL DD *
SORT FIELDS=(1,9,CH,A)
SUM FIELDS=(10,2,CH)
Thanks
Veera.
Its possible with ICETOOL,
First let me give my understanding of your requirement and the way i coded based on it,if my understanding is wrong please correct me.
Say we have a input FILEA with the following records
FILEA -> RECS
dealer part model demand1 demand2 demand3
D1 P1 M1 15 15 15
D1 P1 M1 14 14 14
D2 P2 M2 15 15 15
D2 P2 M2 14 14 14
Final Output Desired as per your requirement
********************************************
dealer part model Sum(demand1+ demand2+ demand3) per each key
D1 P1 M1 87
D2 P2 M2 87
Find below the JCL
//TESTSUMX JOB (AAAA,04,T,0000),'SORTT',CLASS=C,MSGCLASS=T,
// NOTIFY=AAAA
/*JOBPARM L=9999
//*
//JOBLIB DD DSN=AAAA.LOAD,DISP=SHR
//*
//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//*
//IN1 DD *
D1 P1 M1 15 15 15
D1 P1 M1 14 14 14
D2 P2 M2 15 15 15
D2 P2 M2 14 14 14
//*
//TMP1 DD DSN=AAAA.TEST.TMP1,
// DISP=(MOD,PASS),
// UNIT=SYSDA,
// SPACE=(CYL,(1,2),RLSE),
// DCB=(RECFM=FB,LRECL=11,BLKSIZE=110)
//*
//OUT DD DSN=AAAA.TEST.SPLOUTF,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,2),RLSE),
// DCB=(RECFM=FB,LRECL=11,BLKSIZE=110)
//*
//TOOLIN DD *
COPY FROM(IN1) TO(TMP1) USING(CPY1)
SORT FROM(TMP1) TO(OUT) USING(CPY2)
//CPY1CNTL DD *
OUTFIL FNAMES=TMP1,
BUILD=(1,9,(10,2,ZD,ADD,13,2,ZD,ADD,16,2,ZD),EDIT=(TT),SIGNS=(+,-))
//CPY2CNTL DD *
SORT FIELDS=(1,9,CH,A)
SUM FIELDS=(10,2,CH)
Thanks
Veera.
This might help us!
Veera,
Interesting, I have not tried ICETOOL in the past, but this looks like it may be something that could achieve our goal. Thank you for your suggestion!
Interesting, I have not tried ICETOOL in the past, but this looks like it may be something that could achieve our goal. Thank you for your suggestion!
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