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

How to count records in JCL based on some condition.


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Sat Jun 28, 2014 3:11 pm
Reply with quote

I have to count the records in JCL based on some conditions ....

For example :

I have following records :

Counting is based on 1st & 3rd field and file is comma delimited.

123456789012345678901234567890
______________________________
100,12.345,A001
100,14.56,A001
100,15.678,A001
101,12.34,B001
101,14.57,B001
101,67.894,C001

I want in the output as :

100,A001 3
101,B001 2
101,C001 1

Then i have to put the condition on each count and check if it is greater than 1 then need to copy in final output dataset.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sat Jun 28, 2014 4:11 pm
Reply with quote

Your sample data shows that it is already in sequence for counting. Is it really like that?
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Sun Jun 29, 2014 1:13 pm
Reply with quote

Hi Bill ,

I have counted the records which are having 1st & 3rd fields are same . But i couldn't do it for the records which doesn't have 3rd records at the same column because of 2nd field decimal part . So for that i need to add trailing zeros in second field so that all the 3rd fields come at the same column .
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Sun Jun 29, 2014 2:07 pm
Reply with quote

Forget the decimal for now. Is the data, record-to-record, already in the order that you need, as you show in your sample?
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Mon Jun 30, 2014 11:07 am
Reply with quote

Yes , Bill
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 30, 2014 11:58 am
Reply with quote

OK. Your data is already in order, so we don't want to use SORT and SUM (waste of resources).

So use MERGE and SUM. You can use MERGE with a single input file (SORTIN01 instead of SORTIN) and then use SUM.

This allows you to use INCLUDE=/OMIT= in OUTFIL and do the entire processing in one step.

You will need to rearrange your input data, because the second part of your key is not in a fixed position and because you need a value of one on the record to get a count of the keys out of the SUM.

Variable-position data means PARSE. If your input records are fixed-length, extend at the end of the record for the second part of the key and the count of one (the count field needs to be big enough to hold the maximum possible value of the count for a group of records.

So:

INREC with two IFTHEN=(WHEN=INIT to PARSE (add new field) and have a count of one (new field).

MERGE FIELDS=(original-major-key,new-minor-key)
SUM FIELDS=(your-new-value-of-one)

OUTFIL OMIT=(summed-ones equal to one)
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Mon Jun 30, 2014 1:31 pm
Reply with quote

Bill ,

Could you please bit more clear , it would be better if you give code snippet.

Thanks
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Mon Jun 30, 2014 1:42 pm
Reply with quote

I used below JCL , this will give me output :

JCL

//S001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SORTIN DD *
A,17.28,A
A,13.28,A
A,17.29,B
A,17.278,A
B,19.67,B
B,16.59,C
C,11.32,A
C,34.56,C
//RPT DD SYSOUT=*
//RPT1 DD SYSOUT=*
//TOOLIN DD *
OCCUR FROM(SORTIN) LIST(RPT) ON(1,1,CH) ON(9,1,CH) ON(VALCNT) BLANK
/*


OUTPUT

(1,1,CH) (9,1,CH) VALUE COUNT
A , 1
A A 2
A B 1
B B 1
B C 1
C A 1
C C 1



Now i have to put condition on VALUE COUNT if it is greater than 1 then only print the following record in output file . Also i need to add trailing 0 into the decimal part of second field so that i can get the third field at the same column and put the exact column in ON clause.[/img][/i]
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 30, 2014 1:49 pm
Reply with quote

If you want to use ICETOOL (which is fine) look at SELECT, not OCCUR.

You'll need a USING(xxxx) and in that xxxxCNTL file you'll need an INREC with the PARSE. You won't need the count-of-one because SELECT is doing what you want.

If using SELECT, remember to turn off the SORT (SORT FLELDS=NONE or OPTION COPY).
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Jun 30, 2014 3:59 pm
Reply with quote

Your new topic seems to be connected to this, and has been locked.

According to your sample output, you don't even need the field with decimals. Find the third field using PARSE, you can even overwrite the decimal value (according to your output) with the second part of the key from the PARSE.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 11:34 am
Reply with quote

Thanks Bill , PARSE worked for me ...... Now I have to count the records based on 1st & 3rd field and if count > 250 then only need to select .

Could you please give me some pointer , how to do it?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 01, 2014 11:54 am
Reply with quote

I've outlined one with, with the MERGE and SUM of a generated field containing the value one.

Meanwhile, you started moving towards ICETOOL. If you use a generated sequence number from WHEN=GROUP (where the group is the same as your key) in a USING(xxxx) with the SELECT of the last record of a key, you should then be able to use OUTFIL OMIT=/INCLUDE= for your selection.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 12:11 pm
Reply with quote

K Bill , I will try to do with WHEN=GROUP , Could you please tell me from where i can get the details about WHEN=GROUP since i have never used it before.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 01, 2014 12:44 pm
Reply with quote

Lots of examples here, there is a DFSORT Getting Started Guide Application Programming Guide, both available for free use, plus when you get the hang of it look at the Smart DFSORT tricks publication.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 12:49 pm
Reply with quote

I used this card :

SORT FIELDS=COPY
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),PUSH=(9:ID=10))
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,8,
TRAILER3=(1,8,X,COUNT=(M11,LENGTH(10))))

But i need one more field at column 9 in KEYBEGIN . How do i mention that into it .
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 01, 2014 1:27 pm
Reply with quote

If you need contiguous data, make the data contiguous. OVERLAY=(somewhere-convenient:1,3,%03) and you have it.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 1:41 pm
Reply with quote

Hi Bill ,

I made the data contiguous used below sort card :

SORT FIELDS=COPY
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(11:ID=10))
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,10,
TRAILER3=(1,10,X,COUNT=(M11,LENGTH(10)))),
OMIT COND=(13,10,ZD,GT,0000000001)

I am trying to OMIT the record which is having count greater than 1 but it is not working. Please help me out.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 01, 2014 3:15 pm
Reply with quote

The method you have chosen won't allow you to do it in the same step, as you are getting the COUNT *after* the OMIT=/INCLUDE=.

If you look at the MERGE with SUM I suggested, for instance, it should allow you to OMIT= on the OUTFIL.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 5:55 pm
Reply with quote

Hi Bill ,

I am using this sort card . Is it possible than with the help of INREC first i format the record then i use WHEN=GROUP ,


SORT FIELDS=COPY
INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,14),PUSH=(15:ID=10))
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,15,
TRAILER3=(1,15,X,COUNT=(M11,LENGTH(10))))

I have to add

INREC FIELDS=(1,4,77,10)

Please tell me how can i add it.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 01, 2014 6:05 pm
Reply with quote

You can have multiple functions on INREC, OUTREC and OUTFIL by specifying multiple IFTHEN=(WHEN=. In your case, add IFTHEN=(WHEN=INIT to do your BUILD, before your WHEN=GROUP.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 6:41 pm
Reply with quote

Hi Bill ,

I gave this sort card as you said :

SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,93,10,986X)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,14),PUSH=(15:ID=10))
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,15,
TRAILER3=(1,15,X,COUNT=(M11,LENGTH(10))))


But it is not working.

Could you please correct it .
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jul 01, 2014 6:49 pm
Reply with quote

I have no clue of what is not working because you've failed to mention that.
Back to top
View user's profile Send private message
ShobhitVerma10april

New User


Joined: 28 Jun 2014
Posts: 21
Location: INDIA

PostPosted: Tue Jul 01, 2014 7:09 pm
Reply with quote

HI Bill ,
Sorry for inconvenience

Here is the details :

INPUT :
ABC 12 A0
ABC 12 A0
ABC 12 A0
ABC 12 B0
GHT 13 B0
GHT 13 B0
GHT 13 C0
GHT 13 B0
UIO 12 C0
UIO 12 C0
UIO 12 D0


OUTPUT :

ABCA00 00003
ABCB00 00001
GHTB00 00002
GHTC00 00001
GHTB00 00001
UIOC00 00002
UIOD00 00001


SORT CARD :

SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,BUILD=(1,3,8,2,75X)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(6:ID=5))
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,6,
TRAILER3=(1,6,X,COUNT=(M11,LENGTH(5))))

In this , if you see GHTB0 should come 3 times but here it is coming as two occurrence 2 and 1 .
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Jul 01, 2014 7:27 pm
Reply with quote

Please use Code tags for readability.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Jul 01, 2014 8:10 pm
Reply with quote

Hi,

From the second post of Bill I see that, He asked for data to be in order record to record and you said it is sorted, then why did in the above example it is not sorted based on 1st and 3rd field?

Code:
ABC 12 A0
ABC 12 A0
ABC 12 A0
ABC 12 B0
GHT 13 B0
GHT 13 B0
GHT 13 C0
GHT 13 B0
UIO 12 C0
UIO 12 C0
UIO 12 D0
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 -> DFSORT/ICETOOL Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
Search our Forums:

Back to Top