473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 3131
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"."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 "DB2ADMIN"."COORDINATE" APPEND ON;

-- DDL Statements for indexes on Table "DB2ADMIN"."COORDINATE"

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

ALTER TABLE "DB2ADMIN"."COORDINATE"
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.ac.uk> a écrit dans le message de
news:br**********@news.ox.ac.uk...
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.ac.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"."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 "DB2ADMIN"."COORDINATE" APPEND ON;

-- DDL Statements for indexes on Table "DB2ADMIN"."COORDINATE"

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

ALTER TABLE "DB2ADMIN"."COORDINATE"
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"."COORDINATE"

CREATE UNIQUE INDEX "IDX_COORDINATE2" 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_COORDINATE1" ON "COORDINATE"
("FID" ASC,
"AID" ASC)
PCTFREE 10 ALLOW REVERSE SCANS;

-- DDL Statements for primary key on Table "DB2ADMIN"."COORDINATE"

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
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...
2
by: Bing Wu | last post by:
I have a user defined tablespace: Tablespace ID = 4 Name = DATASPACE Type = Database managed space...
8
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...
2
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...
5
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,...
2
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...
10
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...
5
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...
5
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....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.