Performance problem after migration to DB2 v8

[Mario.Reif]

We have developed an application which was running under DB2 v7.2.5
quite well for some years. Four weeks ago we installed DB2 v8.1.5
Express Fixpak 5 on a new Server (hardware is nearly the same as on the
DB2 v7.2.5 machine). The new Server runs on Windows 2003. Last week we
installed another server with the same hardware with DB2 8.1.5 Workgroup
Server Fixpak 5 under Windows 2000 Server. Both servers running DB2 v8
databases are about 10x slower than our old server with DB2 v7.2.5.

On our DB2 7.2.5 server we made an offline backup. On the DB2 8.1.5
machines we made a restore to import and migrate the database.

The parameters for the database manager and the parameters for the
database are the same under v7 and v8.

The database under DB2 v7.2.5 was tuned very well. We used a lot of
indexes and we have allocated enough RAM for the buffer pool.
The DB2 v8.1.5 seems to ignore most of our indexes when executing SQL
statements. We have already tried to use another optimization level
rather than the default level, but the performance problems are the same.

We are NOT using runstats because all of our SQL statements were tuned
against v7.2.5 with indexes. In the past we have seen that runstats
would lead to a non predictable performance behavior of the database.
The database has about 150 tables (5 tables > 3 million records, 5
tables > 100.000 records, the other tables are small). All tablespaces
are SMS. The database is used in an OLTP environment, so we have to
assure that the SQL statements have always the same performance behavior.

Hundreds of our SQL statements within the application are extremely slow
under DB2 v8. It is not a suitable solution for us to tune all these
statements against v8, because this would take some months of work.
We rather think that we have overlooked some important new
feature/parameter in the DB2 v8 software. Or maybe the DB2 v8 SQL
optimizer is really that worse .....??

We have also tried to use the Configuration Advisor. We used the
suggested parameters - without success.

We also know that the problems are caused by single (and quite simple)
SQL statements and not by huge sorts or by huge inserts/updates.

The following example is a simple UPDATE statement which should update
one single record. The columns of the where clause are the same as the
primary key index columns. The snapshot utility show us that the
database is making a full table scan, because it reads 115599 rows (the
complete table). Normally we think the database should use the primary
key index to find the row in this simple example. When we try to use the
same where clause within a SELECT statement, then the database uses the
primary key index and reads exactly 1 row.

Anzahl Ausführungen = 1
Anzahl Kompilierungen = 1
Schlechteste Vorbereitungsze it (ms) = 21
Beste Vorbereitungsze it (ms) = 21
Intern gelöschte Zeilen = 0
Intern eingefügte Zeilen = 0
Gelesene Zeilen = 15599
Intern aktualisierte Zeilen = 2
Geschriebene Zeilen = 5
Sortiervorgänge für Anweisungen = 0
Logische Lesevorgänge im Pufferpool = 15389
Physische Lesevorgänge im Pufferpool = 0
Logische Lesevorgänge in temporären Daten des Pufferpools = 6
Physische Lesevorgänge in temporären Daten des Pufferpools = 0
Logische Lesevorgänge im Pufferpoolindex = 15527
Physische Lesevorgänge im Pufferpoolindex = 0
Logische Lesevorgänge im temporären Pufferpoolindex = 0
Physische Lesevorgänge im temporären Pufferpoolindex = 0
Gesamte Ausführungszeit (sek.ms) = 0.318457
Gesamte Benutzer-CPU-Zeit (sek.ms) = 0.312500
Gesamte System-CPU-Zeit (sek.ms) = 0.000000
Anweisungstext =
UPDATE ag_produktion SET stueck_gesamt = stueck_gesamt + ( 1 * 1 ),
takte = takte + 1 WHERE werkscode = 5 AND
maschinennummer = 'abc' AND auftragsnummer = 'anr' AND
splittnummer = 0 AND arbeitsgangnumm er = 30 AND (beginn <
'2005-06-05-15.00.00' AND status = 'A')

Any kind of answers or hints would be appreciated.

[Mark A]

"Mario.Reif " <Ma********@gam ed.com> wrote in message
news:40******** @e-post.inode.at.. .

We are NOT using runstats because all of our SQL statements were tuned
against v7.2.5 with indexes. In the past we have seen that runstats
would lead to a non predictable performance behavior of the database.
The database has about 150 tables (5 tables > 3 million records, 5
tables > 100.000 records, the other tables are small). All tablespaces
are SMS. The database is used in an OLTP environment, so we have to
assure that the SQL statements have always the same performance behavior.

DB2 version 8 is not slower than version 7, at least not in any measurable
amount. You have some kind of serious problem.

I don't understand your reason for not running runstats in version 7, but
that sounds like it could be your problem.

First you should reorg your tables, then do runstats with full distribution
and index all. Check the command reference for details on the exact syntax.

You can backup your database before hand and then restore it if you don't
like the performance with the runstats. It is very rare that runstats causes
a performance problem, especially if you have the latest fixpak (which I
don't believe you had with version 7).

[Mario.Reif]

Hello Mark,

thank you for your posting!

Today we tried to set all our tables VOLATILE (ALTER TABLE...).
After this the database performance was 500% better than before!!

Tonight we will run a script which will run runstats and reorg for all
tables and indexes as you suggested.
I will let you know about the impact of this script to the database
performance tomorrow.

Regards
Mario

DB2 version 8 is not slower than version 7, at least not in any measurable
amount. You have some kind of serious problem.

I don't understand your reason for not running runstats in version 7, but
that sounds like it could be your problem.

First you should reorg your tables, then do runstats with full distribution
and index all. Check the command reference for details on the exact syntax.

You can backup your database before hand and then restore it if you don't
like the performance with the runstats. It is very rare that runstats causes
a performance problem, especially if you have the latest fixpak (which I
don't believe you had with version 7).

[Mark A]

"Mario.Reif " <Ma********@gam ed.com> wrote in message
news:40******** @e-post.inode.at.. .

Hello Mark,

thank you for your posting!

Today we tried to set all our tables VOLATILE (ALTER TABLE...).
After this the database performance was 500% better than before!!

Tonight we will run a script which will run runstats and reorg for all
tables and indexes as you suggested.
I will let you know about the impact of this script to the database
performance tomorrow.

Regards
Mario

Setting the table to volatile basically ignores the statistics (which is
good in your case since you didn't run runstats) and uses any indexes which
are available.

When you do the runstats, make sure you gather distribution stats at least
on the key columns. DB2 will ignore the new statistics unless you turn
volatile off.