COUNT in Syncsort

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

Moderators: Frank Yaeger, Moderator Group

Post Reply
biswaranjan
Member
Posts: 20
Joined: Thu Aug 28, 2008 10:41 am

COUNT in Syncsort

Post by biswaranjan » Mon Aug 23, 2010 3:28 pm

Code: Select all

//SORT01   EXEC PGM=SORT 
//SORTLIB  DD DSN=SYS1.SORTLIB, 
//            DISP=SHR 
//SYSOUT   DD SYSOUT=* 
//SYSPRINT DD SYSOUT=* 
//SRTWKSP  INCLUDE MEMBER=SRTWRK00 
//SORTJNF1 DD DSN=INPUT1, 
//            DISP=SHR 
//SORTJNF2 DD DSN=INPU2, 
//            DISP=SHR 
//OSORT1   DD DSN=&&TEMP1,DISP=(NEW,PASS) 
//OSORT2   DD DSN=&&TEMP2,DISP=(NEW,PASS) 
//OSORT3   DD DSN=&&TEMP3,DISP=(NEW,PASS) 
//SYSIN DD * 
 JOINKEYS FILE=F1,FIELDS=(001,015,A) 
 JOINKEYS FILE=F2,FIELDS=(001,015,A) 
 JOIN UNPAIRED 
 REFORMAT FIELDS=(F1:001,015,F2:001,023) 
 SORT FIELDS=COPY 
 OUTFIL FNAMES=OSORT1,INCLUDE=((1,15,CH,EQ,16,15,CH),AND, 
               (35,1,CH,EQ,C'Y')), 
 OUTREC=(01:016,20) 
 OUTFIL FNAMES=OSORT2,INCLUDE=((1,15,CH,EQ,16,15,CH),AND, 
               (35,1,CH,EQ,C'N')), 
 OUTREC=(01:016,20) 
 OUTFIL FNAMES=OSORT3,INCLUDE=(16,1,CH,EQ,C' '), 
 OUTREC=(01:01,20) 
//* 
//SORT02   EXEC PGM=SORT 
//SORTLIB  DD DSN=SYS1.SORTLIB, 
//            DISP=SHR 
//SYSOUT   DD SYSOUT=* 
//SYSPRINT DD SYSOUT=* 
//SRTWKSP  INCLUDE MEMBER=SRTWRK00 
//SORTIN   DD DSN=&&TEMP1,DISP=OLD 
//         DD DSN=&&TEMP2,DISP=OLD 
//         DD DSN=&&TEMP3,DISP=OLD 
//SORTOUT  DD SYSOUT=* 
//SYSIN DD * 
 SORT FIELDS=COPY 
/* 
Input1:
375000000102001
375000000341013
375000000431004
375000000651007

Input2:
375000000102009 YYDE
375000000211000 NYDE
375000000341013 YYDE
375000000431004 NYDE
375000000472008 NYDE
375000000651007 YYDE
375000000762002 NYDE


O/P 1:

375000000341013 Y
375000000651007 Y
375000000431004 N
375000000102001

I want to create a O/P file having the count of recors based on the indicator 'Y/N & Space'.

O/P 2:
2 >> INDICATOR Y
2 >> INDICATOR N or Space

Can this ll be acheived in a single SORT Step? If it can be done in a single step how it can be done?

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 Aug 23, 2010 10:32 pm

Can this ll be acheived in a single SORT Step?
Do you mean a single pass over the data? Or do you really mean a single step (which can have multiple passes)?

It's not clear what it is you're trying to do so it's difficult to help you.

Please show an example of the records in each input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the starting position, length and format of each relevant field. Give the RECFM and LRECL of the input files. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.

Also, run this job and show the //SYSOUT messages you receive, so I can see what level you're at:

Code: Select all

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN    DD    *
    OPTION COPY
/*
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

biswaranjan
Member
Posts: 20
Joined: Thu Aug 28, 2008 10:41 am

Post by biswaranjan » Wed Aug 25, 2010 9:03 am

The objective is to capture only the counts in O/P based on certain conditions.

Input1(LRECL=15,RECFM=FB):
375000000102001
375000000341013
375000000431004
375000000651007

Input2(LRECL=23,RECFM=FB):

Card Number(15 digits),4blanks,Indicator(Column Position 20, Values Y and N),Rest 3bytes(Not to be used in file compare)
***********************
375000000102009 YYDE
375000000211000 NYDE
375000000341013 YYDE
375000000431004 NYDE
375000000472008 NYDE
375000000651007 YYDE
375000000762002 NYDE



Conditions:

1. count of matching records which are both in Input1 and Input2 and having indicator as Y(Column postion 20 in input2).

Count : 2

Output:
375000000341013 Y
375000000651007 Y

2.
Sum of the Count of records(Count of 2.1 and 2.2 explained below 1+1=2) based on the below two conditions.

2.1 count of matching records which are both in Input1 and Input2 and having indicator as N(Column postion 20 in input2).

Count :1
Output:
375000000431004 N

2.2 count of records which are only in input1.

Count: 1
Output:
375000000102001

***********
Final Output:
***********
First record shows the count of condition 1 and 2nd record shows the count of condition 2 as mentioned above.

2
2

Please find the O/P message:


SYNCSORT LICENSED FOR CPU SERIAL NUMBER 464FF, MODEL 2097 607 LICEN
SYSIN :
JOINKEYS FILE=F1,FIELDS=(001,015,A)
JOINKEYS FILE=F2,FIELDS=(001,015,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:001,015,F2:001,023)
SORT FIELDS=COPY
OUTFIL FNAMES=OSORT1,INCLUDE=((1,15,CH,EQ,16,15,CH),AND,
(35,1,CH,EQ,C'Y')),
OUTREC=(01:016,20)
OUTFIL FNAMES=OSORT2,INCLUDE=((1,15,CH,EQ,16,15,CH),AND,
(35,1,CH,EQ,C'N')),
OUTREC=(01:016,20)
OUTFIL FNAMES=OSORT3,INCLUDE=(16,1,CH,EQ,C' '),
OUTREC=(01:01,20)
WER276B SYSDIAG= 69025, 733173, 733173, 1665150
WER164B 5,784K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,087,520 BYTES USED
WER146B 4K BYTES OF EMERGENCY SPACE ALLOCATED
WER481I JOINKEYS REFORMAT RECORD LENGTH= 38, TYPE = F
WER110I OSORT1 : RECFM=FB ; LRECL= 20; BLKSIZE= 27980
WER110I OSORT2 : RECFM=FB ; LRECL= 20; BLKSIZE= 27980
WER110I OSORT3 : RECFM=FB ; LRECL= 20; BLKSIZE= 27980
WER410B 4,756K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER410B 0 BYTES RESERVE REQUESTED, 100K BYTES USED
WER405I OSORT1 : DATA RECORDS OUT 2; TOTAL RECORDS OUT 2
WER405I OSORT2 : DATA RECORDS OUT 1; TOTAL RECORDS OUT 1
WER405I OSORT3 : DATA RECORDS OUT 1; TOTAL RECORDS OUT 1

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

Post by Frank Yaeger » Wed Aug 25, 2010 10:06 pm

Although your Subject mentions DFSORT, the WER messages indicate you're using actually using Syncsort, not DFSORT. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

I changed your Subject to say "Syncsort".
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

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