max pages allocated issue

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

On Jun 30, 11:51 am, "Floyd Wellershaus" <fl...@fwellers.com> wrote: > 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. It's a limit for any type of page in a partition, so yeah the index pages count. > > 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 ? 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@yahoo.com> wrote in message news:ef7ea59b-1514-4216-83a1-fd2f0b8c9564@f30g2000vbf.googlegroups.com... On Jun 30, 11:51 am, "Floyd Wellershaus" <fl...@fwellers.com> wrote: > 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. It's a limit for any type of page in a partition, so yeah the index pages count. > > 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 ? >> 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. 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@gmail.com> wrote in message news:0a2e2914-58f3-4d70-bbf6-3d77c8b898df@s9g2000yqd.googlegroups.com... > fragmenting the table resolves the problem, of course... > > Is this limit still around in version 11+? 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@gmail.com> wrote in message news:0a2e2914-58f3-4d70-bbf6-3d77c8b898df@s9g2000yqd.googlegroups.com... > fragmenting the table resolves the problem, of course... Well, it defers it .... > 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 Does that pick up indexes too?