Hi ,

I have a DB2 instance with
2 local DB and 1 system DB(ToolsDB) and 1 Cataloged DB
i have Db2 9.7 WSE, which is of a 32 bit processor on RHEL,

Here is my kernel configurations

[root@WESSSTDW01 netcool]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 4294967295
kernel.shmmni = 10000
kernel.shmall = 536870912
kernel.msgmni = 1024
kernel.sem = 250 256000 32 1024
[root@WESSSTDW01 netcool]#

In this when ever i submit a query it takes a long time to execute .

"SELECT value FROM GEMS.PM_MASTER_RAW WHERE asset LIKE 'C_AC1xL_S001xEC_01' AND pointpath like 'C%KW' AND orgname LIKE 'ACME' AND timestamp between '2011-02-01 09:00:00' AND '2011-02-01 10:00:00' "

How do i tune my DB to get more performance .

Pl. put in your suggestions.

Did you created index(es) on GEMS.PM_MASTER_RAW table?

If so, what are the create index statements?

Hi Sathya,

For having some direction towards solving your issue, first request, take two DB Snapshot taken from the Database before the query is triggered and after the query got executed. There can be hundreds of reason for a slow running query, but to start with discussing for solution, lets see your snapshot first.

Hi Thanks guys for your reply.

@tonkuma : we have index in PM_MASTER_RAW on (pointpath, timestamp, orgname)

@ jayanta

here i have attached the snapshot of the important DB , But we fields are not collected, what should i do to populate all fields .

which will help me in understanding the issue .

you need to switch on the monitors first before capturing the snapshots.

UPDATE MONITOR SWITCHES USING {switch-name {ON | OFF} ...}
[AT DBPARTITIONNUM db-partition-number | GLOBAL]

switch-name:
BUFFERPOOL, LOCK, SORT, STATEMENT, TABLE, TIMESTAMP, UOW