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 

Sorting Detail records
Goto page 1, 2, 3, 4  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
ramg_mar
Beginner


Joined: 28 Nov 2003
Posts: 6
Topics: 2
Location: india
PostPosted: Tue Feb 17, 2004 11:16 pm    Post subject: Sorting Detail records Reply with quote
Hi all,
I have a input file with the following format

HEADER 02/05/2004
522222222 ----------------------------->
517001053 ----------------------------->
517001194 ----------------------------->
517001202 -----------------------------> Detail Records
517001210 -----------------------------> Detail Records
517001228
TRAILER 000000018

Can we have a 'single' sort step to sort only the detail records based on the account no (9 bytes)?. The output file should retain the header and trailer in its previous position.

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


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Wed Feb 18, 2004 6:58 am    Post subject: Reply with quote
Ramg_mar,

The following DFSORT/ICETOOL JCL will give you the desired results. If you have syncsort at your shop then change the pgm name to synctool. A brief explanation of the job.

I assumed that your input file is 80 bytes in length and is of FB recfm.

The first copy operator splits the input records into 2 files. The header and trailer will be copied to t1 and the detailed records to t2. we also a add a constant value of 0 and max of 99999999 for header and trailer respectively , so that we can put the header and trailer as the first and last records of the file once again.

The detailed records copied to t2 are sorted on the first 9 bytes and are written to T3 with a seqnum.

Now concatenate both T1 and T3 and sort on the seqnum and remove the seqnum while writting it out.

I haven't tested it but it should give you the desired results , unless i half awake in the morning.

Code:

//STEP0100 EXEC PGM=ICETOOL                               
//TOOLMSG   DD SYSOUT=*                                   
//DFSMSG    DD SYSOUT=*                                   
//IN        DD *                                           
HEADER 02/05/2004                                         
522222222 ----------------------------->                   
517001053 ----------------------------->                   
517001194 ----------------------------->                   
517001202 -----------------------------> DETAIL RECORDS   
517001210 -----------------------------> DETAIL RECORDS   
517001228                                                 
TRAILER 000000018                                         
//T1       DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//T2       DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//T3       DD DSN=&T3,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//CON      DD DSN=&T1,DISP=OLD,VOL=REF=*.T1             
//         DD DSN=&T3,DISP=OLD,VOL=REF=*.T3 
//OUT      DD DSN=YOUR OUTPUT SORTED FILE,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//TOOLIN   DD *                                           
  COPY FROM(IN)  USING(CTL1)                               
  SORT FROM(T2)  USING(CTL2)                               
  SORT FROM(CON) USING(CTL3)                               
//CTL1CNTL  DD *                                           
  OUTFIL FNAMES=T1,INCLUDE=(1,7,CH,EQ,C'HEADER ',OR,       
                            1,7,CH,EQ,C'TRAILER'),         
  OUTREC=(1,80,81:1,7,CHANGE=(8,C'HEADER ',C'00000000',     
                                C'TRAILER',C'99999999'),   
                                NOMATCH=(C'99999999'))     
  OUTFIL FNAMES=T2,SAVE                                     
//CTL2CNTL  DD *                                   
  SORT FIELDS=(1,9,ZD,A)                           
  OUTFIL FNAMES=T3,OUTREC=(1,80,SEQNUM,8,ZD)       
//CTL3CNTL  DD *                                   
  SORT FIELDS=(81,8,ZD,A)                           
  OUTFIL FNAMES=OUT,OUTREC=(1,80)                   
/*                                                 


Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 28 Nov 2003
Posts: 6
Topics: 2
Location: india
PostPosted: Wed Feb 18, 2004 7:36 am    Post subject: Reply with quote
Exclamation Hi Kolusu,

Thanks for your valuable suggestions..

I've tried to test this, but the output file doesn't have the Header and trailer in its previous position.

here is the output file,

TRAILER 000000018
HEADER 02/05/2004
517001053
517001194
517001202
517001210
517001228
517001236
517001244

Is any changes needs to be done?

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


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Wed Feb 18, 2004 8:31 am    Post subject: Reply with quote
Ramg_mgr,


Did you run the job exactly as I have shown? I just ran the same JCL as is and this is what I get.
Code:

HEADER 02/05/2004                                         
517001053 ----------------------------->                   
517001194 ----------------------------->                   
517001202 -----------------------------> DETAIL RECORDS   
517001210 -----------------------------> DETAIL RECORDS   
517001228                                                 
522222222 ----------------------------->                   
TRAILER 000000018                                         


What is your input file LRECL and recfm? As I mentioned before I assumed that it is 80 bytes and is of FB recfm.

Please post your JCL and and let me take a look at it.

Thanks

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 28 Nov 2003
Posts: 6
Topics: 2
Location: india
PostPosted: Wed Feb 18, 2004 8:39 am    Post subject: Reply with quote
Hi Kolusu,

I am using the input file with record lenght 80 and the recfm is FB only. But, I am not able to get the Header and trailer in correct position.

This is the jcl I am using,

Code:
//STEP0100 EXEC PGM=SYNCTOOL                                     
//TOOLMSG   DD SYSOUT=*                                           
//DFSMSG    DD SYSOUT=*                                           
//IN        DD DSN=TSORGOV.STMP05D1.TEST.SORT,DISP=SHR           
//T1        DD DSN=&T1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)       
//T2        DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(5,5),RLSE)       
//T3        DD DSN=&T3,DISP=(,PASS),SPACE=(CYL,(5,5),RLSE)       
//CON       DD DSN=&T1,DISP=OLD,VOL=REF=*.T1                     
//          DD DSN=&T3,DISP=OLD,VOL=REF=*.T3                     
//OUT       DD DSN=TSORGOV.TEST.ICETOOL.OUTPUT,                   
//            DISP=(NEW,CATLG,DELETE),                           
//            UNIT=SYSDA,                                         
//            SPACE=(CYL,(10,10),RLSE)                           
//TOOLIN    DD *                                                 
  COPY FROM(IN)  USING(CTL1)                                     
  SORT FROM(T2)  USING(CTL2)                                     
  SORT FROM(CON) USING(CTL3)                                     
//CTL1CNTL  DD *                                                 
  OUTFIL FNAMES=T1,INCLUDE=(1,7,CH,EQ,C'HEADER ',OR,             
                            1,7,CH,EQ,C'TRAILER'),               
  OUTREC=(1,80,81:1,7,CHANGE=(8,C'HEADER ',C'00000000',           
                                C'TRAILER',C'99999999'),         
                                NOMATCH=(C'99999999'))       
  OUTFIL FNAMES=T2,SAVE                                       
//CTL2CNTL  DD *                                             
  SORT FIELDS=(1,9,ZD,A)                                     
  OUTFIL FNAMES=T3,OUTREC=(1,80,SEQNUM,8,ZD)                 
//CTL3CNTL  DD *                                             
  SORT FIELDS=(81,8,ZD,A)                                     
  OUTFIL FNAMES=OUT,OUTREC=(1,80)                             
/*                                                           

THanks,
Ramesh
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Wed Feb 18, 2004 8:49 am    Post subject: Reply with quote
Ramg_mgr,


Another way of doing it using disp=mod for the output dataset.The following DFSORT/ICETOOL jcl will give you the desired results. A brief explanation of the job.

The first copy operator copies the header record to the output file.

The second sort operator sorts the detail records omitting the header and trailer records. It appeneds the sorted records to header record in the output file

The third copy operator copies the trailer record and appends it to the output file

Code:

//STEP0200 EXEC PGM=ICETOOL                                   
//TOOLMSG   DD SYSOUT=*                                       
//DFSMSG    DD SYSOUT=*                                       
//IN        DD *                                             
HEADER 02/05/2004                                             
522222222 ----------------------------->                     
517001053 ----------------------------->                     
517001194 ----------------------------->                     
517001202 -----------------------------> DETAIL RECORDS       
517001210 -----------------------------> DETAIL RECORDS       
517001228                                                     
TRAILER 000000018                                             
//OUT      DD DSN=YOUR OUTPUT FILE,                       
//            DISP=(MOD,CATLG,DELETE),                       
//            UNIT=SYSDA,                                     
//            SPACE=(CYL,(X,Y),RLSE)                         
//TOOLIN   DD *                                               
  COPY FROM(IN) USING(CTL1)                                   
  SORT FROM(IN) USING(CTL2)                                   
  COPY FROM(IN) USING(CTL3)                                   
//CTL1CNTL DD *                                               
  OPTION STOPAFT=1                                           
  OUTFIL FNAMES=OUT                                           
//CTL2CNTL DD *                                               
  OMIT COND=(1,7,CH,EQ,C'HEADER ',OR,1,7,CH,EQ,C'TRAILER')   
  SORT FIELDS=(1,9,ZD,A)                                     
  OUTFIL FNAMES=OUT                                           
//CTL3CNTL DD *                                               
  INCLUDE COND=(1,7,CH,EQ,C'TRAILER')                         
  OUTFIL FNAMES=OUT                                           
/*



Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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: 12367
Topics: 75
Location: San Jose
PostPosted: Wed Feb 18, 2004 8:50 am    Post subject: Reply with quote
Ramesh,

There is nothing wrong with the JCL you posted. Can you post the sysout of TOOLMSG and also the DFSMSG?

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 28 Nov 2003
Posts: 6
Topics: 2
Location: india
PostPosted: Wed Feb 18, 2004 7:54 pm    Post subject: Reply with quote
Hi Kolusu,

I got the desired results with the second solution.. (using disp=mod for the output dataset). Thanks for your solution within a short period!


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


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Thu Feb 19, 2004 6:17 am    Post subject: Reply with quote
ramg_mgr,

Even though the second solution worked for you I am curious to know as to why the first solution is giving you wrong results. I am 100% confident that my 1st posted solution will work. If you say it is not then I would like to look at the TOOLMSG and DFSMSG of the Job. As such there is nothing special in that job that will give you the wrong results.

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

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


Joined: 23 Jan 2003
Posts: 211
Topics: 21
PostPosted: Thu Feb 19, 2004 8:36 am    Post subject: Reply with quote
Ramq_mar,
just to add my 2 cents, I ran the first soultion without any changes and it worked fine for me. Thanks
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Wed Apr 21, 2004 4:53 am    Post subject: Reply with quote
Hi All,

I tried to solve the same problem using PGM=SORT in one step (one pass). I don't know whether this solution is efficient. Please give me your valuable suggestions.

Code:

//R012     EXEC PGM=SORT                                               
//SYSOUT   DD  SYSOUT=*                                                 
//SORTIN   DD  *
HEADER 02/05/2004                                         
522222222 ----------------------------->                   
517001053 ----------------------------->                   
517001194 ----------------------------->                   
517001202 -----------------------------> DETAIL RECORDS   
517001210 -----------------------------> DETAIL RECORDS   
517001228                                                 
TRAILER 000000018                                         
/*
//SORTOUT  DD  DSN=MY OUTPUT FILE,
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=SYSDA,                                             
//             AVGREC=K,                                               
//             SPACE=(80,(10,2),RLSE),                                 
//             DCB=(RECFM=FB,LRECL=80)                                 
//SYSIN    DD  *                                                       
 INREC FIELDS=(1,7,CHANGE=(8,C'HEADER ',C'HEADER 1',                   
                            C'TRAILER',C'TRAILER '),                   
                   NOMATCH=(1,7),8,73)                                 
 SORT FIELDS=(8,1,CH,D,1,9,CH,A)                                       
 OUTFIL OUTREC=(1,7,9,73)                                             
/*     


PLEASE NOTE ****: In the code the blank space following TRAILER (in change command) is not a blank. It is a LOW-VALUE character X'00'. When U cut & paste the code on ur mainframe make sure you change the space in C'TRAILER ' to low-value (using HEX ON).

What I have done here is I have modified 'HEADER ' as 'HEADER 1' and 'TRAILER' as 'TRAILER' + X'00' (Low-value) using Change command. The data portion of the file will have a Blank space in the 8th character due to NOMATCH command. (I have temporarily increased the size of file from 80 to 81 by introducing a new char at pos 8 ).

Now when I sort on column 8 in descending order, the Char '1' in HEADER will come in the first line followed by Blanks in data portion. The trailer will end up as the last record because of the low-value in column 8. Now since the data records are grouped together, the second level sorting 1,9,CH,A will arrange them in required order.

Finally using OUTREC I remove the character inserted as pos 8.

Please give me your suggestions,
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
PostPosted: Wed Apr 21, 2004 8:41 am    Post subject: Reply with quote
Phantom,

That is a clever trick. I modified it slightly so that it will be easy to code the control cards. I mean it would be more effort to change to binary zeroes using hex on.

So you can change the header to '3' and the detail records to '2'(no match) and trailer to '1', so that you will not get into editing using hex on.

i.e

Code:

 INREC FIELDS=(1,7,1,7,CHANGE=(1,C'HEADER ',C'3',     
                                 C'TRAILER',C'1'),
                        NOMATCH=(C'2'),8,73)
 SORT FIELDS=(8,1,CH,D,1,9,CH,A)                       
 OUTFIL OUTREC=(1,7,9,73)                             


However I do not like to introduce an addition character in the middle. I prefer adding it at the end.

so the control cards will be as follows:

Code:

 INREC FIELDS=(1,80,1,7,CHANGE=(1,C'HEADER ',C'3',             
                                  C'TRAILER',C'1'),             
                    NOMATCH=(C'2'))                             
 SORT FIELDS=(81,1,CH,D,1,9,CH,A)                               
 OUTFIL OUTREC=(1,80)                                           


Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Wed Apr 21, 2004 8:54 am    Post subject: Reply with quote
Thats really nice Kolusu,

I was breaking my head so much to avoid using the Hexa chars. Nothing came to my mind....

Thanks a lot for the help kolusu....
Back to top
View user's profile Send private message
nguyenh
Beginner


Joined: 09 Mar 2004
Posts: 52
Topics: 6
PostPosted: Tue Nov 09, 2004 11:28 am    Post subject: Reply with quote
Hi Kolusu,
How do I modify this skeleton so that it sorts the detail records in a file that contains multiple headings and detail records like this:
Let say I want to sort by filename (a 6 characters extracted from columns 39-44 of the heading records) and column 77-78, those contains AA, CC....
Input file is 80 characters long, record format FB, record length is 80
Code:

********************************MEMBERBFS     **********************************
********************************MEMBERFILEHDR **********************************
  PEMP      033      090195K.B. THOMAS     7134831380       NSTS21286SM2    AA
EMP.1R............003392.EMP.1R.....                                        AB
********************************MEMBERFILE01  **********************************
  PEMP      033      090195K.B. THOMAS     7134831380       NSTS21286SM2    AA
                                                                           1CA
                     PF01  102S    OUT        1501                          CB01
P03K6097J 04EVENT   DEPL HEATERS PWR ON (K2)          O                     CC
********************************MEMBERFILE02  **********************************
                                                                           2CA
P03K6035L 05EVENT   SFMDM EEPROM WRITE ENABLE CORES                         CC
P03K6035L L  O            ON      02  PF01SIO101501      SS X  S     L    32CD
P03K6035L P03K6035E           P33M9001J                                     CE
********************************MEMBERFILE03  **********************************
                                                                           3CA
********************************MEMBERFILE04  **********************************
 907 53   L  O                       1.00BIPL08051201600200  20032820.184615TA
 907 53FFAFC40  F0001       U0000004         1  2  16                       TB
 906 53   L  O                       1.00BIPL08009800400100  10003125.000000TA
********************************MEMBERFILE05  **********************************
  EMP       033      020491MICHAEL PLATOFF 7134838487       NSTS18411SM2    AA
                                                                           1CA
********************************MEMBERFILE06  **********************************
                                                                           2CA
P33K9901L 24EVENT   HTRS ON/OFF                        HTRS ON/OFF          CC
P33K9901L    O            ON      01                     QQ X H      L      CD
P33K9901L 10003D00018098D5550000020002F1D5550000020001F2D55510000           CL01
P33K9901L               0000022000D3D5550000021000E3D5550000                CL02
P33K9901L               02008073D5550000020040B3                            CL03
P33K9902L 56EVENT   PCB K-RELAYS                       PCB K-RELAYS         CC


The output file will be 86 characters long. a 6 characters file name extracted from columns 39-44 of the heading records. will be abended at the end of each of the records to indicate where the record belong to like this:
Code:

********************************MEMBERFILEHDR **********************************BFS
  EMP       033      020491MICHAEL PLATOFF 7134838487       NSTS18411SM2    AA  FILEHD
  OARE      014      061390RICHARD O. DRAKE7134833043                SM2    AA  FILEHD
  PCSDLST   092      040595M. WILLIS       7134831040       NSTS18411SM2    AA  FILEHD
  PEMP      033      090195K.B. THOMAS     7134831380       NSTS21286SM2    AA  FILEHD
  PTSS      013      090595K.B. THOMAS     7134831380       NSTS21286SM2    AA  FILEHD
  TSS       013      020491MICHAEL PLATOFF 7134838487       NSTS18411SM2    AA  FILEHD
  UMP.UMP3  004      080295SONJA SUMMERS   7134834217       NSTS21249SM2    AA  FILEHD
EMP.1R............003392.EMP.1R.....                                        AB  FILEHD
EMP.1R.HZD.CMDS...003133.EMP.1R.....                                        AB  FILEHD
OARE..............001719............                                        AB  FILEHD
TSS.1R............003392.TSS.1R.....                                        AB  FILEHD
TSS.1R.HZD.CMDS...003133.TSS.1R.....                                        AB  FILEHD
TSS.1R.PCS.SUPPORT003133.TSS.1R.....                                        AB  FILEHD
USMP.3............003407.USMP.3.....                                        AB  FILEHD
                                                                           1CA  FILE01
                     PF01  102S    OUT        1501                          CB01FILE01
  PEMP      033      090195K.B. THOMAS     7134831380       NSTS21286SM2    AA  FILE01
P03K6097J 04EVENT   DEPL HEATERS PWR ON (K2)          O                     CC  FILE01
                                                                           2CA  FILE02
P03K6035L 05EVENT   SFMDM EEPROM WRITE ENABLE CORES                         CC  FILE02
P03K6035L L  O            ON      02  PF01SIO101501      SS X  S     L    32CD  FILE02
                                                                           3CA  FILE03
P03C2201A 10AMP     EMP EXPERIMENT DC CURRENT                               TI  FILE04
P03C2201A J   BSS     L  O        0  500                                    TJ  FILE04


Thank you for your helps!
nguyenhh
Back to top
View user's profile Send private message
nguyenh
Beginner


Joined: 09 Mar 2004
Posts: 52
Topics: 6
PostPosted: Tue Nov 09, 2004 11:29 am    Post subject: Reply with quote
sorry, this line:
Code:
********************************MEMBERFILEHDR **********************************BFS

should be changed to:
Code:
********************************MEMBERFILEHDR **********************************FILEHDR
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
Goto page 1, 2, 3, 4  Next
Page 1 of 4

 
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