IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Utility to find the count in a PS File


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Mon Oct 29, 2007 11:24 pm
Reply with quote

Hi..

Is there any utility that can be used in JCL to find the count of records with respect to a particular field in that file.

Please provide me a JCl or the step...
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Mon Oct 29, 2007 11:26 pm
Reply with quote

Quote:
Please provide me a JCl or the step...

Please provide me the current consultancy rate before code available.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Mon Oct 29, 2007 11:37 pm
Reply with quote

Quote:
Is there any utility that can be used in JCL to find the count of records with respect to a particular field in that file.


It's not clear from your description exactly what you want. Show an example of your input records (relevant fields only) and the expected output records. Give the starting position, length and format of the relevant fields. Give the RECFM and LRECL of the input file.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Mon Oct 29, 2007 11:47 pm
Reply with quote

Input file is like this

RAM ,ADAM ,1000,5
ARUN ,PAUL ,1001,6
MASTER ,RAM ,2000,5
ARUN ,JOHN ,1001,6
MISTER ,ADAM ,2000,5

I need to get the count of records by the last field.

EG: Count of records with 5 = 3
Count of records with 6 = 2

Please let me know if you need any more info.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Mon Oct 29, 2007 11:53 pm
Reply with quote

Is the last field (5, 6, 5, etc) always in the same column in every record (which column?) or can it be in different columns as shown in your example?
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Mon Oct 29, 2007 11:56 pm
Reply with quote

Yes it's an FB file and the last field will always be in that position. Lets assume that the field is in column 30. I'll modify the code to my needs.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 1:10 am
Reply with quote

sathyaraj,

Here are a couple of ways to do it with DFSORT. Each method can be tweaked as necessary.

1) DFSORT/ICETOOL OCCUR operator

Code:

//S1    EXEC  PGM=ICETOOL                                       
//TOOLMSG DD SYSOUT=*                                           
//DFSMSG  DD SYSOUT=*                                           
//IN DD DSN=...  input file
//RPT DD SYSOUT=*                                               
//TOOLIN DD *         
OCCUR FROM(IN) LIST(RPT) BLANK -         
  HEADER('Records with') ON(30,1,CH) -   
  HEADER('Count') ON(VALCNT)             
/*


RPT would have:

Code:

Records with             Count   
------------   ---------------   
5                            3   
6                            2   


2) DFSORT SECTIONS

Code:

//S2    EXEC  PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  SORT FIELDS=(30,1,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    SECTIONS=(30,1,
    TRAILER3=('Count of records with ',30,1,' = ',
      COUNT=(EDIT=(IT))))
/*


SORTOUT would have:

Code:

Count of records with 5 =  3 
Count of records with 6 =  2 


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:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 1:19 am
Reply with quote

Wow... Thats really cool... Thanks for saving my skin..
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 1:29 am
Reply with quote

Glad to help.

In the future, try to give more information about your requirement in your first post to save everyone time. Note that you can use code tags to make your examples look right.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 2:52 am
Reply with quote

Hi Frank..

My Input file has Approx 12,800,000 records.. DFSORT is just giving the output in two digits, hence, I'm not getting the correct count.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 3:29 am
Reply with quote

I just guessed and changed EDIT=(IT) to EDIT=(IIIIIIT) and am getting the correct results now.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 4:02 am
Reply with quote

Well, that's what I meant about giving the correct information in the first post.

The ICETOOL method would work for that number of records as I showed it.

You showed the output as having 1 digit, so I gave you 2 digits for the DFSORT method. If you had showed me 8 digits, I would given you 9 digits. For 9 digits you can use EDIT=(IIIIIIIIT) or M10,LENGTH=9. For 10 digits, you can use EDIT=(IIIIIIIIIT) or M10,LENGTH=10. Adjust accordingly.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 4:17 am
Reply with quote

Code:

RAM ,ADAM ,1000,5
ARUN ,PAUL ,1001,6
MASTER ,RAM ,2000,5
ARUN ,JOHN ,2000,6
MISTER ,ADAM ,2000,5


Hi..

In the above e.g I need to generate one more count.. i.e..

For the number 5 there are 3 records but the third field is 2000 only for 2 records. I just need to count that, and also The records having values other than 2000, thus giving 5 & 2000 - 2 records, other than 2000 1 record.

For the other number 6. Having 6 and 2000 - 1 record, other than 2000 - 1 record..

Sorry if I didn't make it clear.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 4:41 am
Reply with quote

It'd be great if I can get a result like this

Code:

                           2000   others
Count of records with 5 =  2           1 
Count of records with 6 =  1           1

Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 4:52 am
Reply with quote

You can use a DFSORT job like this for your new requirement:

Code:

//S3    EXEC  PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  INREC IFTHEN=(WHEN=(15,4,ZD,EQ,+2000),OVERLAY=(31:C'2000 ')),
        IFTHEN=(WHEN=NONE,OVERLAY=(31:C'Other'))
  SORT FIELDS=(30,6,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    BUILD=(80X),
    SECTIONS=(30,6,
    TRAILER3=('Count of records with ',30,1,' and ',31,5,' = ',
      COUNT=(M10,LENGTH=10)))
/*


SORTOUT will have:

Code:

Count of records with 5 and Other =          1
Count of records with 5 and 2000  =          2
Count of records with 6 and Other =          1
Count of records with 6 and 2000  =          1
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 4:55 am
Reply with quote

Quote:
It ll be great if i can get a result like this


I created the last job before I saw your post with the output you want.

I'm leaving work now - if you need me to, I can show you how to get that output tomorrow. Let me know.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 7:36 pm
Reply with quote

Hi Frank..

Please let me know how to generate the O/P in teh desired format as i have given in the above E.G.

Also one more thing.. Am having the O/P for few hundred pages and I need a line '-------------' at the end of the first page alone. Please let me know if this is possible.. I tried using headers but its giving lines in all the pages.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 10:03 pm
Reply with quote

Here's a DFSORT job that will format the output like the example you showed:

Code:

//S4    EXEC  PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(37:C'0',50:C'1')),
        IFTHEN=(WHEN=(15,4,ZD,EQ,+2000),OVERLAY=(37:C'1',50:C'0'))
  SORT FIELDS=(30,1,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    HEADER2=(34:'2000',45:'others'),
    BUILD=(80X),
    SECTIONS=(30,1,
    TRAILER3=('Count of records with ',30,1,' = ',
      27:TOT=(37,1,ZD,M10,LENGTH=10),
      41:TOT=(50,1,ZD,M10,LENGTH=10)))
/*


For your input example, SORTOUT has:

Code:

                                 2000       others   
Count of records with 5 =          2             1   
Count of records with 6 =          1             1   


Quote:
Am having the O/P for few hundred pages and I need a line '-------------' at the end of the first page alone. I tried using headers but its giving lines in all the pages.


Huh? Since you only get one line per value (e.g. '5') in position 30 - how do you get hundreds of pages from that?

HEADER2 prints lines at the top of the page. How are you using it to print a line at the end of the page? TRAILER2 prints lines at the end of the page. But as you say, HEADER2/TRAILER2 prints lines on every page.

You'd have to explain more about what you're trying to do and what your output looks like before I could try to help you with this requirement.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Tue Oct 30, 2007 10:21 pm
Reply with quote

Thanx for the new code..

Yup.. What i gave was just an example.. I have some hundreds of such values for which the count is to be determined..

Actually Am doing this operation for two files in two steps.. Which will be merged later to form a single report.. So am just giving the line in the header of the second report so that it ll become a partition for the two reports...

Code:

NO OF EMP RECORDS WITH IPLAN IND 7      =    1215355
NO OF DEP RECORDS WITH IPLAN IND 7      =    1675400
NO OF EMP RECORDS WITH IPLAN IND 6      =     191698
NO OF DEP RECORDS WITH IPLAN IND 6      =     373431
NO OF EMP RECORDS WITH IPLAN IND 5      =    1696575
NO OF DEP RECORDS WITH IPLAN IND 5      =    2411017
NO OF DEP RECORDS FOR CUSTOMER  0002284 =       7200
NO OF EMP RECORDS FOR CUSTOMER  0002284 =       3519
NO OF DEP RECORDS FOR CUSTOMER  0006472 =       1668
NO OF EMP RECORDS FOR CUSTOMER  0006472 =       2096


This is the part where i need teh line..

Is there any other way by which i can enter a line...?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Tue Oct 30, 2007 11:24 pm
Reply with quote

If you just want one line at the end of the first part of the report, then you can use TRAILER1 in OUTFIL for the first part of the report:

Code:

   OUTFIL ...,
      ..., 
      TRAILER1=(n'-')


This will give you n dashes as the very last line of the report. Then MOD your second part of the report after that.
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Wed Oct 31, 2007 1:31 am
Reply with quote

That code was really mind blowing.. the logic that has been employed was more impressive than the utility itself.. That '0' & '1' Thing...

Am really felling good about joining this forum.. Wish to have a long run... icon_smile.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Oct 31, 2007 2:02 am
Reply with quote

Quote:
the logic that has been employed was more impressive than the utility itself


More impressive than DFSORT? You obviously haven't explored much of what DFSORT/ICETOOL offers. It's extremely impressive if I do so say myself. icon_wink.gif
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Wed Oct 31, 2007 11:06 pm
Reply with quote

Hi Frank..

Got one more modification in the requirement.. is there anyway to give the serial number for the records?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Wed Oct 31, 2007 11:17 pm
Reply with quote

Quote:
is there anyway to give the serial number for the records


It's not clear what you mean by "serial number" or where it would fit into your report. Do you mean "sequence number"? Please explain what you want more clearly. Show an example of the output you want including the "serial number".
Back to top
View user's profile Send private message
sathyaraj

New User


Joined: 28 Sep 2007
Posts: 71
Location: India.

PostPosted: Thu Nov 01, 2007 12:05 am
Reply with quote

Code:

1.NO OF EMP RECORDS WITH IPLAN IND 7      =    1215355
2.NO OF DEP RECORDS WITH IPLAN IND 7      =    1675400 2.
3.NO OF EMP RECORDS WITH IPLAN IND 6      =     191698
4.NO OF DEP RECORDS WITH IPLAN IND 6      =     373431
5.NO OF EMP RECORDS WITH IPLAN IND 5      =    1696575
6.NO OF DEP RECORDS WITH IPLAN IND 5      =    2411017
1.NO OF DEP RECORDS FOR CUSTOMER  0002284 =       7200
2.NO OF EMP RECORDS FOR CUSTOMER  0002284 =       3519
3.NO OF DEP RECORDS FOR CUSTOMER  0006472 =       1668
4.NO OF EMP RECORDS FOR CUSTOMER  0006472 =       2096


Please let me know if this explains the requiremet....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts To get the count of rows for every 1 ... DB2 3
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top