Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Question Unanswered: Does a SELECT * involve a catalog lookup (z/os v7.1)?

    We're contemplating changing a whole bunch of our programs to switch from using a simple SELECT * to instead have all columns listed on the programs.

    The underlying assumption is that the program has to go lookup the catalog to figure out the column listing on a SELECT * and by listing the columns instead (and we'd simply be replacing the * with ALL columns on the table), we'd skip that catalog lookup, thereby improving performance.

    Is there any documentation that discusses this aspect?

    Thanks
    rm

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are using static SQL, it will not involve a catalog lookup at execution time. Static SQL would normally be used in most COBOL or PLI programs (but not always). If you are using a query tool, you have dynamic SQL and a catalog lookup is necessary in any case.

    However, you should never use select * in a program since the table may change (if you add a column, etc) and the program may no longer work.
    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
    Sep 2003
    Posts
    22
    Quote Originally Posted by Marcus_A
    If you are using static SQL, it will not involve a catalog lookup at execution time. Static SQL would normally be used in most COBOL or PLI programs (but not always). If you are using a query tool, you have dynamic SQL and a catalog lookup is necessary in any case.

    However, you should never use select * in a program since the table may change (if you add a column, etc) and the program may no longer work.
    It is static embedded SQL in all these programs - we understand the impacts on the table structure changes etc. Wondering if you could point to some documentation online (ibm site perhaps) that supports this. My instinct was to doubt that assertion as well, but I'm hoping to find some documents that support the theory in either case...

    Thanks for the response.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is not a theory.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2005
    Posts
    191
    Actually, as in all theories, there are exceptions.

    The exception in this case is if you are prepared to use "DESCRIBE CURSOR c1 INTO :mysqlda" and then use the appropriate processing to allocate storage for the actual columns the SELECT * will be retrieving.

    But if you aren't prepared to do this work up front ...

    http://www.dbazine.com/mullins_view.shtml/ (specifically orientated to "create view (...) as select * ..."
    http://expertanswercenter.techtarget...979420,00.html


    James Campbell

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by jacampbell
    Actually, as in all theories, there are exceptions.

    The exception in this case is if you are prepared to use "DESCRIBE CURSOR c1 INTO :mysqlda" and then use the appropriate processing to allocate storage for the actual columns the SELECT * will be retrieving.
    That is not static SQL, it is dynamic. It is embedded SQL, but not static.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There may be some catalog access required, even for static SQL. This might include plan/package authorizations. But DB2 will not have to resolve the list of columns from the catalog if the program is using static embedded SQL.

    You should have your syscatspace in a bufferpool that is large enough to hold most of the catalog in memory, and large enough to not get flushed out of memory if you are sharing the bufferpool with other tablespaces.

    Because of the headaches that would be caused by schema changes, anyone who uses Select * in embedded static SQL should be drawn and quartered.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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