hi all
I want to reduce the DMS tablespace .so after reorg i tried this command
**ALTER TABLESPACE TABLESPACE NAME REDUCE MAX **
but it is not working.It is showing error.
It will work for db29.5 versions.
ALTER TABLESPACE LOWER HIGH WATER MARK --±---------±-
If any other command i could use .please let me know.
Thanks
Deepshikha
Hello Deepshikha,
Shrinking of tablespaces is different in v9.5 from later versions of Db2. In this case, it is necessary to use DB2DART.
It is necessary to find and ID of the tables pace (if you don’t know it). So, connect to the database and run command
*db2 list tablespaces show detail
*In my case, I found, that the ID is 3.
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 12288
Useable pages = 12256
** Used pages = 672**
Free pages = 11584
** High water mark (pages) = 11424**
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
As you can see there is 672 used pages, but HWM is 11424. I would like to have desired HWM 1000 pages. So i need to run:
[db2inst1@localhost ~]$ db2dart sample /LHWM /TSI 3 /NP 1000
The requested DB2DART processing has completed successfully!
Complete DB2DART report found in:
/home/db2inst1/sqllib/db2dump/DART0000/SAMPLE.RPT
[db2inst1@localhost ~]$
In file you can find something like this:
Highwater Mark: 11424 pages, 357 extents (extents #0 - 356)
Lower highwater mark processing - phase start.
Current highwater mark: 356
Desired highwater mark: 31
Number of used extents in tablespace: 21
Number of free extents below original HWM: 336
Number of free extents below desired HWM: 13
Number of free extents below current HWM: 336
Step #1: Object ID = 5
=> Offline REORG of this table (do not specify a temporary tablespace
and do not use the LONGLOBDATA option).
Table: DB2INST1.TESTHWM
DAT object size: 2
INX object size: 0
XDA object size: 0
LF object size: 0
LOB object size: 0
LOBA object size: 0
BMP object size: 0
Total size of object parts: 2
Minimum number of extents that will move by this operation: 2
Current highwater mark: 20
Desired highwater mark: 31
Number of used extents in tablespace: 21
Number of free extents below original HWM: 336
Number of free extents below desired HWM: 11
Number of free extents below current HWM: 0
** Run the suggested offline REORG for the table first, and then run LHWM
for the suggestion on other objects.
As you can see, you need to run offline reorg of the table DB2INST1.TESTHWM to allow the lowering of HWM.
[db2inst1@localhost ~]$ db2 reorg table testhwm
DB20000I The REORG command completed successfully.
Then if you ran the db2dart command again (with the same parameters), the output stored in the will include:
Highwater Mark: 11424 pages, 357 extents (extents #0 - 356)
Lower highwater mark processing - phase start.
Current highwater mark: 356
Desired highwater mark: 31
Number of used extents in tablespace: 21
Number of free extents below original HWM: 336
Number of free extents below desired HWM: 11
Number of free extents below current HWM: 336
Final highwater mark: Extent #20 (21 extents, 672 pages).
That is good… But we are not at the end of the process. The tablespace still looks like this:
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
** Total pages = 12288**
Useable pages = 12256
** Used pages = 672**
Free pages = 11584
** High water mark (pages) = 672**
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
We lowered the HWM, but the container is still big. Now it is time to run ALTER TABLE.
[db2inst1@localhost ~]$ db2 alter tablespace IBMDB2SAMPLEREL reduce
DB20000I The SQL command completed successfully.
Now the result is as expected:
Tablespace ID = 3
Name = IBMDB2SAMPLEREL
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 704
Useable pages = 672
Used pages = 672
Free pages = 0
High water mark (pages) = 672
Page size (bytes) = 8192
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Hope it helps.
Sources:
ALTER TABLESPACE - https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000890.html