Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    36

    Unanswered: List statistical views

    How do I determine the statistical views that are dependent on a particular table?

    Here is why I need to know:

    I am testing an upgrade of a DB from DB2 9.1 to 9.7. Part of the upgrade requires dropping a column from a table. Every time I do this, it makes any future attempts to create procedures referring to that table fail.

    After several hours of digging recently, I have found this:
    https://www-304.ibm.com/support/docv...id=swg1IZ39997

    The gist of this is that the act of dropping a column from a table having dependent statistical views (which cannot be regenerated) can cause irreversible issues. The preventative measure is described this way:

    If altering a base table on which statistical views are
    dependent:
    1. disable query optimization for all statistical views
    2. perform the alterations required on the base table
    3. enable query optimization for the views once again.

    How, though, can I determine, based on the table name, its dependent statistical views so I know which ones to alter before I drop the column? This is pretty much over my head. I do not really understand (and am not entirely sure I want to) the ins and outs of query optimization and statistical views. I am just trying to get my DB upgraded.

    I posted my initial issue (but got no response) several months ago before I finally figured out it was related to statistical views: http://www.dbforums.com/db2/1664536-...n-failure.html

    (yes...this upgrade has been on hold for that long!)

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try running db2look with the -d database-name -t table-name -e -a options.
    (No guarantees).
    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
    Feb 2011
    Posts
    36
    Thank you, Marcus.

    That gets me the names and text of the dependent views. I have previously done that by querying SYSCAT.VIEWS thus:

    SELECT *
    FROM SYSCAT.VIEWS
    WHERE DEFINER <> 'SYSIBM'
    AND VIEWSCHEMA = 'LYNX'
    AND text like '%MyTableName%'
    ORDER BY VIEWNAME

    However, I still cannot tell which views are statistical, since the result returns just these columns, none of which seems to indicate which are enabled for query optimization:

    ViewSchema
    ViewName
    Owner
    OwnerType
    SeqNo
    ViewCheck
    readOnly
    Valid
    Qualifier
    Func_Path
    Text
    Definer

    While a view can be made statistical using ALTER VIEW...ENABLE [or non-statistical using DISABLE] QUERY OPTIMIZATION, I cannot find a way to determine the current status of QUERY OPTIMIZATION for a specific view.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the PROPERTY column in SYSCAT.TABLES.

  5. #5
    Join Date
    Feb 2011
    Posts
    36
    The property column is an empty string for the table in question. I think this whole thing is a bug in DB2, and this was just a workaround.

    So...I removed my column & REORG'd the table in a test copy of the DB before I backed it up moved it from my live DB2 9.1 server to my test DB2 9.7 server. DB2 9.1 has no such problem when dropping a column as does DB2 9.7. This was supposed to be fixed somewhere prior to 9.7, but I guess not.

    This, at least, gets me past the upgrade without corrupting the catalog with respect to this table.

  6. #6
    Join Date
    Jan 2004
    Location
    UK
    Posts
    11

    db2look may be helpful. steps from db2 version 9.5

    db2look can provide a list of statistical views in database. Following works with DB2 LUW version 9.5 FP 6A.

    db2look -d SAMPLE -e -a |grep -i view |grep "ENABLE QUERY OPTIMIZATION"
    ALTER VIEW "#DBA "."V_LARGE_TABLES" ENABLE QUERY OPTIMIZATION;
    ALTER VIEW "#DBA "."V_TABLESPACES" ENABLE QUERY OPTIMIZATION;

    db2 "alter view #dba.V_TABLESPACES disable QUERY OPTIMIZATION"
    DB20000I The SQL command completed successfully.

    db2look -d SAMPLE -e -a |grep -p -i view |grep "ENABLE QUERY OPTIMIZATION"
    ALTER VIEW "#DBA "."V_LARGE_TABLES" ENABLE QUERY OPTIMIZATION;

    db2 "alter SAMPLE #dba.V_TABLESPACES enable QUERY OPTIMIZATION"
    DB20000I The SQL command completed successfully.

    db2look -d SAMPLE -e -a |grep -p -i view |grep "ENABLE QUERY OPTIMIZATION"
    ALTER VIEW "#DBA "."V_LARGE_TABLES" ENABLE QUERY OPTIMIZATION;
    ALTER VIEW "#DBA "."V_TABLESPACES" ENABLE QUERY OPTIMIZATION;

Posting Permissions

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