Getting the Looked up Data

[Gokul]

Hi,

I have a Job design in which source data is looked up against the data in the hash. The Requirement needs both the source records and matching record(based on key lookup) in the Target file.

The Design which i built and is working fine is

SRC---filter bycond a--->XFM1----->XFM2 using Lookup------> Target
XXX\
XXXX\
XXXXX\ --all records------->XFM3---->Target

here the Target is the same sequential file.

In this case i require 3 Transformer to get the Looked up as well as corresponding matching source record both in Target.

Can this be done in Single Transformer?

[chulett]

Peoples - please use the Code tags when posting ascii art job designs so they don't all slump over to the left and become even more incomprehensible. That and the 'Preview' option so you know what it will look like after you submit it. Thanks.

Can this be done in Single Transformer?

Don't see why not. Hard to say for sure without intimate knowledge of the relationships or dependencies (if any) between the three lookups.

here the Target is the same sequential file.

The same as the source file? If so, that is what is known as a Bad Idea.

[Gokul]

Let make things clear,

"here the Target is the same sequential file"----> By that i mean that both the target stages refer to the same Sequentail file.

Details :

ex .Source Data is...
ID Value----->Columns
1 10
2 20
3 30

Hash Data
Id Value------------>Columns
3 300
23 2300

Then the target shld contain
Targer
Id Value------------>Columns
1 10
2 20
3 30
3 300

[chulett]

"here the Target is the same sequential file"----> By that i mean that both the target stages refer to the same Sequentail file.

Sorry, still a Bad Idea to have two processes writing to the same sequential file simultaneously, if that is what you are truly doing. Results will be... unpredictable at best. It goes against the nature of a sequentially accessed data structure like a 'flat' file.

[ray.wurlod]

It's worse than a Bad Idea - it's impossible. This is not a DataStage restriction - it's an operating system restriction. A sequential file may have N readers or 1 writer. The operative word here is or - you can't be reading and writing the same file simultaneously with standard utilities. Nor can you have two simultaneous writers to the same text file.

[chulett]

Actually, not impossible at all. For whatever reason, I see people do stuff like this all the time - and most of the time they are convinced it is working. At least at first.

I think it depends on the O/S - some allow it and some try to protect you from yourself. But 'allowing it' and it working anything like is expected are two different things.

[talk2shaanc]

it can be done in single transformer: if i have understood the req., then it should be done in the following way.

L1, L2 and L3 are link name. and assuming you have just two columns col1 and col2. col1 is key column.

Code: Select all

 
                Hash
                  |   L2
         L1      |    L3
Seq------>Xfm------->seq

1. Dont give any constraint to link.

2. in the transformer stage have output as only one column 'outcol'.
3. to 'outcol' give a derivation something like.
For windows server:
if Not(IsNull(L2.Col1)) then
L1.col1:'|':L1.col2:char(13):char(10):L2.col1:'|':L2.col2
Else
L1.col1:'|':L1.col2


For Unix server:

if Not(IsNull(L2.Col1)) then
L1.col1:'|':L1.col2:char(10):L2.col1:'|':L2.col2
Else
L1.col1:'|':L1.col2



I feel this would work, do let me know, if you face any problem.

[Gokul]

Thanks Shaan,

That worked.