IBM Support

JR35335: 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,                 *
    * 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 number

    JR35335

  • Reported component name

    FEDERATION SERV

  • Reported component ID

    5724N9700

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-01-10

  • Closed date

    2010-05-16

  • Last modified date

    2010-05-16

  • 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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPY","label":"General Issues"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.5","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 May 2010