Toad World® Forums

Database Space Usage (DPF)

Hi,

I am working with new Toad DB2 5.6 on DB2 LUW V.9.7 FP8 DPF server with 13 partitions across 4 physical Linux servers.

DB Browser> Database> Space Usage tab (‘Total MB, Used MB, Free MB columns) shows much higher values then values i got from db2top and from (SNAP_GET_TBSP_PART (’ ', '-2)) table function for tablespaces.

For example in Toad DB Browser> Database> Space Usage tab ,“Total MB” column shows 7,348,224.0000 for specific tablespace, db2top > Tablespaces screen “Total Size” column shows 598.0G and from SNAP_GET_TBSP_PART (’ ', '-2)) table function i got also 589.0 GB for TBSP_CURRENT_SIZE.

The point is i cannot calculate this 7,348,224.0000 MB anywhere. In DB Browser > tablespaces > Space Usage for containers (sum for all partitions) looks ok.

Regards,

Ivan

Just a guess…

Assuming you are using Automatic Storage or SMS, look at the total storage avaliable on your drives. Does it correlate with what Quest is displaying?

No, i think there is no correlation with total storage… Don’t know what is the query that is used by Quest for calculation

Hi Ivan,

I haven’t been able to recreate this on 5.6. Can you please post a version of the tablespace DDL with any sensitive information redacted?

Adam

Hi Adam,

Please find ddl for tbspace bellow:

CREATE LARGE TABLESPACE TS_TEST

IN DATABASE PARTITION GROUP PGALL

PAGESIZE 16K

MANAGED BY DATABASE

USING ( File ‘/db2/db2inst1/NODE0001/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (1)

USING ( File ‘/db2/db2inst1/NODE0002/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (2)

USING ( File ‘/db2/db2inst1/NODE0003/TESTDB/ts_test/TESTDB001.dbf’ 3145728 )

ON DBPARTITIONNUM (3)

USING ( File ‘/db2/db2inst1/NODE0004/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (4)

USING ( File ‘/db2/db2inst1/NODE0005/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (5)

USING ( File ‘/db2/db2inst1/NODE0006/TESTDB/ts_test/TESTDB001.dbf’ 3145728 )

ON DBPARTITIONNUM (6)

USING ( File ‘/db2/db2inst1/NODE0007/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (7)

USING ( File ‘/db2/db2inst1/NODE0008/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (8)

USING ( File ‘/db2/db2inst1/NODE0009/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (9)

USING ( File ‘/db2/db2inst1/NODE0010/TESTDB/ts_test/TESTDB001.dbf’ 3276800 )

ON DBPARTITIONNUM (10)

USING ( File ‘/db2/db2inst1/NODE0011/TESTDB/ts_test/TESTDB001.dbf’ 3342336 )

ON DBPARTITIONNUM (11)

USING ( File ‘/db2/db2inst1/NODE0012/TESTDB/ts_test/TESTDB001.dbf’ 3342336 )

ON DBPARTITIONNUM (12)

EXTENTSIZE 32

PREFETCHSIZE 128

BUFFERPOOL BP1

OVERHEAD 7.5

TRANSFERRATE 0.06

AUTORESIZE YES

INCREASESIZE 1G

MAXSIZE 54G

NO FILE SYSTEM CACHING

;

Regards,

Ivan

Hi Ivan,

For the container tab we use this SQL: SELECT tbsp_name as tablespace_name, container_name, container_type, total_pages, usable_pages, accessible, stripe_set

FROM SYSIBMADM.SNAPCONTAINER WHERE (tbsp_name = ‘TS_TEST’)

Then the values in megabytes are calculated by pages * pagesize(16384) / MB(1048576)

Do the values from snapcontainer match what you’re seeing in the grid?

Adam

Hi Adam,

This query returns values that mach grid values for Total MB and Usable MB in DB Browser > tablespaces > Space Usage for containers grid, but for grid in DB Browser> Database> Space Usage tab not. What is the sql for this tab?

Thank you,

Ivan

Hi Ivan,

Sorry, I thought you were talking about the tablespace view. But the database view is basically the same thing as tablespace. It uses the container SQL above and some tablespace SQL: SELECT DBPARTITIONNUM, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES, TBSP_NUM_CONTAINERS FROM SYSIBMADM.SNAPTBSP_PART WHERE TBSP_NAME = parameter.

Can you send me a screenshot of what you’re seeing?

Adam

Hi,

i sent you a pm email.

Regards,

Ivan