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

DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  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, db2look will    *
  * generate multiple creation statements for this MQT.          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 9.1.0.9 or higher                             *
  ****************************************************************
  

Problem conclusion

• Problem first fixed in DB2 Version 9.1 Fix Pack 9. 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:

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

  JR35335 JR35336

Fix information

• Fixed component name

  FEDERATION SERV

• Fixed component ID

  5724N9700

Applicable component levels

• R970 PSN

     UP

• R970 PSY

     UP