Topic
1 reply Latest Post - ‏2013-07-30T13:57:12Z by Brian_Laube
Okonita
Okonita
7 Posts
ACCEPTED ANSWER

Pinned topic Data Studio 4.1 Developers, please explain this - Critical question about removal of Support for collecting the actual values to compute the cost of SQL statements in DB2 for z/OS data sources.

‏2013-07-25T05:09:04Z |

Hi all,

As stated in the "What's New and Changed in IBM Data Studio Version 4.1" product documentation, features have been removed and are no longer supported in this release include "Support for collecting the actual values to compute the cost of SQL statements in DB2 for z/OS data sources". My concern about the removal of this feature  is this:

1.) Will we see different estimates and if so based on what?

2.) Will the Explain generate different access paths as a result?

3.) How else are we going to get/compute the cost of SQL statements in DB2 for z/OS or DB2 for LUW data sources?

 

I will appreciate very much an and all verifiable answer to this question.

 

Thanks a million everyone!

 

okonita

 

 

 

  • Brian_Laube
    Brian_Laube
    5 Posts
    ACCEPTED ANSWER

    Re: Data Studio 4.1 Developers, please explain this - Critical question about removal of Support for collecting the actual values to compute the cost of SQL statements in DB2 for z/OS data sources.

    ‏2013-07-30T13:57:12Z  in response to Okonita

    You have not yet received a response from the IBM developers of Data Studio v4.1.

    I will offer my opinion on your questions.


    You are pointing out that IBM Data Studio V4.1 no longer "Support for collecting the actual values to compute the cost of SQL statements in DB2 for z/OS data sources".

    BUT, I do not know how this specific information was represented in previous versions of IBM Data Studio.  I do not know what this change really means to IBM Data Studio v4.1....

    1) Will you see different estimates? 
    Estimated cost of a sql statement can always vary from explain time to explain time.  Remember, we can never assume HOW the optimizer will decide to execute any sql statement.  IBM makes no guantees about access path and it can theoretically vary from one day to another.  There are many variables that determine the access path used by the otpimizer (statistics, available indexes, db2 software version, available cpus, parralleism, phase of the moon, etc).  If the variables change then the access path could change.
    And besides, IBM is not saying they are changing how the optimizer works.  They are just saying that IBM Data Studio will no longer collect the actual values to compute the cost of SQL statements in DB2 for z/OS.  It sounds like IBM data studio v4.1 will not display this specific information.
    Although, I can't find where this specific information was displayed in previous version of IBM data Studio....

    2) Will the explain generate different access path?
    The explain does not generate access paths.  That is the job of the db2 sql statement optimizer.  And that is not changing.  The explain asks the db2 sql statement optimizer and analyze and determine a access path for this sql at this moment in time... AND explain asks the optimizer to put a description of the access path in the plan_table(s).  Then a tool like IBM data studio queries the plan_table(s) and interprets the information and makes a pretty picture and report.

    3) How else will get you get/compute the cost of sql statements in db2 for z/os and db2 for LUW?
    IBM did not change anything related to db2 for LUW -> so that has not changed.
    I use IBM Data Studio V4.1 (and I have used earlier versions) with DB2 for z/OS data sources (which are V10CM and V10NFM). And IBM Data Studio V4.1 access plan graphs continues to report cpu cost estimates. 
    The access path graph diagram continues to report CPU cost for many of the nodes in the acces plan graph:
    * the query node displays a CPU cost (ms) and CPU cost (su)
    * the query block node displays cumulative cpu cost
    * the action nodes (like index scan) displays cumulative cpu cost

    Therefore, if IBM Data Studio v4.1 continues to report the above cpu cost estimates then I am happy.

    I hope I have helped....

     

    ... I also hope that maybe the developers of this software might answer your origianl question... and really explain the meaning of that statement!