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 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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: Nid |
last post by:
How do I do row-level locking on SQL Server?
Thanks,
Nid
|
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,...
|
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....
|
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...
|
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
|
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...
|
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...
|
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...
|
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=()=>{
|
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...
|
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...
|
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...
|
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...
|
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 :...
|
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...
|
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...
|
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...
| |