Improving Performance in DB2

[Rahul B]

Hi,

We have been migrating to DB2 and it has been the trend that the
application has become somewhat slow.
It could be because of the application problems or it could be because
i am not very aware of how to try to improve the performance of the
DB2.
The only thing i am aware is about the RunStats utility.

Can somebody give me various steps that i can take to try to improve
the application performance?

I think the problem will grow a lot when all the people start using
DB2 and performance testing is done.

Thanks

Rahul

[rdudejr]

On Aug 30, 4:52 am, Rahul B <[e-mail]:

Hi,

We have been migrating to DB2 and it has been the trend that the
application has become somewhat slow.
It could be because of the application problems or it could be because
i am not very aware of how to try to improve the performance of the
DB2.
The only thing i am aware is about the RunStats utility.

Can somebody give me various steps that i can take to try to improve
the application performance?

I think the problem will grow a lot when all the people start using
DB2 and performance testing is done.

Thanks

Rahul

A few things to get you started:

db2 reorgchk command will inform you of which tables are in need of
reorganization. Results are built on database statistics, so runstats
needs to have been done recently.

db2 update monitor switches using bufferpool on
db2 get snapshot for bufferpools on database_name

those two commands will show you how the bufferpools look. Find the
BP hit ratio by taking (assync read / (assync read + physical read) )

For oltp 95% BP hit ratio is good, DSS closer to 50% is acceptable.

db2 list applications show detail

Check to see if certain applications are frequently in Lock-wait
state. DB2 default isolation levels are more strict than other
databases such as SQL server, so many times conversions will have
issues with lock contention if isolation levels are not submitted by
the application.

These are just a few things. It would be helpful to post your (db2
get db cfg) and (db2 get dbm cfg) as well as (db2set -all) output so
that your configuration settings can be examined.

[Knut Stolze]

rdudejr wrote:

On Aug 30, 4:52 am, Rahul B <[e-mail]:

>Hi,

We have been migrating to DB2 and it has been the trend that the
application has become somewhat slow.
It could be because of the application problems or it could be because
i am not very aware of how to try to improve the performance of the
DB2.
The only thing i am aware is about the RunStats utility.

Can somebody give me various steps that i can take to try to improve
the application performance?

I think the problem will grow a lot when all the people start using
DB2 and performance testing is done.

Thanks

Rahul

A few things to get you started:

db2 reorgchk command will inform you of which tables are in need of
reorganization. Results are built on database statistics, so runstats
needs to have been done recently.

db2 update monitor switches using bufferpool on
db2 get snapshot for bufferpools on database_name

those two commands will show you how the bufferpools look. Find the
BP hit ratio by taking (assync read / (assync read + physical read) )

For oltp 95% BP hit ratio is good, DSS closer to 50% is acceptable.

db2 list applications show detail

Check to see if certain applications are frequently in Lock-wait
state. DB2 default isolation levels are more strict than other
databases such as SQL server, so many times conversions will have
issues with lock contention if isolation levels are not submitted by
the application.

These are just a few things. It would be helpful to post your (db2
get db cfg) and (db2 get dbm cfg) as well as (db2set -all) output so
that your configuration settings can be examined.

You may also want to run the DB2 Configuration Advisor to get a good
baseline for the system configuration. And running the workload through
the DB2 Design Advisor would also be good.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Do you use any tools to migrate (e.g. Migration Tool Kit)?
Automated tools "fluff up" the code and can make execution slow.
(I have seen 20x is some bad cases).
What you need to do is isolate the critical areas, compare your source
SQL with the produced SQL and remove any excess emulations that aren't
needed.
In the case of Oracle for example hunt for ORA8 function or COALESCE
especially in predicates.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

On Mon, 03 Sep 2007 05:02:21 +0000, Rahul B scribbled:

Here are the database manager and database configuration parameters.
Please suggest the changes that may be required in these.
/home/db2inst1>db2 get dbm cfg

[snipped huge output dump]

I strongly recommend that instead of posting your entire database config
(which is quite pointless - see below) you first try to follow the many
excellent suggestions people have provided you with, including but not
limited to:

1) Run your workload through the DB2 configuration advisor. By "workload"
I mean a good sized sample of the queries and other operations you will
be running against the database (as per Knut's message)

2) Use the REORGCHK command to check for tables and indexes in need of
reorganization, and check the buffer pool hit ratios (see rdudejr's
message)

3) Check any migrated logic for unnecessary emulations and performance
critical statements (see Serge's message)

Please understand that the optimal configuration values for the database
and the database manager depend variously on what workload you are
expecting it to perform (OLTP? OLAP?), the hardware you're using (small?
big? distributed?), and several other factors - for which you have
provided scant information (beyond "it's been migrated from Oracle" and
"I've tried runstats").

Incidentally, that's not a request for another enormous pile of
information - try the suggestions above first.
Cheers,

Dave.

[Knut Stolze]

Dave Hughes wrote:

On Mon, 03 Sep 2007 05:02:21 +0000, Rahul B scribbled:

>Here are the database manager and database configuration parameters.
Please suggest the changes that may be required in these.
/home/db2inst1>db2 get dbm cfg

[snipped huge output dump]

I strongly recommend that instead of posting your entire database config
(which is quite pointless - see below) you first try to follow the many
excellent suggestions people have provided you with, including but not
limited to:

1) Run your workload through the DB2 configuration advisor. By "workload"
I mean a good sized sample of the queries and other operations you will
be running against the database (as per Knut's message)

2) Use the REORGCHK command to check for tables and indexes in need of
reorganization, and check the buffer pool hit ratios (see rdudejr's
message)

3) Check any migrated logic for unnecessary emulations and performance
critical statements (see Serge's message)

Please understand that the optimal configuration values for the database
and the database manager depend variously on what workload you are
expecting it to perform (OLTP? OLAP?), the hardware you're using (small?
big? distributed?), and several other factors - for which you have
provided scant information (beyond "it's been migrated from Oracle" and
"I've tried runstats").

Incidentally, that's not a request for another enormous pile of
information - try the suggestions above first.

I would like to add the following. The OP said that "the application has
become somewhat slow". What does that mean? Does the application still
meet its performance goals? If not, an application trace should reveal
where the performance is lost. (Assuming that the application has the
capabilities to collect such a trace.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany