Deciding to reorg DB2 tables with overflow value

 As table data changes , logically sequential data may be on non-sequential physical pages.  The database needs to perform added reads . Table Fragmentation  leads to a slower response time.

 DB2 has an option to commit automatic reorganization. They can also be defragmented manually with the REORG command. An example:

 

REORG TABLE user1.”myTable” inplace;

 

The inplace keywords maintains an asynchronous reorganization, allowing write access to the tables during reorgnization.

 

When to commit a REORG?

 One method is to check the SYSSTAT.TABLES view and watch the overflow value. The overflow value represents row numbers that don’t fit on the original pages – caused by variable – length columns expanding no longer fitting onto the data page location

 

db2 "select overflow from sysstat.tables order by overflow ASC"

 Read More

How to monitor a DB2 REORG

Reorg table DB2

DB2 table reorg and table truncate

DB2 Used Space and REORG

Reduce the size of the DB2 LRG file

Question : A server is short on disk space . I created a list of the largest file, and the largest is C000000.LRG. What is a LRG file and how can I make it smaller?

 Answer:  The LRG file  is large tablespace container. To decrease the size of file :  delete the data in the table , commit an offline REORG  using the LONGLOBDATA option.

 An Inplace REORG will not work.

 

db2 REORG TABLE APPL_LOG ALLOW NO ACCESS LONGLOBDATA

Read More 

How to monitor a DB2 REORG

Reorg table DB2

DB2 table reorg and table truncate

DB2 Used Space and REORG

How to monitor a DB2 REORG

Question: How do I monitor a DB2 REORG , such as the following statement?

 reorg table SYSIBM.SYSCOLGROUPS inplace allow write access start

Answer:  Use the  db2pd   REORGS option. An example is :

         db2pd -d MYDB –reorgs

 

The resultset returns eleven columns. The “status” column –   is useful. It is possible to pause, stop, resume the REORG.   Be careful with STOP , as you cannot resume the REORG and will have to restart.

Some example commands are :

db2 reorg table     SYSIBM.SYSCOLGROUPS    inplace pause
db2 reorg table SYSIBM.SYSCOLGROUPS  inplace resume
db2 reorg table SYSIBM.SYSCOLGROUPS  inplace stop

 Read More

Reorg table DB2

DB2 table reorg and table truncate

DB2 Used Space and REORG

Troubleshooting SQL2219N and reason code 9

This DB2 error message SQL2219N  reason code 9 appeared in the db2 db2diag file. 

The DB2 documentation details for reason code 9 are “START is not allowed as a prior paused table reorganization has not been stopped”.

I discovered there was a scheduled maintenance of the databases – 1) REORG - managing reorg of tables  (and indexes).  2) RUNSTATS

It conflicted with the AUTO_REORG  on the databases. Turning off AUTO_REORG stopped the error occurring.

2012-03-18-22.49.37.393468+000 E2273446E603        LEVEL: Error
PID     : 25077                TID  : 189054052704 PROC : db2sysc
INSTANCE: db2tdw               NODE : 000          DB   :MYDB
APPHDL  : 0-17413              APPID: *LOCAL.db2tdw.120319003038
AUTHID  : DB2JV  
EDUID   : 82                   EDUNAME: db2agent (MYDB)
FUNCTION: DB2 UDB, data management, sqldOLRValidate, probe:100
MESSAGE : ADM5590E  The specified INPLACE table reorganization action on table 
          "DB2JV .DATA_DETAILS" is not allowed on this node because of 
          SQLCODE -2219 reason code "9".

 

SQL1279W Some indexes may not have been recreated

An unscheduled  table reorganisation was occurring  while attempting a db2sop .. db2start .

 Upon restart of the instance – and an attempt to connect to the database the following message appeared:

 SQL1279W  Some indexes may not have been recreated.

 The database restarted OK. And the currently invalid indexes where recreated when the table was accessed .

Investigate the cause of the issue  in db2diag.log and fix

 See also

Reorg table DB2

Author: Jack Vamvas(http://www.dba-db2.com)