473,837 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

data space missing in user defined tablespace

I have a user defined tablespace:

Tablespace ID = 4
Name = DATASPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 15360000
Useable pages = 15359808
Used pages = 15359808
Free pages = 0
High water mark (pages) = 15359808
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 64
Number of containers = 3

This is 60GB in size and currently hosts a single table with 1.75 billion records (20 bytes per row). So that should be using 35GB of tablespace but instead, it almost used up 60GB space. The indexes are stored in a separate tablespace. Any idea what goes wrong here?

Thanks,

Bing

Nov 12 '05 #1
5 3162
Ian
Bing Wu wrote:
I have a user defined tablespace:

Tablespace ID = 4
Name = DATASPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 15360000
Useable pages = 15359808
Used pages = 15359808
Free pages = 0
High water mark (pages) = 15359808
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 64
Number of containers = 3

This is 60GB in size and currently hosts a single table with 1.75
billion records (20 bytes per row). So that should be using 35GB of
tablespace but instead, it almost used up 60GB space. The indexes are
stored in a separate tablespace. Any idea what goes wrong here?

Thanks,

Bing


Are there other tables in the same tablespace? Are you sure the indexes
exist in a separate tablespace (perhaps there was a mistake in the DDL when
creating the table)?


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #2
Thanks for the reply. Just confirm that there is only ONE table in the DATASPACE tablespace. I have attched the DDL for this.

Cheers,

Bing
-- DDL

CREATE TABLE "DB2ADMIN"."COO RDINATE" (
"FID" INTEGER NOT NULL ,
"AID" INTEGER NOT NULL ,
"X" REAL NOT NULL ,
"Y" REAL NOT NULL ,
"Z" REAL NOT NULL )
IN "DATASPACE" INDEX IN "IDXSPACE" ;
ALTER TABLE "DB2ADMIN"."COO RDINATE" APPEND ON;

-- DDL Statements for indexes on Table "DB2ADMIN"."COO RDINATE"

CREATE UNIQUE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
("FID" ASC,
"AID" ASC)
PCTFREE 10 ALLOW REVERSE SCANS;
-- DDL Statements for primary key on Table "DB2ADMIN"."COO RDINATE"

ALTER TABLE "DB2ADMIN"."COO RDINATE"
ADD CONSTRAINT "PK_COORDINATE8 " PRIMARY KEY
("FID",
"AID");
Ian wrote:
Are there other tables in the same tablespace? Are you sure the indexes
exist in a separate tablespace (perhaps there was a mistake in the DDL
when creating the table)?


Nov 12 '05 #3
F2 formula of reorgchk? (effective space utilization)

Did you load/insert/import the data?
Do you sometimes delete records? (reorg with cleanup option)
(implying also : did you load/insert/import then delete then reload.)

Not sure what happens to space utilisation when append mode is on and let's
say
you have a changed page threshold of 60%.
PM

"Bing Wu" <bi**@biop.ox.a c.uk> a écrit dans le message de
news:br******** **@news.ox.ac.u k...
I have a user defined tablespace:

Tablespace ID = 4
Name = DATASPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 15360000
Useable pages = 15359808
Used pages = 15359808
Free pages = 0
High water mark (pages) = 15359808
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 64
Number of containers = 3

This is 60GB in size and currently hosts a single table with 1.75 billion records (20 bytes per row). So that should be using 35GB of tablespace but
instead, it almost used up 60GB space. The indexes are stored in a separate
tablespace. Any idea what goes wrong here?
Thanks,

Bing

Nov 12 '05 #4
The table is created with the APPEND ON attribute which means that new
records will be added to pages at the end of the table, regradless if
there is freespace in other pages.
The calculation that 1.75 billion records each 20n bytes need 35 GB of
diskspace is not correct in a relational database. there is overhead
for each record (6 -8 bytes ??) and overhead on each page which must
be considered.
control center has a function ESTIMATE SIZE which calculates the
needed space including any overhead. for a short test it tells me that
it will need roughly 52 GB to store your table.

HTH
Joachim

Bing Wu <bi**@biop.ox.a c.uk> wrote in message news:<br******* ***@news.ox.ac. uk>...
Thanks for the reply. Just confirm that there is only ONE table in the DATASPACE tablespace. I have attched the DDL for this.

Cheers,

Bing
-- DDL

CREATE TABLE "DB2ADMIN"."COO RDINATE" (
"FID" INTEGER NOT NULL ,
"AID" INTEGER NOT NULL ,
"X" REAL NOT NULL ,
"Y" REAL NOT NULL ,
"Z" REAL NOT NULL )
IN "DATASPACE" INDEX IN "IDXSPACE" ;
ALTER TABLE "DB2ADMIN"."COO RDINATE" APPEND ON;

-- DDL Statements for indexes on Table "DB2ADMIN"."COO RDINATE"

CREATE UNIQUE INDEX "DB2ADMIN"."IDX _COORDINATE1" ON "DB2ADMIN"."COO RDINATE"
("FID" ASC,
"AID" ASC)
PCTFREE 10 ALLOW REVERSE SCANS;
-- DDL Statements for primary key on Table "DB2ADMIN"."COO RDINATE"

ALTER TABLE "DB2ADMIN"."COO RDINATE"
ADD CONSTRAINT "PK_COORDINATE8 " PRIMARY KEY
("FID",
"AID");
Ian wrote:
Are there other tables in the same tablespace? Are you sure the indexes
exist in a separate tablespace (perhaps there was a mistake in the DDL
when creating the table)?

Nov 12 '05 #5
Now this is being a real pain :-(. Then the calculation would be:

TABLE: COORDINATE 52GB
IDX: IDX_COORDINATE1 54GB

And I'm adding a new index IDX_COORDINATE2 :

-- DDL Statements for indexes on Table "DB2ADMIN"."COO RDINATE"

CREATE UNIQUE INDEX "IDX_COORDINATE 2" ON "COORDINATE "
("AID" ASC)
PCTFREE 10 ALLOW REVERSE SCANS;

Now this will take up about another 50GB. Gosh! I can't believe that 35GB data turns out to be more than 150GB in DB2 and I'm running out of space again. Am I doing wrong? Any good suggestion?

Thanks a lot,

Bing

Joachim Klassen wrote:
The table is created with the APPEND ON attribute which means that new
records will be added to pages at the end of the table, regradless if
there is freespace in other pages.
The calculation that 1.75 billion records each 20n bytes need 35 GB of
diskspace is not correct in a relational database. there is overhead
for each record (6 -8 bytes ??) and overhead on each page which must
be considered.
control center has a function ESTIMATE SIZE which calculates the
needed space including any overhead. for a short test it tells me that
it will need roughly 52 GB to store your table.

HTH
Joachim


------------------------------------------------
-- DDL Statements for table "COORDINATE "
------------------------------------------------

CREATE TABLE "COORDINATE " (
"FID" INTEGER NOT NULL ,
"AID" INTEGER NOT NULL ,
"X" REAL NOT NULL ,
"Y" REAL NOT NULL ,
"Z" REAL NOT NULL )
IN "DATASPACE" INDEX IN "IDXSPACE" ;
ALTER TABLE "COORDINATE " APPEND ON;

-- DDL Statements for indexes on Table "COORDINATE "

CREATE UNIQUE INDEX "IDX_COORDINATE 1" ON "COORDINATE "
("FID" ASC,
"AID" ASC)
PCTFREE 10 ALLOW REVERSE SCANS;

-- DDL Statements for primary key on Table "DB2ADMIN"."COO RDINATE"

ALTER TABLE "COORDINATE "
ADD CONSTRAINT "PK_COORDINATE8 " PRIMARY KEY
("FID",
"AID");

Nov 12 '05 #6

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

Similar topics

3
20832
by: Oliver Stratmann | last post by:
Hello All! I'm looking for a description of the needs of space for a certain data-type. How much space does e.g. a NUMERIC(1,0)- field take? (??? Bytes?) Is there a listing in the DB2-online-documentation? I couldn't find any? I'm sorry to bother you folks! Bye!
2
554
by: Bing Wu | last post by:
I have a user defined tablespace: Tablespace ID = 4 Name = DATASPACE Type = Database managed space Contents = Any data State = 0x0000 Detailed explanation: Normal Total pages = 15360000
8
2585
by: Joachim Mueller | last post by:
Have written a litte sql for showing the utilization of the tablespace. (found it within the newsgroup from Paul Vernon). Looks fine for an one Partition System only. But how i can I see on which Partiton the Tablesapces reside. select substr(TABLESPACE_NAME,1, 18) as TABLESPACE_NAME, case when TABLESPACE_TYPE = 0 then 'DMS' else 'SMS' end as TBS_TYPE,
2
16488
by: Oliver Stratmann | last post by:
Hello All! I've got a problem with our DB2/NT 8.1.0 Database. The following SELECT on a big Table (2,5 Million rows) finishes with the Error "Unable to allocate new pages in table space "TEMPSPACE1".". Sometimes some rows are displayed. We tried all the hints of the Docs but it won't work. Can anybody give me a hint in which way this problem should be analyzed? I'm sorry ti bother You, but this is the last idea i've had.
5
7590
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
2
3616
by: Pablo | last post by:
I have observed a significant discrepancy between the amount of space used in a long tablespace when using import as compared to load. Can anyone explain the following? In an attempt to move data from one table/tablepace to another observed the following: Exported lobfile = 291 MB (average 60K / CLOB object)
10
7834
by: Hemant Shah | last post by:
Folks, I am using DB2 UDB 8.1 on RedHat ES 2.1. In order to improve performance of one of our table, I would like to preload some or all of the data into memory when I start DB2. Is there a way to do it? Thanks.
5
3411
by: rAinDeEr | last post by:
Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character text field. I want to know which Data type I need to use so that it doesnt waste memory. thanks in advance, rAinDeEr
5
6569
by: Troels Arvin | last post by:
Hello, Every so often, I'm asked to help people recover data from tables that were either dropped or where to much data was DELETEed. The complications related to restoring data are a problem. The SAS users are laughing because they can (to a certain extend) easily go back to an earlier SAS table by simply starting dsm. Of course, a flat file table is different than a relational table; but still, the trouble related to restoring DB2...
0
9851
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
9695
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10902
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
10583
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...
0
9420
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...
0
7013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4060
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3128
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.