1

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked.

I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just about any database.

Anyway, say I have two tables in the same database:

select ID, EMAIL from TBL1
select ID, EMAIL from TBL2

What I then want to do is search for a field within those two tables. The field could be in either table.

So, I have tried the following:

select ID, EMAIL from TBL1 where EMAIL = 'blah@aol.com'
union
select ID, EMAIL from TBL2 where EMAIL = 'blah@aol.com'

This works. But is redundant and ugly.

However, is it more efficient than:

select T.* from
(
    select ID, EMAIL from TBL1
    union
    select ID, EMAIL from TBL2
) T where T.EMAIL = 'blah@aol.com'

Both seem to work. Unfortunately, I don't have any real profiling tools for DB2 (along with restricted access) so I can't seem to figure out which is better. They both execute at roughly the same speed in the console but that isn't a good indicator.

Any tips on this (or even how to maybe profile it myself) would be appreciated.

Of course, this is a highly simplified example. The real queries are quite massive and span 9-18 tables, databases, etc.

Thanks

EDIT

The DB2 is on iSeries and I have no idea what version it is. Everything I try to use gives an error. I want to say it's version 8 but I really don't know.

If anyone wants to clue me in on how to give more information I will. I don't know much about DB2.

3
  • 1
    You may want to start by mentioning your DB2 version and platform. However, there is no generic answer to this question: you cannot tune massive queries spanning 9-18 tables and databases by looking at a simplified example.
    – mustaccio
    Aug 18, 2015 at 15:12
  • I know around zero about DB2 but it must not be that difficult to find the version. Try these suggestions: onewebsql.com/blog/finding-your-db2-version Aug 18, 2015 at 15:45
  • Yeah, I've tried that too. I think my permissions are too restricted.
    – cbmeeks
    Aug 18, 2015 at 15:52

1 Answer 1

1

You need to use the explain facilities in DB2 to answer this question, it's highly specific to the actual queries you are executing, and you can't get a general answer.

IBM i 7.2 (and previous versions) have Visual Explain.

If you are restricted from doing this, then you need to speak to the system or database administrator for your environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.