COUNT of records based on distinct field value(ICETOOL/SORT)

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

Moderators: Frank Yaeger, Moderator Group

Post Reply
Suchita
Member
Posts: 6
Joined: Fri Dec 02, 2011 2:45 pm

COUNT of records based on distinct field value(ICETOOL/SORT)

Post by Suchita » Mon Dec 05, 2011 5:05 pm

Hi All,

I have PS (FB, 113 LRECL) file which contains 2 fields:
Field1 - starts at 1 & 6 bytes in length
Field2 - Starts at 8 & 9 bytes in length

Here is sample data in the file-
ASS002 009878654
ASS002 998634664
ASS002 485781533
ARR003 346583746
ARR003 361764465
AZZ987 346764766


My Requirement is, I want output PS in following format -
ASS002 3
ARR003 2
AZZ987 1

i.e. count of Field2 records for each Field1 value.


I wrote following code to get output I required -

Code: Select all

//STP030 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                     
//SORTIN    DD  DISP=SHR,DSN=DEVDW.TEMP2.F40002.SB19    
//SORTOUT   DD DSN=DEVDW.F40002.SB19,                   
//             DSORG=PS,LRECL=113,RECFM=FB,                
//             DISP=(NEW,CATLG,DELETE),                    
//             SPACE=(CYL,(4,50),RLSE)                     
//SYSIN    DD *                                            
  INREC OVERLAY=(114:SEQNUM,8,ZD,RESTART=(114,6))          
  SORT FIELDS=(114,6,CH,A,120,9,ZD,A)                      
  OUTFIL REMOVECC,NODETAIL,                                
    BUILD=(1,113),                                         
    SECTIONS=(114,6,                                       
      TRAILER3=(TOT=(1,6,CH,TO=CH,LENGTH=6),               
        TOT=(8,9,ZD,TO=ZD,LENGTH=9),                       
        COUNT=(M11,LENGTH=9)))                             
/*   



However, I am getting following messages in SYSOUT -
TRAILER3=(TOT=(1,6,CH,TO=CH,LENGTH=6),
£
ICE223A 0 REPORT FIELD ERROR
TOT=(8,9,ZD,TO=ZD,LENGTH=9),
£
ICE005A 0 STATEMENT DEFINER ERROR
COUNT=(M11,LENGTH=9)))
£
ICE005A 0 STATEMENT DEFINER ERROR


This means, my code statements are not supported on my mainframe.

Is there any other/alternative way to acheive output I require using ICETOOL/SORT??


Thanks,
Suchita

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 Dec 05, 2011 11:12 pm

You have several errors in your job. The one that resulted in the syntax error is using TOT=(1,6,CH,TO=CH which makes no sense and is invalid - but even if you fix that, your job won't work the way you coded it because 120,9,ZD is beyond the end of your records.

You can get what you say you want for output (which doesn't match your job at all) using DFSORT control statements like this:

Code: Select all

  OPTION COPY                                   
  OUTFIL REMOVECC,NODETAIL,                     
    SECTIONS=(1,6,                              
      TRAILER3=(1,6,X,COUNT=(M11,LENGTH=9)))    
Alternatively, you could use the OCCURS operator of DFSORT's ICETOOL.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss? ... g3T7000080
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

Suchita
Member
Posts: 6
Joined: Fri Dec 02, 2011 2:45 pm

Post by Suchita » Tue Dec 06, 2011 1:35 pm

Thanks a ton Frank...!!! It is working now with above code.

Thank you again for your time.

Suchita
Member
Posts: 6
Joined: Fri Dec 02, 2011 2:45 pm

Post by Suchita » Fri Dec 09, 2011 6:55 pm

Hi All,

I used above given Frank's code in my JCL to get count,there is small issue in my output file.

When input file is empty/dont have any data, my output file is displaying blank spaces for Field1 (1-6 bytes), followed by zeroes for COUNT (i.e. Field2 in my output file).

Output Required:
Whenever input file is empty, zeroes should not be written in output file (i.e. output file should be empty if input file is empty).

Please help.


Thanks,
Suchita

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

Post by Frank Yaeger » Fri Dec 09, 2011 10:36 pm

Is it ok to get RC=4 with the output file empty when the input file is empty, and RC=0 when the input file is not empty?
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

Suchita
Member
Posts: 6
Joined: Fri Dec 02, 2011 2:45 pm

Post by Suchita » Sat Dec 10, 2011 8:36 pm

Yes Frank, I am fine with RC=4 when output file is empty.

Infact, I am using this RC=4 condition (when output file is empty) in next JCL step to execute some other functionality.

Output Required Is:-
Whenever input file is empty, zeroes should not be written in output file (i.e. output file should be empty if input file is empty) & it should return RC=4

Thank you very much for your reply.
Please help me.

Thanks,
Suchita

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 Dec 10, 2011 9:52 pm

You can use this DFSORT/ICETOOL job to set RC=4 when the input file is empty and RC=0 if it's not empty. Then you can do a conditional test in the next step to use or bypass the DFSORT step that writes the count.

Code: Select all

//S1    EXEC  PGM=ICETOOL         
//TOOLMSG DD SYSOUT=*             
//DFSMSG  DD SYSOUT=*             
//IN DD *                         
RECORD                            
//OUT DD SYSOUT=*                 
//TOOLIN DD *                     
COUNT FROM(IN) EMPTY RC4          
/*
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