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
 

 

Selective records selection

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

Active User


Joined: 06 Sep 2007
Posts: 112
Location: India

PostPosted: Mon Apr 27, 2009 5:27 pm    Post subject: Selective records selection
Reply with quote

Hi

The input file has user and his bus route no details as follows.

USER1 Route1
USER2 Route2
USER3 Route3
USER4 Route5


theres another input file which has only valid route nos. Say there are 2 lakh records in this file

Route1
Route2
Route5
Route9

In my output i want only the records from first file which have valid route nos. Can this be achieved using SPLICE , keeping in view that 2nd file has 2 lakh valid route nos.though we can use INCLUDE COND , but as there are many records in 2nd file is it possible to use SPLICE or any other option.

hence o/p would be

USER1 Route1
USER2 Route2
USER4 Route5
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 Apr 27, 2009 9:17 pm    Post subject:
Reply with quote

What is the RECFM and LRECL of each input file?

Can there be duplicate users in file1?

Can there be duplicate routes in file2?

What is the starting position, length and format of the fields in each input file?
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Apr 28, 2009 3:28 am    Post subject:
Reply with quote

For conformity to a standard, please use the Western numbering system instead of lakh.
Back to top
View user's profile Send private message
Ambili S

Active User


Joined: 06 Sep 2007
Posts: 112
Location: India

PostPosted: Tue Apr 28, 2009 8:15 am    Post subject:
Reply with quote

File 1 will have duplicate route nos.For e.g.

USER1 Route1
USER2 Route2
USER3 Route3
USER4 Route5
USER5 Route3

File 2 has only unique valid route nos.

Both files are FB , Rec length is 80. For File 1 the first field is user name , it's of length 15 , then route nos which starts from 16th position and is of length 20 , then there are other data till 80th pos.

For File 2 , there's only 1 field. It starts from 1 and is of length 20.
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 Apr 28, 2009 8:32 pm    Post subject:
Reply with quote

Assuming you want the output records sorted by the user name field, here's a DFSORT job that will do what you asked for.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(16,20,CH) WITHALL -
  WITH(1,81) USING(CTL3)
SORT FROM(T2) TO(OUT) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(16:1,20,81:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=T2,INCLUDE=(81,2,CH,EQ,C'VB'),BUILD=(1,80)
/*
//CTL4CNTL DD *
  SORT FIELDS=(1,15,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: Tue Apr 28, 2009 9:51 pm    Post subject:
Reply with quote

Here is another way of getting the desired results using the new WHEN=GROUP function

Concatenate your both input files , but have the file2 (which just has the unq route numbers) first in the list.

Since the file2 has all spaces from pos 21 we use that as the indicator for a group.


Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
ROUTE1                                                               
----+----1----+----2----+----3----+----4----+----5----+----6----+----7
ROUTE2                                                               
ROUTE5                                                               
ROUTE9                                                               
//         DD *                                                       
USER1          ROUTE1                                                 
USER2          ROUTE2                                                 
USER3          ROUTE3                                                 
USER4          ROUTE5                                                 
USER5          ROUTE3                                                 
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=(21,60,CH,EQ,C' '),OVERLAY=(81:1,20)),           
  IFTHEN=(WHEN=NONE,OVERLAY=(81:16,20))                               
  SORT FIELDS=(81,20,CH,A),EQUALS                                           
                                                                     
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(21,60,CH,EQ,C' '),PUSH=(101:81,20))
  OUTFIL BUILD=(1,80),                                               
  INCLUDE=(81,20,CH,EQ,101,20,CH,AND,21,60,CH,GT,C' ')               
/*
Back to top
View user's profile Send private message
Ambili S

Active User


Joined: 06 Sep 2007
Posts: 112
Location: India

PostPosted: Tue May 12, 2009 7:50 am    Post subject:
Reply with quote

Thanks , both the JCL's worked .
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


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