DB2 Mode Support WITH UR syntax (uncommitted read)

[Bert Roex]

The DB2 Mode in H2 is quite complete although we use the WITH UR syntax in some queries which does not seem to be supported.

Could you please expand the DB2 Mode to allow the use of the WITH UR DB2 syntax?

[Thomas Mueller]

Hi,

If you just need support for the syntax, but don't actually need the database to use uncommitted reads (because that would be hard to achieve), it should be relatively easy to add support for this in the parser (Parser.java). See also http://h2database.com/html/build.html#providing_patches

Regards,

Thomas

On Wed, Oct 2, 2013 at 2:43 PM, Bert Roex <roex...@gmail.com> wrote:

The DB2 Mode in H2 is quite complete although we use the WITH UR syntax in some queries which does not seem to be supported.

Could you please expand the DB2 Mode to allow the use of the WITH UR DB2 syntax?

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Lukas Eder]

I've been reasoning about this syntax as well for jOOQ, recently. Here are the relevant DB2 manual pages:

- http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0059221.html (isolation-clause)

- http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0059222.html (lock-request-clause)

isolation-clause:

>>-WITH--+-RR--+---------------------+-+-----------------------><
         |     '-lock-request-clause-' |   
         +-RS--+---------------------+-+   
         |     '-lock-request-clause-' |   
         +-CS--------------------------+   
         '-UR--------------------------' 

lock-request-clause:

>>-USE AND KEEP--+-SHARE-----+--LOCKS--------------------------><
                 +-UPDATE----+          
                 '-EXCLUSIVE-'   

Since there's no syntactic magic in all of this, and if only the syntax should be supported, not the locking, I think it might make sense to support it completely, not just UR.

Hope this helps,

Lukas

[litailang]

Hi,

I wrote a patch for this issue.
support DB2's isolation-clause and lock-request-clause.

litailang

2013/10/26 Lukas Eder <lukas...@gmail.com>:

[Noel Grandin]

Hi

litailang, I can't find anywhere where it says that Derby supports this syntax.

Also, your patch contains an unrelated change to the Shell class - any idea what that was for?

Regards, Noel.

[Noel Grandin]

On 2013-11-18 09:44, Noel Grandin wrote:
>
> litailang, I can't find anywhere where it says that Derby supports this syntax.
>

Ah, I see now. It looks like the comments in our existing code were wrong. I have no idea why we thought that Derby
supported this syntax.

Your patch has been applied, thank you very much.