Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: performance problem

    I recently created a db2 database on a new AIX box, using db2look to recreate all the db objects in the new database. I have verified that both databases have the same indexes and I have run runstats on all tables and indexes. However, there is a sql statement that runs twice as long in the new database as the old one. I explained the statement on both systems and the new database is doing 32 sorts and the old database is doing 14. I feel this sorting maybe causing the problem. The major different that I'm aware of between these systems is that the old AIX box has more spindels than the new old. Would fewer spindels cause more sorting? Where can I look for the cause of all the sorting? All the dbm and db configurations have the same values. Thanks in advance for your help.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It might help if we could see the SQL, DDL, and Explain output. What is the DB2 version/fixpak and operating system?

    Also, please provide details about the tablespace and container set-up for the tables in the query, and the same details for the DB2 system temporary tablespaces.

    Depending on how many different containers are stet-up for each of the tablespaces, there can be a difference in the degree of intra-partition parallelism used by DB2.

    Also information about the disk drive hardware configuration would be useful (RAID, etc.).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you compared the db cfg , dbm cfg , registry (db2set) variables and bufferpools also ...


    Cheers
    Sathayram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Apr 2004
    Posts
    190

    config settings

    All the db cfg and dbm cfg settings have the same values. Both databases have the very same number of table spaces, the same number and size logical volumns for each table space. The database is db2 7.1. The OS is AIX 4.3.3. We took extreme care to make sure the new database had the very same settings. The only difference is the new box has fewer spindels. I don't know what the number of spindels are for each system, but I know the new box has fewer. I inherited this database, and all the details about hardware were in place before I arrived.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One thing to try on the problem SQL statement is to increase the query optimization level to 7 (instead of the default of 5).

    Also, the syntax on the runstats has changed on version 8, so review the Command Reference manual and make sure you are capturing all needed stats on the table and all indexes. I would also recommend that you collect distribution stats on key columns (columns in an index).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    both DBs are in same server or diffrent.

    if differ, config is same ?

    RAM, cache, CPU's , disks

    run

    topas

    is there any bottel necks, CPU bounded.

    lot of page swaping

    in DB side , tablespace , no containers, BPs


    while running your process , get snapshot for DBM , DB & application

    see is ther any prefetch wait , BP , catalog , package cache hit ratio, max file closed, sorts, agent details , allocated , used , idle, stolen


    check above things, let me know , if you need more info.

    Hope it may helps.

    Thank You


    Lekharaju Ennam

Posting Permissions

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