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: Drop column failure

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

Try running db2look with the -d database-name -t table-name -e -a options.
(No guarantees).

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.

Check the PROPERTY column in SYSCAT.TABLES.

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.

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;