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]

Re: data space missing in user defined tablespace

Bing Wu wrote:[color=blue]
> 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
>[/color]

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]

Re: data space missing in user defined tablespace

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:[color=blue]
> 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)?
>[/color]

[PM \(pm3iinc-nospam\)]

Re: data space missing in user defined tablespace

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" <bing@biop.ox.a c.uk> a écrit dans le message de
news:brnkdc$c45 $1@news.ox.ac.u k...[color=blue]
> 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[/color]
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?[color=blue]
>
> Thanks,
>
> Bing
>[/color]

[Joachim Klassen]

Re: data space missing in user defined tablespace

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 <bing@biop.ox.a c.uk> wrote in message news:<brnma0$cn 9$1@news.ox.ac. uk>...[color=blue]
> 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:[color=green]
> > 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)?
> >[/color][/color]

[Bing Wu]

Re: data space missing in user defined tablespace

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:[color=blue]
> 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
>[/color]

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