How to determine whether you should attempt to reclaim free storage

About this task

This task will provide you with information that you can use to determine the extent to which you have unused space below the high water mark for your table space. Based on this, you can make a determination as to whether reclaiming free storage would be beneficial.

Restrictions

Although you can determine various usage attributes about all your table spaces, only table spaces created with DB2® Version 9.7 or later have the reclaimable storage capability. If you want to be able to reclaim storage in table spaces created with earlier versions of the DB2 product, you either must unload then reload the data into a table space created with DB2 Version 9.7, or move the data with an online move.

Procedure

To determine how much free space exists below the high water mark:

1. Formulate a SELECT statement that incorporates the MON_GET_TABLESPACE table function to report on the state of your table spaces. For example, the following statement will display the total pages, free pages, used pages, for all table spaces, across all database partitions:

   SELECT varchar(tbsp_name, 30) as tbsp_name, 
     reclaimable_space_enabled,
     tbsp_free_pages,
     tbsp_page_top,
     tbsp_usable_pages
   FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t 
   ORDER BY tbsp_free_pages ASC

2. Run the statement. You will see output that resembles this:

   TBSP_NAME                      RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
   ------------------------------ ------------------------- -------------------- -------------------- --------------------
   TEMPSPACE1                                             0                    0                    0                    1
   SYSTOOLSTMPSPACE                                       0                    0                    0                    1
   TBSP1                                                  1                    0                 1632                 1632
   SMSDEMO                                                0                    0                    0                    1
   SYSCATSPACE                                            1                 2012                10272                12284
   USERSPACE1                                             1                 2496                 1696                 4064
   IBMDB2SAMPLEREL                                        1                 3328                  736                 4064
   TS1                                                    1                 3584                  480                 4064
   TS2                                                    1                 3968                   96                 4064
   TBSP2                                                  1                 3968                   96                 4064
   TBSAUTO                                                1                 3968                   96                 4064
   SYSTOOLSPACE                                           1                 3976                  116                 4092
   
     12 record(s) selected.

3. Use the following formula to determine the number of free pages below the high water mark:
   freeSpaceBelowHWM = tbsp_free_pages - (tbsp_usable_pages - tbsp_page_top)

Results

Using the information from the report in step 2, the free space below the high water mark for USERSPACE1 would be 2496 - (4064 - 1696) = 128 pages. This represents just slightly over 5% of the total free pages available in the table space.

What to do next

In this case, it might not be worth trying to reclaim this space. However, if you did want to reclaim those 128 pages, you could run an ALTER TABLESPACE USERSPACE1 REDUCE MAX statement. If you were to do so, and then run the MON_GET_TABLESPACE table function again, you would see the following:

TBSP_NAME                      RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
------------------------------ ------------------------- -------------------- -------------------- --------------------
TEMPSPACE1                                             0                    0                    0                    1
USERSPACE1                                             1                    0                 1568                 1568
SYSTOOLSTMPSPACE                                       0                    0                    0                    1
TBSP1                                                  1                    0                 1632                 1632
SMSDEMO                                                0                    0                    0                    1
SYSCATSPACE                                            1                 2012                10272                12284
IBMDB2SAMPLEREL                                        1                 3328                  736                 4064
TS1                                                    1                 3584                  480                 4064
TS2                                                    1                 3968                   96                 4064
TBSP2                                                  1                 3968                   96                 4064
TBSAUTO                                                1                 3968                   96                 4064
SYSTOOLSPACE                                           1                 3976                  116                 4092

  12 record(s) selected.

Source: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.dbobj.doc/doc/t0055407.html

How to delete duplicate rows from a table.

Show a select to delete duplicate rows.

If you have duplicate rows as the follow example:

db2 "select * from rank order by cod"

COD         NAME                         
----------- ------------------------------
          1 teste1                       
          2 teste2                       
          3 teste3                       
          3 teste3                       
          4 teste4                       

  5 record(s) selected.

You can put row number in your data output, perform the command below:

db2 "SELECT ROWNUMBER() OVER (ORDER BY <column>) \
AS RANK,<column> FROM <table name>"

Output:
db2 "SELECT ROWNUMBER() OVER (ORDER BY COD) AS RANK,COD FROM rank"

RANK                 COD       
-------------------- -----------
                   1           1
                   2           2
                   3           3
                   4           3
                   5           4

  5 record(s) selected.

Then, You can delete the duplicate rows running the follow select:

db2 "delete from (select ROWNUMBER() OVER (ORDER BY <column>) FROM \
<table name>) as E(pos) where pos=<row number to be deleted>"

Output:
db2 "delete from (select ROWNUMBER() OVER (ORDER BY COD) FROM rank) as E(pos) where pos=3"
DB20000I  The SQL command completed successfully.
db2 "select * from rank order by cod"

COD         NAME                         
----------- ------------------------------
          1 teste1                       
          2 teste2                       
          3 teste3                       
          4 teste4                       

  4 record(s) selected.