473,604 Members | 2,632 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.
Jul 5 '06 #1
4 2027
"Konstantin Andreev" <pl**********@d atatech.ruwrote in message
news:e8******** **@dns.comcor.r u...
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.
Jul 6 '06 #2
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 | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Jul 6 '06 #3
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 "speedomete r" in DB2 - the accurate table size is unknown.

Cheers,
--
Konstantin Andreev.
Jul 6 '06 #4
Hi Konstantin,

"Konstantin Andreev" <pl**********@d atatech.ruwrote in message
news:e8******** **@dns.comcor.r u...
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 "speedomete r"
in DB2 - the accurate table size is unknown.

Cheers,
--
Konstantin Andreev.
have you ever tried the table snapshots from V8.2 syscatv82.snapt ab?

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

------------------------------ --------- ------------------ -------- ----- ------
SNAPSHOT_TIMEST AMP 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_PAG ES SYSIBM BIGINT 8 0
Yes
INDEX_OBJECT_PA GES SYSIBM BIGINT 8 0
Yes
LOB_OBJECT_PAGE S SYSIBM BIGINT 8 0
Yes
LONG_OBJECT_PAG ES SYSIBM BIGINT 8 0
Yes
ROWS_READ SYSIBM BIGINT 8 0
Yes
ROWS_WRITTEN SYSIBM BIGINT 8 0
Yes
OVERFLOW_ACCESS ES 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
Jul 6 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4474
by: Paul | last post by:
Hi: I am totally new in Sun Solaris. I am not sure someone would like to tell me how to rellocate Sun Solaris disk space where oracle installed? I installed Oracle 8i in Sun Solaris and set Oracle main table space called TABLES in unix disk space /dpp/live01. From the following, it's quite easy to see that the disk space of /dpp/live01 is 97%.Therefore how can i move some disk space of SWAP to /dpp/live01 without losing
3
6544
by: es22 | last post by:
Hi, I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data? How many rows can mysql handle in one table?
36
4636
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am proposing a single column table with a field name called vehicle_type and this will contain the vehicle type. Sot it will be
5
7551
by: Yasaswi Pulavarti | last post by:
does a command like, db2 drop table tabschema.tabname when run from the Aix prompt reclaim the disk space? Are there any other options? How can we make sure the disk space is reclaimed? Thanks, Yasaswi
3
3003
by: datapro01 | last post by:
Running db2 8.1.1 on aix 5.1. Tried to do an offline reorg on the system tables and could not reorg SYSIBM.SYSCOLDIST at about 3.5 million rows. Apparently there was not enough space in the default SYSCATSPACE tablespace in which DB2 could create the 'shadow' table. We are trying to determine the space we would need in a temp tablespace that would be specified in the reorg command. I know I've seen the
4
9391
by: sandip | last post by:
Hi, Can anyone tell me how to calculate the ctual disk space needed for a table? The record length and number of records are known. A rough estimate of the disk space would suffice. Please help. Thanks, Sandip.
7
1597
by: Barry | last post by:
Hi All, I am a newcommer to Postgresql, currently I am looking at moving a Pick based application across to PostgreSQL. I am using RH Linux and Postgresql 7.3.6 The test system I am using has a 2 channel raid card with a disk pack connected to each channel. The OS and Postgresql sits on its own internal disk.
5
2164
by: Konstantin Andreev | last post by:
Recently I became interested, - Are the data, bulk loaded in the table with LOAD utility, consume the same disk space as loaded with IMPORT utility? The answer turned out to be NOT ! Here is a nutshell description of the test. The testing was done at "DB2/LINUX 8.2.3". Tables for tests: F4106 has 5203 rows, 32 columns. F42199 has 1399252 rows, 245 columns.
7
2289
by: elgiei | last post by:
Good morning at all, i have to implement a server,that every n-seconds (eg. 10sec) sends to other clients,which files and directory has been deleted or modified. i build a n-tree, for each files on harddisk there's a node into n- tree, this solution is not good for large hard disk.. and i can't use inotify (it's forbidden), and only c solutions are accepted without third party software or external calls.
0
7997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8419
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8406
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8065
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8274
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6737
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5882
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3906
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
1262
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.