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
 

 

Is it possible to relate two files by key fileds

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Is it possible to relate two files by key fileds
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Dec 28, 2006 10:58 pm    Post subject:
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    Post subject:
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    Post subject: Similar situation
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Jan 12, 2007 9:50 pm    Post subject:
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    Post subject: Re: Is it possible to relate two files by key fileds
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jan 16, 2007 9:55 pm    Post subject:
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    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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm
No new posts Icetool or Joinkeys example to compar... ishant chauhan DFSORT/ICETOOL 21 Sat Aug 20, 2016 2:40 am


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