JR35335: DB2LOOK GENERATES DUPLICATE DDL FOR MQT CREATION IF THE MQT DEPENDS ON MULTIPLE NICKNAMES

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

• If mqt table is created on multiple nicknames, the db2look
  generates duplicate ddl script for this mqt table creation.
  
  For example, a mqt table named "mqt1" created on two nicknames
  "nick1", "nick2" and 1 base table "tab" as below:
  
  create summary table mqt1 (col1, col2, col3) as (select col1,
  col2, col3 from nick1, nick2, tab where nick1.c1 = nick2.c1 and
  nick1.c1 = tab.c1) data initially deferred refresh deferred;
  
  db2look will generate the following statements for 2 times:
  
  ------------------------------------------------
  -- DDL Statements for table "IIDEV22 "."MQT1"
  ------------------------------------------------
   SET CURRENT SCHEMA = "MYSCHEMA";
  SET CURRENT PATH =
  "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","IIDEV22";
  create summary table mqt1 (col1, col2, col3) as (select col1,
  col2, col3
  from nick1, nick2, tab where nick1.c1 = nick2.c1 and nick1.c1 =
  tab.c1)
  data initially deferred refresh deferred;
  

Local fix

• Ignore the duplicate MQT table creation statements.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * The db2look users                                            *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * If A MQT is created over multiple nicknames,                 *
  * db2lookwillgenerate multiple creation statements for this    *
  * MQT.                                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 v9.5.0.6                                      *
  ****************************************************************
  

Problem conclusion

• The fix has been delivered into v95fp6. And it can ensure that
  db2look generates a unique creation statement for one MQT.
  

Temporary fix

Comments

APAR Information

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

  JR35317

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

Fix information

• Fixed component name

  FEDERATION SERV

• Fixed component ID

  5724N9700

Applicable component levels

• R950 PSN

     UP

• R950 PSY

     UP