MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SORT - sum of AST, select from 2 files

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
banan
Beginner


Joined: 11 Jan 2005
Posts: 6
Topics: 2
PostPosted: Tue Jan 11, 2005 11:58 am    Post subject: SORT - sum of AST, select from 2 files Reply with quote
Hi,

i’ve searched for this in the forum, but found nothing!

i’ve got a problem with SORT and
- Build the sum of AST
- select records from a file depend on (existing) keys reading from another one.
We have only SORT/ICEMAN, but no ICETOOL! Sad

Precisely: i have 2 files (today und yesterday), both FB, LRECL=19, stucture:

YYYYMMDDnnnnnnnnnn+,
Key (1,8 ) number/ZD (9,10) and sign (19,1)
e.g.
yesterday
200501010000000005-
200501020000000010-
today:
200501010000000005+
200501010000000003-
200501030000000011+

i need to sum the fields, but the record/key only existing in the yesterday-file must be deleted!

result: 2 records
200501010000000003- => key-sum of yersterday (1 time) and today (2 times)
200501030000000011+ => sum of today (1time)
key 20040102 plays no role, it doesnt exists in todays file.

i do belive, that it goes with SORT, but how?!

many thanks
ban
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Jan 11, 2005 12:21 pm    Post subject: Reply with quote
Quote:
We have only SORT/ICEMAN, but no ICETOOL!


I don't understand this statement. If you have DFSORT (PGM=ICEMAN or PGM=SORT) then you have ICETOOL (PGM=ICETOOL). ICETOOL has been shipped free with DFSORT since 1991. Are you saying you use Syncsort (WER messages) rather than DFSORT (ICE messages)?

I can probably come up with a solution using DFSORT's ICETOOL, but I don't answer questions on Syncsort.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Jan 11, 2005 4:10 pm    Post subject: Reply with quote
banan,

Banan,

Try this job. I assuming that you don't have duplicates in your yesterday's file.

Code:

//STEP0100 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD DSN=YOUR YESTERDAY'S FILE,
//            DISP=SHR
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//SYSIN    DD *                                         
  SORT FIELDS=COPY                                       
  INREC FIELDS=(1,8,19,1,9,10)                           
  OUTREC FIELDS=(1,8,9,11,FS,PD,LENGTH=8,7C'0',C'1')     
/*                                                       
//STEP0200 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD DSN=YOUR TODAY'S FILE,
//            DISP=SHR
//SORTOUT  DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//SYSIN    DD *                                         
  SORT FIELDS=COPY                                       
  INREC FIELDS=(1,8,19,1,9,10)                           
  OUTREC FIELDS=(1,8,9,11,FS,PD,LENGTH=8,7C'0',C'2')     
/*                                                       
//STEP0300 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD DSN=&T1,DISP=OLD                           
//         DD DSN=&T2,DISP=OLD                           
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                         
  OPTION EQUALS                                           
  SORT FIELDS=(1,8,CH,A)                                 
  SUM FIELDS=(9,8,PD,17,8,ZD)                                   
  OUTFIL OMIT=(17,8,ZD,EQ,1),                             
  OUTREC=(1,8,9,8,PD,EDIT=(TTTTTTTTTTS),SIGNS=(,,+,-))   
//*                                               



Hope this helps...

Cheers

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

Last edited by kolusu on Tue Jan 11, 2005 8:06 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
banan
Beginner


Joined: 11 Jan 2005
Posts: 6
Topics: 2
PostPosted: Tue Jan 11, 2005 4:15 pm    Post subject: Reply with quote
Hi Frank!

Thanks for the reply.
i am only a stupid programmer. If i use PGM=SORT, it runs from the "standard" steplib. with icetool i get 806 - pgm not found. maybe the sysadmin didnt install it? suppose, we have icetool, i have problem only with convert the AST in PD - AST cant be summarized, or am i mistaken?

thanks
ban
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Jan 11, 2005 5:50 pm    Post subject: Reply with quote
Kolusu,

When I run your job, I get the wrong output:

Code:

200501010000000005- 
200501030000000011+ 


5- instead of 3-. Are you getting 3-?

I don't understand why you're summing on 17,8,ZD since that's the file identifier rather than the converted AST value.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Jan 11, 2005 5:58 pm    Post subject: Reply with quote
Banan,

If you get an 806 ABEND for ICETOOL, then I suspect you're using Syncsort. ICETOOL is installed in the same libaries as DFSORT, so your System Programmers would have had to do something strange to "uninstall" ICETOOL.

Check your messages - for Syncsort, you'll get WER messages whereas for DFSORT you'll get ICE messages.

With z/OS DFSORT V1R5 PTF UQ95214 or DFSORT R14 PTF UQ95213 (Dec, 2004), you could use INREC with the new SFF (signed free form) format to convert your "AST" values to ZD values directly so you can SUM them. Using DFSORT's ICETOOL, you can do it all in one step as follows:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TIN DD DSN=... Today's file
//YIN DD DSN=... Yesterday's file
//TT DD DSN=&&TT,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file
//TOOLIN DD *
  COPY FROM(TIN) TO(TT) USING(CTL1)
  COPY FROM(YIN) TO(TT) USING(CTL2)
  SORT FROM(TT) USING(CTL3)
//CTL1CNTL DD *
* Add 'T' id at end of today records.
  OUTREC FIELDS=(1,19,20:C'T')
//CTL2CNTL DD *
* Add 'Y' id at end of yesterday records.
  OUTREC FIELDS=(1,19,20:C'Y')
//CTL3CNTL DD *
* Refomat records to:
* |date|zd_value|id|
  INREC FIELDS=(1,8,9:9,11,SFF,TO=ZD,LENGTH=11,20:20,1)
* Sort on date.
  SORT FIELDS=(1,8,CH,A)
* Sum on zd_value
  SUM FIELDS=(9,11,ZD)
  OUTFIL FNAMES=OUT,
* Remove extra record with Y id.
    OMIT=(20,1,CH,EQ,C'Y'),
* Reformat records back to:
* |date|ast_value|
    OUTREC=(1,8,9:9,11,ZD,M1,SIGNS=(,,+,-),LENGTH=11)
/*


Of course, if you're not using DFSORT, or you don't have the new PTF yet, you can't do it this way.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

Last edited by Frank Yaeger on Thu May 19, 2005 3:05 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Tue Jan 11, 2005 8:05 pm    Post subject: Reply with quote
Quote:

When I run your job, I get the wrong output:


Frank,

Thanks for pointing it out. I missed the AST field in the sum fields in step0300 of my job. I missed it duing the cut and paste. I am updating my post to reflect the change.

Code:

//SYSIN    DD *                                       
  OPTION EQUALS                                         
  SORT FIELDS=(1,8,CH,A)                               
  SUM FIELDS=(9,8,PD,17,8,ZD)                           
  OUTFIL OMIT=(17,8,ZD,EQ,1),                           
  OUTREC=(1,8,9,8,PD,EDIT=(TTTTTTTTTTS),SIGNS=(,,+,-)) 
/*                                                   


Quote:

I don't understand why you're summing on 17,8,ZD since that's the file identifier rather than the converted AST value.


hmm banan's orginal request was to DELETE record/key which exists only in the yesterday's file .

By summing on the file identifier , and using it in the omit condition we eliminate the non matching records from the output.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Jan 11, 2005 8:31 pm    Post subject: Reply with quote
Quote:
By summing on the file identifier , and using it in the omit condition we eliminate the non matching records from the output.


Oh, that's an interesting way to do it. My approach for that was to sort the Today ('T') records ahead of the Yesterday ('Y') records and just sum on the converted AST field. That way, SUM keeps the 'T' record and I can just use 'Y' to OMIT the extra (nonsummed) 'Y' record.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
banan
Beginner


Joined: 11 Jan 2005
Posts: 6
Topics: 2
PostPosted: Wed Jan 12, 2005 3:55 am    Post subject: Reply with quote
Dear Kolusu,

many thanks for the solution, it works great, even after i made some changes to have exactly i needed Smile

Frank,

many thanks for you as well, till now i didnt realise the difference between syncsort und dfsort Embarassed . As you suggested, PGM=SORT starts here syncsort...

ban
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group