Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  external optimizer directives not working

    Posted 4 days ago

    Hi, after changing onconfig parameter EXT_DIRECTIVES=2 and executing

    SAVE EXTERNAL DIRECTIVES {+AVOID_HASH (table1, table2, table3,....)}
    ACTIVE FOR
      SELECT  ......

    I execute the query with dbaccess and user informix and it gets optimized with the directive, BUT when the exactly same query gets executed with a different user and a JDBC connection it is not using the directive. The session runs (onstat -g ses) with directives=1, same as when i run it with user informix and dbaccess. 

    Am i missing something here ?

    Thanks for any help!

    Jose

      



    ------------------------------
    Jose
    ------------------------------


  • 2.  RE: external optimizer directives not working

    Posted 4 days ago

    Jose:

    What are you seeing from this query?

    dbaccess sysmaster -

    > select * from sysconfig where cf_name = 'EXT_DIRECTIVES';

    cf_id         234
    cf_name       EXT_DIRECTIVES
    cf_flags      163906
    cf_original   1
    cf_effective  1
    cf_default    0

    1 row(s) retrieved.

    What is cf_effective showing?

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: external optimizer directives not working

    Posted 4 days ago

    Hi Art,

    I am getting: 

    cf_id         232
    cf_name       EXT_DIRECTIVES
    cf_flags      163906
    cf_original   2
    cf_effective  2
    cf_default    0

    1 row(s) retrieved.

    Thanks!



    ------------------------------
    Jose
    ------------------------------

    Original Message


  • 4.  RE: external optimizer directives not working

    Posted 4 days ago

    Jose:

    OK, so the setting has taken (I always like to check the "dumb" stuff). The "directives=1" you see in the onstat -g ses output shows that the session is supposed to follow directives. So, the next step would be to run the query as one of the users that are getting a different query plan under SET EXPLAIN and see if the directives are being specifically ignored. If it is not being ignored explicitly then something is not right.

    One possibility would be if these sessions are connected to a different database than the one into which you added the directive (external directives are stored in the sysdirectives catalog table in each database). In that case the session would not be able to "see" the directive in the "remote" database.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


Related Content