IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    JR35317

  • Reported component name

    FEDERATION SERV

  • Reported component ID

    5724N9700

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-01-08

  • Closed date

    2010-04-12

  • Last modified date

    2012-07-11

  • 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

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS2K5T","label":"InfoSphere Federation Server"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
12 October 2021