IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    LI74138

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-01-19

  • Closed date

    2009-12-31

  • Last modified date

    2009-12-31

  • 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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
31 December 2009