The good old quest, - How many disk space the table occupies?

The good old quest, - How many disk space the table occupies? - still insoluble?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Konstantin Andreev - 05 Jul 2006 21:27 GMT

Recently I was engaged in the database optimization for one big commercial application. During this business I was greatly astound by the fact that it's impossible in DB2 to get the accurate size of a table. Indeed, the disk space occupied by a table is composed from three parts:

# part size information @
- ------------------ ------------------
1) data (except LOBs) syscat.tables: (npages,fpages) - exact info
2) indexes syscat.indexes: nleafs - partial info
3) LOBs (no information available)

Only the first of the three parts (1: data) can be counted accurate. For part 2 (indexes) we do not know the count of non-leafs pages, and for part 3 (LOBS) we know nothing about their amount.

There is merely the oblique way, proposed in this conference by "P. Saint-Jacques" in 1998 (http://groups.google.ru/group/comp.databases.ibm-db2/msg/0eba45720fd38bfc) for estimation of LOBs size for a whole database.

The thorough investigation of the Table:'Estimate size' action in the modern DB2 Control Center (NT, 8.2.3) uncovers that nothing changed since 1998. The proposed list of numbers is just the direct reflection of parts 1 and 2 information sources, indicated above.

Please, contradict if I am wrong. Maybe some of IBM gurus here could comment this topic ?

Cheers,
--
Konstantin Andreev.

Reply to this Message

Mark A - 06 Jul 2006 01:03 GMT

> Recently I was engaged in the database optimization for one big commercial
> application. During this business I was greatly astound by the fact that
[quoted text clipped - 27 lines]
> --
> Konstantin Andreev.

Yes, it is a bit difficult to precise estimate the size of a database,
especially if you considering the amount of data versus the amount of space
allocated. But a precise estimate of db size has nothing to do with
"database optimization." For that, you only need rough estimates, and a lot
of skill and experience in optimization and performance tuning.

Reply to this Message

Konstantin Andreev - 06 Jul 2006 12:01 GMT

Hello, Mark. Yesterday you wrote:

> Yes, it is a bit difficult to precise estimate the size of a database, especially if you considering the amount of data versus the amount of space allocated. But a precise estimate of db size has nothing to do with "database optimization." For that, you only need rough estimates, and a lot of skill and experience in optimization and performance tuning.

Hello, Mark.

You are double saying "precise estimate", the phrase is internally discrepant. One could want either "estimate" (for some conditions) or "know precise" (for some moment of time).

The main accent of my notion is that : we could not constantly "estimate". Once we have to stop and verify, - Does estimated size correspond to accurate (precise) size AT THIS specific moment ?

Here is the analogy: I estimate I would drive 30-40 miles per hour, but at this moment I drive 37 exactly. Unfortunately, we can't find "speedometer" in DB2 - the accurate table size is unknown.

Cheers,
--
Konstantin Andreev.

Reply to this Message

Ralph Ganszky - 06 Jul 2006 22:57 GMT

Hi Konstantin,

> Hello, Mark. Yesterday you wrote:
>
[quoted text clipped - 21 lines]
> --
> Konstantin Andreev.

have you ever tried the table snapshots from V8.2 syscatv82.snaptab?

Here the description:
Column Type Type
name schema name Length Scale
Nulls

------------------------------ --------- ------------------ -------- ----- ------
SNAPSHOT_TIMESTAMP SYSIBM TIMESTAMP 10 0
Yes
TABSCHEMA SYSIBM VARCHAR 128 0
Yes
TABNAME SYSIBM VARCHAR 128 0
Yes
TAB_FILE_ID SYSIBM BIGINT 8 0
Yes
TAB_TYPE SYSIBM BIGINT 8 0
Yes
DATA_OBJECT_PAGES SYSIBM BIGINT 8 0
Yes
INDEX_OBJECT_PAGES SYSIBM BIGINT 8 0
Yes
LOB_OBJECT_PAGES SYSIBM BIGINT 8 0
Yes
LONG_OBJECT_PAGES SYSIBM BIGINT 8 0
Yes
ROWS_READ SYSIBM BIGINT 8 0
Yes
ROWS_WRITTEN SYSIBM BIGINT 8 0
Yes
OVERFLOW_ACCESSES SYSIBM BIGINT 8 0
Yes
PAGE_REORGS SYSIBM BIGINT 8 0
Yes
DBPARTITIONNUM SYSIBM SMALLINT 2 0
Yes

14 record(s) selected.

As far as I know the name of the snapshot will change in V9, and it only
reports active tables, but may be this is a beginning.

Regards
Ralph

Reply to this Message

Gregor Kovač - 06 Jul 2006 07:05 GMT

> Recently I was engaged in the database optimization for one big commercial
> application. During this business I was greatly astound by the fact that
[quoted text clipped - 13 lines]
> There is merely the oblique way, proposed in this conference by "P.
> Saint-Jacques" in 1998

(http://groups.google.ru/group/comp.databases.ibm-db2/msg/0eba45720fd38bfc)

> for estimation of LOBs size for a whole database.
>
[quoted text clipped - 9 lines]
> --
> Konstantin Andreev.

Or you can just start DB" Control Center. It will tell you the estimated
size of the database (I'm sure of this) and also maybe tables. :)

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message