How to extend tablespace?
Accepted answer
When you run "db2pd" command against the DB2 database 'CCM', the column "AR" in the Tablespace Autoresize Statistics says "Yes" for the tablespace "CONTENTTS", which means Auto resize is enabled that the tablespace will be extended automatically so you don't need to extend manually.
Database Partition 0 -- Database CCM -- Active -- Up 0 days 02:54:19 -- Date 07/15/2015 11:17:36
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x00002AF9956CFF60 4 DMS Large 16384 32 Yes 192 1 1 Off 1 0 31 CONTENTTS
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
0x00002AF9956CFF60 4 9830400 9830368 5348512 0 4481856 5348512 5348512 0x00000000 0 0 No
-------------------------------------------------------------
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00002AF9956CFF60 4 No Yes 0 -1 No None None No
-------------------------------------------------------------
If not, you can enable by the following db2 command.
====================================================
db2 "alter tablespace <name of tablespace> autoresize yes"
For example:
db2 "alter tablespace CONTENTTS autoresize yes"
====================================================
See more details in Automatic re-sizing of DMS table spaces.
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0012277.html
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0012277.html?lang=en
If you want to extend/resize manually, you can do by the following db2 command.
====================================================
db2 "alter tablespace <name of tablespace> extend (file 'name of container' <extend size>)"
For example:
db2 "alter tablespace CONTENTTS extend (file '/work/dsiddms' 15M)"
or
db2 "alter tablespace CONTENTTS resize (file '/work/dsiddms' 15M)"
====================================================
See more details in ALTER TABLESPACE Statement
http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000890.html
Note: It is recommended you always consult your database administrator before doing this task.
Database Partition 0 -- Database CCM -- Active -- Up 0 days 02:54:19 -- Date 07/15/2015 11:17:36
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x00002AF9956CFF60 4 DMS Large 16384 32 Yes 192 1 1 Off 1 0 31 CONTENTTS
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
0x00002AF9956CFF60 4 9830400 9830368 5348512 0 4481856 5348512 5348512 0x00000000 0 0 No
-------------------------------------------------------------
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00002AF9956CFF60 4 No Yes 0 -1 No None None No
-------------------------------------------------------------
If not, you can enable by the following db2 command.
====================================================
db2 "alter tablespace <name of tablespace> autoresize yes"
For example:
db2 "alter tablespace CONTENTTS autoresize yes"
====================================================
See more details in Automatic re-sizing of DMS table spaces.
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0012277.html
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0012277.html?lang=en
If you want to extend/resize manually, you can do by the following db2 command.
====================================================
db2 "alter tablespace <name of tablespace> extend (file 'name of container' <extend size>)"
For example:
db2 "alter tablespace CONTENTTS extend (file '/work/dsiddms' 15M)"
or
db2 "alter tablespace CONTENTTS resize (file '/work/dsiddms' 15M)"
====================================================
See more details in ALTER TABLESPACE Statement
http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000890.html
Note: It is recommended you always consult your database administrator before doing this task.
Comments
Tiago Moura
Aug 04 '15, 6:56 p.m.It is a quite broad question. Try to give more details, which version? which database?
Jack Smith
Aug 05 '15, 2:25 a.m.It is RTC version 4.0.6 with IBM DB2 9.7.