Informix external directives ?

Hi all, Is it possible to use Informix external directives for statements with host variables ? It works fine for statements without host variables. Now, I need to solve a problem for a statement with a host variable. e.g. ... WHERE (a0.t_ncst > ?) If I add a statement with host variables my directive is not used :-( I am afraid that I can not benefit from external directives if host variables are used. Can someone confirm this ? Regards ... Joerg

Joerg Rueschenschmidt schrieb: > Hi all, > > Is it possible to use Informix external directives for statements with > host variables ? > > It works fine for statements without host variables. > > Now, I need to solve a problem for a statement with a host variable. > > e.g. > > ... WHERE (a0.t_ncst > ?) > > If I add a statement with host variables my directive is not used :-( > > I am afraid that I can not benefit from external directives if host > variables are used. > > Can someone confirm this ? > > Regards ... > > Joerg Sorry, I missed to mention my Informix version is IDS 10.00.FC8 on Linux. Regards ... Joerg

Joerg Rueschenschmidt wrote: > Joerg Rueschenschmidt schrieb: > >> Hi all, >> >> Is it possible to use Informix external directives for statements with >> host variables ? >> >> It works fine for statements without host variables. >> >> Now, I need to solve a problem for a statement with a host variable. >> >> e.g. >> >> ... WHERE (a0.t_ncst > ?) >> >> If I add a statement with host variables my directive is not used :-( >> >> I am afraid that I can not benefit from external directives if host >> variables are used. >> >> Can someone confirm this ? >> >> Regards ... >> >> Joerg > > > Sorry, I missed to mention my Informix version is IDS 10.00.FC8 on Linux. > > Regards ... > > Joerg Why do you HAVE to use directives? Directives are really there just to claim equivalence for other DMBSes. Hopefully, the IDS optimiser produces good plans, if not, then perhaps directives as a workaround to a defect in the optimiser.

TBP schrieb: > Joerg Rueschenschmidt wrote: >> Joerg Rueschenschmidt schrieb: >> >>> Hi all, >>> >>> Is it possible to use Informix external directives for statements >>> with host variables ? >>> >>> It works fine for statements without host variables. >>> >>> Now, I need to solve a problem for a statement with a host variable. >>> >>> e.g. >>> >>> ... WHERE (a0.t_ncst > ?) >>> >>> If I add a statement with host variables my directive is not used :-( >>> >>> I am afraid that I can not benefit from external directives if host >>> variables are used. >>> >>> Can someone confirm this ? >>> >>> Regards ... >>> >>> Joerg >> >> >> Sorry, I missed to mention my Informix version is IDS 10.00.FC8 on Linux. >> >> Regards ... >> >> Joerg > > Why do you HAVE to use directives? > > Directives are really there just to claim equivalence for other DMBSes. > > Hopefully, the IDS optimiser produces good plans, if not, then perhaps > directives as a workaround to a defect in the optimiser. To give you a short answer. Our IDS is configured to use OPT_GOAL=-1 (ALL_ROWS). The goal is fine for almost all queries in our application, but in a few situations I would prefer to have FIRST_ROWS behavior. This is why I would like to use external directives. However, my main question is : Is it possible to use Informix external directives for statements with host variables ? Regards ... Joerg

Hi, first you should be able to set the optimizer goal per session using SET OPTIMIZATION FIRST_ROWS secondly how do you see that the optimizer hints (that is what you mean, right?) are not used? Do you have any helpfull output? (I had to use optimizer hints with Informix only in some rare occasions where the data structures where messed up and had to stay like that for some 'good' reasons ;-) ) Regards, Dirk -- -- -- Dipl.-Math. Dirk Gunsth'vel -- -professional services- -- -- Dirk Gunsth'vel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999) "Joerg Rueschenschmidt" <jrueschenschmidt@t-online.de> schrieb im Newsbeitrag news:486FD4D5.3000706@t-online.de... > TBP schrieb: >> Joerg Rueschenschmidt wrote: >>> Joerg Rueschenschmidt schrieb: >>> >>>> Hi all, >>>> >>>> Is it possible to use Informix external directives for statements with >>>> host variables ? >>>> >>>> It works fine for statements without host variables. >>>> >>>> Now, I need to solve a problem for a statement with a host variable. >>>> >>>> e.g. >>>> >>>> ... WHERE (a0.t_ncst > ?) >>>> >>>> If I add a statement with host variables my directive is not used :-( >>>> >>>> I am afraid that I can not benefit from external directives if host >>>> variables are used. >>>> >>>> Can someone confirm this ? >>>> >>>> Regards ... >>>> >>>> Joerg >>> >>> >>> Sorry, I missed to mention my Informix version is IDS 10.00.FC8 on >>> Linux. >>> >>> Regards ... >>> >>> Joerg >> >> Why do you HAVE to use directives? >> >> Directives are really there just to claim equivalence for other DMBSes. >> >> Hopefully, the IDS optimiser produces good plans, if not, then perhaps >> directives as a workaround to a defect in the optimiser. > > > To give you a short answer. Our IDS is configured to use OPT_GOAL=-1 > (ALL_ROWS). The goal is fine for almost all queries in our application, > but in a few situations I would prefer to have FIRST_ROWS behavior. > > This is why I would like to use external directives. > > However, my main question is : > > Is it possible to use Informix external directives for statements with > host variables ? > > Regards ... > > Joerg >

Dirk Gunsthï¿œvel schrieb: > Hi, > > first you should be able to set the optimizer goal per session using > SET OPTIMIZATION FIRST_ROWS > > secondly how do you see that the optimizer hints (that is what you > mean, right?) are not used? Do you have any helpfull output? > > (I had to use optimizer hints with Informix only in some rare > occasions where the data structures where messed up and > had to stay like that for some 'good' reasons ;-) ) > > Regards, > Dirk > Dirk, I need to set the goal for a single query only. The overall goal ALL_ROWS is okay. Further, I think SET OPTIMIZATION requires access to the source code of the third party product, which does not exist. Assume I could change optimization goal for a database session from outside. It would not help, because the third party product open and close database sessions dynamicly. I would never now what database session will run my "bad" query. Fyi, a query execution plan will give you informations about if a directive is used or not. Regards ... Joerg

Joerg Rueschenschmidt schrieb: > Hi all, > > Is it possible to use Informix external directives for statements with > host variables ? > > It works fine for statements without host variables. > > Now, I need to solve a problem for a statement with a host variable. > > e.g. > > ... WHERE (a0.t_ncst > ?) > > If I add a statement with host variables my directive is not used :-( > > I am afraid that I can not benefit from external directives if host > variables are used. > > Can someone confirm this ? > > Regards ... > > Joerg Hello Joerg, if you really usinf EXTERNAL DIRECTIVES, then you should check: - server: EXT_DIRECTIVES entry in $ONCONGIG - client: environment variable IFX_EXTDIRECTIVES - docs: SQL Syntax V10 pg 2-476 further you will want to check table SYSDIRECTIVES in the system catalog of your database I did some testing of this feature way back when it came in new, but it is session oriented and the customer had a noticeable performance penalty, and therefore we had to find an other solution. As always YMMV, though. HTH dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Hi, after testing Joergs case and some other cases (using version 10.00) I came to the comclusion that 1. Joerg is right. External directives dont work with prepared statements 2. The whole external directives thing only works if you are dealing with a statement that is 100% the same every time you run it. If your Application queries for lets say SELECT * FROM sometable WHERE name='Joerg'you will not be able to optimize the statement where 'Joerg' is exchanged with lets say a user input tomorrow. If this is true external directives are not usable in 99,9% of the cases where you would like to use them. ... i.e. the whole concept is good for (nearly) nothing - something that sounded cool in the tech conferences where I heard about it but fails when you try it. Maybe I missed something here (or maybe it is doing better in a version >10?). Could someone please comment on this? Regards, Dirk -- -- -- Dipl.-Math. Dirk Gunsth'vel -- -professional services- -- -- Dirk Gunsth'vel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999) "Joerg Rueschenschmidt" <jrueschenschmidt@t-online.de> schrieb im Newsbeitrag news:g4q45i$msv$03$1@news.t-online.com... > Dirk Gunsth'vel schrieb: >> Hi, >> >> first you should be able to set the optimizer goal per session using >> SET OPTIMIZATION FIRST_ROWS >> >> secondly how do you see that the optimizer hints (that is what you >> mean, right?) are not used? Do you have any helpfull output? >> >> (I had to use optimizer hints with Informix only in some rare >> occasions where the data structures where messed up and >> had to stay like that for some 'good' reasons ;-) ) >> >> Regards, >> Dirk >> > > Dirk, > > I need to set the goal for a single query only. The overall goal ALL_ROWS > is okay. > > Further, I think SET OPTIMIZATION requires access to the source code of > the third party product, which does not exist. Assume I could change > optimization goal for a database session from outside. It would not help, > because the third party product open and close database sessions > dynamicly. I would never now what database session will run my "bad" > query. > > Fyi, a query execution plan will give you informations about if a > directive is used or not. > > Regards ... > > Joerg

Dirk Gunsthï¿œvel schrieb: > Hi, > > after testing Joergs case and some other cases > (using version 10.00) I came to the comclusion that > > 1. Joerg is right. External directives dont work with > prepared statements > > 2. The whole external directives thing only works > if you are dealing with a statement that is 100% > the same every time you run it. > > If your Application queries for lets say > SELECT * FROM sometable WHERE name='Joerg'> you will not be able to optimize the statement where > 'Joerg' is exchanged with lets say a user input tomorrow. > > If this is true external directives are not usable in > 99,9% of the cases where you would like to use them. > > ... i.e. the whole concept is good for (nearly) nothing - > something that sounded cool in the tech conferences > where I heard about it but fails when you try it. > > Maybe I missed something here (or maybe it is doing > better in a version >10?). Could someone please > comment on this? > > Regards, > Dirk > Thanks Dirk for your investigation. I will open a PMR to get an offical answer from IBM. I will share the result to you. Regards ... Joerg