Joining queries based on different data sources

[ccbarbeau]

Good morning,

I am running into an issue that I think is common but I have not found a way to work around it. Here goes; I am on RS 10.1.1.

First query is based on an Oracle DB. Returns tabular data within seconds.
Second query is based on DB2 DB. Returns tabular data within seconds. Contains query item that is in a separate SQL table; query works fine if run alone.

I am joining them to do a simple speed to volume calculation (ie, time estimated to complete a certain task). No matter how I link them, how I cast the linked fields, the join query runs forever until it times out.

Any ideas on how to make this work?
Thanks!

[Kiran P]

What happens when you simply pull a query item from each of these and run the query without creating any calculations?

- Kiran


Sent from my iPhone using Tapatalk

[ccbarbeau]

A join works, as does a union.

[the6campbells]

You are performing a distributed join. In 10.2.2 with Dynamic Query, a feature was provided known as a filter-join. Hence, if you were to join A to B, the engine could scan A first and dynamically generate a predicate on the query for B to select those rows in B with associated key values from A. In the release you are using, A and B are processed as independent queries and joined within the query engine locally.

[the6campbells]

A report studio report can use the Master-Detail layout, at runtime this is akin to a nested-loop operation. That is, if A is populating the master data container, then for each row of A, the detail query for B is executed where you can pass values from A which become predicate values for B. This pattern is also an implicit left outer join (i.e rows from A are still rendered even if there is no B row). If your business query does not require a relational join semantically then this may be a faster solution.

Keep in mind, if there are 1000 rows in A then that translates to 1000 queries to B. Should you move to using 10.2.2+ with Dynamic Query, you will see that master-detail optimization can is available to reduce the # of queries on B. This optimization also applied to other "implicit" master-detail layout patterns such as Page-Sets.

Details in the latest information center and redbook
https://www.redbooks.ibm.com/Redbooks.nsf/RedbookAbstracts/sg248121.html?Open