Listing table spaces with more than n percent dropped space

When a table is dropped from a table space, the space it occupied cannot be reused. If the percent of dropped space is significant, consider reorganizing the table spaces and use segmented table spaces for the tables. You can find table spaces that have a certain percentage (n) of dropped space by using the provided performance queries. You can then take action on these table spaces, as needed.

Procedure

To list table spaces with more than n percent dropped space:
  1. On the DB2 Administration Menu (ADB2) panel, specify option 3, and press Enter.
  2. On the DB2 Performance Queries panel, in option 4 - Table Spaces with More Than n Percent Dropped Space, change the value of n as needed.
  3. Specify option 4 on the command line, and press Enter.

    The qualifying table spaces are displayed on the Table Spaces With More Than n Percent Dropped Space (ADB234) panel:

    Figure 1. Table Spaces With More Than n Percent Dropped Space (ADB234) panel
     DB2 Admin ---- DB2X Table Spaces with More Than 5 Pct Dropped Space -----------
 Command ===>                                                  Scroll ===> PAGE 
                                                                                
 The following table spaces have more than 5 percent dropped space. When        
 a table is dropped from a table space, the space it occupied cannot be reused. 
 If the percentage of dropped space is significant, you should consider         
 reorganizing the table spaces and/or using segmented table spaces for the      
 tables.                                                                        
                                                                                
 Commands:       O - Reorg   UT - Utilities                                     
 Line commands:  S - Select   O - Reorg                                         
                                                                                
                            Percent                 Primary Secondary           
 S DB Name  TS Name    Part Dropped        Rows    Quantity Quantity            
   *        *             * *                 *           *         *           
 - -------- -------- ------ ------- ----------- ----------- ---------           
   DSQ1STBB DSQ1STBT      0      10         135         100         5				
   D208D001 D208SPRF      0      17         437           3         3				
   D475D001 D475S088      0      94        8552          88        13				
   D154D400 D154STPS      0      24         170           3         2				
   D154D500 D154STEA      0      12           7         125         3				
   D922D01  D922SINC      0      10          72           3         3				
   JFDDBO1  JFDSO4        0      39        1201         984       120				
   JFDDBO1  JFDSO5        0      20        2621        2280       240
    Notes:
    • The percentage of space that is occupied by dropped tables is listed in the Percent dropped column.
    • The number of rows in the table space or partition is listed in the Rows column.
    For information on the other columns, see SYSTABLESPACE catalog table (Db2 13 for z/OS).
  4. Optional: Run REORG or other utilities by using the primary commands and line commands on the panel.
    Tip:

    If some of your table spaces have dropped tables, consider running the MODIFY utility to remove the details of the table from the DBD.