performances Tuning of DB2

[former_member186895]

Hello Experts,

I need to do performances tuning of db2.

Can anybody help help from where i can start?

What are the steps i need to consider for that?

What are the parameters related to it?

Thanks in advances

Ram

[former_member182505 · Accepted solution]

Hello Ramesh

Which Db2 data base version you are using , accordingly  there is a sap standers note for parameters

Thanks

Sadiq

[former_member186895 · Accepted solution]

Hello Sadiq,

Thank for your quick reply.

We are using DB2 LUW 10.1

Any additional steps need to perform for better performances?

Thank you

[Frank-Martin · Accepted solution]

Hi Ramesh,

performanc etuning is a wide area.There are many possible reasons for bad performance. For example bad DB configuration, long running SQL statements due to a bad access plan, badly written applications, slow network connection between DB server and application server ... Depending on the cause you need to do different things.

Do you have a special reason to do performance tuning or do you just want to check your performance proactively?

One first entry point to database performance tuning may be the time spend analysis in DBACOCKPIT. You may also want to look at the top SQL statements in the SQL cache in DBACOCKPIT.

Regards

                Frank

[former_member182505 · Accepted solution]

Hello Ramesh

i Agree with Frank,

Also you can cross check the parameters with below notes,

1700631 - DB6: Using DB2 10.1 with SAP Applications

1692571 - DB6: DB2 10.1 Standard Parameter Settings

Thanks

Sadiq

[Former Member]

Hi Ramesh,

1. Check the EWA report and take the necessary actions related to performance.

DB2 Version 10.1 for Linux, UNIX, and Windows

Insert Time Clustering (ITC) Tables

ITC tables group their records based on the insert time. Space reclamation that is, returning free space in the table back to the tablespace, is possible without a full offline or online REORG.

Index Space Reclaim

For index objects, a new function is available that reclaims free space and returns it to the table space in online mode

Adaptive Compression

In addition to the existing row compression method, adaptive compression works on page level and provides improved table data compression without the need for table reorganization.

Index Skip Scan

Composite indexes can be used during query execution even if the query does not define constraints for all index columns.

Performance Improvements

New functions have been designed for DB2 10.1 to improve performance, for example, smarter index prefetching, hash join improvements, zigzag joins, and early aggregation.

Support of Hidden Columns

During table definition, columns can be marked as implicitly hidden. Such columns are unavailable until they are explicitly referenced.

Storage Groups

The existing automatic storage infrastructure has been extended by a new object, the storage group. Storage groups contain multiple storage paths that you can define and to which you can assign tables paces. This allows for the separation of cold, warm, and hot data that can subsequently be placed on different storage media.

Multi-Core Parallelism

The use of multi-core systems in SMP architecture improves data warehouse environments.

HADR with Multiple Standbys

The HADR feature allows the use of more than one standby server.

Log File Compression

Archived log files are now automatically compressed, which leads to further disk space savings.

Thanks & Regards,

Balaram Mukku

[hans-juergen_zeltwanger]

Hello Ramesh,

I can recommend the following paper:

https://ibm.biz/BdD2Du

Hans-Juergen

[Former Member]

Hello,

what kind of performance problems you have? Take a quick look in you EWA and check the dbacockpit to find some red or yellow alerts.

Regards

Olaf

[martin_mikala]

Hi,

I recommend you as first step check EWA report. There are several section related to performance and configuration, for db too.

B.R. Martin

[former_member188883]

Hi Ramesh,

Before you begin any tuning job, you need to know where the problem is. Is it application or Database?

SAP provide General database configuration parameters which you can refer and setup as a best practice.

Regards,

Deepak Kori