Informix

 View Only
Expand all | Collapse all

PSORT_DBTEMP not working after upgrade to 14.10

  • 1.  PSORT_DBTEMP not working after upgrade to 14.10

    Posted Wed May 25, 2022 12:20 PM
    For some long time we were using script on Informix 11.7:
    #!/bin/ksh
    onmode -wm MAX_PDQPRIORITY=20
    export PSORT_DBTEMP=/SORT
    export PSORT_NPROCS=4
    dbaccess -e DB <<END
    execute function sort ();
    END
    onmode -wm MAX_PDQPRIORITY=1

    After 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


  • 2.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Wed May 25, 2022 01:29 PM


    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
    ------------------------------



  • 3.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Thu May 26, 2022 03:02 AM
    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
    ------------------------------



  • 4.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    Posted Thu May 26, 2022 05:39 AM

    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


    Bez vir??. www.avg.com





  • 5.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Thu May 26, 2022 06:11 AM
    Edited by System Fri January 20, 2023 04:12 PM
    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
    ------------------------------



  • 6.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    Posted Thu May 26, 2022 07:16 AM

    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


    Bez virů. www.avg.com





  • 7.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Fri May 27, 2022 01:40 AM
    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 value

    That forces him onto the disk sort.

    Regards,
    Henri


    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 and 2022
    ------------------------------



  • 8.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Thu May 26, 2022 07:04 AM
    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.

    MARCUS HAARMANN






  • 9.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    Posted Thu May 26, 2022 07:26 AM

    Marcus,

    We have full version:


    Ivo


    Bez virů. www.avg.com





  • 10.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    Posted Fri May 27, 2022 11:28 AM
    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
    ------------------------------



  • 11.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Wed June 22, 2022 07:16 AM

    Hi,

    Did you get a resolution of this or open a support case?

    Regards,
    David.



    ------------------------------
    David Williams
    ------------------------------



  • 12.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    Posted Wed June 22, 2022 07:55 AM
    We opened the case.

    Ivo

    ------------------------------
    Ivan Bednar
    ------------------------------



  • 13.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    Posted Wed June 22, 2022 10:09 AM
    Please provide the case# on this.

    ------------------------------
    Scott Pickett
    WW Informix Lab Services
    spickett@us.ibm.com
    ------------------------------



  • 14.  RE: PSORT_DBTEMP not working after upgrade to 14.10

    IBM Champion
    Posted Tue June 28, 2022 08:34 AM
    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
    ------------------------------