Topic
  • 1 reply
  • Latest Post - ‏2013-09-17T08:52:47Z by G.Paulus
pimuter
pimuter
1 Post

Pinned topic Tablespace Size mismatch in DB2

‏2013-09-12T08:40:49Z | 9.5 aix db2 dbdsupport eee tablespace

I am trying to get the tablespace size for a DB2 EEE setup with DB2 Version 9.5 installed on my AIX box. I was trying to get the size of a tablespaces for database I have created on my EEE setup. 

When I do following command:



db2 
=> 
list tablespaces  Tablespace ID 
= 
1 
Name 
= 
TEMPSPACE1
Type 
= 
System managed space
Contents 
= 
System Temporary data
State 
= 
0x0000 
Detailed explanation
: 
Normal
Total pages 
= 
1 

Useable pages 
= 
1 
Used pages 
= 
1 
Free pages 
= 
Not 
applicable
High water mark 
(
pages
) 
= 
Not 
applicable
Page size 
(
bytes
) 
= 
4096 

Extent size 
(
pages
) 
= 
32 
Prefetch size
(
pages
) 
= 
32 
Number 
of 
containers 
= 
1

Above command works fine for single instance DB while in case of DB2EEE, we need to get the tablespace size from each node and add it up or other way is to use the bellow query:



db2 
-
c 
"
select 
TABLESPACE_NAME
,
usable_pages 
as 
TBSPC_Pages 
from 
table 
(
snapshot_tbs_cfg 
(
'DB2EEE'
,
-2
))
TABLESPACE           
2 
                 TABLESPACE_SIZE
----------------------------------------- --------------------
TEMPSPACE1            
0 
                 
0

Above query is returning number of pages for the same tablespace as '0' (zero) while previous command returned it as '1'.

 

I tried querying to table SNAPTBSP_PART but got the tablespace size as 'zero' only. 

Any idea which one of the above is correct and why?

Or is there any other way to find the size of tablespace in DB2 (Single node and EEE instances both) ?

  • G.Paulus
    G.Paulus
    11 Posts

    Re: Tablespace Size mismatch in DB2

    ‏2013-09-17T08:52:47Z  

    Hi,

    I do not have any idea yet why SNAPTBSP_PART returns a size of 'zero'. But the list tablespace command returns the correct value.

    An other in my eyes easier way to query the size of each tablespace per database partition is the administrative view SYSIBMADM.TBSP_UTILIZATION first introduced in db2 9.5. http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0022022.html

    This view returns the allocated space in the filesystem and the used space within the tablespace for each tablespace and each database partition.

    Here is an example for an output of this view (not all columns).

    DBPARTITIONNUM TBSP_NAME            TBSP_TYPE    TBSP_TOTAL_SIZE_KB    TBSP_USED_SIZE_KB
    -------------- -------------------- ---------- -------------------- --------------------
                 0 SYSCATSPACE          DMS                      196608               183408
                 0 TEMPSPACE1           SMS                           4                    4
                 0 USERSPACE1           DMS                       32768                 3200
                 0 SYSTOOLSPACE         DMS                       32768                  784
                 0 IBMDEFAULTGROUP      DMS                       32768                13568
                 0 TBSP4K01D            DMS                       32768                 1920
                 0 TBSP32K01D           DMS                       32768                 5120
                 0 MOVESPACE            DMS                         768                  384



    Best regards,

    Gerhard