1

I'm trying to run the following:

db2 alter tablespace TABLESPACE1 reduce

I keep getting errors. As you can see, I have around 50% free pages. I'd like to shrink it in order to reclaim OS disk space.

 Tablespace ID                        = 281
 Name                                 = TABLESPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8943720
 Useable pages                        = 8943696
 Used pages                           = 4559696
 Free pages                           = 4384000
 High water mark (pages)              = 8914736
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 8
 Prefetch size (pages)                = 24
 Number of containers                 = 3
 Minimum recovery time                = 2014-12-09-16.28.18.000000

  Container ID                         = 0
 Name                                 = /data/db2dat_10/sida1/TABLESPACE1_01.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /data/db2dat_11/sida1/TABLESPACE1_02.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
 Container ID                         = 2
 Name                                 = /data/db2dat_12/sida1/TABLESPACE1_03.dbf
 Type                                 = File
 Total pages                          = 2981240
 Useable pages                        = 2981232
 Accessible                           = Yes
6
  • Care to share the actual errors? Seeing that the tablespace isn't automatic, you must specify the containers that you want to resize. – mustaccio Dec 15 '14 at 16:40
  • alter tablespace TABLESPACE1 reduce There are conflicting clauses in the ALTER TABLESPACE statement.. SQLCODE=-1763, SQLSTATE=429BC, DRIVER=3.67.28 – DB2DBAZ Dec 15 '14 at 16:49
  • The command alter tablespace TABLESPACE1 reduce looks fine - can you show a screenshot with the command and the error message displayed? – Jack Douglas Dec 15 '14 at 19:42
  • @JackDouglas - no, it does not look fine for the given tablespace. www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/… – mustaccio Dec 15 '14 at 20:24
  • @mustaccio Thank you, I have been reading that IBM note but I can't seem to make it work. Always get the same error. – DB2DBAZ Dec 15 '14 at 20:57
2

I ended up figuring this one out.

Shrink a tablespace with DB2 9.5 is much more difficult. The reduce and resize command exist but will only work if the high water mark is low. You cannot use reduce or resize below the HWM size.

You need to use DB2DART -LHWM and have the database deactivated while doing so.

0

You cannot reduce tablespaces under DB2 9.5 as the reclaimable storage attribute is not set until 9.7. See my answer here regarding migrating from 9.7 to 10.5, where I discuss this.

2
  • What's the REDUCE clause of ALTER TABLESPACE is for then? – mustaccio Dec 16 '14 at 1:45
  • @mustaccio Maybe it has more to do with having migrated to DB2 9.7 from a DB2 9.5 install...but I can say that I could not reduce tablespace sizes either as DMS nor as converted to automatic storage due to the reclaimable storage bit issue. – Chris Aldrich Dec 16 '14 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.