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