RE: Informix: external optimizer directives not working ✓ Answered: green (solid confidence) — Jose reports the root cause (onstat -g ses output reformats SQL text with extra whitespace, breaking sysdirectives text matching) and a confirmed working fix (build the directive INSERT from sqexplain.out or onstat -g stm output instead); Art Kagel's reply is a tangential RFE suggestion, not a further answer. Note: the original question that this is replying to is not itself captured in this 2-message excerpt.

Advisory only.

Hi, just for your info, we fixed this issue: -. I had used the sql syntax text extracted from onstat -g ses output. It seems this command formats the text and the directive gets created with extra spaces, carriage returns, etc. and when the same SQL gests executed it does not match the SQL created in sysdirectives. -. Solution: using 'sysdbsopen' execute a 'set explain on' for the user, or run a dynamic explain, and create the 'insert into sysdirectives' directly using the sqexplain.out file, and with care for not modifying the query itself. You can also use 'onstat -g stm sid' for the running stmt and prepare your insert into sysdirectives from there. Best regards! Jose ------------------------------ Jose ------------------------------

This makes me want to enter an RFE request for a change to how external directives work such that insignificant white space is ignored when attempting to match the saved directive's SQL to a running query. Don't have time right now, but if someone enters such a request, I'd vote for it. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------