Data pages per fragment reached

IHAC who has 4 large tempdbspaces defined (approx 16 gig per dbspace). Customer is doing a select * from a view and after about an hour he gets a -264 error. -264 - Could not write to a temporary file. No ISAM error is generated. He can also get this error by simply doing a dbaccess--->>>Table---->>>Info--->>>Status. He is not running out of tempspace, but found he is running into the max data pages per fragment (16,775,134). How can the customer avoid this? WOuld he need to create more temp dbspaces? Would it be better to create a temp table instead of using a view like they are doing? Any help would greatly be appreciated!! Thanks!

I've had to get around that by creating the table in a dbspace with 8k pages (IDS 10.0). Works for TEMP spaces also. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JON ADAM Sent: Thursday, December 07, 2006 4:13 PM To: ids@iiug.org Subject: Data pages per fragment reached [7937] IHAC who has 4 large tempdbspaces defined (approx 16 gig per dbspace). Customer is doing a select * from a view and after about an hour he gets a -264 error. -264 - Could not write to a temporary file. No ISAM error is generated. He can also get this error by simply doing a dbaccess--->>>Table---->>>Info--->>>Status. He is not running out of tempspace, but found he is running into the max data pages per fragment (16,775,134). How can the customer avoid this? WOuld he need to create more temp dbspaces? Would it be better to create a temp table instead of using a view like they are doing? Any help would greatly be appreciated!! Thanks! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Very good idea....I wish I could use it. I see now I forgot to mention my customer is on 9.40xC8. But thanks much for the idea Bob! :)

The view is creating a temp table for sorting for a GROUP BY or ORDER BY clause. Creating a temp table explicitely will not remove the sort requirement unless you can then index the temp table and update stats so that the sort becomes unneccessary - and that's the rub, the optimizer is free to decide that since all rows from the temp table will be returned the sort is cheaper than using the index. Then the only thing you can do is to use optimizer directives like FIRST_ROWS optimization or a USE_INDEX directive. Still, if the sort work files are larger than 64million pages you're still going to need more than 4 tempdbspaces to hold the temp table. Art S. Kagel ----- Original Message ----- From: Jon Adam <ids@iiug.org> At: 12/07 16:21:54 IHAC who has 4 large tempdbspaces defined (approx 16 gig per dbspace). Customer is doing a select * from a view and after about an hour he gets a -264 error. -264 - Could not write to a temporary file. No ISAM error is generated. He can also get this error by simply doing a dbaccess--->>>Table---->>>Info--->>>Status. He is not running out of tempspace, but found he is running into the max data pages per fragment (16,775,134). How can the customer avoid this? WOuld he need to create more temp dbspaces? Would it be better to create a temp table instead of using a view like they are doing? Any help would greatly be appreciated!! Thanks! ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

So....would using more temp dbspaces get around this? Is that the only option left at this point?

I was just pointing out that either way you would likely need additional temp dbspaces, yes. Whether the temp table solution will ultimately perform the same, better, or worse than the VIEW you can discover only by testing both the VIEW and the temp table versions of the process. Art S. Kagel ----- Original Message ----- From: Jon Adam <ids@iiug.org> At: 12/08 10:14:03 So....would using more temp dbspaces get around this? Is that the only option left at this point? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.