When running "ALTER TABLESPACE LOWER HIGH WATER MARK" and
extents are moved, ADM6030E RC=3 is logged in db2diag.log and
the administration notification long even though all extents in
the tablespace have been completely moved.
Here is a simple scenario:
- load 20k rows into table1 in ts1 // HWM = 20k rows
- load 10k rows into table2 in ts1 // HWM = 30k rows
- drop table1 // HWM = 30k rows
- ALTER TABLESPACE LOWER HIGH WATER MARK // HWM = 10k rows
- ADM6030E RC=3 logged in db2diag.log
$ db2 connect to sample
$ db2 create tablespace ts1 managed by database using (file
'$PWD/ts1.dat' 10000)
$ db2 create table t81942(i int, c1 char(250),c2 char(250),c3
char(250),c4 char(250),i2 int) in ts1
$ db2 load from t81942_10k.del of del insert into t81942
nonrecoverable
$ db2 load from t81942_10k.del of del insert into t81942
nonrecoverable
$ db2 list tablespaces show detail | grep -p TS1 | egrep
Name|State|Use|Free|High
Name = TS1
State = 0x0000
Useable pages = 9996
Used pages = 2876
Free pages = 7120
High water mark (pages) = 2876
$ db2 create table t81942_2 like t81942 in ts1
$ db2 load from t81942_10k.del of del insert into t81942_2
nonrecoverable
$ db2 list tablespaces show detail | grep -p TS1 | egrep
Name|State|Use|Free|High
Name = TS1
State = 0x0000
Useable pages = 9996
Used pages = 4312
Free pages = 5684
High water mark (pages) = 4312
$ db2 drop table t81942
$ db2 list tablespaces show detail | grep -p TS1 | egrep
'Name|State|Use|Free|High'
Name = TS1
State = 0x0000
Useable pages = 9996
Used pages = 1448
Free pages = 8548
High water mark (pages) = 4312
$ db2trc on -f trc2.dmp -t ; db2 alter tablespace ts1 lower high
water mark ; sleep 30; db2trc off
$ db2 list tablespaces show detail | grep -p TS1 | egrep
'Name|State|Use|Free|High'
$ tail -30 ~/sqllib/db2dump/db2diag.log
2015-05-15-01.38.11.117713+540 E111149A515 LEVEL: Info
PID : 16842920 TID : 12972 PROC :
db2sysc 0
INSTANCE: e105q5a NODE : 000 DB :
SAMPLE
APPHDL : 0-16012 APPID: *LOCAL.DB2.150514163811
AUTHID : E105Q5A HOSTNAME: envy
EDUID : 12972 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services,
sqlbExtentMovementEntryPoint, probe:5586
DATA #1 : <preformatted>
Extent Movement started on table space 8
2015-05-15-01.38.28.177909+540 E111665A598 LEVEL: Info
PID : 16842920 TID : 12972 PROC :
db2sysc 0
INSTANCE: e105q5a NODE : 000 DB :
SAMPLE
APPHDL : 0-16012 APPID: *LOCAL.DB2.150514163811
AUTHID : E105Q5A HOSTNAME: envy
EDUID : 12972 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbLockAndMoveExtents,
probe:4621
DATA #1 : String, 126 bytes
The extent movement operation has moved all extents it could.
There are no more free extents lower in the tablespace. Exiting.
2015-05-15-01.38.28.185881+540 E112264A591 LEVEL: Info
PID : 16842920 TID : 12972 PROC :
db2sysc 0
INSTANCE: e105q5a NODE : 000 DB :
SAMPLE
APPHDL : 0-16012 APPID: *LOCAL.DB2.150514163811
AUTHID : E105Q5A HOSTNAME: envy
EDUID : 12972 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbLockAndMoveExtents,
probe:4972
MESSAGE : ADM6030E Extents in the following table space have
not been
completely moved: "TS1". Table space ID: "8". Reason
code: "3". |
