Results 1 to 5 of 5

Thread: DB2 Tuning

  1. #1
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197

    Smile Unanswered: DB2 Tuning

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you created index(es) on GEMS.PM_MASTER_RAW table?

    If so, what are the create index statements?

  3. #3
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  4. #4
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    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 .
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •