Hello,

for reporting (with crystal) I wanted to create a db2-procedure that returns a special constructed table. In fact I did that with a function that returns a table and a stored proc that is called by crystal with some parameters. This works.
The problem is to fill the table with all needed data. The table should have 4 columns.

First part is just a select:
select column1,column2 from tab1

But I want to return column3 and column4, which are in a different table and which depend on col1/col2-values.
Though there will be a select over the given resultset:
select col3,col4 from tab2 where tab2.col5 = <tab1.col1>

col3 and col4 should now be added somehow to the first resultset, and the "new" resultset should be returned as a table.

Any idea?

DB2v9

Wouldn't a join do what you want?

Andy

Thanks for answer, so u understand the problem :-)

No, join is not possible because db2 is not using the index, i don't know why. When using a separate select the index is used. In fact tab2 is a view, consisting of at least 100 daily tables. I already tried to improve the explain plan, no deal.

Rolling the join on your own is bound to be slower than letting DB2 do it. So just use a join and be done with it.

The question why DB2 is not using your index is hard to answer without knowing what DB2 tries to do instead. Maybe it is used another index that better matches the query?

And do you really join 100 tables in a view? That's like begging for problems and even in warehouses you won't see such complex queries often.

As we started the project it was not clear that db2 supports partitioning, to avoid one big table with transaction data we decided for a daily table construction. All the tables do have the same structure. But for some reason we need an ALL-View, e.g. reporting.

My procedure should be faster than db2 join. Why?
The resultset of tab1-select is going to be very small (up to 500 records), the tab2-View has at least 50 million records, but I just want to pick up col3,col4 of 500 records. I thought the db2-join would do that for me, but the explain plans shows the difference.
So my plan was to select the 500 records and then finding the needed information in tab2-view by using the index. A select on tab2-view with an index value is fast enough.

When you say an "ALL-View" you mean a "UNION ALL view", for which DB2 LUW has quite a few performance optimizations?

In any case, I still stand to my comment that doing the join yourself is typically slower than letting DB2 handle it. Why you see some difference in the access plan, is not clear to us because we don't know the table structure, indexes, and queries. So all we can do is some guessing - and Andy and myself just think that your approach is bound to be problematic.

Indeed, we are using union all. I suggest there are no other ways to put all the daily tables together?
For now we can't change to using partitions.

I don't know what you want to know. Doing a join yourself is possible and just requires two queries: one for the outer part of the (nested loop) join and one query for the inner branch.

Using a single query will also work. If such a query has performance problems, I would rather tackle those problems, i.e. identify and solve them. But for this, you would have to provide some more details.

just a table function....