PH00194: ALLOW ROTATE PARTITION ON MATERIALIZED QUERY TABLES OR TABLES WITH A MATERIALIZED QUERY TABLE DEFINED ON IT

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Allow ROTATE PARTITION on a materialized query table, or tables
  with a materialized query table defined on it.          (79242)
  Additional Keywords:
  SQLALTER SQLMQT ZSA2
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Db2 11 and 12 for z/OS users of              *
  *                 materialized query table (MQT) or table      *
  *                 that has dependent MQTs.                     *
  *                                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ALTER TABLE statement with ROTATE    *
  *                      PARTITION clause is not allowed on a    *
  *                      materialized query table (MQT) or       *
  *                      a table that has dependent MQTs if the  *
  *                      table resides in a range-partitioned    *
  *                      table space.                            *
  *                                                              *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  For a materialized query table (MQT) or table having dependent
  MQTs that resides in a range-partitioned table space, the ALTER
  TABLE statement with ROTATE PARTITION clause is restricted.
  An SQLCODE -20183 is issued when attempting to rotate
  partitions of the table.
  

Problem conclusion

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

• Db2 has been enhanced such that the ALTER TABLE statement with
  ROTATE PARTITION clause is no longer restricted for a
  materialized query table (MQT) or table having dependent MQTs
  that resides in a ranged-partition table space. As a part of
  ROTATE PARTITION processing, the packages that are referencing
  the table being altered are invalidated.
  
  This APAR also fixes an existing issue where packages
  referencing an MQT are not invalidated when the MQT is altered
  with the ALTER PARTITION clause to change the partition's limit
  key. This issue may possibly result in incorrect outputs or
  unpredictable symptoms. Packages dependent on MQTs altered
  before the application of this fix should be rebound.
  
  Additional keywords
  SQLALTER
  SQLCODE20183
  SQLMQT
  SQLINCORR
  SQLINCORROUT
  DB2INCORR/K
  
  ++HOLD ACTION for PH00194
  
  This APAR also fixes an existing issue of ALTER TABLE ALTER
  PARTITION of limit key on a materialized query table where
  packages dependent on the MQT are not invalidated. This
  issue may possibly result in incorrect outputs or
  unpredictable symptoms. The packages referencing the MQT
  altered with the ALTER PARTITION clause before the application
  of this APAR should be rebound.
  
  
  ++HOLD DOC for PH00194
  
  PH00194 introduces the following changes to externals:
  
  Change to SQL Reference for ALTER TABLE
  
  table-name
  . . .
  If table-name identifies a materialized query table,
  alterations are limited to the following clauses:
  
          AUDIT
          DATA CAPTURE
          ALTER MATERIALIZED QUERY
          DROP MATERIALIZED QUERY
          ADD RESTRICT ON DROP
          ALTER PARTITION
          ROTATE PARTITION
  
  ROTATE PARTITION:
  
  ROTATE PARTITION must not be specified in the following
  situations:
    o The table is in a partition-by-growth table space.
    o The table has XML columns.
    o The table is a system-period temporal table or a
      history table.
    o The table is an archive-enabled table or an archive table.
  
  
  Change to Db2 z/OS SQLCODE -650
  
  -650
  THE ALTER STATEMENT CANNOT BE EXECUTED, REASON reason-code
  Explanation
  
  The ALTER statement cannot be executed for the indicated
  reason.
  . . .
  . . .
  
  27
      For an ALTER TABLE statement that names a materialized
  query table, the alterations are limited to the following
  situations:
  
        - Changing the materialized query table attributes
        - Turning the materialized query table to a base table
        - Specifying the following clauses:
           o ADD RESTRICT ON DROP
           o ALTER MATERIALIZED QUERY
           o AUDIT
           o DATA CAPTURE
           o DROP MATERIALIZED QUERY
           o DROP RESTRICT ON DROP
           o ROTATE PARTITION
           o ALTER PARTITION
  
      ADD RESTRICT ON DROP and DROP RESTRICT ON DROP clauses
      cannot be used in the same statement.
  
  Change to Db2 z/OS SQLCODE -20183
  
  -20183
  THE PARTITIONED, ADD PARTITION, ADD PARTITIONING KEY, ALTER
  PARTITION, ROTATE PARTITION, OR PARTITION BY RANGE CLAUSE
  SPECIFIED ON CREATE OR ALTER FOR table-name IS NOT VALID
  
  Explanation
  A clause was specified for a statement, but the clause is not
  valid for the indicated table.
  
  table-name
      The name of the table.
  
  The PARTITIONED, ADD PARTITION, ADD PARTITIONING KEY, ALTER
  PARTITION, and ROTATE PARTITION clauses cannot be specified
  on ALTER TABLE, CREATE INDEX, or CREATE TABLE in the following
  cases:
    o The table is a non-partitioned table.
    o The table is a materialized query table or a materialized
      query table is defined on this table. This restriction
      does not apply if the clause was an ALTER PARTITION clause
      to alter the limit key or ROTATE PARTITION clause to
      rotate partition.
  
  . . .
  . . .
  
  Note that the text changes above are intended as guidance,
  and that the actual text may change in the final published
  version.
  
  Information about this changed option will be included in the
  IBM Knowledge Center
  (https://www.ibm.com/support/knowledgecenter) at a later date.
  

APAR Information

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

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

  UI58689 UI58690

Modules/Macros

•    DSNXIAB7 DSNXIATB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI58690

     UP18/10/10 P F810 Ž

• RC10 PSY UI58689

     UP18/10/10 P F810 Ž

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.