PI97616: DB2 ADMIN TOOL - DDL COMMAND/CREATE ERROR FOR CREATE MQT "NOT LOGGED"

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Using DDL line command on a materialized query table returns:
  Unexpected token after NOT keyword - expected VOLATILE keyword
  
  ADB2PA: The following statement could not be parsed:
  
  CREATE TABLE SAS.ARRM AS ( SELECT * FROM BNCDH01.ARRM )
  DATA INITIALLY
  DEFERRED REFRESH DEFERRED NOT LOGGED
  
  Token number =     21, Token = LOGGED
  
  The resulting DDL does not get produced correctly
  
    SET CURRENT SQLID='SAS';
    SET CURRENT SCHEMA='JNH';
  CREATE TABLE SAS.ARRM AS ( SELECT * FROM BNCDH01.ARRM )
  DATA INITIALLY
  DEFERRED REFRESH DEFERRED NOT LOGGED
    MAINTAINED BY SYSTEM
    ENABLE QUERY OPTIMIZATION
      PARTITION BY SIZE EVERY 8 G
      AUDIT NONE
      DATA CAPTURE NONE
      CCSID      EBCDIC
      NOT VOLATILE
      APPEND NO  ;
    COMMIT;
  
  Try and execute and get following error
  
   DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD
            MAINTAINED. TOKEN IN EDITPROC VALIDPROC AUDIT
            DATA WITH VOLATILE NOT WAS EXPECTED
  
  because the "NOT LOGGED" is incorrectly placed in the
  refreshable-table options.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Administration Tool for z/OS    *
  *                 who use DDL or GEN line command on an MQT.   *
  ****************************************************************
  * PROBLEM DESCRIPTION: DDL and GEN line commands do not        *
  *                      support the Table attribute "LOGGED"/   *
  *                      "NOT LOGGED".                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The DDL and GEN line commands do not support the Table
  attribute "LOGGED"/"NOT LOGGED" for MQTs. The resulting
  DDL is incorrect and does not contain the "LOGGED"/"NOT
  LOGGED" attribute as part of the CREATE TABLE statement.
  

Problem conclusion

• 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:

Modules/Macros

•    ADBCDTS  ADB2CMT  ADB2PA   ADB2REG
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RB20 PSY UI58621

     UP18/09/26 P F809

• RC10 PSY UI58622

     UP18/09/26 P F809

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.