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
 

 

Grouping of records in a file using sort/DFsort

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

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Thu Aug 20, 2009 10:25 pm    Post subject: Grouping of records in a file using sort/DFsort
Reply with quote

Hi All,

I need help for grouping the records in a file, not sure whether we can do this using sort/ICEtool.Here is my requirement.

I need to group the records in my input file based on few fileds in the file

Input file is 2000 bytes long.

Grouping based on

Group 1)

Only Unique records ,Key starts at 13 length 12 ( no duplicates )

Group 2)

All Records with same values in Position starting from 13 ,12 bytes long( char) and position starting from 62,20 bytes long( PD)

Group 3)

All Records with same values in Position starting from 13 ,12 bytes long( char) and position starting from 62,10 bytes long( PD) and different values in position starting from 72,10 byte long (PD).

Group 4)

All Records with same values in Position starting from 13 ,12 bytes long( char) and position starting from 72,10 bytes long( PD) and different values in position starting from 62,10 byte long (PD).

Group 5)

All Records with Position startin from 13 ,12 bytes long( char) and different values in position starting from 62,20 bytes long( PD).

Expected Output file :-

The group name needs to be appended to the end of the each record and needs to be in sorted order based on group name and 13,12 as key.
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 Aug 20, 2009 10:33 pm    Post subject:
Reply with quote

Please show an example of the records in your input file (relevant fields only) and what you expect for output, for all of the different cases.

Quote:
62,20 bytes long( PD)


Do you really have 20-byte PD values? That would be 39 digits and would exceed DFSORT's limit of 31 digits for PD values. Are all of these PD values positive with the same sign (all C or all F), or can they be positive and negative or have some C signs and some F signs?

Quote:
The group name needs to be appended to the end of the each record


Where does the "group name" come from? Hopefully, your example will show this.
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Aug 21, 2009 12:48 am    Post subject:
Reply with quote

Frank,

Thanks for your quick response.

I am attaching the input and expected output.

20 byte fields is a combination of 4 fields.

20 byte PD can be split into 10 and 10 and we can use.

Group name is just an identifier to identify to which group the record belongs( as i stated earlier the records needs to be grouped to 5 groups)
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: Fri Aug 21, 2009 1:28 am    Post subject:
Reply with quote

Your expected output attachment doesn't show any group numbers, so it doesn't help me figure out which records in your input example should go to which groups.

Could you please just show the example input records and output records inline here like this:

Code:

Input
   CH       CH       PD      PD     PD      PD
13-21     9-12    62-67   68-71  72-78   79-81
BBBBBBBBB BBB  99999999       0      0    1000                                                                                                   

...

Output
   CH       CH       PD      PD     PD      PD     CH ?
13-21     9-12    62-67   68-71  72-78   79-81     90 ?
BBBBBBBBB  BBB 99999999       0      0    1000      1 ?

...


Use ubb code tags.

Explain which record or records represents which case.
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Aug 21, 2009 2:09 am    Post subject:
Reply with quote

Thanks Frank for the response.

The input file is as follows.

Code:
KEY 1-9         KEY 9-12          ...KEY 62-67           KEY 68-71  PD               ....KEY 72- 78 PD        Key   
                                                                                                              79-81   
(13-21)          (22-24)              (62-67)             (68-71)                         (72-78)              PD 
8--------------- 9----------------    36----------------- 37--------------------------    39-----------------   
*********************************     ********************************* TOP OF DATA **    *******************   
JJJJJJJJJ        JJJ                             11111111                         999                     150  2000 
EEEEEEEEE        EEE                             99999999                          111                      0  1000
MMMMMMMMM        MMM                             99999999                          999                    750  3000
SSSSSSSSS        SSS                             11111111                          111                    111  4321 
KKKKKKKKK        KKK                             11111111                          123                    999  2000
DDDDDDDDD        DDD                             11111111                          999                      0  5000
JJJJJJJJJ        JJJ                             11111111                          999                    750  2000
DDDDDDDDD        DDD                             11111111                          999                      0  5000
EEEEEEEEE        EEE                             99999999                          111                      0  1000
AAAAAAAAA        AAA                             33333333                          333                    333  1234     
CCCCCCCCC        CCC                                99999                            0                      0  1000
MMMMMMMMM        MMM                             99999999                          999                    150  2000
SSSSSSSSS        SSS                             33333333                          333                    333  1234
BBBBBBBBB        BBB                             99999999                            0                      0  1000
RRRRRRRRR        RRR                             12343567                          321                    999  2000
KKKKKKKKK        KKK                             99999999                          123                    999  2000
RRRRRRRRR        RRR                             76543211                          123                    999  2000
AAAAAAAAA        AAA                             11111111                          111                    111  4321


The expected output is as follows.

Group name is just to identify which group the record belongs to.



Code:
KEY 1-9         KEY 9-12           ....KEY 62-67(PD)       EY 68-71    (PD)            ..KEY 72-78   KEY 79-81 Grp name
9/AN             3/AN                  6/PS                4/PS                          PD          3/PS      char (4)
(13-21)          (22-24)               (62-67)             (68-71)                       (72-78)     (79-81)   2001-2005
8--------------- 9-----------------    36----------------- 37--------------------------  39-----------------
***** TOP OF DATA *****************    ***************************** TOP OF DATA ******  *********************
BBBBBBBBB        BBB                              99999999                            0            0   1000   grp1 
CCCCCCCCC        CCC                                 99999                            0            0   1000   grp1   
DDDDDDDDD        DDD                              11111111                          999            0   5000   grp2
DDDDDDDDD        DDD                              11111111                          999            0   5000   grp2
EEEEEEEEE        EEE                              99999999                          111            0   1000   grp2
EEEEEEEEE        EEE                              99999999                          111            0   1000   grp2
JJJJJJJJJ        JJJ                              11111111                          999          750   2000   grp3
JJJJJJJJJ        JJJ                              11111111                          999          150   2000   grp3
MMMMMMMMM        MMM                              99999999                          999          750   3000   grp3
MMMMMMMMM        MMM                              99999999                          999          150   2000   grp3
KKKKKKKKK        KKK                              11111111                          123          999   2000   grp4
KKKKKKKKK        KKK                              99999999                          123          999   2000   grp4
RRRRRRRRR        RRR                              12343567                          321          999   2000   grp4
RRRRRRRRR        RRR                              76543211                          123          999   2000   grp4
AAAAAAAAA        AAA                              33333333                          333          333   1234   grp5
AAAAAAAAA        AAA                              11111111                          111          111   4321   grp5
SSSSSSSSS        SSS                              97654321                          333          444   1234   grp5
SSSSSSSSS        SSS                              11111111                          111          111   4321   grp5


The group crieteria is given in my intial post.
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: Fri Aug 21, 2009 3:20 am    Post subject:
Reply with quote

Are there only one or two records with each 13,12 key as shown in your example, or could there be more (for example, three D...D keys)?
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Aug 21, 2009 5:41 am    Post subject:
Reply with quote

There will be only one or two records in the file with the same key value.
There wont be more than 2 for each key ( 13,12).
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Aug 21, 2009 5:43 am    Post subject:
Reply with quote

Something to add...

If there is only one record for the key (13,12) then it will fall in group 1 and if there are two records for the key (13,12) then it will fall in any of other groups depending on other key values.

Thanks for your help.
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: Sat Aug 22, 2009 1:12 am    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD DSN=...  input file (FB/2000)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/2005)
//TOOLIN   DD    *
SORT FROM(IN) TO(T1) USING(CTL1)
SPLICE FROM(T1) TO(T2) ON(13,12,CH) KEEPNODUPS -
   WITH(2001,2000) WITH(4001,1) USING(CTL2)
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  OPTION EQUALS
  SORT FIELDS=(13,12,CH,A)
  OUTREC IFTHEN=(WHEN=INIT,
       OVERLAY=(4001:SEQNUM,1,ZD,RESTART=(13,12))),
    IFTHEN=(WHEN=(4001,1,ZD,EQ,2),OVERLAY=(2001:1,2000))
/*
//CTL2CNTL DD *
  OUTFIL FNAMES=T2,
    IFTHEN=(WHEN=(4001,1,ZD,EQ,1),BUILD=(1,2000,C' grp1')),
    IFTHEN=(WHEN=(62,6,PD,EQ,2062,6,PD,AND,68,4,PD,EQ,2068,4,PD,AND,
        72,7,PD,EQ,2072,7,PD,AND,79,3,PD,EQ,2079,3,PD),
      BUILD=(1,2000,C' grp2',/,2001,2000,C' grp2')),
    IFTHEN=(WHEN=(62,6,PD,EQ,2062,6,PD,AND,68,4,PD,EQ,2068,4,PD),
      BUILD=(1,2000,C' grp3',/,2001,2000,C' grp3')),
    IFTHEN=(WHEN=(72,7,PD,EQ,2072,7,PD,AND,79,3,PD,EQ,2079,3,PD),
      BUILD=(1,2000,C' grp4',/,2001,2000,C' grp4')),
    IFTHEN=(WHEN=NONE,
      BUILD=(1,2000,C' grp5',/,2001,2000,C' grp5'))
/*
//CTL3CNTL DD *
  SORT FIELDS=(2001,5,CH,A,13,12,CH,A)
/*
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Sat Aug 22, 2009 2:04 am    Post subject:
Reply with quote

Thanks for the response.

I tried using this Job and I'm getting RC 16.

Seems like T2 is exceeding the max record length.

"END OF T2 FIELD BEYOND MAXIMUM RECORD LENGTH "

Please find the attachment for ToolMsg and DFSMSG.

Regards,
Kham
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: Sat Aug 22, 2009 2:35 am    Post subject:
Reply with quote

It appears that your site has changed DFSORT's shipped default of SOLRF=YES to SOLRF=NO. This change is NOT recommended and can cause unwanted results as in your case. To use SOLRF, you can add the following to your job:

Code:

//DFSPARM DD *
   OPTION SOLRF
/*


You might want to alert your System Programmers to DFSORT's recommendation against the change they made.

In the future, please post text inline rather than as downloads. Your downloads were not very readable for me (just a jumble of words instead of individual lines).
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Aug 25, 2009 1:10 am    Post subject:
Reply with quote

Frank,

You are the best !!! Its perfect !!!

I was trying to decode what you are doing ? Can you just let me know why are you doing overlay or what is CTL1 and why in the CTL2 you are splicing the records...

Thanks for your support !!!
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 Aug 25, 2009 1:48 am    Post subject:
Reply with quote

I'm using OVERLAY and SPLICE to create one record for each pair of keys that has the data for the first record of the pair in positions 1-2000 and the data for the second record of the pair in positions 2001-4000. For example:

Code:

first DDD... rcd | second DDD... rcd
1-2000           | 2001-4000
DDD...   5000    | DDD...  5000      |


Once we have the data for each pair of records in a single record, we can use INCLUDE to compare the PD values on the two sides and add the correct group number.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000080
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Aug 25, 2009 11:49 pm    Post subject:
Reply with quote

Thanks Frank

The document was very useful.

In the CTL2 you are you building a 4005 byte record ?

Can you please help me in understanding what you are trying to build in CTL2 and what is the significance of "/" in the below statement?

BUILD=(1,2000,C'CASE5',/,2001,2000,C'CASE5'))
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 Aug 26, 2009 1:20 am    Post subject:
Reply with quote

Quote:
In the CTL2 you are you building a 4005 byte record ?


No - I'm building 2005 byte records for the various cases.

Code:

  BUILD=(1,2000,C'CASE5',/,2001,2000,C'CASE5'))


This uses the "combined" record as input and splits it back into two parts. The first record has positions 1-2000 (original first record with each key) followed by 'CASE5' and the second record has 2001-4000 (original second record with each key) followed by 'CASE5'. The / means start a new record.
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Wed Aug 26, 2009 7:39 pm    Post subject:
Reply with quote

Thank you very much Frnak !!! I got it..I was looking for the meaning of "/".
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Thu Aug 27, 2009 10:09 pm    Post subject:
Reply with quote

Frank,

A small change in reqirement...

Apart from Key in postion 13, 12 long I am going to have one more key field in 82nd postion which is 5 byte long packed decimal, (date field ex:- "20040804").

So I need to add this field also in the sort cards, but i am getting error 1when i am adding this field in the RESTART of seq number.
Code:
//TOOLIN   DD    *                                                   
SORT FROM(IN) TO(T1) USING(CTL1)                                     
SPLICE FROM(T1) TO(T2) ON(13,12,CH) ON(82,5,PD)  KEEPNODUPS -         
   WITH(2001,2000) WITH(4001,1) USING(CTL2)                           
SORT FROM(T2) TO(OUT) USING(CTL3)                                     
/*                                                                   
//CTL1CNTL DD *                                                       
  OPTION EQUALS                                                       
  SORT FIELDS=(13,12,CH,A,82,5,PD,A)                                 
  OUTREC IFTHEN=(WHEN=INIT,                                           
       OVERLAY=(4001:SEQNUM,1,ZD,RESTART=(13,12,82,5))),             
    IFTHEN=(WHEN=(4001,1,ZD,EQ,2),OVERLAY=(2001:1,2000))             
/*                                                                   
//CTL2CNTL DD *                                                       
  OUTFIL FNAMES=T2,                                                   
    IFTHEN=(WHEN=(4001,1,ZD,EQ,1),BUILD=(1,2000,C'CASE1')),           
    IFTHEN=(WHEN=(62,6,PD,EQ,2062,6,PD,AND,68,4,PD,EQ,2068,4,PD,AND, 
        72,7,PD,EQ,2072,7,PD,AND,79,3,PD,EQ,2079,3,PD),               
      BUILD=(1,2000,C'CASE2',/,2001,2000,C'CASE2')),                 
    IFTHEN=(WHEN=(62,6,PD,EQ,2062,6,PD,AND,68,4,PD,EQ,2068,4,PD),     
      BUILD=(1,2000,C'CASE3',/,2001,2000,C'CASE3')),         
    IFTHEN=(WHEN=(72,7,PD,EQ,2072,7,PD,AND,79,3,PD,EQ,2079,3,PD), 
      BUILD=(1,2000,C'CASE4',/,2001,2000,C'CASE4')),               
    IFTHEN=(WHEN=NONE,                                             
      BUILD=(1,2000,C'CASE5',/,2001,2000,C'CASE5'))               
/*                                                                 
//CTL3CNTL DD *                                                   
  SORT FIELDS=(2001,5,CH,A,13,12,CH,A)                             
/*                                                                 
//DFSPARM DD *                                                     
   OPTION SOLRF                                                   
/*                                                                 
         


I am getting RC=16 , saying sysntax is wrong for restart.Please advise how to tacke this scenario?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Aug 28, 2009 2:03 am    Post subject:
Reply with quote

khamarutheen,

The restart parm needs the keys to be contiguous bytes. So you need to pad the keys together. Change your CTL1CNTL to the following and re-run your job.

Code:

//CTL1CNTL DD *                                                   
  INREC OVERLAY=(4002:13,12,82,5)                                 
  SORT FIELDS=(13,12,CH,A,82,5,PD,A),EQUALS 
  OUTREC IFOUTLEN=4001,IFTHEN=(WHEN=INIT,                         
  IFTHEN=(WHEN=INIT,OVERLAY=(4001:SEQNUM,1,ZD,RESTART=(4002,17))),
  IFTHEN=(WHEN=(4001,1,ZD,EQ,2),OVERLAY=(2001:1,2000))           
//*
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Aug 28, 2009 9:36 pm    Post subject:
Reply with quote

Kolusu,
It worked !!!
I changed IFTHEN=(WHEN=INIT from below code as it was giving syntax error
Code:

OUTREC IFOUTLEN=4001,IFTHEN=(WHEN=INIT,


and I also added the the new key to the splice statement.

thanks Kolusu for the help.[/code]
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Aug 28, 2009 9:43 pm    Post subject:
Reply with quote

khamarutheen,

oops my control cards had a typo when i was extending the control cards to the next line. sorry about that , but l am glad that you fixed them.

The correct control cards are
Code:

//CTL1CNTL DD *                                                   
  INREC OVERLAY=(4002:13,12,82,5)                                 
  SORT FIELDS=(13,12,CH,A,82,5,PD,A),EQUALS
  OUTREC IFOUTLEN=4001,
  IFTHEN=(WHEN=INIT,OVERLAY=(4001:SEQNUM,1,ZD,RESTART=(4002,17))),
  IFTHEN=(WHEN=(4001,1,ZD,EQ,2),OVERLAY=(2001:1,2000))           
//*
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 Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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