DB2 9.5 – Shrink Tablespace

db2db2-9.5

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

Best Answer

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.

Related Solutions

DB2 9.5 How to get tablespace monitor information

Use the TBSP_UTILIZATION administrative view - Retrieve table space configuration and utilization information. See the "Essential DB2 Health Check" for a recommendation on how to use it, especially the sections on File System Free Space and DMS Tablespace Free Space.

Example:

select substr(tbsp_name,1,10) "Name", tbsp_utilization_percent "Used%", tbsp_free_pages "FreePages", tbsp_usable_Pages "UsablePages", tbsp_used_pages "UsedPages", tbsp_page_top "HWM", tbsp_auto_resize_Enabled "AutoResize 1=Yes", tbsp_increase_size "Increase Size", tbsp_max_size "MaxSizeBytes -1=ulimited", tbsp_last_resize_failed "ResizeFailed 1=Yes", tbsp_page_size "PgSize Bytes"
from SYSIBMADM.TBSP_UTILIZATION
where tbsp_type = 'DMS';

Specifically, you are after the tbsp_increase_size field from the SYSIBMADM.TBSP_UTILIZATION table, where tbsp_type = 'DMS' as show in the query above. (The query has a few other things that are helpful in it too.)

Db2 – INSERT INTO with SELECT subquery db2 9.5

I'll put this as an answer as it is easier to comment on.

Generally you need to run RUNSTATS on every table (I'd vote for at least once a week). This tells the optimizer the number of records (and if specified, which I do recommend) the distribution of data across the records. With this information the optimizer can make better decisions regarding access paths.

The optimizer also uses packages as well. REBINDING the packages essentially alters the packages (ie, their access paths) based on the RUNSTATS found.

In your case you could run RUNSTATS on the tables you care about

RUNSTATS ON TABLE <schema>.<table> WITH DISTRIBUTION AND DETAILED INDEXES ALL

If you have a lot of tables, then I'd recommend hunting for them dynamically

--do note syntax is for Unix, having to worry about escape characters and what not.
db2 -tnx  "select 'runstats on table ' || 
ltrim(rtrim(tabschema))||'.'||ltrim(rtrim(tabname))
|| ' with distribution and detailed indexes all ;'
from syscat.tables where type = 'T' and ownertype = 'U' and 
tabschema not in ('SYSCAT','SYSIBM','SYSIBMADM','SYSPUBLIC','SYSSTAT','SYSTOOLS') 
order by tabschema asc" >> runstats.sql

You can then read this in to your database and execute it

db2 -tvf runstats.sql

REBINDs I find the easiest to do using the db2rbind utility. It beats having to know all the specific package names. Since we are a small shop I just tend to use the following syntax each time.

db2rbind <my db> -l rebind.log all -r any

If there are errors, you will see it in the log.

So generally you would want to issue the RUNSTATS first, then issue a COMMIT to release any locks. Then you can run the REBINDs through db2rbind. If you have not run these in a while you may wish to throw a db2 flush package cache dynamic in between the RUNSTATS and the db2rbind.

Best Answer

Related Solutions

DB2 9.5 How to get tablespace monitor information

Db2 – INSERT INTO with SELECT subquery db2 9.5

Related Question