RE: sysmaster query help
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
Floyd Wellershaus wrote: > 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. > 116000 tables is a lot really... But some ERPs may have more. In one current project the count is around 130000... And you're right about your concern relative to systabauth, but please check the others... To give you an example (4KB pages): tabname nptotal total_kb npused npdata systables 5508 22032 4549 2768 syscolumns 25258 101032 23259 15992 sysindexes 9508 38032 7289 3893 systabauth 3008 12032 2346 814 sysconstraints 58758 235032 58692 22689 syscoldepend 25258 101032 22487 6330 sysdistrib 11232 44928 11232 9959 sysobjstate 68758 275032 59128 15963 Of course your environemnt may be different... But check all the catalog tables... Regards.