I have a typical problem as follows..
In a mappings two sources, one as flat file and other relational (DB2) and my requirement is to find out the change only records from db.
so i put one joiner with master outer join (DB2 as master and flat file as Detail) then i compare all the fields in the filter and as it goes..

Problem: when i debug the session, all the records are being read from both the sources and when it reaches the joiner transformation it is not displaying the data of the flat file and so compariosn is not taking place in the filter.

When u have Master outer join , u allow all the rows from Detail source
and matching rows from Master source to pass while unmatched rows from the
master source are discarded .
so , In ur case all the rows from flat file should pass the joiner and if
there are not any matching records for flat file record , then
corresponding Master fields should be NULL .

Two relational tables existing in separate databases
♦ Two flat files in potentially different file systems
♦ Two different ODBC sources
♦ Two instances of the same XML source
♦ A relational table and a flat file source
♦ A relational table and an XML source
You use the Joiner transformation to join two sources with at least one matching port. The
Joiner transformation uses a condition that matches one or more pairs of ports between the
two sources.
For example, you can join a flat file with in-house customer IDs and a relational database
table that contains user-defined customer IDs.
If two relational sources contain keys, then a Source Qualifier transformation can easily join
the sources on those keys. Joiner transformations typically combine information from two
different sources that do not have matching keys, such as flat file sources.

Can I use a joiner transformation any where within a mapping.
Suppose if I have two expression transformation and each of expression transformation has a common port ID, can I join these two transformations using a joiner transformation .
I am under the impression that joiner transformation should always follow source qualifier, please correct me if I am wrong.

Hi,
Check whether the flat file source has been imported correctly.
Since you need the rows from the db, try using detail outer join.
This will select all rows from the master(DB2 in your case) and matching rows from detail(flat file in your case).

HI
To join sources from heterogenious sources(or homogenious) u should use joiner trans formation.
but u can join only two sources by using single joiner transformation.if u want to join more than that u should use more source qualifier.in other words nuber of joiners required is (n-1).where n is the number of sources.
ex source1
joiner 1

source2 joiner2
source3
u can use source qualifier transformation to join homo genious sources if it is having relations.if there is no mathcing columns in both the tables u can use surrogate key to match the tables and after that u can use either joiner or sq transformation.

Hi,
I have few questions regarding Informatica. They are:
1.While joining in a joiner how do you decide which table is master and which is detail?
2.How does sorted input data used with aggregator works? I know peformance increases but how?
3.Diff between partition types?

Hi Arpita,
For your 2nd question, the session's performance will definitely increase when you give sorted input to aggregator sorted by GROUP BY port because the to-be grouped rows will be one after the other=2E Sorting by some other ports will not improve the aggregator performance and so there is no need for checking the option "Sorted Input"=2E

If you do Sorted Input before sending data to Aggregation Transformation, the grouping will be easy and it doesn't need to store the entire data in cache to do aggregation..

while doing aggregation (based on ID) if it finds a newid (ID :2) immediately it will make this(2) as new group, and (id1) as a group.
but if you don't do Sorted Input it cannot make a new group even it finds a new id. and it has to read the entire data(table) to make a group. This will definitely take more time.

When we do aggregation it groups similar records and sums them up.
For eg.
If data is
Name Sal
A 10
B 20
C 22
D 33
A 30
C 40

Here when u perform grouping on Name for Salary, Informatica cahes the first
entry for name
A in the cache and keep it in Cache until it reaches the last record ie C,
since it is not sure whether there will be furhter recordes with Name -- A

Now if data is sorted,

Name Sal
A 10
A 30
B 20
C 22
C 40
D 33

Now here it will flush the cache as soon as it receives the record
with Name as B, since it know that the dast is sorted and directly summed
data is passed on.

I am having two sources, I have joined with joiner transformtion.I have taken detail outer join type . and my join condition is like

a.cid=b.cid( a and b are two tables).
But when i run the mapping i am getting cartesian join. Can anybody tell me wht is the problem. I am having common data in both tables. Only 2 records are will not match in detail file

Hi,
If you have joined the two tables without joining the key values of master
table to key values of the detail table or viceversa, you are bound to get
a cartesian. You need to know the data and underlying constraints before
joining. You can use a SQL outside informatica and test the same outside
before defining the joins.

On Mon, 10 Apr 2006 geetha_varma5...via informatica-l wrote :
>
>
>Hi,
>
>I have a problem with joiner transformtion,
>
>I am having two sources, I have joined with joiner transformtion.I have taken detail outer join type . and my join condition is like
>
>a.cid=3Db.cid( a and b are two tables).
> But when i run the mapping i am getting cartesian join. Can anybody tell me wht is the problem. I am having common data in both tables. Only 2 records are will not match in detail file
>
>
>thanks,
>geetha
>
>
>
>
>
>

i have two tables tab1 and tab 2. I want to make a join of both tables with
fields fld1, fld2, fld3. The point here is, fld2 is having null values on
both tables.
And the business is to join for the condition tab1.fld1= tab2.fld1 and
tab1.fld2= tab2.fld and tab1.fld3= tab2.fld3. Even though the value for
fld1 and fld 3 are same and both fld2 has null, the partcular row doesn;t
get joined.
How do i solve this issue consider the null value?

Hi i need a small help. i'm getting some 20 million records from my source table lets assume A and 10 million records from source B. How much time does Informatica takes to cache and join these tables.Here we are using master outer join here where as mater as A and Detail as B?

If both table belongs to the same database and if a given Load Id has access
to both of them, then the best and optimum way of doing this is to perform
the join in SQ
As for the time, I am not really sure as it also depends on the record
length of Source A and B.
In case you plan to use Source Qualifier, then to find an approximate time -
all you need is to fire this query in a SQL editor and the time taken by
INFA to perform the join is slightly higher than that of the former

Hi Abha
we need Joiner Transformation here to join the sources
from Oracle and Sql Server Because there are Heterogeneous sources
If they r Homogeneous sources then there is no need of joiner we can do it in Source Qualifier itself