LI74138: DB2CKMIG DOES NOT WARN USERS VIEWS DEFINED ON CATALOG TABLES THAT GETS UPDATED IN THE FUTURE RELEASE(S).

DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error 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.
  

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.
  

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.
  

Problem conclusion

• First fixed in DB2 Version 9.5 Fix Pack 5.
  

Temporary 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.
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IC62183

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R950 PSY

     UP