You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Patrick Linskey <pl...@gmail.com> on 2007/09/06 22:28:44 UTC
DB2 and "FOR UPDATE OF" clause
Hi,
I'm seeing SQL like so:
SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS t0
FOR UPDATE OF
Is this valid DB2 SQL? I'm using a DB2 database that returns SQL08016
from a call to getDatabaseProductVersion(). I'm guessing that the
checks for the FOR UPDATE clauses are getting tripped up somewhere.
Any suggestions about what it should be for this version of DB2?
-Patrick
--
Patrick Linskey
202 669 5907
Re: DB2 and "FOR UPDATE OF" clause
Posted by catalina wei <ca...@gmail.com>.
Hi Patrick,
what version of DB2 are you running ?
Catalina
On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
>
> Hi,
>
> I'm seeing SQL like so:
>
> SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS t0
> FOR UPDATE OF
>
> Is this valid DB2 SQL? I'm using a DB2 database that returns SQL08016
> from a call to getDatabaseProductVersion(). I'm guessing that the
> checks for the FOR UPDATE clauses are getting tripped up somewhere.
> Any suggestions about what it should be for this version of DB2?
>
> -Patrick
>
> --
> Patrick Linskey
> 202 669 5907
>
Re: DB2 and "FOR UPDATE OF" clause
Posted by catalina wei <ca...@gmail.com>.
Hi,
I have created jira issue OPENJPA-360, and attached OPENJPA-360.patch.
As soon as it passes regressions, I will commit the patch.
Catalina
On 9/8/07, Kevin Sutter <kw...@gmail.com> wrote:
>
> Thanks, Catalina!
>
> On 9/7/07, catalina wei <ca...@gmail.com> wrote:
> >
> > Patrick & Kevin,
> > Both of you are right.
> > On the other hand, we do have a testcase to test FOR UPDATE OF string
> > where
> > we assert SQL string for "FOR UPDATE OF optimize for 1 row" under
> > org.apache.openjpa.jdbc.TestSelectForUpdateOverride.
> >
> > I mislead you guys by only looking at the testcase without verifying it
> > with
> > right version of DB2.
> > I doubt that the testcase ever ran successfully for DB2 type:
> > *
> >
> > db2ISeriesV5R3OrEarlier or db2UDBV81OrEarlier
> > * I will have someone who has the right version of DB2 to run the test,
> > and
> > will post the result shortly.
> > Sorry for the confusion that I have caused.
> >
> > Catalina
> >
> > On 9/7/07, Kevin Sutter <kw...@gmail.com> wrote:
> > >
> > > Catalina,
> > >
> > > On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> > > >
> > > >
> > > > FWIW, I was also seeing an exception complaining about the 'FOR
> UPDATE
> > > > OF' clause. I was surprised by the syntax, as I would have expected
> it
> > > > to say 'FOR UPDATE OF <something>'.
> > >
> > >
> > > I agree with Patrick on this question. According to the db2 manual,
> > the
> > > "FOR UPDATE OF" clause is supposed to be followed by a list of
> > column(s).
> > > Otherwise, the clause should just be "FOR UPDATE". I looked at the
> sql
> > > generation code and I didn't see where we are supplying any column
> > > identifiers. Am I missing something?
> > >
> > > For reference:
> > >
> > >
> >
> http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/xf6a19.htm#xf6a19
> > >
> > > Thanks,
> > > Kevin
> > >
> > > -Patrick
> > > >
> > > > On 9/6/07, catalina wei <ca...@gmail.com> wrote:
> > > > > Hi Patrick,
> > > > > That SQL is correct syntax if you are running DB2 UDB version 8.1or
> > > > earlier
> > > > > and the isolation level is set pessimistic.
> > > > >
> > > > > If you are not running the said DB2 version and still seeing "FOR
> > > UPDATE
> > > > OF"
> > > > > string, then we have a problem in DB2Dictionary.
> > > > >
> > > > > Catalina
> > > > >
> > > > >
> > > > > On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I'm seeing SQL like so:
> > > > > >
> > > > > > SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM
> SIMPLEPERSISTENTCLASS
> > > t0
> > > > > > FOR UPDATE OF
> > > > > >
> > > > > > Is this valid DB2 SQL? I'm using a DB2 database that returns
> > > SQL08016
> > > > > > from a call to getDatabaseProductVersion(). I'm guessing that
> the
> > > > > > checks for the FOR UPDATE clauses are getting tripped up
> > somewhere.
> > > > > > Any suggestions about what it should be for this version of DB2?
> > > > > >
> > > > > > -Patrick
> > > > > >
> > > > > > --
> > > > > > Patrick Linskey
> > > > > > 202 669 5907
> > > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Patrick Linskey
> > > > 202 669 5907
> > > >
> > >
> >
>
Re: DB2 and "FOR UPDATE OF" clause
Posted by Kevin Sutter <kw...@gmail.com>.
Thanks, Catalina!
On 9/7/07, catalina wei <ca...@gmail.com> wrote:
>
> Patrick & Kevin,
> Both of you are right.
> On the other hand, we do have a testcase to test FOR UPDATE OF string
> where
> we assert SQL string for "FOR UPDATE OF optimize for 1 row" under
> org.apache.openjpa.jdbc.TestSelectForUpdateOverride.
>
> I mislead you guys by only looking at the testcase without verifying it
> with
> right version of DB2.
> I doubt that the testcase ever ran successfully for DB2 type:
> *
>
> db2ISeriesV5R3OrEarlier or db2UDBV81OrEarlier
> * I will have someone who has the right version of DB2 to run the test,
> and
> will post the result shortly.
> Sorry for the confusion that I have caused.
>
> Catalina
>
> On 9/7/07, Kevin Sutter <kw...@gmail.com> wrote:
> >
> > Catalina,
> >
> > On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> > >
> > >
> > > FWIW, I was also seeing an exception complaining about the 'FOR UPDATE
> > > OF' clause. I was surprised by the syntax, as I would have expected it
> > > to say 'FOR UPDATE OF <something>'.
> >
> >
> > I agree with Patrick on this question. According to the db2 manual,
> the
> > "FOR UPDATE OF" clause is supposed to be followed by a list of
> column(s).
> > Otherwise, the clause should just be "FOR UPDATE". I looked at the sql
> > generation code and I didn't see where we are supplying any column
> > identifiers. Am I missing something?
> >
> > For reference:
> >
> >
> http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/xf6a19.htm#xf6a19
> >
> > Thanks,
> > Kevin
> >
> > -Patrick
> > >
> > > On 9/6/07, catalina wei <ca...@gmail.com> wrote:
> > > > Hi Patrick,
> > > > That SQL is correct syntax if you are running DB2 UDB version 8.1 or
> > > earlier
> > > > and the isolation level is set pessimistic.
> > > >
> > > > If you are not running the said DB2 version and still seeing "FOR
> > UPDATE
> > > OF"
> > > > string, then we have a problem in DB2Dictionary.
> > > >
> > > > Catalina
> > > >
> > > >
> > > > On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I'm seeing SQL like so:
> > > > >
> > > > > SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS
> > t0
> > > > > FOR UPDATE OF
> > > > >
> > > > > Is this valid DB2 SQL? I'm using a DB2 database that returns
> > SQL08016
> > > > > from a call to getDatabaseProductVersion(). I'm guessing that the
> > > > > checks for the FOR UPDATE clauses are getting tripped up
> somewhere.
> > > > > Any suggestions about what it should be for this version of DB2?
> > > > >
> > > > > -Patrick
> > > > >
> > > > > --
> > > > > Patrick Linskey
> > > > > 202 669 5907
> > > > >
> > > >
> > >
> > >
> > > --
> > > Patrick Linskey
> > > 202 669 5907
> > >
> >
>
Re: DB2 and "FOR UPDATE OF" clause
Posted by catalina wei <ca...@gmail.com>.
Patrick & Kevin,
Both of you are right.
On the other hand, we do have a testcase to test FOR UPDATE OF string where
we assert SQL string for "FOR UPDATE OF optimize for 1 row" under
org.apache.openjpa.jdbc.TestSelectForUpdateOverride.
I mislead you guys by only looking at the testcase without verifying it with
right version of DB2.
I doubt that the testcase ever ran successfully for DB2 type:
*
db2ISeriesV5R3OrEarlier or db2UDBV81OrEarlier
* I will have someone who has the right version of DB2 to run the test, and
will post the result shortly.
Sorry for the confusion that I have caused.
Catalina
On 9/7/07, Kevin Sutter <kw...@gmail.com> wrote:
>
> Catalina,
>
> On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> >
> >
> > FWIW, I was also seeing an exception complaining about the 'FOR UPDATE
> > OF' clause. I was surprised by the syntax, as I would have expected it
> > to say 'FOR UPDATE OF <something>'.
>
>
> I agree with Patrick on this question. According to the db2 manual, the
> "FOR UPDATE OF" clause is supposed to be followed by a list of column(s).
> Otherwise, the clause should just be "FOR UPDATE". I looked at the sql
> generation code and I didn't see where we are supplying any column
> identifiers. Am I missing something?
>
> For reference:
>
> http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/xf6a19.htm#xf6a19
>
> Thanks,
> Kevin
>
> -Patrick
> >
> > On 9/6/07, catalina wei <ca...@gmail.com> wrote:
> > > Hi Patrick,
> > > That SQL is correct syntax if you are running DB2 UDB version 8.1 or
> > earlier
> > > and the isolation level is set pessimistic.
> > >
> > > If you are not running the said DB2 version and still seeing "FOR
> UPDATE
> > OF"
> > > string, then we have a problem in DB2Dictionary.
> > >
> > > Catalina
> > >
> > >
> > > On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> > > >
> > > > Hi,
> > > >
> > > > I'm seeing SQL like so:
> > > >
> > > > SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS
> t0
> > > > FOR UPDATE OF
> > > >
> > > > Is this valid DB2 SQL? I'm using a DB2 database that returns
> SQL08016
> > > > from a call to getDatabaseProductVersion(). I'm guessing that the
> > > > checks for the FOR UPDATE clauses are getting tripped up somewhere.
> > > > Any suggestions about what it should be for this version of DB2?
> > > >
> > > > -Patrick
> > > >
> > > > --
> > > > Patrick Linskey
> > > > 202 669 5907
> > > >
> > >
> >
> >
> > --
> > Patrick Linskey
> > 202 669 5907
> >
>
Re: DB2 and "FOR UPDATE OF" clause
Posted by Kevin Sutter <kw...@gmail.com>.
Catalina,
On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
>
>
> FWIW, I was also seeing an exception complaining about the 'FOR UPDATE
> OF' clause. I was surprised by the syntax, as I would have expected it
> to say 'FOR UPDATE OF <something>'.
I agree with Patrick on this question. According to the db2 manual, the
"FOR UPDATE OF" clause is supposed to be followed by a list of column(s).
Otherwise, the clause should just be "FOR UPDATE". I looked at the sql
generation code and I didn't see where we are supplying any column
identifiers. Am I missing something?
For reference:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/xf6a19.htm#xf6a19
Thanks,
Kevin
-Patrick
>
> On 9/6/07, catalina wei <ca...@gmail.com> wrote:
> > Hi Patrick,
> > That SQL is correct syntax if you are running DB2 UDB version 8.1 or
> earlier
> > and the isolation level is set pessimistic.
> >
> > If you are not running the said DB2 version and still seeing "FOR UPDATE
> OF"
> > string, then we have a problem in DB2Dictionary.
> >
> > Catalina
> >
> >
> > On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> > >
> > > Hi,
> > >
> > > I'm seeing SQL like so:
> > >
> > > SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS t0
> > > FOR UPDATE OF
> > >
> > > Is this valid DB2 SQL? I'm using a DB2 database that returns SQL08016
> > > from a call to getDatabaseProductVersion(). I'm guessing that the
> > > checks for the FOR UPDATE clauses are getting tripped up somewhere.
> > > Any suggestions about what it should be for this version of DB2?
> > >
> > > -Patrick
> > >
> > > --
> > > Patrick Linskey
> > > 202 669 5907
> > >
> >
>
>
> --
> Patrick Linskey
> 202 669 5907
>
Re: DB2 and "FOR UPDATE OF" clause
Posted by Patrick Linskey <pl...@gmail.com>.
It's not my database; all I know is what I got from the database metadata logs.
FWIW, I was also seeing an exception complaining about the 'FOR UPDATE
OF' clause. I was surprised by the syntax, as I would have expected it
to say 'FOR UPDATE OF <something>'.
-Patrick
On 9/6/07, catalina wei <ca...@gmail.com> wrote:
> Hi Patrick,
> That SQL is correct syntax if you are running DB2 UDB version 8.1 or earlier
> and the isolation level is set pessimistic.
>
> If you are not running the said DB2 version and still seeing "FOR UPDATE OF"
> string, then we have a problem in DB2Dictionary.
>
> Catalina
>
>
> On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
> >
> > Hi,
> >
> > I'm seeing SQL like so:
> >
> > SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS t0
> > FOR UPDATE OF
> >
> > Is this valid DB2 SQL? I'm using a DB2 database that returns SQL08016
> > from a call to getDatabaseProductVersion(). I'm guessing that the
> > checks for the FOR UPDATE clauses are getting tripped up somewhere.
> > Any suggestions about what it should be for this version of DB2?
> >
> > -Patrick
> >
> > --
> > Patrick Linskey
> > 202 669 5907
> >
>
--
Patrick Linskey
202 669 5907
Re: DB2 and "FOR UPDATE OF" clause
Posted by catalina wei <ca...@gmail.com>.
Hi Patrick,
That SQL is correct syntax if you are running DB2 UDB version 8.1 or earlier
and the isolation level is set pessimistic.
If you are not running the said DB2 version and still seeing "FOR UPDATE OF"
string, then we have a problem in DB2Dictionary.
Catalina
On 9/6/07, Patrick Linskey <pl...@gmail.com> wrote:
>
> Hi,
>
> I'm seeing SQL like so:
>
> SELECT t0.ID, t0.VERSN, t0.STRINGFIELD FROM SIMPLEPERSISTENTCLASS t0
> FOR UPDATE OF
>
> Is this valid DB2 SQL? I'm using a DB2 database that returns SQL08016
> from a call to getDatabaseProductVersion(). I'm guessing that the
> checks for the FOR UPDATE clauses are getting tripped up somewhere.
> Any suggestions about what it should be for this version of DB2?
>
> -Patrick
>
> --
> Patrick Linskey
> 202 669 5907
>