Table size and index size

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dunleav1 - 16 Mar 2005 21:25 GMT

I am trying to write a sql script to estimate size and count of rows of
all tables and indexes within a schema.

Here's what I have so far but need a little help.

#$1 is database name and $2 is schema name
db2 connect to $1
db2 select tabname,t1.fpages * t2.pagesize as bytes from syscat.tables
t1, syscat.tablespaces t2 where t1.tabschema='$2' and
t1.tbspace=t2.tbspace

How can I add the index_size for each index under a table?
#syscat.indexes lists nleafs but it doesn't compute.
How can I list the number of rows for each table as I progress through
the query.

Reply to this Message

Pierre Saint-Jacques - 17 Mar 2005 21:35 GMT

Since you select t1.fpages, it seems to me you rely on your statistics to be
current.
Given that, t1.card will give you the number of rows in the table.

However, t1.fpages will always include the count of empty pages, so
fpages*pagesize as bytes may never reflect reality.
The same applies to indexes. nleafs indicate the number of pages in the
last level. They are pages holding keys and rids. Some may be full, some
may be empty. It does not count the space that other nodes occupy.
HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> I am trying to write a sql script to estimate size and count of rows of
> all tables and indexes within a schema.
[quoted text clipped - 11 lines]
> How can I list the number of rows for each table as I progress through
> the query.

Reply to this Message

dunleav1 - 17 Mar 2005 22:21 GMT

Yes, I am relying on my statistics to be current.
Thanks for card definition to equal the number of rows.
Since fpages may by 0, what is the best way to calculate the size for
the table and index?

Reply to this Message

Pierre Saint-Jacques - 17 Mar 2005 22:55 GMT

If you look at your docs., you'll find that table stats. include
FPAGES: Total number of pages to hold the table
NPAGES: Number of page holding at least one row.
The ratio of npages over fpages is an indication of the amount of "fluff" in
your table.
If FPAGES is 0 then CARD should be 0 also.

You can use Google to find stuff like that. I did a google search on: db2
size estimate.
HTH, Pierre.
Here's what Craig Tobias from IBM suggests;
estimate size
Posted by: Craig Tobias, 2004 March 29 02:21 PM

Jean-Marc,

In v8.1, we created some stored procedures to capture and save storage
snapshots. Snapshots can be captured for Databases, Table spaces,
Tables,
Indexes, and Database Partition Groups. To capture storage data
(including
estimated size) for all objects (including tables) in a database, you
should
capture a database snapshot.

Before capturing a snapshot, you must create the storage management
tables
(where all snapshot data is stored). Run the following command to
create the
storage tables, and specify a table space for the tables.

db2 connect to
db2 call create_storagemgmt_tables('')

Now, capture a snapshot at the database level... this can take a while
for
large databases. You may also want to update your table statistics
using
the Runstats utility before running the storage snapshot. To capture a
snapshot, run the following command:

db2 call capture_storagemgmt_info(0, ' ', '')

The table related data is stored in the SYSTOOLS.STMG_TABLE table. You
also
need to do a name lookup in the SYSTOOLS.STMG_OBJECT table. To query
the
storage management tables for estimated table size, run the following
command...

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

The number of bytes used by the table is displayed in the
ESTIMATED_SIZE
column.

You can also delete historical snapshots by deleting entries from the
SYSTOOLS.STMG_ROOT_OBJECT table. To delete all historical data, run
the
following query:

DELETE FROM SYSTOOLS.STMG_ROOT_OBJECT

There is also a stored procedure which will drop all of the storage
management tables.

db2 call drop_storagemgmt_tables(0)

All of the snapshot data can easily be viewed using the storage
management
tool which is launched from the DB2 Control Center, Database > Manage
Storage...

The storage management tool provides detailed historical analysis of
the
captured snapshot data for all storage related objects in the
database, as
well as a way to schedule regular snapshots, and delete historical
snapshots.

Craig Tobias
DB2 Admin. Tools Development
IBM Toronto Lab

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> Yes, I am relying on my statistics to be current.
> Thanks for card definition to equal the number of rows.
> Since fpages may by 0, what is the best way to calculate the size for
> the table and index?

Reply to this Message