#412 Valid SQL is failing: DB2 Isolation Clause

2.3rc1
closed-works-for-me
Rob Manning
Core (461)
5
2006-10-10
2006-08-07
wildarr
No

SQL: SELECT * FROM TABLE-NAME WITH UR

Error: java.sql.SQLException: [IBM][CLI
Driver][DB2/LINUX] SQL0104N An unexpected token
"select * from TABLE_NAME wit" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include:
"<query_expr_body>". SQLSTATE=42601, SQL State: 42601,
Error Code: -104

Driver: IBM DB2 JDBC Drivers

DB2 allows an isolation clause on its SQL. The clause
"WITH UR" prevents table locks from occurring on
Long/complex queries.

The SQL will fail in Squirrel, but succeeds in other
applications using the same driver.

I'm not sure what more I can tell you. The UR
highlights Red in the SQL tab, while the WITH
highlights blue.

I think Squirrel is thinking that the WITH should begin
the statement (creating a temporary table).

However, it is strange, because the error that is
generated is a CLI error.

Discussion

  • Rob Manning
    Rob Manning
    2006-08-12

    • milestone: 126228 --> 2.3rc1
    • assigned_to: colbell --> manningr
    • status: open --> open-works-for-me
     
  • Rob Manning
    Rob Manning
    2006-08-12

    Logged In: YES
    user_id=1287991

    Hello,

    I don't think we have all of the information we need to
    troubleshoot this. This seems to work fine for me with
    the latest code and DB2 V9.1 (Express Edition) both
    running on Linux (FC5). Of course all that I have
    in the SQL editor is that single SQL
    statement. It looks like somehow SQuirreL sent

    select * from TABLE_NAME wit
    h ur

    to the server. Like perhaps there was a newline character
    between the token "wit" and the token "h". I get the
    same error code and state code when I simple put the
    cursor between the "t" and "h" in "with" and press "enter"
    to insert a newline character. Is it possible that the
    script has non-platform end-of-line characters or some
    other issue? Can you try not loading a script and just
    typing the SQL statement in. As I said before, when I
    do this, the statement executes fine. What version of
    SQuirreL, DB2 and driver version are you using?

    Rob

     
  • Logged In: NO

    WITH UR caluse works fine with JCC driver of 8.2

     
  • Rob Manning
    Rob Manning
    2006-09-25

    • status: open-works-for-me --> pending-works-for-me
     
    • status: pending-works-for-me --> closed-works-for-me
     
  • Logged In: YES
    user_id=1312539

    This Tracker item was closed automatically by the system. It was
    previously set to a Pending status, and the original submitter
    did not respond within 14 days (the time period specified by
    the administrator of this Tracker).