data space missing in user defined tablespace

[Bing Wu]

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

[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! =-----

[Bing Wu]

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)?

[PM \(pm3iinc-nospam\)]

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

[Joachim Klassen]

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)?

[Bing Wu]

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");