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

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:

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

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

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:

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

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:

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

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:

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

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

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:

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

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:

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