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.
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 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.
Related Information
[{"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"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21197191