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

[Wolfgang Bachmann]

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

[Joachim Mueller]

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]

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

[Joachim Mueller]

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