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
 

 

Sort Card Used to filter records based on a Key

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

New User


Joined: 11 Aug 2005
Posts: 45
Location: India

PostPosted: Thu Sep 27, 2007 8:57 am    Post subject: Sort Card Used to filter records based on a Key
Reply with quote

Hi friends,

I have one requirement. It goes like this,


If my input file contains the records, ( first five characters are KEY)


AAAAA111
AAAAA
AAAAA222
BBBBB
BBBBB444
BBBBB555

The output should be

AAAAA111
AAAAA222
BBBBB444
BBBBB555

If my input file contains the records,


AAAAA
CCCCC
CCCCC567
CCCCC789

The output should be

AAAAA
CCCCC567
CCCCC789

If my input file contains the records,


AAAAA345
CCCCC
CCCCC567
CCCCC789

The output should be

AAAAA345
CCCCC567
CCCCC789



If my input file contains the records,

CCCCC
CCCCC178
DDDDD777


The output should be


CCCCC178
DDDDD777

In brief,

If there is only one record with a given Key, irrespective of the values in the positions 6-8 , the record should go to output.

If there are multiple records with a given key, we have to check the characters in the position 6-8, if there is blank in the position 6-8 we have to ignore that record otherwise we have to move the record to output.


Can we handle this situation using a sort card, if so please help

Thanks in advance
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu Sep 27, 2007 1:29 pm    Post subject:
Reply with quote

Ganesh,

Solution for your problem -
Code:
//STEP1     EXEC PGM=ICETOOL                                       
//TOOLMSG DD SYSOUT=*                                             
//DFSMSG DD SYSOUT=*                                               
//INFILE DD *                                                     
AAAAA111                                                           
AAAAA                                                             
AAAAA222                                                           
DDDDD                                                             
DDDDD1                                                             
EEEEE                                                             
11111222                                                           
BBBBB                                                             
BBBBB444                                                           
BBBBB555                                                           
CCCCC                                                             
/*                                                                 
//TEMPFILE DD DSN=&&TEMP,DISP=(MOD,PASS),LRECL=8,RECFM=FB         
//OUTFILE DD SYSOUT=*     <--- OP FILE                             
//FIRST DD DUMMY       <--- NOT REQ BUT YOU CAN USE FOR YOUR REF   
//TOOLIN DD *     
  COPY FROM(INFILE) TO(TEMPFILE) USING(CTL1)                         
  SELECT FROM(TEMPFILE) TO(OUTFILE) ON(1,5,CH) NODUPS                 
  SELECT FROM(TEMPFILE) TO(FIRST) ON(1,5,CH) FIRST DISCARD(OUTFILE)   
/*                                                                   
//CTL1CNTL DD *                                                       
  SORT FIELDS=(1,8,CH,A)                                             
/*


OP:

Code:
CCCCC       
EEEEE       
11111222   
AAAAA111   
AAAAA222   
BBBBB444   
BBBBB555   
DDDDD1     


LRECL 8 assumed for IP file.
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 Sep 27, 2007 9:22 pm    Post subject:
Reply with quote

Ganesh,

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
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//    DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//OUT DD DSN=...  output file
//TOOLIN   DD    *
SELECT FROM(IN) TO(T1) ON(1,5,CH) NODUPS DISCARD(T2) USING(CTL1)
SORT FROM(CON) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1
  OUTFIL FNAMES=T2,OMIT=(6,3,CH,EQ,C' ')
/*
//CTL2CNTL DD *
  SORT FIELDS=(1,8,CH,A)
/*


It will produce the output you asked for with all of your input examples sorted by 1,8,CH. For Murali's example, OUT would have:

Code:

AAAAA111     
AAAAA222     
BBBBB444     
BBBBB555     
CCCCC         
DDDDD1       
EEEEE         
11111222     
Back to top
View user's profile Send private message
Ganesh Kalam
Warnings : 1

New User


Joined: 11 Aug 2005
Posts: 45
Location: India

PostPosted: Thu Sep 27, 2007 11:49 pm    Post subject: Reply to: Sort Card Used to filter records based on a Key
Reply with quote

Thanks very much. I tried both JCLs,

Franks JCL is giving the correct output but in a sorted order. Frank, if i have to get the records in the same order as in the input, what changes shall i do to the JCL ?

Murali, i tried your jcl also, but it is giving me the following output

----+---
AAAAA
AAAAA222
BBBBB444
BBBBB555
DDDDD1
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 Sep 28, 2007 12:58 am    Post subject:
Reply with quote

Quote:
if i have to get the records in the same order as in the input, what changes shall i do to the JCL ?


You can use a DFSORT/ICETOOL job like this. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//    DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN   DD    *
SELECT FROM(IN) TO(T1) ON(1,5,CH) NODUPS DISCARD(T2) USING(CTL1)
SORT FROM(CON) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD)
  OUTFIL FNAMES=T1
  OUTFIL FNAMES=T2,OMIT=(6,3,CH,EQ,C' ')
/*
//CTL2CNTL DD *
  SORT FIELDS=(81,8,ZD,A)
  OUTREC BUILD=(1,80)   
/*


OUT would have:

Code:

AAAAA111   
AAAAA222   
DDDDD1     
EEEEE     
11111222   
BBBBB444   
BBBBB555   
CCCCC     
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Sep 28, 2007 11:11 am    Post subject:
Reply with quote

Ganesh,

Quote:
Murali, i tried your jcl also, but it is giving me the following output

----+---
AAAAA
AAAAA222
BBBBB444
BBBBB555
DDDDD1

Are you sure, you got the above OP? Did you copy my step properly. I reran the step and I got the OP as yesterday's one.

Even I tried with your 2 IPs provided in original post. Follow their OPs below -

Input:
Code:
AAAAA111 
AAAAA     
AAAAA222 
BBBBB     
BBBBB444 
BBBBB555 


Output:
Code:
AAAAA111
AAAAA222
BBBBB444
BBBBB555


IP:

Code:
AAAAA
CCCCC
CCCCC567
CCCCC789


OP:

Code:
AAAAA     
CCCCC567 
CCCCC789 


Could try again and let me know who/where went wrong?
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 SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm


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