Help required for a sort step.

In this Mainframe Forum - You can post your queries on JCL, OS/390 JCL, MVS JCL, z/OS JCL, JES2 & JES3

Moderators: Frank Yaeger, DikDude, Moderator Group

Post Reply
ambilileela
Member
Posts: 11
Joined: Tue Jun 06, 2006 2:12 pm

Help required for a sort step.

Post by ambilileela » Tue Jun 06, 2006 2:16 pm

Hi ,

I have a small query regarding usage of SORT card utility.

Situation:

There say a table(A) with the following data

Table A

Code: Select all

a1      a4  amts1  amts2    amts3      adate
james   30  40     40       40         24-jan-2005
james   30  13     23       23         25-jan-2005
james   30  13     23       23         26-jan-2005
james   30  13     23       23         27-jan-2005
james   30  13     23       23         28-jan-2005
...........................................
arnd 15 records for same column a1 value 'james'.

Similarly, i may have a no: of records with same value in column a1.

Now, the pbm is that

1) I need to check for the total no: of records for a given column 'a1', if the count is greater than 10 (say it is 'y'),then i need to merge the first (y-9) records as a single record, and then the rest will follow sequentially.

Let me explain with an example.

say i have 15 records with a1 value as 'James', then i need to merge first (15-9) records into a single record, with the date field(adate) pointing to the latest date of the six records. That means , at any given point of time
the record count for each unique column a1 value should not be more than 10 records.

And I have to incorporate this logic in the JCL Only .. meaning no COBOL code change.

Thanks,
Ambili.

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

Post by Frank Yaeger » Tue Jun 06, 2006 10:18 pm

Some questions:

Do you only have one key (e.g. "James") in the file, or can you have multiple keys (e.g. "James", "Richard", "Carl").

What is the RECFM and LRECL of the input file?

Are the adate values already in ascending order as shown in your example?

Can you please show a better example of the input records (with more than 10 instances of the key and multiple keys if that's what you have) and what you expect for output.
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

ambilileela
Member
Posts: 11
Joined: Tue Jun 06, 2006 2:12 pm

Post by ambilileela » Tue Jun 13, 2006 3:35 pm

Hi ,



Let me explain you in a more elaborate way:



Let the table be having the following columns:



Type Sub-Type Credit-Amt Debit-Amt



Type Sub-Type Date-field Credit-Amt Debit-Amt

Cereals Wheat 3/14/2001 15 17

Cereals Wheat 3/14/2001 24 22

Cereals Wheat 3/14/2001 2 4

Cereals Wheat 3/14/2001 3 3

Cereals Wheat 3/14/2001 3 3

Cereals Wheat 3/14/2001 10 9

Cereals Wheat 3/24/2001 17 17

Cereals Wheat 3/14/2001 22 22

Cereals Wheat 3/22/2001 4 4

Cereals Wheat 3/23/2001 3 3

Cereals Wheat 3/14/2001 3 3

Cereals Wheat 3/25/2001 9 9

Cereals Maize 3/26/2001 17 17

Cereals Maize 3/27/2001 22 22

Cereals Maize 3/14/2001 4 4

Cereals Maize 3/14/2001 3 3

Cereals Maize 3/14/2001 3 3

Cereals Maize 3/14/2001 9 9

Cereals Maize 3/14/2001 17 17

Cereals Maize 3/14/2001 22 22

Cereals Maize 3/24/2001 4 4

Cereals Maize 3/14/2001 3 3

Cereals Maize 3/19/2001 3 3

Cereals Maize 3/20/2001 9 9

Cereals Maize 3/21/2001 17 17

Cereals Maize 3/22/2001 22 22

Cereals Rice 4/9/2001 4 4

Cereals Rice 4/10/2001 3 3

Cereals Rice 3/14/2001 3 3

Cereals Rice 3/14/2001 9 9

Cereals Rice 3/14/2001 17 17

Cereals Rice 3/14/2001 22 22

Cereals Rice 3/14/2001 4 4

Cereals Rice 3/14/2001 3 3

Cereals Rice 3/24/2001 3 3

Cereals Rice 3/14/2001 9 9

Cereals Rice 3/17/2001 17 17

Cereals Rice 3/18/2001 22 22

Juices Beverages 3/19/2001 4 4

Juices Beverages 3/20/2001 3 3

Juices Beverages 4/23/2001 3 3

Juices Beverages 4/24/2001 9 9

Juices Beverages 4/25/2001 17 17

Juices Beverages 4/26/2001 22 22

Juices Beverages 4/27/2001 4 4

Juices Beverages 4/28/2001 3 3





Now I have sorted the records based on the Type and Date-field, and summed the

Credit-Amt and Debit-Amt fields thro a SORT step.



So say for a given Type Cereal and Date field '3/14/2001', it will sum all amts.



Now comes my problem.



given the above example , my file records after sum/sort will look like this:



Type Date-field Credit-Amt Debit-Amt

Cereals 3/14/2001 225 225

Cereals 3/17/2001 3 3

Cereals 3/19/2001 26 26

Cereals 3/20/2001 20 20

Cereals 3/21/2001 9 9

Cereals 3/22/2001 6 6

Cereals 3/23/2001 4 4

Cereals 3/24/2001 49 48

Cereals 3/26/2001 3 3

Cereals 3/27/2001 2 4

Cereals 4/9/2001 24 22

Cereals 4/10/2001 15 17

Cereals 4/23/2001 3 3

Juices 3/19/2001 4 4

Juices 3/20/2001 3 3

Juices 4/23/2001 3 3

Juices 4/24/2001 9 9

Juices 4/25/2001 17 17

Juices 4/26/2001 22 22

Juices 4/27/2001 4 4

Juices 4/28/2001 3 3





Now, for a given Type, there should not be more than 10 records, but as you see

that Type 'Cereal' is having 13 no: of records, so what i intend to do is that

take the count of records for a given Type (in this case '13'), then

merge the first (13-9=4) records into the first record by summing up the amts, and taking

the latest date value among the first(13-9=4) records.

And then the rest of the records(13-4) reocrds can be sequentially added , so that I will

have just (9+1=10) records at any given time.



Taking the above example, we will have the final output as:



Type Date-field Credit-Amt Debit-Amt

Cereals 3/20/2001 274 274 (After merging of first 4 records)

Cereals 3/21/2001 9 9

Cereals 3/22/2001 6 6

Cereals 3/23/2001 4 4

Cereals 3/24/2001 49 48

Cereals 3/26/2001 3 3

Cereals 3/27/2001 2 4

Cereals 4/9/2001 24 22

Cereals 4/10/2001 15 17

Cereals 4/23/2001 3 3

Juices 3/19/2001 4 4

Juices 3/20/2001 3 3

Juices 4/23/2001 3 3

Juices 4/24/2001 9 9

Juices 4/25/2001 17 17

Juices 4/26/2001 22 22

Juices 4/27/2001 4 4

Juices 4/28/2001 3 3



Hence, total of 10 records(max) for a given Type.





I hope this example has cleared your queries.



Do let me know for any solution reg this.





Btw, RECFM-FB

LRECL-38 of the input file.



Thanks in advance,

Ambili

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 Jun 14, 2006 12:11 am

Ok, now I understand what you want to do. It's rather tricky, but the DFSORT/ICETOOL job below will give you what you asked for. Since you didn't give me the starting position, length and format of each of your fields, I assumed the following:

Code: Select all

Type        =   1,12,CH
Date-field  =  13,10,CH
Credit-Amt  =  23,5,ZD
Debit-Amt   =  28,5,ZD
I also assumed that your input records were already in order as shown.

So the input records for Cereals look like this:

Code: Select all

Cereals     03/14/20010022500225
Cereals     03/17/20010000300003
Cereals     03/19/20010002600026
Cereals     03/20/20010002000020
Cereals     03/21/20010000900009
Cereals     03/22/20010000600006
Cereals     03/23/20010000400004
Cereals     03/24/20010004900048
Cereals     03/26/20010000300003
Cereals     03/27/20010000200004
Cereals     04/09/20010002400022
Cereals     04/10/20010001500017
Cereals     04/23/20010000300003
and the output records for Cereals look like this:

Code: Select all

Cereals      3/20/20010027400274
Cereals      3/21/20010000900009
Cereals      3/22/20010000600006
Cereals      3/23/20010000400004
Cereals      3/24/20010004900048
Cereals      3/26/20010000300003
Cereals      3/27/20010000200004
Cereals      4/09/20010002400022
Cereals      4/10/20010001500017
Cereals      4/23/20010000300003
You can change the job appropriately if my assumptions are incorrect.

Code: Select all

//S1    EXEC PGM=ICETOOL
//TOOLMSG   DD SYSOUT=*
//DFSMSG    DD SYSOUT=*
//IN  DD DSN=... input file (FB/38)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//    DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//T3 DD DSN=&&T3,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/38)
//TOOLIN DD *
COPY FROM(IN) USING(CTL1)
SPLICE FROM(CON) TO(T3) ON(1,12,CH) -
  WITHALL WITH(1,52) USING(CTL2)
COPY FROM(T3) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,REMOVECC,NODETAIL,
    BUILD=(54X),
    SECTIONS=(1,12,
      TRAILER3=(1,12,53:COUNT-9=(TO=ZD,LENGTH=2)))
  OUTFIL FNAMES=T2,
    OVERLAY=(39:1,12,51:SEQNUM,2,ZD,RESTART=(1,12),2X)
/*
//CTL2CNTL DD *
  OUTFIL FNAMES=T3,
    IFTHEN=(WHEN=(51,2,ZD,LE,53,2,ZD),OVERLAY=(51:C'00'))
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
    BUILD=(1,38),
    SECTIONS=(39,14,
      TRAILER3=(1,22,
        TOT=(23,5,ZD,TO=ZD,LENGTH=5),
        TOT=(28,5,ZD,TO=ZD,LENGTH=5)))
/*
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

ambilileela
Member
Posts: 11
Joined: Tue Jun 06, 2006 2:12 pm

Post by ambilileela » Tue Jul 04, 2006 5:30 pm

Hi,

Thnx for the reply and the solution. I havent tried that solution , but then I wanted to know where does this code takes care of having maximum of those (count-9) records date field?

Do let me know at the earliest and Oblige.

Thanks in advance.
Ambili

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

Post by Frank Yaeger » Tue Jul 04, 2006 9:00 pm

wanted to know where does this code takes care of having maximum of those (count-9) records date field?
Well, it's rather tricky. Let's just take the Cereals records for an example.

T1 will have:

Code: Select all

Cereals                                             04 

T2 will have:

Code: Select all

Cereals      3/14/2001  225  225      Cereals     01    
Cereals      3/17/2001    3    3      Cereals     02    
Cereals      3/19/2001   26   26      Cereals     03    
Cereals      3/20/2001   20   20      Cereals     04    
Cereals      3/21/2001    9    9      Cereals     05    
Cereals      3/22/2001    6    6      Cereals     06    
Cereals      3/23/2001    4    4      Cereals     07    
Cereals      3/24/2001   49   48      Cereals     08    
Cereals      3/26/2001    3    3      Cereals     09    
Cereals      3/27/2001    2    4      Cereals     10    
Cereals      4/09/2001   24   22      Cereals     11    
Cereals      4/10/2001   15   17      Cereals     12    
Cereals      4/23/2001    3    3      Cereals     13    
We SPLICE the 04 from T1 into the T2 records to get:

Code: Select all

Cereals      3/14/2001  225  225      Cereals     0104    
Cereals      3/17/2001    3    3      Cereals     0204    
Cereals      3/19/2001   26   26      Cereals     0304    
Cereals      3/20/2001   20   20      Cereals     0404    
Cereals      3/21/2001    9    9      Cereals     0504    
Cereals      3/22/2001    6    6      Cereals     0604    
Cereals      3/23/2001    4    4      Cereals     0704    
Cereals      3/24/2001   49   48      Cereals     0804    
Cereals      3/26/2001    3    3      Cereals     0904    
Cereals      3/27/2001    2    4      Cereals     1004    
Cereals      4/09/2001   24   22      Cereals     1104    
Cereals      4/10/2001   15   17      Cereals     1204    
Cereals      4/23/2001    3    3      Cereals     1304    
We use:

IFTHEN=(WHEN=(51,2,ZD,LE,53,2,ZD),OVERLAY=(51:C'00'))

to get:

Code: Select all

Cereals      3/14/2001  225  225      Cereals     0004
Cereals      3/17/2001    3    3      Cereals     0004
Cereals      3/19/2001   26   26      Cereals     0004
Cereals      3/20/2001   20   20      Cereals     0004
Cereals      3/21/2001    9    9      Cereals     0504
Cereals      3/22/2001    6    6      Cereals     0604
Cereals      3/23/2001    4    4      Cereals     0704
Cereals      3/24/2001   49   48      Cereals     0804
Cereals      3/26/2001    3    3      Cereals     0904
Cereals      3/27/2001    2    4      Cereals     1004
Cereals      4/09/2001   24   22      Cereals     1104
Cereals      4/10/2001   15   17      Cereals     1204
Cereals      4/23/2001    3    3      Cereals     1304
Then we use SECTIONS, NODETAIL and TRAILER3 to get only the last of the four '0004' duplicate records along with the unique '0504'-'1304' records. That gives us the last 10 records you wanted.
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