IBM Support

How to identify used pages in a tablespace

Question & Answer


Question

LIST TABLESPACES SHOW DETAIL does not correctly display used pages in a table space; it shows allocated pages. The INSPECT command will correclty show used pages.

Answer

You can use INSPECT to report the number of used and allocated pages in IBM DB2 UDB.

LIST TABLESPACES SHOW DETAIL will list under Total Pages, the allocated pages, not the used pages.


EXAMPLE:
1.- Run command to inspect the tables in tablespace userspace1:


    db2 inspect check tablespace name userspace1 results inspect.log

    This will generate a file inspect.log on the instance's diagnostic directory (usually <instance directory>/sqllib/db2dump). If multinode, it will generate a file for each node, adding the node number at the end of the filename. ie: inspect.log.000, inspect.log.001, etc.
    2.- Run command to db2inspf to format the file:

       db2inspf  inspect.log inspect.fmt
      This an example of a fragment from the formatted file:
      Table phase start (ID Signed: 8, Unsigned: 8; Tablespace ID: 2) :

           Data phase start. Object: 8  Tablespace: 2
           The index type is 2 for this table.
            DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 63 %
           Data phase end.

           Index phase start. Object: 8  Tablespace: 2
            INX Object Summary: Total Pages 3 - Used Pages 3
           Index phase end.

           LOB phase start. Object: 8  Tablespace: 2
            LOB Object Summary: Total Pages 128 - Used Pages 65
            LBA Object Summary: Total Pages 2 - Used Pages 2
           LOB phase end.
         Table phase end.


    3.- Run the following command:

    grep Used inspect.log.001.fmt
       DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 65 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 57 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 66 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 48 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 48 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 60 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 63 %
           INX Object Summary: Total Pages 3 - Used Pages 3
           LOB Object Summary: Total Pages 128 - Used Pages 65
           LBA Object Summary: Total Pages 2 - Used Pages 2
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 65 %
           INX Object Summary: Total Pages 3 - Used Pages 3
           LOB Object Summary: Total Pages 64 - Used Pages 2
           LBA Object Summary: Total Pages 2 - Used Pages 2
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 57 %
           DAT Object Summary: Total Pages 1 - Used Pages 0 - Free Space 70 %
           DAT Object Summary: Total Pages 1 - Used Pages 0 - Free Space 70 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 44 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 44 %
           DAT Object Summary: Total Pages 4 - Used Pages 1 - Free Space 90 %
           DAT Object Summary: Total Pages 1 - Used Pages 1 - Free Space 57 %


    4.- To obtain the number of used pages for that tablespace, add the "Used Pages" in all the lines from the previous step, ie. 90 used pages in this example.

    [{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tablespaces","Platform":[{"code":"PF002","label":"AIX"}],"Version":"9.7;10.1;10.5;11.1","Edition":"Enterprise;Workgroup","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

    Document Information

    Modified date:
    16 June 2018

    UID

    swg21197191

    Page Feedback