Hi,

I can't seem to find this here so I thought I'd ask.

Before I ask, I know about the sysviews table that allows us to see view definitions. My question is more regarding a view that selects from multiple views, which also select from multiple views.

Is there a utility that will take a view name and give you what the actual DBMS sends to be run when you try to select from a view?

In other words.

If the query is:

select cats from grey_stray_pets

gray_stray_pets is:

select cats, dogs, birds from stray_pets
where color = 'grey'

and stray_pets is:

select cats, dogs, birds, monkeys, walruses
from pets

I would like a utility that would translate the first query into the actual SQL DB2 sends to be run which might look something like this:

select cats from
(select cats, dogs, birds from
(select cats, dogs, birds, monkeys, walruses from pets) as pets
where color = 'grey') as stray_grey_pets

That was all very rough but I think I got my idea across. Any ideas would be appreciated.

Thanks!

You can do an Explain, and look at the Optimizer Query re-write.

Thanks. I'll give that a shot. But would that be in "optimizer-eeze" with actual logic rewritten for the sake of optmization? Or will it be just the views replaced with the actual SQL underneath them?

It will be an optimizer version, i am afraid.

Ok. I thought so. Thanks.

Its surprising there isn't a utility that explodes these views.

Is it really? I would not think this would be such a common task to "explode views".

May I ask why you need this? What is the ultimate goal of "exploding these views"?

The source system that our customer needs us to pull data from has all of their table access through views. They are considering removing their views. So, to continue using their database, we will have to begin pulling from tables. In order to do so, we need to understand the views which can be done from a catalog.

I'd rather have a tool do it.

Considering it could likely be automated with a script, I would imagine that the functionality would already exist.

Given that you're quoting how I'm expressing what I need, I'm curious. Is there a more industry appropriate way to describe what I'm talking about?

I can't say this is a common situation, and I doubt there is an existing tool that does the substitution you need. Typically the database physical model documentation would assist in this task, but I have a feeling that there's none in your case.

If you want to minimize your efforts I think the brute force approach of getting the rewritten query from the explain output, suggested by Marcus, should satisfy your needs. Otherwise you would probably write a recursive query against SYSCAT.VIEWS to extract view definitions and substitute the corresponding references.

No, I'm not aware of an alternative term. I didn't mean to criticize your choice of words, it just was unfamiliar to me.

Its ok. I didn't take it that way. I just tend to make up terms when I Don't know one for them based on similar uses of the term.

Syscat it is then.