PI38552: ERASERULE IS NOT UPDATED PROPERLY FOR PARTITION-BY-GROWTH TABLESPACES AFTER EXECUTING ALTER TABLESPACE ERASE STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• After executing an ALTER TABLESPACE ERASE statement, column
  ERASERULE in SYSIBM.SYSTABLESPACE catalog table is not updated
  properly for PARTITION-BY-GROWTH tablespaces.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS and DB2 11 for z/OS      *
  *                 users of ALTER TABLESPACE ERASE statement.   *
  ****************************************************************
  * PROBLEM DESCRIPTION: Executing an ALTER TABLESPACE ERASE     *
  *                      statement gets return code 0, but the   *
  *                      SYSIBM.SYSTABLESPACE catalog table      *
  *                      ERASERULE column is not updated         *
  *                      accordingly if the table space is       *
  *                      defined as PARTITION-BY-GROWTH (PBG)    *
  *                      table space and has more than one       *
  *                      partition allocated.                    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Executing an ALTER TABLESPACE statement with ERASE option gets
  return code 0, but the SYSIBM.SYSTABLESPACE catalog table
  ERASERULE column is not updated accordingly if the table space
  is defined as a PBG table space and has more than one partition
  allocated. Also, the OBD did not get updated the ERASE option
  as well.
  
  Following is an example of such failing scenario:
  1. Create a database and a PBG table space:
     SET CURRENT RULES = 'STD';
     CREATE DATABASE PMB25278   STOGROUP PM25278  ;
  
      CREATE  TABLESPACE T000927X IN PMB25278
        MAXPARTITIONS 10
        SEGSIZE 64
        USING STOGROUP PM25278
              PRIQTY -1
              SECQTY -1
        PCTFREE    10
        TRACKMOD NO
        MAXROWS    1
        CCSID      EBCDIC
        DSSIZE     1 G ;
  
  2. Create a table in that PBG table space:
     CREATE TABLE PMB25278.PMRLTEST_DATA
      ( UID          DECIMAL(15)
      , CONTENT_ROW  ROWID GENERATED ALWAYS NOT NULL
      , CONTENT      CLOB(10485760) FOR SBCS DATA
                     INLINE LENGTH 500
       )
       IN PMB25278.T000927X ;
  
  3. Load/Insert data into that table which grows more than one
     partition.
     INSERT INTO PMB25278.PMRLTEST_DATA (UID, CONTENT)
            WITH  TAL (T1, T2) AS
            (SELECT 1 AS T1,
                    CHAR(CURRENT TIMESTAMP) AS T2
               FROM SYSIBM.SYSDUMMY1
             UNION ALL
             SELECT  T1 + 1  AS T1,
                     CHAR(CURRENT TIMESTAMP) AS T2
               FROM TAL WHERE TAL.T1 < 100000)
             SELECT T1, T2  FROM TAL;
  
  4. Stop the PBG table space:
     -STOP DATABASE(PMB25278) SPACENAM(T000927X)
  
  5. Alter the table space ERASE option from NO to YES:
     ALTER TABLESPACE PMB25278.T000927X
       ERASE YES;
     DB2 returns return code 0, but the OBD and
     SYSIBM.SYSTABLESPACE catalog table ERASERULE column are not
     updated.
  
  
  Applying the fix will not fix previously executed ALTER
  TABLESPACE ERASE statements. User needs to re-execute those
  ALTER TABLESPACE ERASE statements in order to update the OBD
  setting and ERASERULE column.
  

Problem conclusion

• DB2 has been modified to correctly update the ERASERULE column
  of SYSIBM.SYSTABLESPACE catalog table and OBD setting when
  altering a PBG table space.
  
  Additional Keyword: SQLALTER PBG SQLPBG
  

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:

  UI30296 UI30297

Modules/Macros

• DSNXIATS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI30296

     UP15/09/03 P F509

• RB10 PSY UI30297

     UP15/09/03 P F509

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.