PSORT_DBTEMP not working after upgrade to 14.10
Ivan Bednar's sort script using PSORT_DBTEMP stopped sorting to disk after moving from 11.7 to 14.10FC7, with growing memory and error -208. Posters suggested checking DS_ and SHM_ settings, explain output, PDQ and edition limits. Andreas Legner reported it was a hash join issue, where overflow to disk no longer worked, tracked as APAR IT41351 with a workaround to follow.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
For some long time we were using script on Informix 11.7:
#!/bin/ksh
onmode -wm MAX_PDQPRIORITY=20
export PSORT_DBTEMP=/SORTexport PSORT_NPROCS=4
dbaccess -e DB <<ENDexecute function sort ();END
onmode -wm MAX_PDQPRIORITY=1After upgrade to 14.10FC7 sorting to filesystem is not working, filesystem /SORT is always empty,
server is allocating more and more memory ... and we are getting ERROR: -208 :-((
Thank you for any comments.
Regards,
Ivan Bednar
------------------------------
Ivan Bednar
------------------------------
#Informix
Hi,
What are the DS_ and SHM_ onconfig parameters set to on each instance?
Try with SET EXPLAIN ON, is the plan different between the instances?
Are any environment variables different between the instance? (onstat -g env)?
What does onstat -g ses for the session give whilst the sort is running?
What does onstat -g mgm give whilst the query is running?
Regards,
David.
------------------------------
David Williams
------------------------------
Hi Ivan,
I guess the optimizer decides to use the memory sort - which is certainly an advantage. Try the points David has written. And maybe a few additional ideas:
Change SHMTOTAL ( 0 or a value)
PSORT_NPROCS=14
Monitors with onstat -g seg if new segments are created or how the memory usage is, play with the DS_TOTAL_MEMORY values.
And if you prefer to stay with the disk sort, set PDQPRIORITY to 4.
The sqexplain.out will give you further hints.
Have fun
Henri
------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021 and 2022
------------------------------
Hi Henri,
Thank you for your recommendations.
1. In our function we
as one of the first things
running this type of select into temp:
select --+ USE_HASH (b) FULL (a)
a.aaa, ....
from a, b
where a.id = b.id and ( ....)
into temp diff_tmp with no log;
??This tables a,b are very huge, in 11.7 enviroment this query takes more then 300GB space in???? PSORT_DBTEMP filesystem,
so we must stay with disk sort?? :-(
2. I tried to experiment with??
PSORT_NPROCS ( not to set, 0, 4, 14 ) and with
PDQPRIORITY ( 100 [from MAX_PDQPRIORITY=20 ], 4, 0 )
but result was always the same, onstat -g seq -r 1?? was showing more and more consumption of virtual memory segments
and it ended in error -208: Memory allocation failed
It looks that there is problem, that Informix server??
does not accept the PSORT_DBTEMP variable setting :-((
We also opened case in IBM, but I hoped, that we are not only
ones with such experience.
Ivo
Hi Ivan, what's about using temp dbspaces , table fragmentation, reduce DS_TOTAL_MEMORY and PDQ , set SHMTOTAL to a limit that fits to the machine? The optimizer decides to use Memory Sort - that's the difference to the past. If you come back to disk sort, the PSORT_DBTEMP will be used (or better temp dbs :-) Have fun Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 and 2022 ------------------------------
Just a thought, what edition are you running on ? If this is not Enterprise or Advanced Enterprise, but Workgroup, it might be that parallel data query has been possible in 11.70 (though not allowed in growth edition) and is now restricted due to edition limits.
Hi Henry, This problem task we run only 2x in a month. And as I wrote this task requires a large amount of memory or space. We do not have that much memory available and therefore we have used PSORT_DBTEMP. I understand that there may have been some changes in the optimizer, but there should be a way to impose my strategy on him - some directives. Is there something (directive for example ) that will force the optimizer to use PSORT_DBTEMP variable? Ivo
Marcus, We have full version: Ivo
Hi Ivo,
use "set explain on avoid_execute;" to see what the optimizer plan to do.
Reduce the resources for the execution:
set pdqpriority 4 or 2 ....DS_TOTAL_MEMORY to a small valueThat forces him onto the disk sort.
Regards,
Henri
------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021 and 2022
------------------------------
PSORT_DBTEMP is working fine for me on 14.10.FC7W1DE and 14.10.FC8DE, don't have 14.10.FC7 itself available. ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Hi, Did you get a resolution of this or open a support case? Regards, David. ------------------------------ David Williams ------------------------------
We opened the case. Ivo ------------------------------ Ivan Bednar ------------------------------
Please provide the case# on this. ------------------------------ Scott Pickett WW Informix Lab Services spickett@us.ibm.com ------------------------------
This turned out to be a problem with hash joins rather than with sorting, and it's not about PSORT_DBTEMP, but rather hash table overflowing no longer working (and hence PSORT_DBTEMP not even considered). Watch out for APAR IT41351: HASH JOINS REQUIRING OVERFLOW NOW FAILING WITH -208 There's also a workaround going to be mentioned in that APAR. ------------------------------ Andreas Legner ------------------------------