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.

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.).

Have you compared the db cfg , dbm cfg , registry (db2set) variables and bufferpools also ...


Cheers
Sathayram

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.

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).

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