Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Seattle, WA, USA
    Posts
    3

    Unanswered: Informix Directives through JDBC

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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
    ***********************************************

  3. #3
    Join Date
    Jan 2003
    Location
    Seattle, WA, USA
    Posts
    3
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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
    ***********************************************

  5. #5
    Join Date
    Jan 2003
    Location
    Seattle, WA, USA
    Posts
    3
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Bonn/Germany
    Posts
    152
    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
    ***********************************************

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •