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

How can I select out a specific record with same keys?


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jun 26, 2009 1:55 pm
Reply with quote

Hello,
I have the following requirement:
I want to select out the last record which has the same key with some other record?

for the following input data,
Code:

22546B.T.0000721...34.01481..       
22546B.T.0000454...34.91454..    ==to be selected

366977.T.0000721...34.01481.. 
366977.T.0000454...34.91454..   
366977.T.0000372...34.56432..    ==to be selected

50630B.T.0000121...34.01472..2
50630B.T.0000454...34.91454..   
50630B.T.0000234...34.91454..
50630B.T.0000313...34.91345..    ==to be selected

72447A.T.0000134...34.01424..2   
72447A.T.0000454...34.91454..    ==to be selected 


The expected output is like this:
Code:

22546B.T.0000454...34.91454..
366977.T.0000372...34.56432..
50630B.T.0000313...34.91345..
72447A.T.0000454...34.91454..



Thank you in advance.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jun 26, 2009 1:57 pm
Reply with quote

I forgot to state that:

in the above instance, the key is from column 1 to column 6.
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Fri Jun 26, 2009 2:17 pm
Reply with quote

Hi,

Look at the last record in the below sample. Is the below scenario possible in your file. If yes, would you have to omit the record?
Code:



22546B.T.0000721...34.01481..       
22546B.T.0000454...34.91454..    ==to be selected

366977.T.0000721...34.01481.. 
366977.T.0000454...34.91454..   
366977.T.0000372...34.56432..    ==to be selected

50630B.T.0000121...34.01472..2
50630B.T.0000454...34.91454..   
50630B.T.0000234...34.91454..
50630B.T.0000313...34.91345..    ==to be selected

72447A.T.0000134...34.01424..2   
72447A.T.0000454...34.91454..    ==to be selected

99999B.T.0000234...34.91454..    == will your file have a unique record?
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Fri Jun 26, 2009 2:29 pm
Reply with quote

Hi,

Here are the codes for both the requirement. Choose whichever you want.

Soln 1:
Code:

//S010 EXEC PGM=ICETOOL                               
//TOOLMSG DD SYSOUT=*                                 
//DFSMSG DD SYSOUT=*                                 
//FILE1 DD *                                         
22546B SENTHIL                                       
22546B VARUNIL                                       
22546B KRISHNA                                       
22333B SANGEET                                       
22333B SOUNDAR                                       
22123B VIJAYIL                                       
/*                                                   
//FILEOUT DD SYSOUT=*                                 
//TOOLIN DD *                                         
 SELECT FROM(FILE1) TO(FILEOUT) ON(1,6,CH) LAST       
/*                                                   


Output:
Code:

22123B VIJAYIL
22333B SOUNDAR
22546B KRISHNA


Soln 2:
Code:

//S010 EXEC PGM=ICETOOL                           
//TOOLMSG DD SYSOUT=*                             
//DFSMSG DD SYSOUT=*                               
//FILE1 DD *                                       
22546B SENTHIL                                     
22546B VARUNIL                                     
22546B KRISHNA                                     
22333B SANGEET                                     
22333B SOUNDAR                                     
22123B VIJAYIL                                     
/*                                                 
//FILEOUT DD SYSOUT=*                             
//TOOLIN DD *                                     
 SELECT FROM(FILE1) TO(FILEOUT) ON(1,6,CH) LASTDUP
/*                                                 


output:
Code:

22333B SOUNDAR   
22546B KRISHNA   
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Jun 26, 2009 3:39 pm
Reply with quote

Yes, the above method works.

But if there are two (or more) input files and only one output file, how should I change the above jcl?

Thanks.
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Fri Jun 26, 2009 4:00 pm
Reply with quote

Assuming the second soln is the one you want, here is how you need to do.

Here second file is the input file from which you want to have the last record in your output file. OPTION EQUALS is used to retain the original input order.

Similarly handle for multiple input files
Code:

//S010   EXEC PGM=ICETOOL,COND=(0,LT),PARM='DYNALLOC=(3390,50)'   
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//TOOLOUT  DD SYSOUT=*                                             
//EIN1     DD DSN=your First file...,                                     
//            DISP=SHR                                             
//         DD DSN=your second file...,                                     
//            DISP=SHR                                             
//AUS1     DD DSN=output file....,                               
//            DISP=(NEW,CATLG,DELETE),                             
//            DCB=*.EIN1,                                         
//            SPACE=(CYL,(2,4),RLSE),                             
//TOOLIN   DD *                                                   
 SELECT FROM(EIN1) ON(1,06,CH) TO(AUS1) LASTDUP USING(CTL1)       
/*                                                                 
//CTL1CNTL DD *                                                   
 OPTION EQUALS                                                     
 SORT FIELDS=(1,6,CH,A)                                           
/*                                                                 
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 26, 2009 6:40 pm
Reply with quote

senjay,

You don't need CTL1CNTL when you are already using the same fields in the ON statement. SELECT has EQUALS by default. So remove it
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Fri Jun 26, 2009 7:18 pm
Reply with quote

Hi Kolusu,

I had this doubt on EQUALS. Thanks a lot for correcting me.

So the solution is,

Code:

//S010   EXEC PGM=ICETOOL,COND=(0,LT),PARM='DYNALLOC=(3390,50)'   
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//TOOLOUT  DD SYSOUT=*                                             
//EIN1     DD DSN=your First file...,                                     
//            DISP=SHR                                             
//         DD DSN=your second file...,                                     
//            DISP=SHR                                             
//AUS1     DD DSN=output file....,                               
//            DISP=(NEW,CATLG,DELETE),                             
//            DCB=*.EIN1,                                         
//            SPACE=(CYL,(2,4),RLSE),                             
//TOOLIN   DD *                                                   
 SELECT FROM(EIN1) ON(1,06,CH) TO(AUS1) LASTDUP       
/*                                                                 


From the manual,
Quote:

ICETOOL passes the EQUALS option to DFSORT to ensure that duplicates are kept in their original input order.


I need to look in to the manuals deeply before posting any. icon_rolleyes.gif
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 26, 2009 8:46 pm
Reply with quote

senjay,

You cannot pass parms to ICETOOL via Parm statement in your JCL. You need to pass them via DFSPARM DD statement. The ideal solution would be using the LAST keyword instead of LASTDUP. With Lastdup keyword you would miss any unique items

Code:

//STEP0100 EXEC PGM=ICETOOL                           
//TOOLMSG  DD SYSOUT=*                               
//DFSMSG   DD SYSOUT=*                               
//DFSPARM  DD *                                       
  OPTION DYNALLOC=(3390,50)                           
//IN       DD *                                       
22546B.T.0000721...34.01481..                         
22546B.T.0000454...34.91454..    ==TO BE SELECTED     
366977.T.0000721...34.01481..                         
366977.T.0000454...34.91454..                         
366977.T.0000372...34.56432..    ==TO BE SELECTED     
50630B.T.0000121...34.01472..2                       
50630B.T.0000454...34.91454..                         
50630B.T.0000234...34.91454..                         
50630B.T.0000313...34.91345..    ==TO BE SELECTED     
72447A.T.0000134...34.01424..2                       
72447A.T.0000454...34.91454..    ==TO BE SELECTED     
72447B.T.0000454...34.91454..    ==TO BE SELECTED     
//OUT      DD SYSOUT=*                               
//TOOLIN   DD *                                       
  SELECT FROM(IN) ON(1,6,CH) TO(OUT) LAST             
/*
Back to top
View user's profile Send private message
senjay

Active User


Joined: 10 May 2007
Posts: 147
Location: India

PostPosted: Fri Jun 26, 2009 9:02 pm
Reply with quote

Hi Kolusu,

Quote:
You cannot pass parms to ICETOOL via Parm statement in your JCL


Yes, copy paste error.

Quote:
With Lastdup keyword you would miss any unique items


From OP,
Quote:
I want to select out the last record which has the same key with some other record


So i believe he would need LASTDUP and not LAST. Anyways both the solutions are provided. The OP can use whatever he wants.
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 26, 2009 9:32 pm
Reply with quote

Quote:
PARM='DYNALLOC=(3390,50)'


If you are going to post solutions in the DFSORT Forum, then please don't override other sites' default values arbitrarily. There's no reason to assume 3390,50 is needed for this job. And as Kolusu points out, PARM= values are ignored for ICETOOL.

Quote:
I need to look in to the manuals deeply before posting any


Or alternatively, let the two DFSORT developers on this board (Kolusu and myself) answer the DFSORT and ICETOOL questions.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sun Jun 28, 2009 8:40 am
Reply with quote

Thanks to your help, my problem has been resolved.

Thanks once again!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
Search our Forums:

Back to Top