Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need Sort utility solution for this Problem

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

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Wed Nov 29, 2006 3:32 pm    Post subject: Need Sort utility solution for this Problem
Reply with quote

Hi All,

I have a requirement. I want to solve this with SORT utility.

Input
-------
12345ASA123
12345ASA123
12345ASB123456
12345ASB123567
12346ASA124
12346ASA125
12346ASB123567
12346ASB123567

Required output
------------------
12345ASA123
12345ASB123456
12345ASB123567
12346ASA124
12346ASA125
12346ASB123567

Here the first 5 digits are account number. Then we have the table name position 6 to 8. Then we have the record content. Now aim is to remove duplicates records based on table index key.

For ASA table the key is at position from column 9 to 11.
But for ASB table the key position is column 12 to 14.

Could you please help me a solution?

Let me know if more information is required from my side.
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 Nov 29, 2006 9:41 pm    Post subject:
Reply with quote

Here's a DFSORT job that will do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but you can change the job appropriately for other attributes.

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/80)
//SORTOUT DD DSN=...  output file (FB/80)
//SYSIN    DD    *
  INREC IFTHEN=(WHEN=(6,3,CH,EQ,C'ASA'),OVERLAY=(81:9,3)),
        IFTHEN=(WHEN=(6,3,CH,EQ,C'ASB'),OVERLAY=(81:12,3))
  SORT FIELDS=(1,8,CH,A,81,3,CH,A)
  SUM FIELDS=NONE
  OUTREC BUILD=(1,80)
/*
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Wed Nov 29, 2006 10:00 pm    Post subject: Re: Need Sort utility solution for this Problem
Reply with quote

Thanks a lot Frank for the timely help, the solution is fantastic !
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Nov 30, 2006 10:19 am    Post subject:
Reply with quote

Hi All,

Would u please tell me what is equivllant to IFTHEN & WHEN in syncsort?
Code:

INREC IFTHEN=(WHEN=(6,3,CH,EQ,C'ASA'),OVERLAY=(81:9,3)),
          IFTHEN=(WHEN=(6,3,CH,EQ,C'ASB'),OVERLAY=(81:12,3))
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Nov 30, 2006 7:45 pm    Post subject: Re: Need Sort utility solution for this Problem
Reply with quote

Hi Frank,

I require some more help from you. For the above case I forgot to tell the following

I have 40 tables in my input file and all the tables are having

1. different number keys
2. different data types for the keys

Hence this will increase the number of keys to be specified sort fields. For my case there are approx 20 keys in the SORT FIELDS.
My file is a VB and LRECL = 3000, since we are using the OUTREC BUILD hence 'NUMBER OF BYTES SORTED' will also increase.

My input file is having more than 20 million records. Will it substanially reduce the performance. If so is there a way to fine tune it??

My table and exact keys are given in the attachment.
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 Nov 30, 2006 11:22 pm    Post subject:
Reply with quote

For a VB file, the DFSORT job would be slightly different:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (VB)
//SORTOUT DD DSN=...  output file (VB)
//SYSIN    DD    *
  INREC IFTHEN=(WHEN=(10,3,CH,EQ,C'ASA'),BUILD=(1,4,5:13,3,8:5)),   
        IFTHEN=(WHEN=(10,3,CH,EQ,C'ASB'),BUILD=(1,4,5:16,3,8:5))   
  SORT FIELDS=(8,8,CH,A,5,3,CH,A)                                   
  SUM FIELDS=NONE                                                   
  OUTREC BUILD=(1,4,5:8)                                           
/*


Of course, you'd have to adjust that for multiple keys.

Adding 20 keys this way may have an impact on performance. The only way to know if that performance impact would be a "problem" for you would be to try it and see.
Back to top
View user's profile Send private message
Hritam

New User


Joined: 27 Jun 2005
Posts: 36
Location: India

PostPosted: Fri Dec 01, 2006 4:03 am    Post subject:
Reply with quote

Hi,
Ekta: I dont think we have anything equivalent to IFTHEN and WHEN in Syncsort, but for some operations where we need less number of selection criteria we can use 'Include' and perform that task.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Dec 01, 2006 10:05 am    Post subject:
Reply with quote

Hi Hritam,

In that case we wont be able to solve it throgh SYNCSORT
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Mon Dec 04, 2006 3:20 pm    Post subject: Re: Need Sort utility solution for this Problem
Reply with quote

Hi Frank,

Thanks for the solution !

The solution is working, but i am not able to understand the solution.
Can you please explain about the column positions specified in INREC BUILD and OUTREC BUILD and what BUILD does?
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: Mon Dec 04, 2006 10:16 pm    Post subject:
Reply with quote

Code:
 
  INREC IFTHEN=(WHEN=(10,3,CH,EQ,C'ASA'),BUILD=(1,4,5:13,3,8:5)),   
        IFTHEN=(WHEN=(10,3,CH,EQ,C'ASB'),BUILD=(1,4,5:16,3,8:5))   
  SORT FIELDS=(8,8,CH,A,5,3,CH,A)                                   
  SUM FIELDS=NONE                                                   
  OUTREC BUILD=(1,4,5:8)                                           
/*


The first INREC IFTHEN clause says: when an input record has 'ASA' in 10-12, create a reformatted record with the RDW in positions 1-4 followed by input positions 13-15 and input positions 5 to the end of the record. For the first ASA record, that would be:

|RDW|123|12345ASA123|

The second INREC IFTHEN clause says: when an input record has 'ASB' in 10-12, create a reformatted record with the RDW in positions 1-4 followed by input positions 16-18 and input positions 5 to the end of the record. For the first ASB record, that would be:

|RDW|456|12345ASB123456|

So now we have the account number and table name starting at position 8 and the key starting at position 5. The SORT statement sorts on those fields in that order.

SUM FIELDS=NONE eliminates duplicates.

The OUTREC statement says: create an output record with the RDW in positions 1-4 followed by positions 8 to the end of the record. This removes the key we added and gives us back the original record. For the first ASA and ASB record, that would be:

|RDW|12345ASA123|
|RDW|12345ASB123456|
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Mon Dec 04, 2006 10:56 pm    Post subject: Re: Need Sort utility solution for this Problem
Reply with quote

Thanks a lot Frank,

This logic is superb !
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 Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 4 Fri Sep 22, 2017 4:56 pm
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts OC4 in SORT Mohan Kothakota DFSORT/ICETOOL 2 Tue Sep 12, 2017 6:30 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm
No new posts Changing sort Card Dynamically vickey_dw DFSORT/ICETOOL 5 Sat Aug 19, 2017 2:33 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us