By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 800 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Current and Estimate Table Data Size and Index Size for DB2

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.