Technical Blog Post
Abstract
In db2luw tablespace reduce max failing with error in extent movement
Body
Trying to reclaim some space in a Db2luw tablespace. It's failing with extent movement error.
db2 alter tablespace MYTBSP REDUCE MAX
Returning :
"Error occurred during extent movement."
In the db2diag.log following kind of messages noticed :
2018-06-05-20.48.21.404015+000 I8772425A2116 LEVEL: Severe
PID : 9633899 TID : 18410 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDB
APPHDL : 0-59888 APPID: <>
AUTHID : DB2INST1 HOSTNAME: myhost
EDUID : 18433 EDUNAME: db2agent (MYDB) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbFindEMPMappingMovedExtent, probe:2267
MESSAGE : ZRC=0x8002045B=-2147351461=SQL
"Error occurred during extent movement."
....
Pagekey: {pool:5;obj:65535;type:64} PPNum:0
Obj: {pool:5;obj:65535;type:64} Parent={5;65535}
......
{pool:7;obj:65535;type:64} means it's EMP page of Object table for tablespace 5
obj 65535 -> Object table object id.
Type 64 -> 0x40 -> EMP (Extent Map Page)
Need to first check db2dart for the entire tablespace 5
db2dart MYDB /TS /TSI 5 /rptn dartTS5.out
In the output file it shows,
------------------------------
..........
Checking for orphaned extents:
Error: Extent number 1013140 in tablespace 5 is orphaned
Error: Extent number 1028790 in tablespace 5 is orphaned
Error: Extent number 1073853 in tablespace 5 is orphaned
Tablespace-info inspection phase end.
Error: This phase encountered an error and did not complete.
Tablespace inspection phase end.
------------------------------
And, db2dart on the tablespace will fail after complaining about the orphan extents.
These are orphaned extents in the tablespace which are causing the issue here.
These must be introduced as a side effect of other issues earlier.
There is a patching way to cleanup the orphan extents in a tablespace.
But, any patching action has a possible risk. Just need to keep that in mind.
It's a specific db2dart option which can cleanup the orphan extents.
For the exact db2dart syntax and the needed service password to run that db2dart syntax the IBM Support to be contacted.
Once the orphan extents are cleaned-up the alter tablespace reduce max should work successfully provided no other issues faced.
UID
ibm11139902