Lookup into a file to make changes



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Lookup into a file to make changes

Postby nkvprasad » Mon Nov 14, 2011 9:25 pm

Hi,
I have a requirement where I will get two files from the source as below.

FILE-1:
OLD_ID NEW_ID
--------- ----------
AA1 121
AA2 122
AA3 123
AA4 NONE

FILE-2:
CUST ID LOCATION
-----------------------------
XXX AA1 LONDON
YYY AA3 PARIS
ZZZ AA4 LONDON

They are FB datasets. Now I need to process the FILE-2 to replace the ID with its corresponding NEW_ID from FILE-1. The ID in the FILE-2 is the OLD_ID in the FILE-1. My output should be like:

OUTPUT:

CUST ID LOCATION
-----------------------------
XXX 121 LONDON
YYY 123 PARIS

Please note that the record should NOT be written into the output dataset whenever its corresponding NEW_ID is "NONE". (so why the last records has not been written into the output.

- Prasad
nkvprasad
 
Posts: 3
Joined: Mon Nov 14, 2011 9:07 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Lookup into a file to make changes

 

Re: Lookup into a file to make changes

Postby skolusu » Tue Nov 15, 2011 2:06 am

nkvprasad,

The following DFSORT JCL will give you the desired results

//STEP0100 EXEC PGM=SORT                     
//SYSOUT   DD SYSOUT=*                       
//INA      DD *                             
----+----1----+----2----+----3----+----4----+
AA1 121                                     
AA2 122                                     
AA3 123                                     
AA4 NONE                                     
//INB      DD *                             
XXX AA1 LONDON                               
YYY AA3 PARIS                               
ZZZ AA4 LONDON                               
//SORTOUT  DD SYSOUT=*                       
//SYSIN    DD *                             
  OPTION COPY                               
  JOINKEYS F1=INA,FIELDS=(1,3,A)             
  JOINKEYS F2=INB,FIELDS=(5,3,A)             
  REFORMAT FIELDS=(F2:1,4,F1:5,4,F2:9,72)   
//*                                         
//JNF1CNTL DD *                             
  OMIT COND=(5,4,CH,EQ,C'NONE')             
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Lookup into a file to make changes

Postby nkvprasad » Tue Nov 15, 2011 3:09 pm

Thanks Kolusu!! But the issue is, these values are stored and set in two different datasets and there can be thousands of records in each dataset.
nkvprasad
 
Posts: 3
Joined: Mon Nov 14, 2011 9:07 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Lookup into a file to make changes

Postby BillyBoyo » Tue Nov 15, 2011 3:13 pm

Have you tried the sort cards provided? Do they work (yes, with what you have said so far)? What problem do you have with the solution?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Lookup into a file to make changes

Postby Frank Yaeger » Wed Nov 16, 2011 12:06 am

But the issue is, these values are stored and set in two different datasets and there can be thousands of records in each dataset.


What about this do you think is an issue and why? Just point INA, INB and SORTOUT to the appropriate data sets using Kolusu's job and run it. If it doesn't do what you want, then you can tell us exactly what the "issue" is. "thousands of records" is truly trivial for DFSORT.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Lookup into a file to make changes

Postby nkvprasad » Wed Nov 16, 2011 6:50 pm

Thanks for your replies. I could achieve this using the ICETOOL's "SPLICE" operator.

Thanks to Kolusu also, I did miss some part of his code as my browser didn't load the code properly. The solution he provided worked perfectly.
nkvprasad
 
Posts: 3
Joined: Mon Nov 14, 2011 9:07 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post