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

Is it possible to relate two files by key fileds


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

New User


Joined: 29 Nov 2006
Posts: 8
Location: India

PostPosted: Thu Dec 28, 2006 4:58 pm
Reply with quote

Hi,

Is it possible to relate two files by key fileds when one of them contain multiple records corresponding the key.
Lets take an example.

My Driver file contains only one field [Account no]

Code:

123
234
345
456
567


My transaction file looks like this with two fields [Account No], [Record Type]

Code:

123 01
123 02
123 03
119 01      <= Should be excluded in output , doesn't exist in driver file
119 02      <= Should be excluded in output, doesn't exist in driver file
234 01
234 02
345 03



Now my output requires all the records from transaction file corresponding to the account present in driverfile.

Desired output wud be like :

Code:

123 01
123 02
123 03
234 01
234 02
345 03


As per my knowledge splice operator can be used only in case there is an one to one correspondence in driver and transaction file. I am really looking for a cool solution for this.

Useful input would be of great help.

Thanks
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: Thu Dec 28, 2006 10:58 pm
Reply with quote

Quote:
As per my knowledge splice operator can be used only in case there is an one to one correspondence in driver and transaction file.


Your knowledge is faulty. SPLICE can handle this.

Here's a DFSORT/ICETOOL 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/3)
//IN2 DD DSN=...  input file2 (FB/6)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/6)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) -
  WITHALL WITH(1,7) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(7:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(7:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(7,2,CH,EQ,C'VB'),
    BUILD=(1,6)
/*
Back to top
View user's profile Send private message
Neilabh

New User


Joined: 29 Nov 2006
Posts: 8
Location: India

PostPosted: Fri Dec 29, 2006 9:31 am
Reply with quote

Thanks Frank for correcting me.
Back to top
View user's profile Send private message
samdasamda

New User


Joined: 12 Jan 2007
Posts: 2
Location: chennai

PostPosted: Fri Jan 12, 2007 2:23 pm
Reply with quote

Hi I ve got a similar situation here. I've got two input files FILEA and FILEB (both Variable blocked files). FILEA contains the key in the first 15 characters (position 1, length 15). FILEB contains the key split into two different positions (position 1, length 6 and position 10, length 9). FILEA has no duplicates but FILEB has duplicates based on the keys.

Now For each FILEA record, I need to pull all matching records (based on key) from FILEB to a new file - FILEC. FILEA and FILEB are already in sort oredr of the key.

Please advise.
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 Jan 12, 2007 9:50 pm
Reply with quote

Please show an example of the records in each input file and the records you expect for output. If fileA can have duplicates within it, show that in the example. If fileB can have duplicates within it, show that in the example.
Back to top
View user's profile Send private message
samdasamda

New User


Joined: 12 Jan 2007
Posts: 2
Location: chennai

PostPosted: Tue Jan 16, 2007 4:52 pm
Reply with quote

Here is the sample content of both the Input Files:

FILE A:
LRECL 5004, VB
KEY : Starting position 1, Length 15 (NO DUPLICATES)

Code:

004764158508623        ....0
004965101010101        ....0
005708227981716        ....0



FILE B:
LRECL 5004, VB
KEY : Composite key (Pos 1, length 6 + Pos 9, Length 9) (HAS DUPLICATES)

Code:

004764..158508623        .rj.
004764..158508623        .rj.
004965..100348210        .r ?
004965..100348210        .r ?
005708..227981716        ....
005708..227981716        ....
005708..227981716        ....
005708..227981716        ....
005708..227981716        ....


OUTPUT FILE : FILE C :
Should be same format as FILE B.
Should contain all duplicates of FILEB if FILEA KEY = FILEB KEY

Code:

004764..158508623        .rj.
004764..158508623        .rj.
005708..227981716        ....
005708..227981716        ....
005708..227981716        ....
005708..227981716        ....
005708..227981716        ....
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: Tue Jan 16, 2007 9:55 pm
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/5004)
//IN2 DD DSN=...  input file2 (VB/5004)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS),
// LRECL=5006
//OUT DD DSN=...  output file (VB/5004)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(7,6,CH) ON(15,9,CH) -
  WITHALL WITH(6,5001) VLENOVLY USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,4,5:C'BB',7:5,6,15:11,9)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,4,5:C'VV',7:5)
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(5,2,CH,EQ,C'BV'),
    BUILD=(1,4,5:7)
/*
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 Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts How to append a PS file into multiple... JCL & VSAM 3
Search our Forums:

Back to Top