By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,719 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,719 IT Pros & Developers. It's quick & easy.

Tablespace - mysteriously lost pages.

P: n/a
Hello, all.

Let the tablespace SY810T4K is almost full:
---------------
db2 =list tablespaces show detail
...
Name = SY810T4K
Type = Database managed space
Contents = Any data
Total pages = 332800
Useable pages = 332736
Used pages = 268480
---------------

I thoroughly checked that only tables' data (no indexes or LOBs) lie in this tablespace, run statistics for each table in tablespace and computed:

Allocated pages: "sum( fpages ) from syscat.tables" == 256679
(for tables in tablespace SY810T4K)

Surprisingly, this number is not equal to "Used pages" number returned by "list tablespaces" command.
Who may use the rest of the uses pages in tablespace ?

"Used pages" - "Allocated pages" = 268480 - 256679 = 11801 LOST (?) pages.

Thank you in advance,
--
Konstantin Andreev.
Jul 10 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
They are not LOST. When was the last time u ran REORG on the tables
in this tablespace ?
U might need to re-claim the space using REORG

cheers...
Shashi Mannepalli
Konstantin Andreev wrote:
Hello, all.

Let the tablespace SY810T4K is almost full:
---------------
db2 =list tablespaces show detail
...
Name = SY810T4K
Type = Database managed space
Contents = Any data
Total pages = 332800
Useable pages = 332736
Used pages = 268480
---------------

I thoroughly checked that only tables' data (no indexes or LOBs) lie in this tablespace, run statistics for each table in tablespace and computed:

Allocated pages: "sum( fpages ) from syscat.tables" == 256679
(for tables in tablespace SY810T4K)

Surprisingly, this number is not equal to "Used pages" number returned by "list tablespaces" command.
Who may use the rest of the uses pages in tablespace ?

"Used pages" - "Allocated pages" = 268480 - 256679 = 11801 LOST (?) pages.

Thank you in advance,
--
Konstantin Andreev.
Jul 10 '06 #2

P: n/a
"Konstantin Andreev" <pl**********@datatech.ruwrote in message
news:e8**********@dns.comcor.ru...
Hello, all.

Let the tablespace SY810T4K is almost full:
---------------
db2 =list tablespaces show detail
...
Name = SY810T4K
Type = Database managed space
Contents = Any data
Total pages = 332800
Useable pages = 332736
Used pages = 268480
---------------

I thoroughly checked that only tables' data (no indexes or LOBs) lie in
this tablespace, run statistics for each table in tablespace and computed:

Allocated pages: "sum( fpages ) from syscat.tables" == 256679
(for tables in tablespace SY810T4K)

Surprisingly, this number is not equal to "Used pages" number returned by
"list tablespaces" command.
Who may use the rest of the uses pages in tablespace ?

"Used pages" - "Allocated pages" = 268480 - 256679 = 11801 LOST (?)
pages.

Thank you in advance,
--
Konstantin Andreev.
The ANSI Standards Board has set those pages to NULL and you will never see
them again.
Jul 10 '06 #3

P: n/a
Shashi Mannepalli wrote:
They are not LOST. When was the last time u ran REORG on the tables in this tablespace ?
Never, yesterday, a month ago... it does not matter. You are looking in wrong place.
U might need to re-claim the space using REORG
REORG <tablereclaims pages excessively allocated for this <table(fpages - npages). REORG has nothing to do with pages, used in tablespace, but *NOT* allocated by any table. For sure, I have run REORG and RUNSTAT for each table in tablespace, but got just another number of LOST pages:

---------------
db2 =list tablespaces show detail
...
Tablespace ID = 22
Used pages = 268480
---------------

db2 =select sum(fpages) from syscat.tables where tbspaceid=22
1
-----------
270105

LOST: (270105 - 268480) = 11847 pages.
Cheers,
Konstantin Andreev.
>
>Let the tablespace SY810T4K is almost full:
---------------
db2 =list tablespaces show detail
...
Name = SY810T4K
Type = Database managed space
Contents = Any data
Total pages = 332800
Useable pages = 332736
Used pages = 268480
---------------

I thoroughly checked that only tables' data (no indexes or LOBs) lie in this tablespace, run statistics for each table in tablespace and computed:

Allocated pages: "sum( fpages ) from syscat.tables" == 256679
(for tables in tablespace SY810T4K)

Surprisingly, this number is not equal to "Used pages" number returned by "list tablespaces" command.
Who may use the rest of the uses pages in tablespace ?

"Used pages" - "Allocated pages" = 268480 - 256679 = 11801 LOST (?) pages.
Jul 11 '06 #4

P: n/a
DMS managed tablespaces require overhead of one extent per container.
Also, free pages aren't freed up unless you do an offline reorg. You
should see them as pending free on a tablespace snapshot...PG
Konstantin Andreev wrote:
Hello, all.

Let the tablespace SY810T4K is almost full:
---------------
db2 =list tablespaces show detail
...
Name = SY810T4K
Type = Database managed space
Contents = Any data
Total pages = 332800
Useable pages = 332736
Used pages = 268480
---------------

I thoroughly checked that only tables' data (no indexes or LOBs) lie in this tablespace, run statistics for each table in tablespace and computed:

Allocated pages: "sum( fpages ) from syscat.tables" == 256679
(for tables in tablespace SY810T4K)

Surprisingly, this number is not equal to "Used pages" number returned by "list tablespaces" command.
Who may use the rest of the uses pages in tablespace ?

"Used pages" - "Allocated pages" = 268480 - 256679 = 11801 LOST (?) pages.

Thank you in advance,
--
Konstantin Andreev.
Jul 11 '06 #5

P: n/a
DMS managed tablespaces require overhead of one extent per container.

Its actually more than that. The one extent/container you quote is for
the container tag only - this is reflected in the disparity between
'total pages' and 'usable pages'.

There is additional overhead associated with the tablespace's metadata
- both per-tablespace (ie, the SMP extents) and per-object within the
tablespace (ie, the EMP extents for each object).

jsoh

Jul 11 '06 #6

P: n/a
Check this technote

http://www-1.ibm.com/support/docview...=utf-8&lang=en

cheers...
Shashi Mannepalli
Josh Tiefenbach wrote:
DMS managed tablespaces require overhead of one extent per container.

Its actually more than that. The one extent/container you quote is for
the container tag only - this is reflected in the disparity between
'total pages' and 'usable pages'.

There is additional overhead associated with the tablespace's metadata
- both per-tablespace (ie, the SMP extents) and per-object within the
tablespace (ie, the EMP extents for each object).

jsoh
Jul 12 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.