PH27875: DB2 ADMIN TOOL: GEN FOR MQTS WITH MULTIPLE JOINS, GENERATES DUPLICATE PARAMETERS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Db2 Admin Tool: DDL and GEN MQT errors:
  
  MQTs with multiple joins can be mishandled by the formatter,
  resulting in a bad ddl returned by GEN. For example, User
  executes the following:
  
  
  CREATE TABLE  COPOR.AEOI_STAT_IES_020_INT AS (
    SELECT PAY_SUB.PERSON_ID, SS.CODE MSG_ORIGINATING_COUNTRY,
  IS_NP,
  ...
  ...
      FROM (SELECT distinct PAY.PERSON_ID,
  MSG.ORIGINATING_COUNTRY_ID,
            (case when NP.NATURAL_PERSON_ID is null then 0 else 1
  end
            ) IS_NP
              FROM AEOI_EXP_MESSAGE MSG
                   inner join
                   AEOI_EXP_IE_RECIPIENT REC
                   on MSG.MESSAGE_ID = REC.MESSAGE_ID
                   inner join
                   AEOI_EXP_IE_RELATIONSHIP REL
                   on REC.IE_RECIPIENT_ID = REL.IE_RECIPIENT_ID
                   inner join
                   AEOI_EXP_IE_PAYER PAY
                   on REL.IE_PAYER_ID = PAY.IE_PAYER_ID
                   left join
                   AEOI_EXP_NATURAL_PERSON NP
                   on PAY.PERSON_ID = NP.PERSON_ID
              WHERE REC.INFORMATION_STATUS IN('NEW', 'CORRECTED')
      ) PAY_SUB
  inner join AEOI_EXP_COUNTRY SS on PAY_SUB.ORIGINATING_COUNTRY_ID
  = SS.COUNTRY_ID left join AEOI_EXP_PERSON_IDENTIFIER PI on
  PI.PERSON_ID = PAY_SUB.PERSON_ID group by PAY_SUB.PERSON_ID,
  SS.CODE, IS_NP )
  DATA INITIALLY DEFERRED REFRESH DEFERRED
  MAINTAINED BY USER
  DISABLE QUERY OPTIMIZATION
  IN COPORDB.COPORM03
  AUDIT NONE
  DATA CAPTURE NONE
  CCSID      UNICODE
  NOT VOLATILE
  APPEND NO  ;
  
  
  However formatter will cause GEN to return the following:
  
  CREATE TABLE COPOR.AEOI_STAT_IES_020_INT AS (
    SELECT PAY_SUB.PERSON_ID, SS.CODE MSG_ORIGINATING_COUNTRY,
  IS_NP,
  ...
  ...
      FROM (SELECT distinct PAY.PERSON_ID,
  MSG.ORIGINATING_COUNTRY_ID,
            (case when NP.NATURAL_PERSON_ID is null then 0 else 1
  end
            ) IS_NP
              FROM AEOI_EXP_MESSAGE MSG
                   inner join
                   AEOI_EXP_IE_RECIPIENT REC
                   on MSG.MESSAGE_ID = REC.MESSAGE_ID
                   inner join
                   AEOI_EXP_IE_RELATIONSHIP REL
                   on REC.IE_RECIPIENT_ID = REL.IE_RECIPIENT_ID
                   inner join
                   AEOI_EXP_IE_PAYER PAY
                   on REL.IE_PAYER_ID = PAY.IE_PAYER_ID
                   left join
                   AEOI_EXP_NATURAL_PERSON NP
                   on PAY.PERSON_ID = NP.PERSON_ID
              WHERE REC.INFORMATION_STATUS IN('NEW', 'CORRECTED')
      ) PAY_SUB
           inner join AEOI_EXP_COUNTRY SS
       on PAY_SUB.
     ORIGINATING_COUNTRY_ID = SS.
     COUNTRY_ID left join AEOI_EXP_PERSON_IDENTIFIER PI
       on PI.
     PERSON_ID = PAY_SUB.
     PERSON_ID group by PAY_SUB.
     PERSON_ID, SS.
     CODE,
     IS_NP)
    DATA INITIALLY DEFERRED REFRESH DEFERRED BY USER
       DISABLE QUERY OPTIMIZATION
    IN COPORDB.COPORM03
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID UNICODE
    NOT VOLATILE
    APPEND NO
  MAINTAINED BY USER
  DISABLE QUERY OPTIMIZATION
    IN COPORDB.COPORM03
    PARTITION BY SIZE
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      UNICODE
    NOT VOLATILE
    APPEND NO  ;
  COMMIT;
  
  The final 'inner join' is not mis-formatted, and subsequently
  causes the the code to skip the section that normally handles
  MQTs. This then results in duplicate keywords.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the Db2 Administration Tool for     *
  *                 z/OS Reverse Engineering (GEN) function.     *
  ****************************************************************
  * PROBLEM DESCRIPTION: When generating DDL for a materialized  *
  *                      query table (MQT), certain              *
  *                      fields might be generated more than     *
  *                      once.                                   *
  ****************************************************************
  When processing MQT DDL with multiple JOIN
  clauses, final formatting of the generated
  DDL might include repeated clauses.
  These repeated clauses can result in SQL
  errors when that DDL is
  then reused.
  
  For example:
  
   DATA INITIALLY DEFERRED REFRESH DEFERRED
   BY USER
       DISABLE QUERY OPTIMIZATION
    IN DB.TS
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID UNICODE
    NOT VOLATILE
    APPEND NO
  MAINTAINED BY USER
  DISABLE QUERY OPTIMIZATION
    IN DB,TS
    PARTITION BY SIZE
    AUDIT NONE
    DATA CAPTURE NONE
    CCSID      UNICODE
    NOT VOLATILE
    APPEND NO  ;
  

Problem conclusion

• The problem has been resolved.
  

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:

  UI72225

Modules/Macros

• ADB2FMT  ADB2REG
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI72225

     UP20/10/29 P F010

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.