Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sort/sum and choice of any value different of space

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Tue Mar 13, 2007 5:00 am    Post subject: Sort/sum and choice of any value different of space
Reply with quote

is it possible to sort and sum on the field M1 like this ?

before sort :
Key1 space A space M1
Key1 space space B M1
Key1 C space space M1
Key2 A space space M1
Key2 space space space M1

after sort :
Key1 C A B sum(M1)
Key2 A space space sum(M1)

That is to say, the sort takes any value rather than a space.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue Mar 13, 2007 5:20 am    Post subject:
Reply with quote

I can't figure out what you want to do. I think your input data looks like this (b for blank):

Code:

Key1bAbM1
Key1bbBM1
Key1CbbM1
Key2AbbM1
Key2bbbM1


and you want your output data to look like this:

Code:

Key1CABsum(M1)
Key2Abbsum(M1)


But what is sum(M1)? Is M1 a numeric value you want summed? If so, what is the starting position, length and format of M1?

And what exactly are the "rules" for the three positions after Keyx?? For example, if you had the following input:

Code:

Key1bAbM1
Key1bGXM1
Key1bbBM1
Key1CbbM1
Key1DFbM1


What would you expect for the output records? What are the "rules" for getting from input to output?

Also, what is the RECFM and LRECL of your input file?
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Tue Mar 13, 2007 1:53 pm    Post subject: Re: Sort/sum and choice of any value different of space
Reply with quote

The three fields after key have in common to be out of the key and not summed.
M1 is a packed decimal value to be summed.

The problem is that DFSORT chooses at random one value among all possible
To take your example,

code :
Code:

Key1bAbM1
Key1bGXM1
Key1bbBM1
Key1CbbM1
Key1DFbM1


after sort/sum only one record will be written, but which value in position 4, 5 and 6 ?
in position 4 : may be b(lank) or C, or D...
The rule : I want any non blank value if it exists (here in position 4, C or D, it doesn't matter, but not b)

I want after sort/sum

Code:

Key1CAXsum(M1)


or
Code:

Key1DAXsum(M1)


or
Code:

Key1CGXsum(M1)


or, etc...
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue Mar 13, 2007 9:30 pm    Post subject:
Reply with quote

Quote:
The problem is that DFSORT chooses at random one value among all possible


Not really. If you have EQUALS in effect, then DFSORT chooses the first record with each key. If you have NOEQUALS in effect, then DFSORT chooses one record with each key. But you want to take a different field from different records with the same key. There's no built-in function to do that.

However, you can do it with the following DFSORT/ICETOOL job. You didn't tell me the starting postion and length of your PD field as I asked, so for the example, I assumed it was in positions 8-9. You can change the job appropriately if needed.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file
//TOOLIN   DD    *
SELECT FROM(IN) TO(T1) ON(1,4,CH) FIRST USING(CTL1)
SELECT FROM(IN) TO(T1) ON(1,4,CH) FIRST USING(CTL2)
SELECT FROM(IN) TO(T1) ON(1,4,CH) FIRST USING(CTL3)
SORT FROM(IN) TO(T1) USING(CTL4)
SPLICE FROM(T1) TO(OUT) ON(1,4,CH) -
 WITHEACH WITH(6,1) WITH(7,1) WITH(8,2)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,4,CH,A,5,1,CH,D)
/*
//CTL2CNTL DD *
  SORT FIELDS=(1,4,CH,A,6,1,CH,D)
/*
//CTL3CNTL DD *
  SORT FIELDS=(1,4,CH,A,7,1,CH,D)
/*
//CTL4CNTL DD *
  SORT FIELDS=(1,4,CH,A)
  SUM FIELDS=(8,2,PD)
/*
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Wed Mar 14, 2007 5:24 am    Post subject: Re: Sort/sum and choice of any value different of space
Reply with quote

Good job !

However, I don't understand (may be time) why there's no "WITH(5,1)"
in

Quote:
WITHEACH WITH(6,1) WITH(7,1) WITH(8,2)


Another question : what about the performance of such a step with million of records ?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Wed Mar 14, 2007 8:26 pm    Post subject:
Reply with quote

Quote:
I don't understand why there's no "WITH(5,1)"


WITH(5,1) isn't necessary. 5,1 comes from the base record (first record). 6,1, comes from the first overlay record (second record). 7,1 comes from the second overlay record (third record). 8,2 comes from the third overlay record (fourth record).

For complete details on how the SPLICE operator of DFSORT's ICETOOL works, see:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

Quote:
what about the performance of such a step with million of records?


"Performance" in the context you're using it is a very vague term. Compared to what? Measured by what criteria? The only way to determine if "performance" is "acceptable" by your criteria is to run some experiments and evaluate the results for yourself.
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Wed Mar 14, 2007 10:12 pm    Post subject: Re: Sort/sum and choice of any value different of space
Reply with quote

OK, thank you, I've understood for the base record in (5,1)

About performance, I wander how the sort is done.
Are there 4 sorts running one after the other ? In this case, it might be expensive...

I compare with a COBOL program where MOVE for non blank value are made after "internal" sort and before summing and writing in output file.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Thu Mar 15, 2007 1:01 am    Post subject:
Reply with quote

Quote:
Are there 4 sorts running one after the other ? In this case, it might be expensive...



There are actually 5 sorts here. Each SELECT does a sort. SORT does a sort. And SPLICE does a sort. Each SELECT only writes a subset of the records. The SORT and SPLICE only sort a subset of the records. Whether it's "expensive" depends on how many records are in the input file and what you consider "expensive". The reason we need all of these sorts is that you're looking for several fields in each record among a group of records. DFSORT is record oriented, not group oriented, although it can do "tricks" with groups. It might be possible to do it another way using less sorts (e.g. COPYs instead of SORTs), but I don't have time to figure it out for you.

Quote:
I compare with a COBOL program where MOVE for non blank value are made after "internal" sort and before summing and writing in output file.


You asked for a sort solution, so I assumed you didn't want to write a program. This could certainly be done faster using DFSORT and an E35 exit with the appropriate logic (probably similar to whatever you'd use for your COBOL program and perhaps faster than the COBOL program or perhaps not).

Certainly, nobody is stopping you from writing a COBOL program if that's what you want.
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Thu Mar 15, 2007 4:41 am    Post subject: Re: Sort/sum and choice of any value different of space
Reply with quote

Quote:
Certainly, nobody is stopping you from writing a COBOL program if that's what you want.


Note that I don't want to write a COBOL program... for the reason this one already exists in prod.

But I ask myself if it would be better to do it with DFSORT in the future.

Quote:
This could certainly be done faster using DFSORT and an E35 exit with the appropriate logic (probably similar to whatever you'd use for your COBOL program and perhaps faster than the COBOL program or perhaps not).


In fact, the E35 could be a way to improve performance... I think nobody here at work have run it yet
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Thu Mar 15, 2007 5:37 am    Post subject:
Reply with quote

If you already have the COBOL program, then you could run it and the DFSORT solution I gave you with the same data and see what the performance looks like for yourself. I suspect the COBOL program will be "faster" in this case because it's more targeted to what it has to do whereas DFSORT is a general purpose utility. On the other hand, DFSORT with an E35 might be faster than the COBOL program because it could also be targeted to what it has to do.

Note that if you had said up-front that you already have a COBOL program and were looking for a DFSORT solution that would run faster than the COBOL program, I probably wouldn't have bothered to post my solution. I thought you didn't have a way to do what you wanted and were looking for one. Please try to be more forthcoming about what you're trying to do in the future.
Back to top
View user's profile Send private message
Searchman

New User


Joined: 28 Dec 2006
Posts: 80
Location: France

PostPosted: Thu Mar 15, 2007 1:49 pm    Post subject: Re: Sort/sum and choice of any value different of space
Reply with quote

Note that your solution was very interesting for me and I will certainly use it in the future. Sure It would have been a shame you didn't post the solution. I'm here to learn.

Now, I recognize there was two questions in my initial post :

1- how to do it with DFSORT ?
2- which solution is the best with ten millions of records ?

I have not preconceived idea on the way to use either DFSORT or COBOL.
The fact is that we have more than 2000 COBOL programs and a few problems of performance. I wonder if in some cases, it wouldn't be better to replace "internal" SORT by DFSORT.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us