I am trying to figure out how much unused space (in pages) tablespaces used. Erring on the said of caution I factored in 20%
Running the following query helps determine the number of data pages and creates an alter tablespace statement.
select 'alter tablespace '||substr(tbsp_name,1,30)||' reduce (ALL '||
smallint(sum(tbsp_usable_pages) - (tbsp_usable_pages * .2))||' ) ;'
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t
where tbsp_type = 'DMS'
group by tbsp_name, TBSP_USABLE_PAGES,TBSP_TOTAL_PAGES,TBSP_FREE_PAGES,TBSP_USED_PAGES,TBSP_AUTO_RESIZE_ENABLED,TBSP_PAGE_SIZE
order by tbsp_name ;
Does anyone know why I received the following error, or has a better way to reduced unused tablespace data pages?
alter tablespace ACCESSGRPACCTYPIS reduce (ALL 768 )
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1422N The size of the container is invalid. SQLSTATE=54039
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
7 replies Latest Post - 2012-11-30T16:44:48Z by db2girl_bella
Pinned topic SQL1422N while attempting to reduce container table pages
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-11-30T16:44:48Z at 2012-11-30T16:44:48Z by db2girl_bella
Re: SQL1422N while attempting to reduce container table pages2012-11-29T14:35:01Z in response to ljkarlNot even trying to understand what "sum(tbsp_usable_pages) - (tbsp_usable_pages * .2)" might mean, 20% of usable pages seems like an arbitrary measure. I would probably look at tbsp_free_pages to determine if there's any unused space. Also, don't forget about the high watermark.
Re: SQL1422N while attempting to reduce container table pages2012-11-29T15:51:58Z in response to nivanov1Indeed 20% is a arbitrary, yet precautionary number. The query I posted uses the usable available page space. So I'm taken 20% of usable page space and subtracting it from the usable page space.
It seems my error is associated to not being able to reduce the number of pages below the high watermark. It looks as if I can use the alter tablespace lower high watermark to move the extents lower in the tablespace. I'll only be able to reduce data pages to the high watermark. Will I need to repeat this process until all desired unused space is remove?
To expand on the other part of my inquiry, I have compressed the DB2 tables and indexes in my DB2 9.7. And am now attempting to reduce the space. I've already reorg. Does anyone have an efficient way of doing this? (reduce high watermark, followed by the reduction of tablespace pages?)
mwandishi 100000PY7N47 PostsACCEPTED ANSWER
Re: SQL1422N while attempting to reduce container table pages2012-11-29T16:47:29Z in response to ljkarlHi,
'alter tablespace xxxx lower high water mark..' is the preferred option and I believe this will even shift SMPs, which historically couldn't be moved by reorgs for instance.
I'd actually advise a dump of the HWM, both these can be run online:
1) Dump the HWM: db2dart xxx /dhwm /tsi 5 ( where: xxx is the dbname & 5 is the ID of the offending tbspace ).
2) Provide suggestions on lowering: db2dart xxx /lhwm /tsi 5 /np 0
I refer you too: http://www-01.ibm.com/support/docview.wss?uid=swg21006526
Re: SQL1422N while attempting to reduce container table pages2012-11-29T18:36:53Z in response to ljkarl> It seems my error is associated to not being able to reduce the number of pages below the high watermark.
Probably not. It seems you may be misunderstanding what the usable pages number means. It is the total number of pages in a tablespace that are available for user objects, which includes both currently used and free pages. So, you might have something like this:
Total number of pages = 14336
Number of usable pages = 14332
Number of used pages = 13908
Number of pending free pages = 0
Number of free pages = 424
High water mark (pages) = 13908
Will you be able to lower the HWM? No. Will you be able to reduce the tablespace size by 20% of the usable pages (2867 pages)? No.
Re: SQL1422N while attempting to reduce container table pages2012-11-29T21:14:03Z in response to nivanov1Because we did a restore redirect using a offline backup from a DB2 9.5 environment, the restored tablespaces did not have reclaimable storage activated.
using http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20057ef0-7952-2c10-949f-decd4fcbb392?QuickLink=index&overridelayout=true as a resource, it seems a requirement to reduce the high watermark is the tablespaces have to have reclaimable storage activated, and the only way to do this is by creating new tablespaces and moving the tables and indexes to it.
"Existing automatic storage or DMS table spaces created with an earlier version of DB2 cannot reduce their high-water mark through extent movement."
The following query obtains the current HWM
select varchar(tbsp_name, 34) as tbsp_name, tbsp_free_pages, tbsp_total_pages, tbsp_page_top from table (mon_get_tablespace('',-2)) as t
Now check to see how many free pages are above and below the high-water mark using the MON_GET_TABLESPACE table function:
db2 "select varchar(tbsp_name, 15) as tbsp_name, tbsp_free_pages, tbsp_total_pages, tbsp_page_top from table (mon_get_tablespace('EXTENTREMAP',-2)) as t"
TBSP_NAME TBSP_FREE_PAGES TBSP_TOTAL_PAGES TBSP_PAGE_TOP
EXTENTREMAP 6526 3538280 3536394
1 record(s) selected.
Looking at the column TBSP_PAGE_TOP, we see that the high-water mark is 3536394 pages. Looking at the column TBSP_TOTAL_PAGES, we see that the total pages used by the table space is 3538280. So there are only 1886 free pages above the high-water mark and 4640 free pages below the high-water mark. With older versions of DB2, those 4640 pages would be trapped since the high-water mark cannot be lowered.
Since this table space has been created with DB2 9.7 and it is enabled for reclaimable storage, the high-water mark can be reduced through extent movement.
So, as I progress and increase my knowledge I have learned that I have to create tablespaces with reclaimable storage activated in order to reduce the hwm via alter tablespace < tsp name> LOWER HIGH WATER MARK. Maybe there is an option in restore redirect that will do this.
A lot of work. Would be nice that once table and index compression is activated a mechanism was in place to automatically reduce the HWM and possibly reduced the tablespace page size