ODBC join tables limitation

[emma]

I would like to know if there is any known limitation on how many table can I join into an ODBC connector.
It seems to have problems for 4 and more.

[ray.wurlod]

Theoretically, within DataStage there is no limit. Practically, of course, there will be, not least because the joins are performed pairwise left-to-right and the intermediate result sets have to be stored somewhere. If you're relying on inserted tsort operators, then you must also factor in the total memory demand for effecting these sorts.

Can you push some of the joins back into the database (using user-defined SQL or, at the very least, the entire join specification in the Table Name property)?

[chulett]

What kind of problem / errors are you seeing when there is more than 4? Are you supplying the SQL or is the stage generating it? I've seen limitations in the past with other stages when the size of the user-defined SQL exceeds what the stage could hold and the end of it would get trimmed off, resulting in all kinds of odd errors being reported.

[emma]

These are my query joins :

from table 1 lst
inner join table2 ent ON ent.ENT_NO=convert(varchar(9),lst.key) and ent.CUR_FLG = 'Y'
inner join table3 ctry on ent.key = ctry.key and ctry.CUR_FLG='Y'
inner join table4 on ent.ENT_NO = convert(varchar(9),dps.key) and dps.FLAG = 'Y'
left outer join table5 ifo on ifo.key= dps.key

If I join 3 of them (no specific order) it works , but I try to add more tables doesn't work anymore.
What's bizarre that I try to view the results on the ODBC connector works but when I run it fails.

[chulett]

Time to involve support, if you haven't done so already.

[ray.wurlod]

What is the stage type downstream of the ODBC Connector stage in your job?

[emma]

In fact doesn't fail , is just gone for the eternity ...

In the job I have

ODBC -----> Transformer (very basic transformation) -----> Difference( DataSet ) ------> Target table

I didn't involve the support by now, I just workaround by splitting the query and using Join stages.

[ray.wurlod]

Does the four join query work executed from some client other than DataStage? How long does it take to return any rows?

[emma]

Hi Ray : Yes the query runs perfectly from another tool (SQL Query Manager tool)

I can say definitely that ODBC doesn't handle more that 3 join tables.

I tried another one ; works with 3 tables but not with the 4th one

Select count (*)
FROM TABLE_1
inner join TABLE_2 ON tbl1.key = tbl2.key
inner join TABLE_3 ON tbl3.key = tbl2.key
left join TABLE_4 ON tbl4.key = tbl2.key
WHERE TABLE_4.KEY is null

Something more , I expect in my case to have 0 records out from the query but the ODBC connector still pull out the number of records like he doesn't see the 4th table.
Again if I try the <View Data> option from the connector interface , gives me the right answer.
And I have the right number by using another tool.

What wonders me is nobody rise this issue by now !

[kduke]

Not the same query in the 2 examples. The first example:

Code: Select all

from table 1 lst 
inner join table2 ent ON ent.ENT_NO=convert(varchar(9),lst.key) and ent.CUR_FLG = 'Y' 
inner join table3 ctry on ent.key = ctry.key and ctry.CUR_FLG='Y' 
inner join table4 on ent.ENT_NO = convert(varchar(9),dps.key) and dps.FLAG = 'Y' 
left outer join table5 ifo on ifo.key= dps.key 

Second example:

Code: Select all

Select count (*) 
FROM TABLE_1 
inner join TABLE_2 ON tbl1.key = tbl2.key 
inner join TABLE_3 ON tbl3.key = tbl2.key 
left join TABLE_4 ON tbl4.key = tbl2.key 
WHERE TABLE_4.KEY is null 

The first has extra column in your "ON" list. I expect this column is not a key field therefore your database cannot optimize this. Move the second field to the "WHERE" clause and it will optimize your joins and run fine.

[emma]

The issue was the buffer not the number of tables. After I've increased the buffer and the administrator put some indexes on the tables keys, the query worked well.