RSS

[ SQL2216N] REORG Failure – “Error Code ‘-968’ while reorganization of the database”

14 May

I observed this issue recently when I was monitoring the output of the shell script that performs REORG and RUNSTATS operation periodically on all the tables of our UAT database.

We perform continuous performance tests on our application. Therefore, periodic REORG and RUNSTATS becomes mandatory for better performance.

When I found error in the output file, I observed that for the couple of high cardinality tables the REORG operation failed with the following error message:

SQL2216N Error Code ‘-968’ while reorganization of the database

One of such table was having 8.5 millions rows with 170 columns including couple of CLOB columns. For such huge tables, records were getting inserted during every transaction and it was getting bigger and bigger after every performance test.

With such large tables in place, file system was almost FULL and FAILURES increased at an alarming rate while testing.

Solutions:

1) I tried our usual solution of lowering the High Water Mark and re-sizing the tablespace as follows:

alter tablespace Trnsact_TSI lower high water mark;
alter tablespace Trnsact_TSI resize (all 4500);
set integrity for TrnsactTable immediate checked;

But I was facing the same issue while resizing the table saying

SQL0968C The file system is full. SQLSTATE=57011

2) For few other tables in the same tablespace, it was possible to purge history data, so I did not waste a single moment to purge the data in those tables. I made my work faster using following ways

i) For tables where I needed to purge complete data instead of delete and getting into the Transaction Full Problem, I used

load from /dev/null of del replace into Trnsact_Hist nonrecoverable/ ;

ii) For other tables where I was allowed to delete only few older records, older than particular date I used EXPORT and LOAD with REPLACE method as follows. (Thanks to @saurabhska presentation @DB2NightShow on Database Purging)

EXPORT to tabname.ixf of ixf select * from tabname where <…> ;

LOAD from <export file>.ixf of ixf replace into <tabname>
DATA BUFFER 131072 SORT BUFFER 131072
CPU_PARALLELISM 16 DISK_PARALLELISM 16;

Note: Please consider the desired values for DATA BUFFER, SORT BUFFER, CPU_PARALLELISM and DISK_PARALLELISM. Appropriate values set for these parameters during the LOAD operation will fasten the query execution. Values mentioned are as per my own database configurations and settings.

2) For the tables where data purge was not allowed and REORG was mandatory I followed the following steps:

i) I had sufficient space in the other file system so I created a temporary tablespace in that file system. This temporary tablespace should have the page size equal to your base tablespace’s page size.

CREATE TEMPORARY TABLESPACE tmpspc_reorg IN DATABASE PARTITION
GROUP IBMTEMPGROUP PAGESIZE 8192 MANAGED BY SYSTEM
USING (‘/db2_backup/work/temp_ts’) EXTENTSIZE 48 PREFETCHSIZE AUTOMATIC
BUFFERPOOL TEMP8K01 OVERHEAD 12.500000 TRANSFERRATE 0.100000 FILE
SYSTEM CACHING DROPPED TABLE RECOVERY OFF;

ii) Perform the REORG operation using this newly created temporary tablespace

REORG TABLE TrnsactTable USE tmpspc_reorg;

iii) Lower the high water mark and re-size the tables space to free up some space in the file system.

REORG TABLE TrnsactTable USE tmpspc_reorg;

alter tablespace Trnsact_TSI lower high water mark;
alter tablespace Trnsact_TSI resize (all 4500);
set integrity for TrnsactTable immediate checked;

Root Cause Analysis:

While performing the REORG operation I was facing the error and file system was getting FULL. When I looked back about REORG, I understood that in BUILD phase of the REORG operation, if we do not mention the temporary tablespace the re-organization will take place in the same tablespace and a copy of the original object will be created which requires additional space. Therefore, when sufficient space was not acailable, it FAILED with the FILE SYSTEM FULL error. But when we used temporary tablespace it went good.

Monitoring Notes/Recommendation:

i) As a best practice, we use the temporary tablespace where the data is built to reorganize as we do not want to blow up the table’s own tablespace.

ii) If you have enough space in your table’s tablespace, DO NOT specify the option of temporary tablespace. This improves the performance of next phase which is REPLACE.

iii) To monitor the status of REORG operation we can use SYSIBMADM.SNAPTAB_REORG monitoring view:

SELECT Substr(tabname, 1, 15) AS TAB_NAME,
 Substr(tabschema, 1, 15) AS TAB_SCHEMA,
 reorg_phase,
 Substr(reorg_type, 1, 20) AS REORG_TYPE,
 reorg_status,
 reorg_completion,
 FROM sysibmadm.snaptab_reorg;

iv) SNAP_GET_TAB_REORG table function returns the same information as the SNAPTAB_REORG administrative view

select * from table(snap_get_tab_reorg(”));

Only difference is that it allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.

References:

i) Study more about ‘How REORG Works’ at

http://db2specialist.blogspot.in/2013/03/performance-tuning-of-classic-reorg.html

ii) Check out Question raised by me on ittoolbox DB2 Community at

http://tiny.cc/i8xqfx

iii) Find monitoring views and table functions at

http://tiny.cc/bfyqfx

Advertisements
 
Leave a comment

Posted by on May 14, 2014 in Databases, DB2

 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: