Topic
IC4NOTICE: 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.
7 replies Latest Post - ‏2012-11-30T16:44:48Z by db2girl_bella
ljkarl
ljkarl
10 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: SQL1422N while attempting to reduce container table pages

    ‏2012-11-29T14:35:01Z  in response to ljkarl
    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
      ACCEPTED ANSWER

      Re: SQL1422N while attempting to reduce container table pages

      ‏2012-11-29T15:51:58Z  in response to nivanov1
      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
        ACCEPTED ANSWER

        Re: SQL1422N while attempting to reduce container table pages

        ‏2012-11-29T16:47:29Z  in response to ljkarl
        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
        ACCEPTED ANSWER

        Re: SQL1422N while attempting to reduce container table pages

        ‏2012-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.
        • ljkarl
          ljkarl
          10 Posts
          ACCEPTED ANSWER

          Re: SQL1422N while attempting to reduce container table pages

          ‏2012-11-29T21:14:03Z  in response to nivanov1
          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
            ACCEPTED ANSWER

            Re: SQL1422N while attempting to reduce container table pages

            ‏2012-11-29T22:56:03Z  in response to ljkarl
            As Stephen pointed out, you can use DB2DART to move the HWM.
            • db2girl_bella
              db2girl_bella
              20 Posts
              ACCEPTED ANSWER

              Re: SQL1422N while attempting to reduce container table pages

              ‏2012-11-30T16:44:48Z  in response to nivanov1
              It would help if you post the output from "db2pd -d <db name> -tab"