Hi all

I am new to DB2,

came across the following cursor syntax and i ve some doubts in cursor and isolation levels.. cud any one pls clarify me on this...

DECLARE RSLT_CUR CURSOR FOR SELECT * FROM test FOR READ ONLY WITH RR ;

i suppose the RR here defines some isolation level (repeatable read)

cud any one clarify me
1. does this statement holds any lock on the table specified in the select statement (test)?
if so when will the lock be released ?
if not how the isolation is maintained ?

2. if i did not specify any isolation level there in the statement, how the system will act now
ie., what is the default isolation locking in the DB2?
How do we access this from command line or is it just a setting in sql.ini file etc.

How do we change isolation level for entire db2 server i.e. administrative server db2admin, for a particular instance and a particular database in an instance and transactions while connected to a database.

3. i would like to know how the select statement in the cursor is evaluated..?
does the DB2 evaluates the select statement and uses a temp. table to store the results
or it evaluates each time when the Fetch command is issued ...

4) Is it true that by setting lowest isolation level i.e. uncommited read, we reduce chances of unreleased locks and deadlocks. ?


Thanks in advance
Sathya

1. RR will hold share locks on all the rows accessed. The share locks will be released when the next commit takes place (either explicit or because of auto-commit).

2. The default is CS. Here is information about setting it in the CLI interface:

TXNISOLATION

Keyword Description:
Set the default isolation level.

db2cli.ini Keyword Syntax:
TXNISOLATION = 1 | 2 | 4 | 8 | 32

Default Setting:
Read Committed (Cursor Stability)

DB2 CLI/ODBC Settings Tab:
Transaction

Only Applicable when:
the default isolation level is used. This keyword will have no effect if the application has specifically set the isolation level.

Equivalent Statement Attribute:
SQL_ATTR_TXN_ISOLATION

Usage Notes:

Sets the isolation level to:

1 = Read Uncommitted (Uncommitted read)
2 = Read Committed (Cursor stability) (default)
4 = Repeatable Read (Read Stability)
8 = Serializable (Repeatable read)
32 = (No Commit, DATABASE 2 for AS/400 only; this is similar to autocommit)

To change it for the entire DB2 server, you can use the AUTOCONFIGURE command. See the Command Reference for details.

3. DB2 will try to read the rows in place, but if a sort is needed becasue of ORDER BY then DB2 may materialize the rows in a temp table (there are other reasons why DB2 may materialize the rows in a temp table).

4. You will not get any lock contention or deadlocks with UR because UR does not take any locks and ignores locks taken by others. However, for most applications CS should be sufficient. Keep in mind that multiple share locks (result of a SELECT) can coexit without any contention.

You may also need to change LOCKLIST and MAXLOCKS DB Config parms to discourage lock escalation to table level in an OLTP environment. Serach this forum for more info.

If a procedure using RR does not have any commit or rollback statement in it .. when will the locks held by the procedure be released?
i want to know when will the auto-commit takes place..

The next auto-commit is scheduled on March 31 at 7pm, Eastern time.

Thanks for the auto commit schedule ... now i understand my ignorance...