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 and Overlay

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


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Wed May 20, 2009 12:57 pm    Post subject: Sort and Overlay Reply with quote
Hi All ,

I need to sort out records from file2 which matches the empnum and deptnum in file1 and also overlay pos 352 len 11 with the value given in file 1. Below is an example.

Code:


File 1  ( Length : 80 byte)

Emp num : Pos 1 length 6
dept num : pos 7 length 9
Amount :  Pos 16 length 11


12345612345678900000083317
12345611111111100000121909
12345622222222200000086353
12345655555555500000066680
12345666666666600000066680
12345688888888800000067305


File 2  (length : 2000)

Emp num : Pos 3 length 6
dept num : pos 18 length 9
Amount :  Pos 351length 11 ( 2 DECIMAL ASSUMED)

       pos 3             pos 18                           pos 351
XXX123456XXXXXXXXX....8888888888.......................0000011467A......
XXX123456XXXXXXXXX....6666666666.......................0000038250{......
XXX123456XXXXXXXXX....5555555555.......................0000000000{......
XXX123456XXXXXXXXX....4444444444.......................0000031150{......
XXX123456XXXXXXXXX....3333333333.......................0000030050{......

Output  ( Length : 2000 )

      pos 3             pos 18                           pos 351
XXX123456XXXXXXXXX....8888888888.......................0000006730E......
XXX123456XXXXXXXXX....6666666666.......................0000006668{......
XXX123456XXXXXXXXX....5555555555.......................0000006668{......




Please let me know if this can be done?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Wed May 20, 2009 2:52 pm    Post subject: Reply with quote
Martin,

Martin,

The following DFSORT JCL will give you the desired results. I assumed that your input 80 byte file does not have duplicates. I used $$ in first 2 pos to differentiate that it is a record from 80 byte file. Make sure that your 2000 byte input file does not have '$$' in pos 1 and 2.

Code:

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD DSN=Your 80 byte file,DISP=SHR
//SORTOUT  DD DSN=&&I1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)   
//SYSIN    DD *                                               
  SORT FIELDS=COPY
  OUTREC BUILD=(C'$$',1,6,18:7,9,351:16,11,2000:X)           
/* 
//STEP0200 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD DSN=&&I1,DISP=SHR                           
//         DD DSN=Your 2000 byte file to be replaced,DISP=SHR
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                           
  SORT FIELDS=(3,6,CH,A,18,7,CH,A),EQUALS                 
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'$$'),     
  PUSH=(351:351,11,2001:3,6,18,9))                       
  OUTFIL BUILD=(1,2000),                                 
  INCLUDE=(01,2,CH,NE,C'$$',AND,                         
           03,6,CH,EQ,2001,6,CH,AND,                     
           18,9,CH,EQ,2007,9,CH)                         
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Wed May 20, 2009 3:19 pm    Post subject: Reply with quote
Thanks a lot Kolusu .. But unfortunately we do not have latest DFSORT patch. Is there a way to achieve this without the GROUP clause?

Please let me know.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Wed May 20, 2009 4:22 pm    Post subject: Reply with quote
Martin,

Replace step0200 with the following

Code:

//STEP0200 EXEC PGM=ICETOOL                         
//TOOLMSG  DD SYSOUT=*                             
//DFSMSG   DD SYSOUT=*                             
//IN       DD DSN=&&I1,DISP=SHR                     
//         DD DSN=Your 2000 byte file to be replaced,DISP=SHR
//OUT      DD SYSOUT=*                             
//TOOLIN   DD *                                     
  SPLICE FROM(IN) TO(OUT) ON(3,6,CH) ON(18,9,CH) - 
  WITH(1,2000) USING(CTL1)
//CTL1CNTL DD *                                 
  INREC OVERLAY=(2001:351,11,1,2)               
  OUTFIL FNAMES=OUT,INCLUDE=(2012,2,CH,EQ,C'$$'),
  BUILD=(1,350,2001,11,362,1639)                 
/*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Wed May 20, 2009 10:37 pm    Post subject: Reply with quote
wow... this works perfect.... MVSFORUMS rocks !!! Very Happy Thanks Once again Kolusu..

If you some time Could you please explain CTL1 for me ?

Code:


//CTL1CNTL DD *                                 
  INREC OVERLAY=(2001:351,11,1,2)               
  OUTFIL FNAMES=OUT,INCLUDE=(2012,2,CH,EQ,C'$$'),
  BUILD=(1,350,2001,11,362,1639)                 
/*

Back to top
View user's profile Send private message
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Thu May 21, 2009 12:41 pm    Post subject: Reply with quote
Kolusu,

The last byte in amount field ( 351 : 11 ) needs to hold the sign as well. But the above solution doesn't take care of this. Could you please let me know what changes need to be made?

Thanks for all the help !!

- Martin
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu May 21, 2009 12:52 pm    Post subject: Reply with quote
Martin,

The explanation of the control cards

1. The INREC is putting the amount value from pos 351 for 11 bytes in pos 2001 for 11 bytes and the 2 byte indicator from pos1 from your input. So for every matched record in 80 byte file the indicator will be '$$'.

2. The splice statement will will splice the amount value and the indicator on to matching keys on 3,6, and 18,9

3. Using an OUTFIL include , we only pick the values which got a match from file2. The indicator specifies that it is match .

Now for the sign overpunch on the last byte , does your 80 byte file have positive and negative values? Question Question
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Thu May 21, 2009 1:41 pm    Post subject: Reply with quote
Thanks once again !!
Back to top
View user's profile Send private message
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Thu May 21, 2009 1:47 pm    Post subject: Reply with quote
kolusu wrote:
Martin,

The explanation of the control cards

1. The INREC is putting the amount value from pos 351 for 11 bytes in pos 2001 for 11 bytes and the 2 byte indicator from pos1 from your input. So for every matched record in 80 byte file the indicator will be '$$'.

2. The splice statement will will splice the amount value and the indicator on to matching keys on 3,6, and 18,9

3. Using an OUTFIL include , we only pick the values which got a match from file2. The indicator specifies that it is match .

Now for the sign overpunch on the last byte , does your 80 byte file have positive and negative values? Question Question



Yes .. my file can have both negative and positive values:

ex for negative value :

-0000011620 ( first byte of the field is -)

positive value :

00000011620
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Thu May 21, 2009 2:27 pm    Post subject: Reply with quote
Martin wrote:



Yes .. my file can have both negative and positive values:

ex for negative value :

-0000011620 ( first byte of the field is -)

positive value :

00000011620


Change your Step0100 OUTREC to the following

Code:

OUTREC BUILD=(C'$$',1,6,18:7,9,351:16,11,SFF,ZDC,LENGTH=11,2000:X)

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Wed Jun 10, 2009 1:28 pm    Post subject: Reply with quote
I have small change in the requirement.

I need to sort off all the records from file 2 for dept = 123456. Example shown below.


Code:

File 1  ( Length : 80 byte)

Emp num : Pos 1 length 6
dept num : pos 7 length 9
Amount :  Pos 16 length 11


12345612345678900000083317
12345611111111100000121909
12345622222222200000086353
12345655555555500000066680
12345666666666600000066680
12345688888888800000067305


File 2  (length : 2000)

Emp num : Pos 3 length 6
dept num : pos 18 length 9
Amount :  Pos 351length 11 ( 2 DECIMAL ASSUMED)

       pos 3             pos 18                           pos 351
XXX123456XXXXXXXXX....8888888888.......................0000011467A......
XXX123456XXXXXXXXX....6666666666.......................0000038250{......
XXX123456XXXXXXXXX....5555555555.......................0000000000{......
XXX123456XXXXXXXXX....4444444444.......................0000031150{......
XXX123456XXXXXXXXX....3333333333.......................0000000000{......
XXX123456XXXXXXXXX....1111111199.......................0000030050{...... 
XXX111111XXXXXXXXX....3333333333.......................0000030050{......
XXX111111XXXXXXXXX....3333333333.......................0000030050{......


Output  ( Length : 2000 )

      pos 3             pos 18                           pos 351
XXX123456XXXXXXXXX....8888888888.......................0000006730E......
XXX123456XXXXXXXXX....6666666666.......................0000006668{......
XXX123456XXXXXXXXX....5555555555.......................0000006668{......
XXX123456XXXXXXXXX....1111111199.......................0000030050{...... 
XXX123456XXXXXXXXX....3333333333.......................0000000000{......

Here 33333333 and 11111199 records should be sorted out as well even though there is no matching record in file 1.



Plese let me know if this can be done.

Thanks,
Martin
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Wed Jun 10, 2009 1:53 pm    Post subject: Reply with quote
Martin,

Do you have duplicates on the combo key (3,6 and 18,9) in 2000 byte file? or the combination keys unique?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Wed Jun 10, 2009 2:36 pm    Post subject: Reply with quote
Hi Kolusu,

Yes , we do have duplicates on the 2000 byte file.


I need all the records from file 2 where dept num = 123456 ( pos 3 , 6) and the amount should be overlayed for the ones which match comb key.

Thanks,
Martin
Back to top
View user's profile Send private message
Martin
Beginner


Joined: 20 Mar 2006
Posts: 133
Topics: 58
PostPosted: Fri Jun 12, 2009 10:53 am    Post subject: Reply with quote
Could someone help me with this pls?

- Martin
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