469,926 Members | 1,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Current and Estimate Table Data Size and Index Size for DB2

We are on DB2 UDB 8.2.2 on AIX. I know this question has been asked
many times on the ng but I am just not finding a real good answer. I
need to be able to not only determine existing table data size and
index size but also estimate for the future. I keep seeing info
regarding using reorgchk and the such however my dba will not allow us
developers to have access to the comands necessary such as runstats.
How can a lowely developer such as myself get acess to the information
I need.

In a perfect world what I would like to have is a query/stored proc
that I might use which I feed it schema name and table name which would
return back to me how much space is used by data and indexes. Another
query/stored proc which would be useful is one to feed it again schema
name and table name and number of rows which would then output
estimated data size and index size that would be used by that number of
rows.

I also saw this Query out on the ng but I don't have privs to
SYSTOOLS.STMG_OBJECT.

SELECT
MAX(tables.STMG_TIMESTAMP) as SNAPSHOT_TIMESTAMP,
tables.OBJ_ID,
object.OBJ_NAME,
object.obj_schema,
tables.ESTIMATED_SIZE
FROM
SYSTOOLS.STMG_TABLE tables
INNER JOIN
SYSTOOLS.STMG_OBJECT object
ON tables.obj_id = object.obj_id AND tables.stmg_timestamp =
object.stmg_timestamp
GROUP BY tables.STMG_TIMESTAMP, tables.ESTIMATED_SIZE,
tables.obj_id,
object.obj_name, object.obj_schema

Any help would be appreciated!
Spencer

Feb 9 '06 #1
1 4314
stabbert wrote:
We are on DB2 UDB 8.2.2 on AIX. I know this question has been asked
many times on the ng but I am just not finding a real good answer. I
need to be able to not only determine existing table data size and
index size but also estimate for the future. I keep seeing info
regarding using reorgchk and the such however my dba will not allow us
developers to have access to the comands necessary such as runstats.
How can a lowely developer such as myself get acess to the information
I need.

In a perfect world what I would like to have is a query/stored proc
that I might use which I feed it schema name and table name which would
return back to me how much space is used by data and indexes. Another
query/stored proc which would be useful is one to feed it again schema
name and table name and number of rows which would then output
estimated data size and index size that would be used by that number of
rows.

I also saw this Query out on the ng but I don't have privs to
SYSTOOLS.STMG_OBJECT.

SELECT
MAX(tables.STMG_TIMESTAMP) as SNAPSHOT_TIMESTAMP,
tables.OBJ_ID,
object.OBJ_NAME,
object.obj_schema,
tables.ESTIMATED_SIZE
FROM
SYSTOOLS.STMG_TABLE tables
INNER JOIN
SYSTOOLS.STMG_OBJECT object
ON tables.obj_id = object.obj_id AND tables.stmg_timestamp =
object.stmg_timestamp
GROUP BY tables.STMG_TIMESTAMP, tables.ESTIMATED_SIZE,
tables.obj_id,
object.obj_name, object.obj_schema

Any help would be appreciated!
Spencer

Table look at SYSCAT.TABLES and SYSCAT.COLUMNS. From the cardinality and
the various sizes it's easy to compute size.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Feb 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jay | last post: by
12 posts views Thread by xixi | last post: by
4 posts views Thread by Alvin SIU | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.