472,969 Members | 1,320 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,969 software developers and data experts.

Migrating from 5.1 to 8.1: Locking problems (isolation level ?)

We migrated a database from Version 5.1 to 8.1 and are experiencing
massive locking problems. We migrated in the following steps:

0) Server 5.2, Clients 5.2: everithing was fine
1) Server 5.2, Clients 6.1: everything still fine
2) Server 8.1, Clients 6.1: here the problem occurs
3) server 8.1, Clients 8.1: not yet done.

Steps 1 & 2 were nessecary because there are to many clients spread
over to much area to migrate all at once from 5.2 to 8.1, and the 5.2
client can't work with the 8.1 server.

Since switching to Server 8.1 we experience massive locking problems,
the lock list is permanently full (>98%) althoug it's now far bigger
than before (ca. 32MB).

The Server and clients are running on W2K, the clients connect via
ODBC (standard installation of DB2 client). We have not and do not now
explicitly set an isolation level.

I've read at some places that the default isolation level was changed
from CS to RR in 8.1, and that would nicely explain the symptoms we
experience. If that's the case, then I'd VERY MUCH like to know how to
change this default back to CS.

We have no statically bound applications that we know of, only
dynamically generated ODBC connections.
I've thought of some possible solutions:

1) Set the server's default isolation level back to CS (WHERE ?).
2) Edit all client's db2cli.ini files. (Yuck)
3) Set the isolation level at the beginning of each
connection/transaction (YUCK)
4) Modify all appropriate SELECT statements to add "FOR READ ONLY"
(MAJOR pain in the a..)

Anyone has a good idea for me ? I'm running out of them.

TIA, Wolfgang
Nov 12 '05 #1
3 2707
Wolfgang,
one of the steps after Migration is to set
the indices to type-2 indices.
It resolves -afaik - the next key locking...

Regards,
Joachim

"Wolfgang Bachmann" <wo***************@ecofinance.com> schrieb im
Newsbeitrag news:1e**************************@posting.google.c om...
We migrated a database from Version 5.1 to 8.1 and are experiencing
massive locking problems. We migrated in the following steps:

0) Server 5.2, Clients 5.2: everithing was fine
1) Server 5.2, Clients 6.1: everything still fine
2) Server 8.1, Clients 6.1: here the problem occurs
3) server 8.1, Clients 8.1: not yet done.

Steps 1 & 2 were nessecary because there are to many clients spread
over to much area to migrate all at once from 5.2 to 8.1, and the 5.2
client can't work with the 8.1 server.

Since switching to Server 8.1 we experience massive locking problems,
the lock list is permanently full (>98%) althoug it's now far bigger
than before (ca. 32MB).

The Server and clients are running on W2K, the clients connect via
ODBC (standard installation of DB2 client). We have not and do not now
explicitly set an isolation level.

I've read at some places that the default isolation level was changed
from CS to RR in 8.1, and that would nicely explain the symptoms we
experience. If that's the case, then I'd VERY MUCH like to know how to
change this default back to CS.

We have no statically bound applications that we know of, only
dynamically generated ODBC connections.
I've thought of some possible solutions:

1) Set the server's default isolation level back to CS (WHERE ?).
2) Edit all client's db2cli.ini files. (Yuck)
3) Set the isolation level at the beginning of each
connection/transaction (YUCK)
4) Modify all appropriate SELECT statements to add "FOR READ ONLY"
(MAJOR pain in the a..)

Anyone has a good idea for me ? I'm running out of them.

TIA, Wolfgang

Nov 12 '05 #2
Hallo Joachim,

thanx for this tip. I tried it, but unfortunately it didn't help. :-(
So if you have another idea, i'd be more than happy.

ru, Wolfgang

Joachim Mueller wrote:
Wolfgang,
one of the steps after Migration is to set
the indices to type-2 indices.
It resolves -afaik - the next key locking...

Regards,
Joachim

"Wolfgang Bachmann" <wo***************@ecofinance.com> schrieb im
Newsbeitrag news:1e**************************@posting.google.c om...
We migrated a database from Version 5.1 to 8.1 and are experiencing
massive locking problems. We migrated in the following steps:

0) Server 5.2, Clients 5.2: everithing was fine
1) Server 5.2, Clients 6.1: everything still fine
2) Server 8.1, Clients 6.1: here the problem occurs
3) server 8.1, Clients 8.1: not yet done.

Steps 1 & 2 were nessecary because there are to many clients spread
over to much area to migrate all at once from 5.2 to 8.1, and the 5.2
client can't work with the 8.1 server.

Since switching to Server 8.1 we experience massive locking problems,
the lock list is permanently full (>98%) althoug it's now far bigger
than before (ca. 32MB).

The Server and clients are running on W2K, the clients connect via
ODBC (standard installation of DB2 client). We have not and do not now
explicitly set an isolation level.

I've read at some places that the default isolation level was changed
from CS to RR in 8.1, and that would nicely explain the symptoms we
experience. If that's the case, then I'd VERY MUCH like to know how to
change this default back to CS.

We have no statically bound applications that we know of, only
dynamically generated ODBC connections.
I've thought of some possible solutions:

1) Set the server's default isolation level back to CS (WHERE ?).
2) Edit all client's db2cli.ini files. (Yuck)
3) Set the isolation level at the beginning of each
connection/transaction (YUCK)
4) Modify all appropriate SELECT statements to add "FOR READ ONLY"
(MAJOR pain in the a..)

Anyone has a good idea for me ? I'm running out of them.

TIA, Wolfgang


--

Wolfgang Bachmann

ecofinance Finanzsoftware & Consulting GmbH
Graz / Vienna / Essen / London
Grieskai 10, 8020 Graz, Austria
Phone: (++43) 316 908030
Fax: (++43) 316 908030-24
http://www.ecofinance.com
Nov 12 '05 #3
Wolfgang,

APAR IY44380 describes the problem you have:
http://www-306.ibm.com/cgi-bin/db2ww...aparno=IY44380

Solution:
Carry out direct migration to DB2 UDB v8 FixPak 2 or higher.The relevant
APAR is IY44380.

If the problem exists already, you may be able to remedy it with a DROP and
repeated binding
of the CLI packages. An upgrade to a higher FixPak does not help in this
case.

The procedure is as follows (as an instance owner in the sqllib/bnd</>
directory).

Generate a script
db2 select "'drop package nullid.' concat char(name,8)
from sysibm.sysplan where name like 'SYS%'
order by name" > del_cli_packages.scr

Execute the script
db2 -vf del_cli_packages.scr

Check that there are no more packages with the name SYS%
db2 "select char(creator,8),char(name,8),explicit_bind_time
from sysibm.sysplan order by creator,name"

Rebind the packages
db2 bind @db2cli.lst blocking all grant public
db2 bind @db2ubind.lst blocking all grant public

Regards,
Joachim

"Wolfgang Bachmann" <wo***************@ecofinance.com> schrieb im
Newsbeitrag news:ne********************@inet.ecofinance.com...
Hallo Joachim,

thanx for this tip. I tried it, but unfortunately it didn't help. :-(
So if you have another idea, i'd be more than happy.

ru, Wolfgang

Joachim Mueller wrote:
Wolfgang,
one of the steps after Migration is to set
the indices to type-2 indices.
It resolves -afaik - the next key locking...

Regards,
Joachim

"Wolfgang Bachmann" <wo***************@ecofinance.com> schrieb im
Newsbeitrag news:1e**************************@posting.google.c om...
We migrated a database from Version 5.1 to 8.1 and are experiencing
massive locking problems. We migrated in the following steps:

0) Server 5.2, Clients 5.2: everithing was fine
1) Server 5.2, Clients 6.1: everything still fine
2) Server 8.1, Clients 6.1: here the problem occurs
3) server 8.1, Clients 8.1: not yet done.

Steps 1 & 2 were nessecary because there are to many clients spread
over to much area to migrate all at once from 5.2 to 8.1, and the 5.2
client can't work with the 8.1 server.

Since switching to Server 8.1 we experience massive locking problems,
the lock list is permanently full (>98%) althoug it's now far bigger
than before (ca. 32MB).

The Server and clients are running on W2K, the clients connect via
ODBC (standard installation of DB2 client). We have not and do not now
explicitly set an isolation level.

I've read at some places that the default isolation level was changed
from CS to RR in 8.1, and that would nicely explain the symptoms we
experience. If that's the case, then I'd VERY MUCH like to know how to
change this default back to CS.

We have no statically bound applications that we know of, only
dynamically generated ODBC connections.
I've thought of some possible solutions:

1) Set the server's default isolation level back to CS (WHERE ?).
2) Edit all client's db2cli.ini files. (Yuck)
3) Set the isolation level at the beginning of each
connection/transaction (YUCK)
4) Modify all appropriate SELECT statements to add "FOR READ ONLY"
(MAJOR pain in the a..)

Anyone has a good idea for me ? I'm running out of them.

TIA, Wolfgang


--

Wolfgang Bachmann

ecofinance Finanzsoftware & Consulting GmbH
Graz / Vienna / Essen / London
Grieskai 10, 8020 Graz, Austria
Phone: (++43) 316 908030
Fax: (++43) 316 908030-24
http://www.ecofinance.com

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: heynothanks | last post by:
(MSSQL2000) I have read the transaction/locking sections in the MS-help, online and several books. What I want to understand is the transaction behavior in single statements . If I have a...
16
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
5
by: Ritesh | last post by:
Hi All, According to my observation using SP_WHO2 in my database, some INSERT statements are getting blocked by SELECT statements. Though the blocking SELECT statement is having ReadPast hint,...
3
by: mahajan.sanjeev | last post by:
Hi All, I am using a SQLTransaction to insert records into a table. At one time, there are 5000 or more records to be inserted one by one. It takes some 20-25 mins for the entire process to run....
8
by: dan.c.roth | last post by:
Hi I am having a debate with one of the Postgres developers (Tom Lane) according to him ms-sql does not implement predicate locking(SERIALIZABLE). Where predicate locking is defined as (from...
375
by: rkusenet | last post by:
This article is very bleak about future of DB2. How credible is the author. http://www.eweek.com/article2/0,1895,1839681,00.asp
12
by: Rami | last post by:
I have some requirement for an automated payment system. The system has four machines setup as follows: 1- Two machines have a clustered database. 2- Two machines have a .net business logic...
3
by: Brad Pears | last post by:
I have a vb.net 2005 app that asks for a job number. After the job number has been entered, I run an SQL 2000 stored procedure that selects the appropriate row from the Jobs table. I do not want to...
0
by: Caper | last post by:
Hello everyone, I am a bit confused about Innodb deadlock and the "SELECT…FOR UPDATE” statement. 1. Here is a deadlock example 1) Create actor table CREATE TABLE actor(actor_id INT...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.