IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

sorting a file- a challege for you guys :-))
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
ppandey07

New User


Joined: 27 Nov 2008
Posts: 51
Location: Delhi, India
PostPosted: Fri Jun 25, 2010 4:05 pm
 Reply with quote
Hi

I have a file with format as below:

Code:
ABC002010-06-212010-05-26Q00001            
ABC01DEF10000000014000000001APPCAMPCOD
ABC01DEF10000000024000000002APPCAMPCOD
ABC01DEF10000000034000000003APPCAMPCOD
ABC01MNO10000000014000000001APPCAMPCOD
ABC01XYZ10000000014000000001APPCAMPCOD
ABC02DEF500000000110000000014000000001QUOCAMPCOD
ABC02DEF500000000110000000014000000004QUOCAMPCOD
ABC02DEF500000000110000000014000000005QUOCAMPCOD
ABC02UBN500000000110000000014000000001QUOCAMPCOD
ABC02XYZ500000000110000000014000000001QUOCAMPCOD
ABC03DEF4000000001SAM'S
ABC03DEF4000000002SAM'S
ABC03DEF4000000003SAM'S
ABC03DEF4000000004SAM'S
ABC03DEF4000000005SAM'S
ABC992010-06-212010-05-26000050000500005000015

In the trailer record, position 26-30 is the count of ABC01 records, 31-35 is the count of ABC02, 36-40 is the count of ABC03 and 41-46 is the total count (ABC01 + ABC02 + ABC03)

I want to split this file into 2 different files (F1, F2). F1 having record types ABC01, ABC02 (position 1-5) and F2 having record type ABC03.

Now F1, F2 should again be split into 3 different files each based on values at position 6-8 (DEF, XYZ, MNO). So we now have F3, F4, F5 (out of F1) and F6, F7, F8 (out of F2)

Now all these 6 files (F3, F4, F5, F6, F7, F8) should have a record with record type ABC00 as the header record (copied from F1).

Now F3, F4, F5 will only contain records of ABC00, ABC01, ABC02…...I want a trailer at the end of these files having same data as test data given above from position 1-25 of last record, than count of ABC01 (5bytes), than count of ABC02 (5bytes), than total count (6bytes).

Now F6, F7, F8 will only contain records of ABC00, ABC03…...I want a trailer having same data as given above from position 1-25 of last record, than count of ABC03 (5bytes), than total count (this would be same as that of ABC03 but defined as 6bytes).

After this I want to sort the files (F3, F4, F5) based on a key field which is present at position 19-28 for record type ABC01 and 29-38 for record type ABC02…..this will be followed by sorting on the record type present at location 1-5.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Jun 25, 2010 4:35 pm
 Reply with quote
ppandey07,

You call it a challenge and you haven't given us LRECL and RECFM.
Although You have given us enough requirements,it would be helpful if you can also post sample output.

Also do you need files F1 and F2 for future processing? Can we jump to files 03 through 06?

Thanks,
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10902
Location: italy
PostPosted: Fri Jun 25, 2010 4:39 pm
 Reply with quote
what about ?
Code:
ABC02UBN500000000110000000014000000001QUOCAMPCOD
Back to top
View user's profile Send private message
ppandey07

New User


Joined: 27 Nov 2008
Posts: 51
Location: Delhi, India
PostPosted: Fri Jun 25, 2010 4:45 pm
 Reply with quote
sqlcode1,

it's a challenge dude. let's c how quickly you do it :-))

lrecl= 450, recfm= fb
Back to top
View user's profile Send private message
ppandey07

New User


Joined: 27 Nov 2008
Posts: 51
Location: Delhi, India
PostPosted: Fri Jun 25, 2010 4:46 pm
 Reply with quote
enrico-sorichetti,

sorry that was a typo. UBN in that record should be MNO.

Cheers
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Jun 25, 2010 4:49 pm
 Reply with quote
You still didn't answer my other question, do you care for file1 and file2 for any of the future processing?

Also please show us expected output if possible

thanks,
Back to top
View user's profile Send private message
ppandey07

New User


Joined: 27 Nov 2008
Posts: 51
Location: Delhi, India
PostPosted: Fri Jun 25, 2010 4:56 pm
 Reply with quote
sqlcode1,

i don't care about those files. those can be temporary ones.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Fri Jun 25, 2010 5:05 pm
 Reply with quote
ppandey07 wrote:
it's a challenge dude. let's c how quickly you do it :-))
Dare I ask if this is just a challenge in that the results have no real value and you may be wasting everybodys time,
or is it merely a psychological ploy in an attempt to get your work done faster ?
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Jun 25, 2010 6:04 pm
 Reply with quote
ppandey07,

For the final sort on 2 different keys, do you want just sort on just detail records (keep header and trailer as is.)? If you do want to sort detail records only change IFTHEN to group condition and use PUSH instead of OVERLAY.

But since you haven't told us, I am not coding for that assumption. Also what about files 06 through 08?


Try this untested. I really didn't get time to finetune this since I need to run for the work, If I get some more time during the day I will work on tunning if required.



Code:
//SORT01 EXEC  PGM=ICETOOL                                           
//DFSMSG   DD SYSOUT=*                                               
//TOOLMSG  DD SYSOUT=*                                               
//IN       DD *                                                     
ABC002010-06-212010-05-26Q00001                                     
ABC01DEF10000000014000000001APPCAMPCOD                               
ABC01DEF10000000024000000002APPCAMPCOD                               
ABC01DEF10000000034000000003APPCAMPCOD                               
ABC01MNO10000000014000000001APPCAMPCOD                               
ABC01XYZ10000000014000000001APPCAMPCOD                               
ABC02DEF500000000110000000014000000001QUOCAMPCOD                     
ABC02DEF500000000110000000014000000004QUOCAMPCOD                     
ABC02DEF500000000110000000014000000005QUOCAMPCOD                     
ABC02UBN500000000110000000014000000001QUOCAMPCOD                     
ABC02XYZ500000000110000000014000000001QUOCAMPCOD                     
ABC03DEF4000000001SAM'S                                             
ABC03DEF4000000002SAM'S                                             
ABC03DEF4000000003SAM'S                                             
ABC03DEF4000000004SAM'S                                             
ABC03DEF4000000005SAM'S                                             
ABC992010-06-212010-05-26000050000500005000015                       
//SORTOUT  DD  SYSOUT=*                                             
//HDR      DD DSN=&&HDR,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)           
//TRL      DD DSN=&&TRL,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)           
//TEMPFILE DD DSN=&&T1,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)             
//CARDFILE DD DSN=&&T2,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)             
//OUT03    DD DSN=&&OUT01,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//OUT04    DD DSN=&&OUT02,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//OUT05    DD DSN=&&OUT03,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//OUT06    DD DSN=&&OUT04,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//OUT07    DD DSN=&&OUT05,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//OUT08    DD DSN=&&OUT06,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)         
//TEMP03   DD SYSOUT=*                                               
//TEMP04   DD SYSOUT=*                                               
//TEMP05   DD SYSOUT=*                                               
//TEMP06   DD SYSOUT=*                                               
//TEMP07   DD SYSOUT=*                                               
//TEMP08   DD SYSOUT=*                                               
//TOOLIN   DD *                                                         
 SUBSET FROM(IN) TO(OUT03) HEADER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT04) HEADER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT05) HEADER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT06) HEADER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT07) HEADER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT08) HEADER KEEP INPUT                           
 COPY FROM(IN)  USING(CTL1)                                             
 SUBSET FROM(IN) TO(OUT03) TRAILER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT04) TRAILER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT05) TRAILER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT06) TRAILER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT07) TRAILER KEEP INPUT                           
 SUBSET FROM(IN) TO(OUT08) TRAILER KEEP INPUT                           
 COPY FROM(OUT03) TO(TEMP03) USING(CTL2)                               
 COPY FROM(OUT04) TO(TEMP04) USING(CTL2)                               
 COPY FROM(OUT05) TO(TEMP05) USING(CTL2)                               
 COPY FROM(OUT06) TO(TEMP06) USING(CTL3)                               
 COPY FROM(OUT07) TO(TEMP07) USING(CTL3)                               
 COPY FROM(OUT08) TO(TEMP08) USING(CTL3)                               
/*                                                                     
//CTL1CNTL DD *                                                         
 OUTFIL INCLUDE=(01,08,CH,EQ,C'ABC01DEF',OR,                           
                 01,08,CH,EQ,C'ABC02DEF'),FNAMES=OUT03                 
 OUTFIL INCLUDE=(01,08,CH,EQ,C'ABC01XYZ',OR,                           
                 01,08,CH,EQ,C'ABC02XYZ'),FNAMES=OUT04                 
 OUTFIL INCLUDE=(01,08,CH,EQ,C'ABC01MNO',OR,                           
                 01,08,CH,EQ,C'ABC02MNO'),FNAMES=OUT05                 
 OUTFIL INCLUDE=(01,08,CH,EQ,C'ABC03DEF'),FNAMES=OUT06                 
 OUTFIL INCLUDE=(01,08,CH,EQ,C'ABC03XYZ'),FNAMES=OUT07                 
 OUTFIL INCLUDE=(01,08,CH,EQ,C'ABC03MNO'),FNAMES=OUT08                 
/*                                                                     
//CTL2CNTL DD *                                                         
  INREC IFTHEN=(WHEN=(01,05,CH,EQ,C'ABC01'),OVERLAY=(451:19,10)),       
        IFTHEN=(WHEN=(01,05,CH,EQ,C'ABC02'),OVERLAY=(451:29,10))       
  SORT FIELDS=(451,10,CH,A,01,05,CH,A)                                 
  OUTREC BUILD=(1,450)                                                 
/*                                                                     
//CTL3CNTL DD *                                                         
  SORT FIELDS=COPY                                                     
/*                                                                     
//SYSOUT DD SYSOUT=*                                                   
//*                                                                     


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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Jun 25, 2010 10:04 pm
 Reply with quote
Wow SQCLODE1. Please do NOT post solutions using 19 passes of data when the whole exercise can be completed in just 1 pass. Please refrain from posting inefficient solutions. you are making DFSORT look bad by posting such solutions. Just because OP defined the steps to get the data , you don't have to follow the same. Get the gist of the requirement and try to code the program/solution accordingly.

ppandey07,

Use the following DFSORT JCL which will give you the desired results all in a single pass of data.

Code:

//STEP0100 EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//SORTIN   DD DSN=Your input FB 450 bytes file,DISP=SHR
//F12DEF   DD SYSOUT=*                     
//F12MNO   DD SYSOUT=*                     
//F12XYZ   DD SYSOUT=*                     
//F03DEF   DD SYSOUT=*                     
//F03MNO   DD SYSOUT=*                     
//F03XYZ   DD SYSOUT=*                     
//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC00'),OVERLAY=(451:10X'00')),     
        IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC01'),OVERLAY=(451:19,10)),       
        IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC02'),OVERLAY=(451:29,10)),       
        IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC03'),OVERLAY=(451:10C'8')),       
        IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC99'),OVERLAY=(451:10C'9'))       
                                                                       
  SORT FIELDS=(451,10,CH,A,1,5,CH,A),EQUALS                           
                                                                       
  OUTREC IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC01DEF',OR,1,5,CH,EQ,C'ABC99'),   
  OVERLAY=(462:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC02DEF',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(468:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC03DEF',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(474:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
                                                                       
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC01MNO',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(480:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC02MNO',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(486:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC03MNO',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(492:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
                                                                       
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC01XYZ',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(498:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC02XYZ',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(504:SEQNUM,5,ZD,START=0),HIT=NEXT),                         
  IFTHEN=(WHEN=(1,8,SS,EQ,C'ABC03XYZ',OR,1,5,CH,EQ,C'ABC99'),         
  OVERLAY=(510:SEQNUM,5,ZD,START=0),HIT=NEXT)                         
                                                                       
  OUTFIL FNAMES=F12DEF,IFOUTLEN=450,                               
  INCLUDE=(1,8,SS,EQ,C'ABC01DEF,ABC02DEF',OR,                     
           1,5,SS,EQ,C'ABC00,ABC99'),                             
  IFTHEN=(WHEN=(1,5,SS,EQ,C'ABC99'),                               
  BUILD=(1,25,462,5,468,5,462,5,ZD,ADD,468,5,ZD,M11,LENGTH=6))     
                                                                   
  OUTFIL FNAMES=F12MNO,IFOUTLEN=450,                               
  INCLUDE=(1,8,SS,EQ,C'ABC01MNO,ABC02MNO',OR,                     
           1,5,SS,EQ,C'ABC00,ABC99'),                             
  IFTHEN=(WHEN=(1,5,SS,EQ,C'ABC99'),                               
  BUILD=(1,25,480,5,486,5,480,5,ZD,ADD,486,5,ZD,M11,LENGTH=6))     
                                                                   
  OUTFIL FNAMES=F12XYZ,IFOUTLEN=450,                               
  INCLUDE=(1,8,SS,EQ,C'ABC01XYZ,ABC02XYZ',OR,                     
           1,5,SS,EQ,C'ABC00,ABC99'),                             
  IFTHEN=(WHEN=(1,5,SS,EQ,C'ABC99'),                               
  BUILD=(1,25,498,5,504,5,498,5,ZD,ADD,504,5,ZD,M11,LENGTH=6))     
                                                                   
  OUTFIL FNAMES=F03DEF,IFOUTLEN=450,                               
  INCLUDE=(1,8,CH,EQ,C'ABC03DEF',OR,1,5,SS,EQ,C'ABC00,ABC99'),     
  IFTHEN=(WHEN=(1,5,SS,EQ,C'ABC99'),                               
  BUILD=(1,25,474,5,C'0',474,5))                                   
                                                                   
  OUTFIL FNAMES=F03MNO,IFOUTLEN=450,                               
  INCLUDE=(1,8,CH,EQ,C'ABC03MNO',OR,1,5,SS,EQ,C'ABC00,ABC99'),     
  IFTHEN=(WHEN=(1,5,SS,EQ,C'ABC99'),                               
  BUILD=(1,25,492,5,C'0',492,5))                                   
                                                                   
  OUTFIL FNAMES=F03XYZ,IFOUTLEN=450,                               
  INCLUDE=(1,8,CH,EQ,C'ABC03XYZ',OR,1,5,SS,EQ,C'ABC00,ABC99'),     
  IFTHEN=(WHEN=(1,5,SS,EQ,C'ABC99'),                               
  BUILD=(1,25,510,5,C'0',510,5))                                   
//*
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Jun 25, 2010 11:13 pm
 Reply with quote
Kolusu,
Agreed that my solution was not efficient and that's why I had written that I didn't get time to finetune!!! And I do agree that even if I would have had time I couldn't have reached your solution.

Also I am curious to know (not that I disagree with you), how is it that my solution has 19 passes? Would getting HEADER and TRAILER records from input be considered a single pass? As far as I know, DFSort simply strips first and last record of the file when we use SUBSET ..WITH HEADER TRAILER. Does reading an Input file, accounts for a single pass?

Ofcourse there is no doubt that your solution would be more efficient.

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Fri Jun 25, 2010 11:28 pm
 Reply with quote
sqlcode1,

Although it's not 19 complete passes over the input data, it's still a very large number of unnecessary reads.

SUBSET FROM(IN) TO(OUT03) HEADER KEEP INPUT

Reads/writes only the first record.

SUBSET FROM(IN) TO(OUT03) TRAILER KEEP INPUT

Reads all of the records and writes the last record.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Jun 25, 2010 11:32 pm
 Reply with quote
Frank,

Thanks for the explanation.

Thanks,
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Sat Jun 26, 2010 12:03 am
 Reply with quote
Quote:
you are making DFSORT look bad by posting such solutions

non-DFSORT comment
There is not yet (nor probably ever will be) a language/utility/tool that is not able to completely drain even the largest system - when the worst possible "solutions" are permitted to be implemented.
/non-DFSORT comment

@sqlcode1 - this is not directed towards you personally, but rather to the increasing number of implementations that use exponentially more resources than necessary to meet a requirement.

One of my personal favorites is the approach that opens/reads/closes "file2" millions of times in order to match every record of file1 against file2. . .

icon_smile.gif

d
Back to top
View user's profile Send private message
ppandey07

New User


Joined: 27 Nov 2008
Posts: 51
Location: Delhi, India
PostPosted: Tue Jun 29, 2010 2:20 pm
 Reply with quote
Skolusu,

If i want to sort files F03DEF, F03MNO, F03XYZ (as per your sortcard) on the basis of a key present from 9-19 in the ascending order except header record and trailer record, what changes would i need to make in your sortcard?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
PostPosted: Tue Jun 29, 2010 7:43 pm
 Reply with quote
The challange i see is getting the complete definition of what is wanted. . .

What have you tried? What happened?

d
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Jun 29, 2010 10:07 pm
 Reply with quote
ppandey07 wrote:
Skolusu,

If i want to sort files F03DEF, F03MNO, F03XYZ (as per your sortcard) on the basis of a key present from 9-19 in the ascending order except header record and trailer record, what changes would i need to make in your sortcard?


change this line on INREC to
Code:

IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC03'),OVERLAY=(451:10C'8')),


to
Code:

IFTHEN=(WHEN=(1,5,CH,EQ,C'ABC03'),OVERLAY=(451:9,10)),
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts FileAid move data from two files to o... Compuware & Other Tools 5
No new posts Sorting date into YYYYMMDD, and creat... DFSORT/ICETOOL 13
No new posts To Sort detail records in a file with... SYNCSORT 5
No new posts To Populate Trailer cnt in the file w... SYNCSORT 1
No new posts Sort card to know that creation date ... DFSORT/ICETOOL 10
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us