Re: sysmaster query help
116,000 tables?! Your developers have entirely too much time on their hands dude! You need to schedule more meetings to slow them down! ;-) Didi anyone look to see if the TABLESPACE TABLESPACE (or partition table) is out of extents? That would be my guess. In sysmaster:systabnames, TABLESPACE TABLESPACE entries have a tablename that's the same as the dbspace they reside in. See how many extents there are for that dbspace's partition table. If that is it, then the solution, as IBM suggested, is to reorg the dbspace with a full export, drop the dbspace, recreate the dbspace using the -ef & -en options to set the size of the partition table's extents (so you destroy the existing fragmented partition table) and reload the data. Yes, I know that you aren't trying to create any tables, but maybe your queries are attempting to create logged temp tables in that dbspace. Anyway, worth a look-see. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Nov 11, 2009 at 6:23 PM, Floyd Wellershaus <floyd@fwellers.com>wrote: > > Thank you. I can make do with that much. > FYI, we're hitting some weird behavior. Getting a 136 error for tables in > a certain dbspace. But those tables have plenty of extents left. They are > nowhere near out of extents or near the 16million page limit. > So far IBM is stumped and has asked me to totally reorg the dbspace just > for GP. > I am resisting that because there are over 116,000 tables in it. > > Weird. Maybe having too many tables in a dbspace can cause some issue. Not > sure. > > > > > ----- Original Message ----- > From: "Fernando Nunes" <domusonline@gmail.com> > Sent: Wed, November 11, 2009 17:55 > Subject:Re: sysmaster query help > > > Floyd Wellershaus wrote: > > Does anyone have a query that will provide the Number of pages allocated > > for each partition in the database, along with the tablename/indexname > > that the partition belongs to, and the partiton name itself ? > > > > Thanks. > > Floyd > > Check systabnames and sysptnhdr. > They can be connected by partnum. And you can connect all the partitions > of a table because they all have the same lockid. The partition where > partnum=lockid defines the table name... > > I would have to start an engine, but maybe something like: > > > SELECT > ( > SELECT > SUM(p1.nptotal) > FROM > sysptnhdr p1 > WHERE > p1.lockid = p2.lockid > ), > t.tabname > FROM > systabnames t, sysptnhdr p2 > WHERE > p2.partnum = t.partnum AND > p2.lockid = p2.partnum > > > Hmmm... this would mix up index partitions and data partitions in the > same SUM, for each table... > If you need to split data from pages it could be harder... > Of course (and it's probably better) you can start with the database > catalog (systables, sysfragments, sysindices) and then join with the two > tables above (or possibly only with sysptnhdr)... > > Regards. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > > > ----- End of original message ----- > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >