Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    51

    Unanswered: Poor performance after migrating DB2 from 9.5 to 9.7

    Hi guys,

    I'm facing terrible performance issues after DB2 migration from V9.5 to V9.7

    Here are the details :

    #1 Query :
    SELECT 1 FROM db2ent.ebcc_customer
    where state_prov_county !='' and
    legal_entity_no='6222902'
    group by country_code
    having count( distinct state_prov_county ) > 1
    and country_code ='897'"

    #2 Server using DB2 V9.5
    time db2 "SELECT 1 FROM db2ent.ebcc_customer
    where state_prov_county !='' and
    legal_entity_no='6222902'
    group by country_code
    having count( distinct state_prov_county ) > 1
    and country_code ='897'"

    1
    -----------
    1

    1 record(s) selected.


    real 0m0.71s
    user 0m0.02s
    sys 0m0.01s

    #3 Server using DB2 V9.7
    time db2 "SELECT 1 FROM db2ent.ebcc_customer
    where state_prov_county !='' and
    legal_entity_no='6222902'
    group by country_code
    having count( distinct state_prov_county ) > 1
    and country_code ='897'"

    1
    -----------
    1

    1 record(s) selected.


    real 5m16.82s
    user 0m0.02s
    sys 0m0.01s

    #4 - both dbs use exactly the same configuration parameters (db and instance)

    #5 - about stats
    I've ran maint routines .
    5.1 reorg table - for all tables
    5.2. reorg indexes all - for all tables
    5.3. runstats with distribution and detailed indexes all - for all tables

    #6 - details for server using version 9.7

    db2level
    DB21085I This instance or install (instance name, where applicable: "dbcctst")
    uses "64" bits and DB2 code release "SQL09077" with level identifier
    "08080107".
    Informational tokens are "DB2 v9.7.0.7", "s121002", "IP23367", and Fix Pack
    "7".
    Product is installed at "/opt/IBM/db2/V9.7".

    oslevel
    6.1.0.0

    Any ideas ?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How did you migrate?

    Did you have a chance to look at the explain plans?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2003
    Posts
    51
    Hi,

    yes, I generated 'explain plans' for both servers/dbs .. They are exactly the same !!

    Of course, the cost for V9.7 is greater ..

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ajfreitas View Post
    They are exactly the same !!

    Of course, the cost for V9.7 is greater ..
    These two statements seem to contradict each other. However, since you chose to keep the plans to yourself, I can only wish you good luck in your endeavour.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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