473,839 Members | 1,485 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cursor stability isolation level

11 New Member
1st Session:

$ db2 values current isolation

1
--
CS

1 record(s) selected.

$ db2 "select * from venkat"

EMPID EMPNAME
----------- ------------------------------
1 a
2 b
3 c
4 d
5 e

5 record(s) selected.


$ db2 "declare c1 cursor for select * from venkat"
DB20000I The SQL command completed successfully.
$ db2 +c open c1
DB20000I The SQL command completed successfully.
$ db2 +c fetch from c1

EMPID EMPNAME
----------- ------------------------------
1 a

1 record(s) selected.

*************** *************** *************** ***** *************** *************** **************
2nd Session:

$ db2 values current isolation

1
--
CS

1 record(s) selected.


$ db2 "update venkat set empname='2a' where empid=1"
DB20000I The SQL command completed successfully.


As you can see i was able to update the row where empid=1, in Session 2 ,which is currently being fetched in Session 1.

My Question:
As per the Cursor Stability isolation level, the row where empid=1 which is currently being fetched in Session 1 with CS isolation level should not be updatable by Session 2 (or other application). So why i am able to update the currently fetched row in session 1 from session 2?
Mar 6 '11 #1
2 4305
vijay2082
112 New Member
Hi Venkat,

Fetch/Select in normal clase won't prohibit you/otehr application for updating the table/column value. That's the beauty of isolation levels. If you want to really see CS working then declare a cursor for updating the columns and see the result. I am pasting snaps from session1 and session2 for your easy ref. Enjoying swimming into the world of DB2

session 1
=============

C:\backup>db2 connect

Database Connection Information

Database server = DB2/NT 9.5.5
SQL authorization ID = VIJAY
Local database alias = PAULB001


C:\Users\vijay> set DB2OPTIONS=+c

C:\Users\vijay> db2 list command options

Command Line Processor Option Settings

Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) = +c

Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit OFF


C:\backup>db2 values current isolation

1
--


1 record(s) selected.


C:\backup>
C:\backup>db2 "set current isolation CS"
DB20000I The SQL command completed successfully.

C:\backup>db2 values current isolation

1
--
CS

1 record(s) selected.

C:\backup>db2 "declare c1 cursor for select name from vijay.test5 for update of name"
DB20000I The SQL command completed successfully.

C:\backup>db2 "open c1"
DB20000I The SQL command completed successfully.

C:\backup>db2 "fetch from c1"

NAME
----------
VIJAY

1 record(s) selected.


C:\backup>db2 "fetch from c1" <<< this sould lock the record

NAME
----------
AJAY

1 record(s) selected.

Now look at the session 2. The update command is in lock-wait status as session has got locks on the record ( Cursor stability )


Lets look at the application snapshot to conform our point and preferrablly a lock snapshot on database

Lock snapshot
--------------
Application handle = 74
Application ID = *LOCAL.DB2.1103 06134846
Sequence number = 00003
Application name = db2bp.exe
CONNECT Authorization ID = VIJAY
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 4
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x0200050105002 0110000000052
Lock Attributes = 0x00000000
Release Flags = 0x00000001
Lock Count = 1
Hold Count = 0
Lock Object Name = 287309829
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = VIJAY
Table Name = TEST5
Mode = U

Lock Name = 0x0100000001000 0000100B90056
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Variation Lock
Mode = S

Lock Name = 0x53514C4332473 1350655EBAA41
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Plan Lock
Mode = S

Lock Name = 0x0200050100000 0000000000054
Lock Attributes = 0x00000000
Release Flags = 0x00000001
Lock Count = 1
Hold Count = 0
Lock Object Name = 261
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = VIJAY
Table Name = TEST5
Mode = IX


Application snapshot
---------------------

C:\backup>db2 "list applications show detail"

CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status

----------------- -------------------- ---------- -------------------------- ----- ---------- ---------------- --------------- ----------------------
VIJAY db2taskd 76 *LOCAL.DB2.1103 06134851 00001 1 0 7544 Connect Completed
VIJAY db2bp.exe 89 *LOCAL.DB2.1103 06135815 00001 1 0 8060 Lock-wait
VIJAY db2stmm 75 *LOCAL.DB2.1103 06134850 00001 1 0 7112 Connect Completed
VIJAY db2bp.exe 74 *LOCAL.DB2.1103 06134846 00003 1 0 6408 UOW Waiting


>> perform a commit over here so that session2 comes out of lock wait and completes the transaction

C:\backup>db2 commit
DB20000I The SQL command completed successfully.

as soon as you will execute a commit in session1 see the sesion 2 window for completion status message

=========
SEssion 2
=========



C:\Users\vijay> set DB2OPTIONS=+c

C:\Users\vijay> db2 list command options

Command Line Processor Option Settings

Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) = +c

Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit OFF



C:\Users\vijay> db2 "update vijay.test5 set name='VENKAT' where name ='AJAY' << this session goes to lock wait

-------------------------

Cheers, Vijay
Mar 6 '11 #2
Venkat G
11 New Member
I clearly understand it now. Thank you, Vijay.
Mar 6 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

11
12731
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to read the new date. So process B starts "select ..." but does not get the previously inserted row. The timespan between commit and select is very short. (NOTE: two different sessions are used) Questions: 1.) Does commit when returning from call...
4
14887
by: Eddie | last post by:
I wondering which one of the following I should use to get the best performance. 1. "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" OR 2. "WITH (NOLOCK)" I notice that when I use the #1 "SET TRANSACTION..." it sets a lock Mode type of "Sch-S" (Schema stability Lock) which described by SQL Books Online as "Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks"
2
8860
by: klh | last post by:
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...
1
2762
by: cwahlmeier | last post by:
I have been researching ADO.NET in order to train our programming staff. I am curious if someone would know what the default isolation level is with and without a transaction. Furthermore, if I specify the isolation level on the transaction object, which one is the most like cursor stability? Is it read commited? Any other opinions/best practices regarding ADO.NET are welcome. I have been reading the redbook DB2 for UDB V8.2 on the...
3
8671
by: Eric Porter | last post by:
Dear All, I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB that performs various bits of SQL. I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which uses the ADODB.Interop library supplied with .NET. The VB6.COM program above calls this C#.NET library in an effort to perform the equivalent functionality.
2
8143
by: kanda | last post by:
Hello. I am developing the application (VBA&ODBC, to be exact) which periodically calls the stored procedures in the IBM DB2. A few of the procedures require executing with isolation level RR ( ANSI "SERIALIZABLE" ), not the default; default is CS (ANSI "Read Committed")). The procedure language is SQL. According to the documentation, I can adjust procedure *run*-time isolation level by setting *compile*-time dataserver-wide option
1
1624
by: Michel Esber | last post by:
DB2 LUW versions v7 and v8 We have an application that opens a cursor, retrieves the result set into memory and summarizes it, saving data into another DB. Due to high network bandwidth usage, we have decided to implement the same concept using C++ stored procedures. The procedure is called, reads and summarizes data, saving data into a table. The application will then read this summarized data and save it into another DB.
5
3352
by: m0002a | last post by:
Is there some way to track the isolation level of an indivual SQL statement submitted via JDBC in a snaphot or some other similar means? I have JDBC programs that are changing the isolation level via Connection.setTransactionIsolation(int level) method, such as TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ and I need to verify that in DB2 at a SQL statement level. I don't see isolation level in a snapshot for Dynamic SQL.
0
2489
by: nagappan | last post by:
Hi, My application program opens a cursor.It does update or delete on some set of conditions. It commits after every 10 records and open again cursor for the remaining set of records. I opened with cursor "FOR FETCH ONLY WITH UR". Delete is issued from the application program but it is not committed in the database. Can u guide me for selecting the isolation level for this type of application program. Thanking You, Nagappan.
3
3274
by: RG | last post by:
How can I lookup the current isolation level? Thanks in advance
0
9854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9696
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10903
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10645
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9425
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7827
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5681
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5865
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4482
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.