RE: sysmaster query help

Eeewww! Ok... BTW, I'm cc'ing this to the list because you're not the only one who may see this problem and it could have been avoided if they did some thing different. I guess its kind of late to change your import and aggregation process, but you may be able to modify it, if you can tweak the code. I'd recommend writing a stored procedure that returns a different table space name so you can then modify your program to create the table in that table space. Or you could change your schema(s) slightly to use the same table for all of the similar import processes, and add a job_id or time_stamp or both as new columns. Then you could pull your data from the single table per job. Every day, you then run a batch process that purges any records that are 30 days old or older. (Run it once a week if necessary.) That could help save you ... I'm sure there are other options... This was just something I just thought about as I type... HTH -G Date: Thu, 12 Nov 2009 10:35:37 -0600 Subject: RE: sysmaster query help From: floyd@fwellers.com To: im_gumby@hotmail.com; art.kagel@gmail.com CC: informix-list@iiug.org You are correct, except that our aggregation process, the lifecycle of those tables is generally around 5-20 days. Some longer. So they are temporary, but do hang around for way longer than any session or even any day. They are all in the same dbspace. That is part of the problem. If I had multiple dbspaces to put them in and have the program designed to round robin the location of them ( they are dynamically created ) then it would help, but that's not happening either. In the end, if we get even busier , I may have to reorg the dbspace they go in, and as Art said, create a larger extent size for the partition tables. I just forced an assertfailure and sent IBM the output so they can determine exactly which resource is trying to make an extent, but I think they will come up with the answer Art gave. I suppose it may also help temporarily to have the indexes created in a separate space. The whole schema really stinks as far as I'm concerned. It was designed a long time ago when we weren't this big or busy. I hate having my sysmaster queries take so long because it's loaded with hundereds of thousands of tables info. ----- Original Message ----- From: im_gumby@hotmail.com Sent: Thu, November 12, 2009, 11:08 AM Subject: 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.tabnam