Determining the number of free table pages without locking

Question

This article describes a method of finding the number of free pages within a table while avoiding locking issues.

Answer

You may run into locking issues when running oncheck -pT tablename on a high use table. You can run a query using the sysmaster database instead to avoid encountering locking issues.
The following select statement from the sysmaster database will provide free page information similar information to oncheck -pt:

select count(pb_bitmap), pb_bitmap
  from sysptnbit p,systabnames t
 where t.tabname = "tablename"
   and t.dbsname = "databasename"
   and p.pb_partnum = t.partnum
 group by pb_bitmap;

This select will not place any locks on the base table tablename . The query produces output similar to:

         (count)   pb_bitmap

            2841          12
             196           0
              26           4
               1           8

4 row(s) retrieved.

The pb_bitmap column is the decimal value of the bitmap for each page in the table. The values mean the following:

pb_bitmap Description
========= =========
  0       Unused
  4       Data Page with room for another row
  8       Index Page
 12       Full Data Page

Note: You could use the query to help estimate the number of pages still available within the table. You should estimate on being able to use only 30% of the pages with the value of '4', meaning 'a data page with room for another row'.

Using the example data: The engine would need another extent after using the remaining space in the partially used pages:
(26 * 30%) = 7.8 pages

http://www-01.ibm.com/support/docview.wss?uid=swg21660676&myns=swgimgmt&mynp=OCSSGU8G&mync=E