IBM Support

Why auto reorg is failing with SQL0284

Technical Blog Post


Abstract

Why auto reorg is failing with SQL0284

Body

Auto  reorg  is enabled.

But,  it's  failing with  SQL0284 messages in the db2diag.log  similar to following,

 

2016-03-02-08.39.56.808159-360 I4469422E526          LEVEL: Error
PID     : 11111                TID : 46912895825555  PROC : db2acd 0
INSTANCE: db2inst1              NODE : 000            DB   :  testdb1
APPID   : <appid>
HOSTNAME:  db2host1
FUNCTION: DB2 UDB, Health Monitor, db2HmonEvalReorg, probe:997
MESSAGE : Failed to evaluate table "MYSCHEMA "."MYTAB1".
Probe 535 .
          SQLCODE = -284
DATA #1 : Hexdump, 4 bytes
0x00002AAAC27D8CCC : E4FE FFFF         

                         ....

SQL0284N  Table creation failed because the table space
      "<tablespace-name>" that was specified in the statement after the
      clause "<clause>" is not a supported type of table space for that
      clause. Table space type: "<tablespace-type>".

 

The reason of the failure being the  SYSTOOLSTMPSPACE  is defined  incorrectly.

If this tablespace is defined  with type/Content  anything other than user  temporary tablespace  the auto reorg will face this issue.

 

Need to recreate the same tablespace  following the guideline in this page  to  fix the problem,

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.gui.doc/doc/c0023713.html?lang=en

 

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140802