MVSFORUMS.com

[Cogito-Ergo-Sum]

Hi,
In general, would it be faster to unload a table which has WHERE conditions applied to it or a View built with the same WHERE condition ?

In my case, I have to unload from a join of two H U G E tables and then apply WHERE conditions one of which is a date function, as

[kolusu]

Cogito-Ergo-Sum,

I would use a correlated sub-query to get the date instead of it excueting it for every row.

[CraigG]

[jsharon1248]

I'm looking at the BMC Unload manual and it looks like you can only unload from a single table, no joins. I don't have BMC utilities to try this. Does BMC allow the joining of tables in the Unload utility?

Here's a link to their Unload manual.

http://documents.bmc.com/supportu/documents/10/66/71066/71066.pdf

[kolusu]

[Cogito-Ergo-Sum]

Kolusu,
Thanks.

I think, I will have a SORT step to get the (CURRENT DATE -190 DAYS) and pass it to the unload card so that the evaluation is removed altogether.

jsharon1248,
Yes, you can. Look at the DIRECT option in the UNLOAD statement. I think the default value was YES that does not allow standard SQL statements - in other words, WHERE clause. With DIRECT NO, you can have WHERE clauses, etc. However, the usage of DIRECT NO causes a trade-off with performance.

Actually, this is the reason, I posted this question. I thought of using UNLOAD with DIRECT YES (to utilize best performance). Since, I have two tables here, I thought of constructing a VIEW and then use UNLOAD with DIRECT YES on the whole VIEW.

PS: I tried replying earlier. I kept getting 'Invalid Session. Please resubmit the form.' message.
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.

[jsharon1248]

Thanks for the followup. I reviewed the DIRECT=NO option and it's exactly as you stated.

I have my doubts that you'd be able to trick the utility by using a view to 'hide' the join. I'm pretty sure that BMC reads directly from the tablespace dataset when you specify DIRECT=YES. That being said, you're not going in through the DB engine. Even if the tables in the view are in the same tablespace, I don't think BMC will be able to do the work without developing an access path and all that. It's certainly worth a try, but you might be forced to use an alternative. I'll be interested to see what you find.

[Cogito-Ergo-Sum]

jsharon1248,
I have been never good in the DB2 "internals" and the ways these utilities work.

I am not exactly "chums" with our DBAs nowadays, So, it will be quite sometime until I set up the test to report on.

Thanks anyway !
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.