Hello,

I am trying to use the "--+ordered" directive through the Informix JDBC driver. I keep getting a syntax error when I try and run it as I would through dbaccess. Inofrmix tech support has not been able to locate the documentation, but I have heard of other people using it.

Thanks.

You might need to use curly braces "{}" as comments
not the double hyphens "--".

For example:
--------------
ResultSet rs = stmt.executeQuery("select {FULL(state)} * from state");

This works at least on Linux, IIF 9.30 and JDBC 2.2

HTH.

Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

I am confused. I thought "--" was comments. The DBA said it wasn't. Are directives read as comments?

Sorry, I am new to directives.

Thanks.

Yes, optimizer hints will be specified as comments.

I think this has to do with the fact that you might write
an application which should run against different
versions of the Informix databaseserver.

Not all Informix versions support optimizer directives,
but because they are specified as a comment, your
application will also run against the older Informix
versions.

Normally you could use curly braces or hyphens
for comments (for example in 'dbaccess') but it seems
that thru JDBC you have to use the curly braces only.
I don't know if that is somewhere documented, I didn't
find it in the JDBC programmers manual.

I would not recommend the use of optimizer hints, at least
not if you are running on Informix.
Informix has a very clever optimizer that is much more
sophisticated than Oracle's optimizer. The use of optimizer
hints might prevent the selection of a better access path
in the future. You think the optimizer hint you use is perfect,
but it is only perfect for the current point in time,
your data changes, so another access path might be
better in the future.
Run regular 'update statistics', that should be enough.

HTH.

Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

Thanks for the info!

The Informix DBA is the one that suggested the use of the "ordered" directive. Informix tech support could also not find any reference to JDBC and directives...undocumented feature for sure.

The query still cralws using the directive through DBACCESS, but screams on SQL Server.

The SQLEXPLAIN shows all indecies are being hit, but there are a few nested loops. Cost was about 68K. Update statistics was just ran. Any idea?

Thanks.

This is difficult to analyze without more information.

The Schema of the involved tables (including indices),
the actual number of rows and used datapages for each table,
the number of unique values inside each index and the
complete 'sqexplain.out' file would be helpful.

But to be honest, I earn my money doing consulting, so such
an analysis would be to much effort for doing it for free.

Just a few hints that might help:

1) Let your DBA check the setting of OPTCOMPIND in
the $ONCONFIG
-> 0 is normally could for OLTP systems but might hurt
complex queries, 2 might be better
you might specify this in JDBC thru the environment
settings in the connect string

2) Think about using PDQ (Parallel Database Query). This
could be a big improvement for complex queries.
However your DBA might also increase DS_TOTAL_MEMORY
if you want to use PDQ.

3) Maybe an 'update statistics high' (on the columns included
in the indexes) might help

HTH.

Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************