|
Problem IC62183
|
Status: Closed |
DB2CKMIG DOES NOT WARN USERS VIEWS DEFINED ON CATALOG TABLES THAT GETS UPDATED IN THE FUTURE RELEASE(S). |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Consider the below example.
On DB2 V8.2
$ db2 "CREATE view ALT.SEQVALUES AS SELECT seq.seqschema,
seq.seqname, seq.lastassignedval FROM sysibm.syssequences seq"
DB20000I The SQL command completed successfully.
$ db2 "CREATE view ALT.SEQ AS SELECT seqschema, seqname,
lastassignedval FROM sysibm.syssequences"
DB20000I The SQL command completed successfully.
$ db2 "CREATE view ALT.SEQVAL AS SELECT seqname,
lastassignedval FROM sysibm.syssequences"
DB20000I The SQL command completed successfully.
# /opt/IBM/db2/V9.5fp2/instance/db2imigr -u db2inst1 db2inst1
| tee db2imigr.log
db2ckmig was successful. Database(s) can be migrated.
# su - db2inst1
On DB2 V9.5
$ db2start
12/31/2008 15:10:05 0 0 SQL1063N DB2START processing
was successful.
SQL1063N DB2START processing was successful.
$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code
release "SQL09052" with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s080811", "U817470",
and Fix Pack"2".
Product is installed at "/opt/IBM/db2/V9.5fp2".
$ db2 migrate db mytest | tee mytest.mig
DB20000I The MIGRATE DATABASE command completed successfully.
$ db2 connect to MYTEST
Database Connection Information
Database server = DB2/AIX64 9.5.2
SQL authorization ID = DB2INST1
Local database alias = MYTEST
$ db2 "select Viewname from syscat.views where valid != 'Y'
for read only"
VIEWNAME
------------------------------
SEQVALUES
SEQVAL
SEQ
3 record(s) selected.
Here we can see how migrating user views referencing catalog
table may inoperate the view.
To prevent inconveniences for customers, db2ckmig should return
warnings on views that may get
inoperate via migration.
In the documentation, We have advised customers to define views
or applications based on the catalog views rather than the base
catalog tables.
Applications should be written to the SYSCAT and SYSSTAT views
rather than the base catalog tables.
(http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.
ibm.db2.luw.sql.ref.doc/doc/r0008443.html)
The reason behind this decision was because customers shouldn't
be referencing the catalog tables
directly and migrating objects defined upon catalog tables are
not supported.
Catalog views are created for external usage to protect
customers from
hitting possible incompatibility across releases due to these
catalog table changes. |
| Problem Summary: |
Consider the below example.
On DB2 V8.2
$ db2 "CREATE view ALT.SEQVALUES AS SELECT seq.seqschema,
seq.seqname, seq.lastassignedval FROM sysibm.syssequences seq"
DB20000I The SQL command completed successfully.
$ db2 "CREATE view ALT.SEQ AS SELECT seqschema, seqname,
lastassignedval FROM sysibm.syssequences"
DB20000I The SQL command completed successfully.
$ db2 "CREATE view ALT.SEQVAL AS SELECT seqname,
lastassignedval FROM sysibm.syssequences"
DB20000I The SQL command completed successfully.
# /opt/IBM/db2/V9.5fp2/instance/db2imigr -u db2inst1 db2inst1
| tee db2imigr.log
db2ckmig was successful. Database(s) can be migrated.
# su - db2inst1
On DB2 V9.5
$ db2start
12/31/2008 15:10:05 0 0 SQL1063N DB2START processing
was successful.
SQL1063N DB2START processing was successful.
$ db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code
release "SQL09052" with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s080811", "U817470",
and Fix Pack"2".
Product is installed at "/opt/IBM/db2/V9.5fp2".
$ db2 migrate db mytest | tee mytest.mig
DB20000I The MIGRATE DATABASE command completed successfully.
$ db2 connect to MYTEST
Database Connection Information
Database server = DB2/AIX64 9.5.2
SQL authorization ID = DB2INST1
Local database alias = MYTEST
$ db2 "select Viewname from syscat.views where valid != 'Y'
for read only"
VIEWNAME
------------------------------
SEQVALUES
SEQVAL
SEQ
3 record(s) selected.
Here we can see how migrating user views referencing catalog
table may inoperate the view.
To prevent inconveniences for customers, db2ckmig should return
warnings on views that may get
inoperate via migration.
In the documentation, We have advised customers to define views
or applications based on the catalog views rather than the base
catalog tables.
Applications should be written to the SYSCAT and SYSSTAT views
rather than the base catalog tables.
(http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.
ibm.db2.luw.sql.ref.doc/doc/r0008443.html)
The reason behind this decision was because customers shouldn't
be referencing the catalog tables
directly and migrating objects defined upon catalog tables are
not supported.
Catalog views are created for external usage to protect
customers from
hitting possible incompatibility across releases due to these
catalog table changes. |
| Local Fix: |
After migration, to identify views that may have gotten
inoperated, do the following query:
SELECT viewname FROM syscat.views WHERE valid != 'Y'
To correct the state of these views, drop and recreate them
manually. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 9.7 Fix Pack 1. |
| Workaround |
After migration, to identify views that may have gotten
inoperated, do the following query:
SELECT viewname FROM syscat.views WHERE valid != 'Y'
To correct the state of these views, drop and recreate them
manually. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.07.2009
31.12.2009
31.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
