Causes of Tablespace Overflow

Folks, I have an IBM RS6000 model 59H running OnLine 7.10.UC2 with applications written in compiled 4gl 6.01.UD2 Recently, I have had occasion to create a new report which is simply a set of account statements. All of the account statements are created correctly, except one (admittedly a rather large one... but I have much more complex and long reports which work), which always bombs with the message: Program stopped at "otrasumlar.4gl", line number 115. SQL statement error number -567. Cannot write sorted rows. SYSTEM error number -141. ISAM error: TBLspace table overflow I am trying to find out if anyone can give me any clues as to what causes tablespaces to be created. I have looked at all relevant entries I can find in the manuals. I have talked to Tech Support. They tell me just to up the max tablespaces limit since they can't give me a better clue as to why so many are being created. I have already upped the max tablespaces parameter in my onconfig file from 2000 to 3000 to attempt to solve this problem, and I am not comfortable just throwing resources at it. The best I have been able to find is that basically, if all tables in my online instance are open, this would require less than 700 tablespaces. We are not using fragmentation, so the only other reason we should have tablespaces created is for temp tables. We do use temp tables, but not nearly in these numbers, so the upshot is that theoretically, these tablespaces must be getting created by the report as sort temp tables or something. But until I understand what's happening, I can't tune my application to prevent it. I have monitored the tablespaces as the program is running and have found that: Time Number Tablespaces Before program starts 8:23am 79 active Program running 8:32am 90 active 8:37am 103 active 8:44am 116 active 8:50am 129 active 8:58am 146 active 9:04am 544 active 9:11am 1815 active 9:20am 2948 active Program bombs ... 9:26am 113 active The interesting thing is is that the tablespaces created seem to all be of size 0 as the numbers of them explode. To me this seems like I have triggered something which causes the engine to sit, spin it's wheels, and create temp tables until it runs out. I am not convinced that upping the tablespace parameter will help if this is the case. I will continue to work to attempt to isolate the problem, but any clues or suggestions are greatly appreciated! Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (970) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205