Topic
  • 7 replies
  • Latest Post - ‏2012-11-30T16:44:48Z by db2girl_bella
ljkarl
ljkarl
10 Posts

Pinned topic SQL1422N while attempting to reduce container table pages

‏2012-11-28T18:22:11Z |
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

Thanks!

Larry Karl
Updated on 2012-11-30T16:44:48Z at 2012-11-30T16:44:48Z by db2girl_bella
  • nivanov1
    nivanov1
    231 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T14:35:01Z  
    Not 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.
  • ljkarl
    ljkarl
    10 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T15:51:58Z  
    • nivanov1
    • ‏2012-11-29T14:35:01Z
    Not 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.
    Indeed 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?)

    Thanks!
  • mwandishi
    mwandishi
    47 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T16:47:29Z  
    • ljkarl
    • ‏2012-11-29T15:51:58Z
    Indeed 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?)

    Thanks!
    Hi,

    '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

    Regards,
    Stephen.
  • nivanov1
    nivanov1
    231 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T18:36:53Z  
    • ljkarl
    • ‏2012-11-29T15:51:58Z
    Indeed 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?)

    Thanks!
    > 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.
  • ljkarl
    ljkarl
    10 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T21:14:03Z  
    • nivanov1
    • ‏2012-11-29T18:36:53Z
    > 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.
    Because 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
  • nivanov1
    nivanov1
    231 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T22:56:03Z  
    • ljkarl
    • ‏2012-11-29T21:14:03Z
    Because 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
    As Stephen pointed out, you can use DB2DART to move the HWM.
  • db2girl_bella
    db2girl_bella
    20 Posts

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-30T16:44:48Z  
    • nivanov1
    • ‏2012-11-29T22:56:03Z
    As Stephen pointed out, you can use DB2DART to move the HWM.
    It would help if you post the output from "db2pd -d <db name> -tab"