MVSFORUMS.com :: View topic - Using ICETOOL in SUM and SELECT

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Using ICETOOL in SUM and SELECT

MVSFORUMS.com Forum Index -> Utilities

View previous topic :: View next topic

Author

Message

Novice
Beginner

Joined: 27 Dec 2002
Posts: 46
Topics: 15

Posted: Tue May 24, 2005 3:19 am Post subject: Using ICETOOL in SUM and SELECT

Here is my problem described in the following three steps. For clearer understanding the input file records structure and relation are explained(no of records in input file are close to 1 Million)

My Input file consists of the following

Code:

USER A/C - X(6) - SIX BYTES ALPHA NUMERIC
MANAGEMENT A/C - X(6) - SIX BYTES ALPHA NUMERIC
GEOGRAPHICAL REGION - X(1) - CONTAINS NUMERIC VALUES FROM 1 TO 9
ACCOUNT TYPE - X(1) - ONE BYTE ALPHA NUMERIC
AMOUNT - PIC 9(11) V (2) COMP-3.

(RELATION BETWEEN USER A/C AND MANAGEMENT A/C IS MULTIPLE TO 1)

Here are the requirements

Quote:

Step1 : Separate the records based on account type. Pick up records where account type = 1
Step 2 :
1)From the records selected in the previous step, Sum up amounts for a specific management account . Then arrange them in descending order of amount irrespective of the management account
2) Also add a indicator at the end of record for these 25 records as follows

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose

Posted: Tue May 24, 2005 8:04 am Post subject:

Novice,

Here is a DFSORT/ICETOOL JCL which will give you the desired results. I used the features OVERLAY and RESTART which are part of the new DFSORT PTFs UQ95214/UQ95213 from December, 2004.

Fyi.. I did not test this JCL as my shop has syncsort which does not support the overlay or restart features.

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=ISCP03.MGMT.EXTR.FOUND.D0524,
// DISP=SHR
//T1 DD DSN=&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(X,Y),RLSE)
//OUT1 DD DSN=YOUR OUTPUT FILE1,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//T2 DD DSN=&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(CYL,(X,Y),RLSE)
//OUT2 DD DSN=YOUR OUTPUT FILE2,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(1,1),RLSE)
//TOOLIN DD *
SORT FROM(IN) USING(CTL1)
SORT FROM(T1) USING(CTL2)
SORT FROM(T1) USING(CTL3)
SORT FROM(T2) USING(CTL4)
/*
//CTL1CNTL DD *
INCLUDE COND=(14,1,CH,EQ,C'1') $ ACT-TYPE '1' RECORDS
SORT FIELDS=(7,6,CH,A) $ SORT ON MGMT-ACT
SUM FIELDS=(15,7,PD) $ SUM ON AMT
OUTFIL FNAMES=T1 $ TEMP O/P FILE T1
/*
//CTL2CNTL DD *
SORT FIELDS=(15,7,PD,D) $ SORT ON AMT DESC
OUTFIL FNAMES=OUT1,ENDREC=25, $ TOP 25 RECORDS
OUTREC=(1,21, $ TOTAL LRECL
C'B', $ CONSTANT 'B'
SEQNUM,3,ZD) $ SEQNUM
/*
//CTL3CNTL DD *
SORT FIELDS=(13,1,CH,A) $ SORT ON REGION
SUM FIELDS=(15,7,PD) $ SUM ON AMT
OUTFIL FNAMES=T2 $ TEMP O/P FILE T2
/*
//CTL4CNTL DD *
SORT FIELDS=(13,1,CH,A, $ SORT ON REGION ASC
15,7,PD,D) $ SORT ON AMT DESC
OUTREC OVERLAY=(22:SEQNUM,8,ZD,RESTART=(13,1)) $ CREATE RANKING
OUTFIL FNAMES=OUT2, $ O/P FILE2
INCLUDE=(22,8,ZD,LE,10), $ INCLUDE TOP 10
OUTREC=(1,21, $ TOTAL LRECL
C'B', $ CONSTANT 'B'
13,1, $ REGION TYPE
SEQNUM,2,ZD) $ SEQNUM
/*

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose

Posted: Tue May 24, 2005 10:12 am Post subject:

Novice,

I just realized that you cannot use the temp file t1 for creating output2 as t1 is created by summing the amount based on mangement account.

So you need to change your control cards a little bit here.

1. change the 3rd toolin stmt from the following

Code:

SORT FROM(T1) USING(CTL3)

Code:

SORT FROM(IN) USING(CTL3)

2. Add the following line in CTL3CNTL as the first line.

Code:

INCLUDE COND=(14,1,CH,EQ,C'1') $ ACT-TYPE '1' RECORDS

Thanks

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Novice
Beginner

Joined: 27 Dec 2002
Posts: 46
Topics: 15

Posted: Thu May 26, 2005 1:53 am Post subject:

Thanks Kolusu. But right now my instalation do not have th feature of OVERLAY as the job shows a Syntax error. Thanks once again for your help Novice

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose

Posted: Thu May 26, 2005 6:07 am Post subject:

Novice, Please post your error messages. Kolusu _________________ Kolusu www.linkedin.com/in/kolusu

Display posts from previous:

	MVSFORUMS.com Forum Index -> Utilities	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum