RE: sysmaster query help
Huh? Ok... I'm going to assume that these are intermediate tables used in a load/aggregation/etc type process. So I have to ask... why they just don't create temp tables? Assuming that you persist the connection between the processes. The other option would be to have them create their temp tables in a specific dbspace/table space. Then at the end of the day, you run a process that will drop the temp tables that were created before that day. (Just in case someone was in the middle of running a process and the whole process completes within 24 hours.) Date: Thu, 12 Nov 2009 08:14:34 -0600 Subject: RE: sysmaster query help From: floyd@fwellers.com To: im_gumby@hotmail.com; art.kagel@gmail.com CC: informix-list@iiug.org They are just not cleaning up after themselves. we have a weird method of creating submission files. Each submission file gets it's own 'temporary' database table ( or 3 ). In production we have an archive/removal process to try and keep these tables down, but it's still too many. You should see how big our systabauth table is. Actually I think I did something a while back to make the extent size of that bigger due to this. ----- Original Message ----- From: im_gumby@hotmail.com Sent: Thu, November 12, 2009, 7:51 AM Subject: RE: sysmaster query help I'd look to see what's causing the tables to be created. Are your developers using tables when they should be using temp tables? Or are they programming in java and are using some form of persistence incorrectly? Then there's another possibility. Are all of your developers using the same database instead of getting their own databases in the same instance? From: art.kagel@gmail.com Date: Wed, 11 Nov 2009 18:38:33 -0600 Subject: Re: sysmaster query help To: floyd@fwellers.com CC: informix-list@iiug.org 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 Hotmail: Trusted email with Microsoft's powerful SPAM protection. Sign up now. _________________________________________________________________ Bing brings you maps, menus, and reviews organized in one place. http://www.bing.com/search?q=restaurants&form=MFESRP&publ=WLHMTAG&crea=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1