DB2 Connect, Dynamic SQL, and chosen Isolation Level

[klh]

We use DB2 Connect v 7.2 FP7 in Windows NT hitting a OS/390 DB2 v7.1
database. We have a Websphere (java) application that issues dynamic
SQL. Most of the time when we issue dynamic SQL SELECT statements,
like through a DB2 command window, the command will be processed using
a package like SQLLF000 which uses an isolation level of Cursor
Stability. However sometimes in the Websphere application when a
dynamic SELECT statement is issued the command is processed using the
package SQLLC300 which uses an isolation level of Read Stability that
locks all the rows it reads (causing contention with other
applications).

When we performed the binds on the packages for DB2 Connect we used
the defaults. We could rebind the packages to remove the Read
Stability, but I'm sure their may be a situation where we would need
to use Read Stability.

The SELECT statement in question is: SELECT field1, field2, field3
from VIEW1. Where VIEW1 is a view built on a single table like SELECT
field1, field2, field3, field4 from TABLE1 where field4 = 'X'.

I don't know if I have all of the package names listed correctly, but
my question is, how does DB2 decide which package to use for
processing a SELECT statement?

Can we change the SQL statement to influence which package is used?

Should we rebind the package so that it performs Cursor Stability?

Thanks for any insight into this situation.
klh

[PM \(pm3iinc-nospam\) CGO]

The SQL Reference for Cross-Platform Development
http://www-106.ibm.com/developerwork...206sqlref.html

isolation-clause

WITH RR

RS

CS

UR




The optional isolation-clause specifies the isolation level at which the
select

statement is executed.

v RR - Repeatable Read

v RS - Read Stability

v CS - Cursor Stability

v UR - Uncommitted Read

WITH UR can be specified only if the result table is read-only. If
isolation-clause is

not specified, the default isolation is used with the exception of a default
isolation

level of uncommitted read. With uncommitted read, the default isolation
level of

the statement depends on whether the result table is read-only; if the
result table is

read-only then the default will be UR; if the result table is not read-only
then the

default will be CS. See "Isolation Level" on page 13 for a description of
how the

default is determined.

PM

"klh" <kl*******@kcc.usda.gov> a écrit dans le message de
news:40**************************@posting.google.c om...

We use DB2 Connect v 7.2 FP7 in Windows NT hitting a OS/390 DB2 v7.1
database. We have a Websphere (java) application that issues dynamic
SQL. Most of the time when we issue dynamic SQL SELECT statements,
like through a DB2 command window, the command will be processed using
a package like SQLLF000 which uses an isolation level of Cursor
Stability. However sometimes in the Websphere application when a
dynamic SELECT statement is issued the command is processed using the
package SQLLC300 which uses an isolation level of Read Stability that
locks all the rows it reads (causing contention with other
applications).

When we performed the binds on the packages for DB2 Connect we used
the defaults. We could rebind the packages to remove the Read
Stability, but I'm sure their may be a situation where we would need
to use Read Stability.

The SELECT statement in question is: SELECT field1, field2, field3
from VIEW1. Where VIEW1 is a view built on a single table like SELECT
field1, field2, field3, field4 from TABLE1 where field4 = 'X'.

I don't know if I have all of the package names listed correctly, but
my question is, how does DB2 decide which package to use for
processing a SELECT statement?

Can we change the SQL statement to influence which package is used?

Should we rebind the package so that it performs Cursor Stability?

Thanks for any insight into this situation.
klh

[PM \(pm3iinc-nospam\) CGO]

The SQL Reference for Cross-Platform Development
http://www-106.ibm.com/developerwork...206sqlref.html

isolation-clause

WITH RR

RS

CS

UR




The optional isolation-clause specifies the isolation level at which the
select

statement is executed.

v RR - Repeatable Read

v RS - Read Stability

v CS - Cursor Stability

v UR - Uncommitted Read

WITH UR can be specified only if the result table is read-only. If
isolation-clause is

not specified, the default isolation is used with the exception of a default
isolation

level of uncommitted read. With uncommitted read, the default isolation
level of

the statement depends on whether the result table is read-only; if the
result table is

read-only then the default will be UR; if the result table is not read-only
then the

default will be CS. See "Isolation Level" on page 13 for a description of
how the

default is determined.

PM

"klh" <kl*******@kcc.usda.gov> a écrit dans le message de
news:40**************************@posting.google.c om...

We use DB2 Connect v 7.2 FP7 in Windows NT hitting a OS/390 DB2 v7.1
database. We have a Websphere (java) application that issues dynamic
SQL. Most of the time when we issue dynamic SQL SELECT statements,
like through a DB2 command window, the command will be processed using
a package like SQLLF000 which uses an isolation level of Cursor
Stability. However sometimes in the Websphere application when a
dynamic SELECT statement is issued the command is processed using the
package SQLLC300 which uses an isolation level of Read Stability that
locks all the rows it reads (causing contention with other
applications).

When we performed the binds on the packages for DB2 Connect we used
the defaults. We could rebind the packages to remove the Read
Stability, but I'm sure their may be a situation where we would need
to use Read Stability.

The SELECT statement in question is: SELECT field1, field2, field3
from VIEW1. Where VIEW1 is a view built on a single table like SELECT
field1, field2, field3, field4 from TABLE1 where field4 = 'X'.

I don't know if I have all of the package names listed correctly, but
my question is, how does DB2 decide which package to use for
processing a SELECT statement?

Can we change the SQL statement to influence which package is used?

Should we rebind the package so that it performs Cursor Stability?

Thanks for any insight into this situation.
klh