PH13310: ALTER TABLESPACE MAXROWS WITH DEFINE NO PLACES OBJECT IN ADVISORY REORG PENDING AREO UNECESSARY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Scenario:
  tablespace with MAXROWS 100 DEFINE NO and created a table in
  this tablespace.
  
  After that, table is still empty - so no physical define of ts -
  and runnnit an ALTER TABLESPACE dbname.tsname MAXROWS 255
  and receives DSNT404I SQLCODE = 610, WARNING:  A CREATE/ALTER ON
  OBJECT DB2.TS2 HAS PLACED OBJECT IN ADVISORY REORG PENDING
  
  and requires a REORG on empty tablespace, which also
  materialises dataset.
  
  Keywords:
  SQLCODE610 SQLALTER
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 and 12 for z/OS users of ALTER    *
  *                 TABLESPACE MAXROWS statement.                *
  ****************************************************************
  * PROBLEM DESCRIPTION: Db2 incorrectly issues SQLCODE610       *
  *                      and places the table space in           *
  *                      advisory reorg pending when the user    *
  *                      attempts to alter the MAXROWS of a      *
  *                      DEFINE NO table space.                  *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  When Db2 processes an ALTER TABLESPACE MAXROWS statement, the
  table space DEFINE YES or DEFINE NO attribute is not being
  checked. Hence, the table space is always being placed in
  advisory reorg pending state with SQLCODE610 even when
  the table sapce has a DEFINE NO attribute.
  
  Example of the incorrect dbet states:
  
  -DISPLAY DB(DB2) SPACENAM(*) LIMIT(*)
  DSNT360I  -DB2A ***********************************
  DSNT361I  -DB2A *  DISPLAY DATABASE SUMMARY
                  *    GLOBAL
  DSNT360I  -DB2A ***********************************
  DSNT362I  -DB2A     DATABASE = DB2  STATUS = RW
                     DBD LENGTH = 4028
  DSNT397I  -DB2A
  NAME     TYPE PART  STATUS
  
  -------- ---- ----- ----------------- -------- --------
  TS2      TS    0001 RW,AREO*
  TS2      TS
  
  Example of the incorrect SQLCODE610 issued:
  
  RESULT OF SQL STATEMENT:
  DSNT404I SQLCODE = 610, WARNING:  A CREATE/ALTER ON OBJECT
                     DB405621.TS405621 HAS PLACED OBJECT IN
                     ADVISORY REORG PENDING
  DSNT418I SQLSTATE   = 01566 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXIATS SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = 156  0  0  -1  0  0
                        SQL DIAGNOSTIC INFORMATION
  DSNT416I SQLERRD    = X'0000009C'  X'00000000'  X'00000000'
                        X'FFFFFFFF'  X'00000000'  X'00000000'
                        SQL DIAGNOSTIC INFORMATION
  

Problem conclusion

• Db2 code has been fixed. On an ALTER TABLESPACE MAXROWS
  statement, Db2 will not issue SQLCODE610 and place the
  table space in advisory reorg pending state if it is
  a DEFINE NO table space.
  However, applying APAR PH13310 does not change the existing
  table spaces' dbet state which might be incorrectly in
  advisory reorg pending state.
  AREO* should not impact any further DDL/DML but is primarily
  for performance, which is not an issue for DEFINE NO objects.
  User can run REPAIR utility with NOAREORPENDSTAR option to
  clear the table space advisory reorg pending state.
  
  ADDITIONAL KEYWORDS:
  
  SQLALTER
  SQLCODE610
  

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:

  UI65508 UI65510

Modules/Macros

•    DSNGDADP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI65510

     UP19/10/11 P F910

• RC10 PSY UI65508

     UP19/10/11 P F910

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.