Predictive Maintenance and Quality version 1.0 performance tuning guidelines

Product documentation


Abstract

You can tune the performance for IBM Predictive Maintenance and Quality.

Content

Problem: Very slow response times for running the IBM Cognos Business Intelligence reports

In some circumstances, you can experience very slow response times for Cognos reports, particularly Site overview reports.

This happens when the DB2 database contains large volumes of data, for example, if the EVENT OBSERVATION table contains more than three million records.

Guidance

To improve response times, create database indexes, and run runstats on the indexes. You can get recommendations for the most appropriate actions to take by running the db2advis command (DB2 design adviser).

Steps

1. Create the EXPLAIN table using the following command:


db2 -tvf ~/sqllib/misc/EXPLAIN.DDL

2. Bind the following files found in the sqllib/bnd folder in the home directory of the db2 user :
db2 bind db2advis.bnd blocking all grant public
db2 bind db2advworkload.bnd blocking all sqlerror continue grant public
db2 bind db2advworkload.bnd blocking all sqlerror continue grant public
db2 bind db2advis.bnd blocking all grant public

3. Execute the db2advis command to get the recommendation using the following command:

db2advis -d <database name> -s "< sql of the Cognos report >"  >recommendation.txt

Where:
-d database-name
Specifies the name of the database to which a connection is to be established.
-s " statement"
Specifies the text of a single SQL statement to be assessed and have indexes suggested by the Design Advisor. The statement must be enclosed by double quotation marks.
For more information, see http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0002452.html


Problem: Deadlock errors happen when parallel processing is enabled
This problem is typically seen when parallel processing is enabled by increasing additional instances, and all messages are routed to single folders and queues.

The error message is named EventError.txt and is found in the \error folder in the IBM WebSphere Message Broker node, location defined by the MQSI_FILENODES_ROOT_DIRECTORY environment variable.

Error message text:
"Error:Label:StdEventLoad_1.LoadEvent:TransactionId:fbc6b4c0-b434-11e2-8336-09762ee50000TransactionTime:2013-05-04 02:34:022322:Child SQL exception:[unixODBC][IBM][CLI Driver][DB2/LINUXX8664] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2". SQLSTATE=40001"

Guidance

Implement Parallel processing as defined in the IBM Predictive Maintenance and Quality Solution Guide. You can find this in the IBM Predictive Maintenance and Quality information center ( http://pic.dhe.ibm.com/infocenter/pmq/v1r0m0/index.jsp).

Steps

If the error persists, try these steps:

1. Connect to the database using the following command:

$ db2 connect to db <dbname [IBMPMQ]>

2. Set the isolation level to RR using the following command:

$ db2 set isolation level to RR

3. Check the value of the dead lock check time setting using the following command:

$ db2 get db cfg |grep DL

The recommended values are shown in this table:

Interval for checking deadlock (ms) (DLCHKTIME) = 20000
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST


4. If the value for the DLCHKTIME property is less than 2000, then set the value using the following command:

'db2 "update db cfg for <dbname> using DLCHKTIME 20000 immediate" '

5. Check the value of Lock list and percentage of Locks allowed per application

$ db2 get db cfg |grep LOCK

The recommended values are shown in this table:

Maximum storage for lock list (4KB) (LOCKLIST) = 100000
Percentage of lock lists per application (MAXLOCKS) = 97
Lock timeout (sec) (LOCKTIMEOUT) = -1
Block non logged operations (BLOCKNONLOGGED) = NO
Lock timeout events (MON_LOCKTIMEOUT) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE

6. If the value for the LOCKLIST property is less 1000, then set the value using the following command:

'db2 "update db cfg for <dbname> using LOCKLIST 100000 immediate" '

7. If the value for the MAXLOCKS property is less than 97, then set the value using the following command:

 'db2 "update db cfg for <dbname> using MAXLOCKS 97 immediate" '

Rate this page:

(0 users)Average rating

Document information


More support for:

Predictive Maintenance and Quality

Software version:

1.0

Operating system(s):

Linux

Reference #:

7038975

Modified date:

2013-09-26

Translate my page

Machine Translation

Content navigation