SEO

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.d...ba45720fd38bfc) 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.

"Konstantin Andreev" <plafcow4odno@datatech.ru> wrote in message
news:e8h6o7$s5g$1@dns.comcor.ru...
> 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.d...ba45720fd38bfc)
> 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.

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.

Konstantin Andreev wrote:

> 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.d...ba45720fd38bfc)
> 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.

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

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gregor.Kovac@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

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.

Hi Konstantin,

"Konstantin Andreev" <plafcow4odno@datatech.ru> wrote in message
news:e8iq0g$nsi$1@dns.comcor.ru...
> 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.

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