Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Posts
    23

    Unanswered: Show what the DBMS runs when selecting from a view (that selects from multi views)

    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!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can do an Explain, and look at the Optimizer Query re-write.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2008
    Posts
    23
    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?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It will be an optimizer version, i am afraid.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Oct 2008
    Posts
    23
    Ok. I thought so. Thanks.

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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hifferyj View Post

    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"?

  7. #7
    Join Date
    Oct 2008
    Posts
    23
    Quote Originally Posted by n_i View Post
    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?

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hifferyj View Post
    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 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.

    Quote Originally Posted by hifferyj View Post
    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?
    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.

  9. #9
    Join Date
    Oct 2008
    Posts
    23
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •