Hi,
We've ran into this problem before, where we hit the limit of the number of pages that can be allocated to a dbspace for a table. It's something like 16million. After that, you can't add anymore pages to the table.

I have a script to warn me when it gets close. Basically just grabbing the pages allocated from an onstat -pt.

We have a table very close now, over 15million pages. My question is this just datapages for the limit or index pages also. The reason is, this table has 2 indexes created with the 'in table' clause.

I am thinking that instead of reorging the whole table into fragments I can just drop the indexes and create them in another dbspace to buy some time.

Would that in effect provide me with a lower number of allocated pages and solve the problem for now ?

Thanks,
floyd

Floyd,

Lets see if I have this straight...

You have a dbspace with ~15million pages.
This dbspace contains both the table and its index.
You ask if you drop the indexes and then recreate them as detached and in adifferent dbspace if you would 'buy more time' ...

Hmmm... I'd be inclined to say that if you remove something from a dbspace that either those pages are removed or at least recycled... so yeah, it should buy you more time.

On a side note... wouldn't moving your indexes to a different dbspace also improve performance too?

But hey! What do I know? I'm not really in to being a physical dba so I could be wrong ...

-G


Date: Tue, 30 Jun 2009 11:51:10 -0500
Subject: max pages allocated issue
From: floyd (AT) fwellers (DOT) com
To: informix-list (AT) iiug (DOT) org

Hi,
We've ran into this problem before, where we hit the limit of the number of pages that can be allocated to a dbspace for a table. It's something like 16million. After that, you can't add anymore pages to the table.

I have a script to warn me when it gets close. Basically just grabbing the pages allocated from an onstat -pt.

We have a table very close now, over 15million pages. My question is thisjust datapages for the limit or index pages also. The reason is, this table has 2 indexes created with the 'in table' clause.

I am thinking that instead of reorging the whole table into fragments I canjust drop the indexes and create them in another dbspace to buy some time.

Would that in effect provide me with a lower number of allocated pages and solve the problem for now ?

Thanks,
floyd

__________________________________________________ _______________
Windows Live™: Keep your life in sync.
http://windowslive.com/explore?ocid=...n_synch_062009

On Jun 30, 11:51*am, "Floyd Wellershaus" <fl... (AT) fwellers (DOT) com> wrote:
It's a limit for any type of page in a partition, so yeah the index
pages count.

Yes, if you drop those indexes those pages should be marked as free
available pages for the table to use for data. You would not see a
decrease in allocated pages since those pages are still allocated to
the table. But you should see an increase in the number of free pages
(as seen in oncheck -pT) based on the number of pages those indexes
are taking up.

Jacques Renaut
IBM IDS Advanced Support
APD Team

"jrenaut" <jprenaut (AT) yahoo (DOT) com> wrote

On Jun 30, 11:51 am, "Floyd Wellershaus" <fl... (AT) fwellers (DOT) com> wrote:
It's a limit for any type of page in a partition, so yeah the index
pages count.

decrease in allocated pages since those pages are still allocated to
the table. But you should see an increase in the number of free pages
(as seen in oncheck -pT) based on the number of pages those indexes
are taking up.


Be aware that dropping an "in table" index is not the instant operation that
dropping a detateched index is. It can take a very long time indeed to
drop, especially in a logged db. I'm not sure if the table is locked for
the duration. But do it well before the table fills: if you are foolish
enough to get into position where it does fill and you need to drop an index
before *anything* can continue on that table it can be very, very stressful
as increasingly senior and more irate executives call you to ask how much
longer it will be before the company can re-start its operations.

Er, apparently ....!

fragmenting the table resolves the problem, of course...

Is this limit still around in version 11+?

I normally keep an eye with something like:
select systabnames.partnum, tabname, npused
from sysptnhdr, systabnames
where sysptnhdr.partnum = systabnames.partnum
and systabnames.dbsname = "dbname"
and npused > 15000000
order by npused desc

The oncheck mentioned is great too.

"DevNull" <gentsch (AT) gmail (DOT) com> wrote

Yes, apparently.
There was a thread on this a few months ago.
Those of us who opined that the limit seems pretty low for modern systems
were told to shut up and put up with it ... ;-)

"DevNull" <gentsch (AT) gmail (DOT) com> wrote


Well, it defers it ....

Does that pick up indexes too?

The answer however is that removing the indexes won't help. According to
Informix support, who I called to make sure, the 16,775 million page limit
is against data pages only.
I was also using the wrong value from the oncheck -pt.
That's bollocks. If the indexes are "in table", as you said in the OP, the
16g limit applies to the data and indexes.

Neil Truby wrote:
Droping an index "in table" of such a big table will take time.
Be aware of that.


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Fernando Nunes wrote:
Also, keeping the index in the same partitions as the data will impact
HDR performance because the BTREE log records will have to be processed
by the same recovery thread as the data log records.