Isolation Level on DB2 UDB 9.1.1

SUBSCRIBE DB2-L Anonymous

Isolation Level on DB2 UDB 9.1.1
How can I retrieve the current isolation level?

Thanks in advance

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Grant Allen

Re: Isolation Level on DB2 UDB 9.1.1
(in response to SUBSCRIBE DB2-L Anonymous)
> How can I retrieve the current isolation level?
>
> Thanks in advance

Check syscat.packages for your packages. If you've set the isolation
level in your session, then the current isolation special register has
the value. E.g.

db2 => set isolation level UR
DB20000I The SQL command completed successfully.
db2 => values current isolation

1
--
UR

1 record(s) selected.

Otherwise it's whatever your default is from any db2cli.ini or other
settings.

And here's a friendly hint ... change your DB2-L subscription name,
because it's currently a listserv subscription command. This will cause
normal replies to be rejected by the list server. No big deal if you
don't want to ... you'll just find very few people replying to your posts :)

Ciao
Fuzzy
:-)
------------------------------------------------
Dazed and confused about technology for 20 years
http://fuzzydata.wordpress.com/

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Isolation Level on DB2 UDB 9.1.1
(in response to Grant Allen)
Thanks for your help. I took care of the last point you made.

I ran the folowing command and below, is the result.

db2 => values current isolation

1
--


What does it mean?


Thanks again

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Isolation Level on DB2 UDB 9.1.1
(in response to SUBSCRIBE DB2-L Anonymous)
Correction. I meant to say what would be prevailing or resultant isolation
level chosen by db2 in this case.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Grant Allen

Re: Isolation Level on DB2 UDB 9.1.1
(in response to SUBSCRIBE DB2-L Anonymous)
Roman Gelfand wrote:
> Correction. I meant to say what would be prevailing or resultant isolation
> level chosen by db2 in this case.

You'll be at the default Cursor Stability isolation level. The special
register is empty if you haven't set it explicitly, so what you're
seeing is normal. If you're in any doubt (and
middleware/object-relational mapping layers can do weird things), always
go for an explicit setting in your code.

Ciao
Fuzzy
:-)
------------------------------------------------
Dazed and confused about technology for 20 years
http://fuzzydata.wordpress.com/

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Isolation Level on DB2 UDB 9.1.1
(in response to Grant Allen)
FYI... this db2 on aix 5.3, dynamic queries. If an isolation level has not been
explicitly set, is the default always CS or db2 decide depending on various
factors?

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

SUBSCRIBE DB2-L Anonymous

Re: Isolation Level on DB2 UDB 9.1.1
(in response to SUBSCRIBE DB2-L Anonymous)
Is there a way to tell if middleware/object-relational mapping layers do weird
things?

On Mon, 14 Jan 2008 14:27:02 +1100, Grant Allen <[login to unmask email]>
wrote:

>Roman Gelfand wrote:
>> Correction. I meant to say what would be prevailing or resultant isolation
>> level chosen by db2 in this case.
>
>You'll be at the default Cursor Stability isolation level. The special
>register is empty if you haven't set it explicitly, so what you're
>seeing is normal. If you're in any doubt (and
>middleware/object-relational mapping layers can do weird things), always
>go for an explicit setting in your code.
>
>Ciao
>Fuzzy
>:-)
>------------------------------------------------
>Dazed and confused about technology for 20 years
>http://fuzzydata.wordpress.com/
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: Isolation Level on DB2 UDB 9.1.1
(in response to SUBSCRIBE DB2-L Anonymous)
Roman,
I don't have easy access to AIX DB2 manuals being a mainframe person but I
suggest you review numbered page 17 (Physical page 35) of "SQL REFERENCE
FOR CROSS PLATFORM DEVELOPMENT"


ftp://ftp.software.ibm.com/ps/products/db2/info/xplatsql/pdf/en_US/cpsqlrv2.p
df


In addition please be aware that stored procedures are usually static not
dynamic ... its one of the advantages of using stored procedures.

Historically the defaults have been RR. This is because the original
deplaoyment of DB2 only supported what is called RR. IBM started supported
other levels soon after the introduction of DB2 (V 1.3 I think) but the SQL
standard was updated to support other levels in 2003. Usually the default
when a feature is introduced is the way it always worked. It may not matter
what the default level of DB2 is on the server now, the issue is what was the
level when the procedure was created.

Regards
Avram Friedman

On Mon, 14 Jan 2008 03:42:13 +0000, Roman Gelfand
<[login to unmask email]> wrote:

>FYI... this db2 on aix 5.3, dynamic queries. If an isolation level has not been
>explicitly set, is the default always CS or db2 decide depending on various
>factors?
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

James Campbell

Re: Isolation Level on DB2 UDB 9.1.1
(in response to Avram Friedman)
For reference, some information centers:

DB2 LUW V8: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp

DB2 LUW V9: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

DB2 LUW V9R5: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp

These make it fairly simple to search the LUW manuals without having physical books.

James Campbell

On 14 Jan 2008 at 14:06, Avram Friedman wrote:

> Roman,
> I don't have easy access to AIX DB2 manuals being a mainframe person

<rest snipped>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms