change block size and swap size

[Kuglie]

hi,

i am not an expert in progress and i have a question to you.

we are running a program from mentor graphics with a progress 9.1E db. we have an own program which executes some querys from the db. the db is now nearly 3 gb big and at some querys we get following error message:

Unable to allocate disk block for temp table use [MM - No data block] (7631)

i searching the web what i can do for this and i found out that i should changing the block and the swap size.

now my question: can somebody tell me how and where i can change this????

thanks

 

[Casper]

This is because the query you use forces sql to create temp-tables. And these temp-tables exceed the maximum default size of 500MB. You can do 3 things (in my favorite order):

1. Take a good look at your query with the query plan. and see if the query uses the right indexes. (make also sure that update statistics has been run against your database). Don't use order by if you don't need to.
2. Check if you have enough diskspace for the tmp files (most lilely in your WRKDIR.)
3. If all above isn't the cause then you can put -SQLTempDisk parameter in the startup parameter file for your SQL broker and set it at a higher value then the default 500000. (which is in K units).

I say this in this order because most times this happens it has to do with the query not being correct.

If you plan to move to a more up to date Progress release then don't use this parameter. Lots of stuff has changed in current progress releases. one of the things that changed is a (quote): "new high performance temp data manager". Which uses this parameter in an entirely different context. In 10.1A and higher there is no more need to set this parameter.

HTH,
Casper.

 

[Kuglie]

Hello Casper,

at first thank you for your support!!!

1. All of our queries are optimized, but without success.
2. We have enough diskspace.

And now:

3. I have put -SQLTempDisk in the startup parameter file for our SQL broker and it is working now!!!!


Thanks!

 

[TomBascom]

1. All of our queries are optimized, but without success.

Have you run UPDATE STATISTICS?

If you're having trouble optimizing queries you might also consider starting a thread on that topic and posting an example query.

 

[valeron]

Hi, im new to progress, can anyone advice me how this can be done?

3.If all above isn't the cause then you can put -SQLTempDisk parameter in the startup parameter file for your SQL broker and set it at a higher value then the default 500000. (which is in K units). /
3. I have put -SQLTempDisk in the startup parameter file for our SQL broker and it is working now!!!!

Thanks...

Hi, I'm using Progress9.1D and I've tried to add the -sqltempdisk 1000000 into startup.pf, however, I'm getting an error... did I miss anything?

 

[valeron]

HELP, Anyone?

Hi.. anyone can help me out?
I tried adding this -SQLTempStoreDisk 100000000 to the .pf file, but it doesnt work...