IBM Support

In db2luw tablespace reduce max failing with error in extent movement

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=SQLB_EM_SCAN_ERROR

          "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.

 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm11139902