Hi all,

Our DB2 7.2 server is running on Windows 2000 and the server was setup by someone 4 years ago, who is not working here anymore. Few weeks ago, we changed our DB2 server's hardware from Intel PIII 800MHz with 1G RAM to Xeon 2.8MHz with two CPUs and 1.5G RAM. The database's data is moved to the new server by restoring through an offline backup.

The new server is running okay but when it runs a query, which uses UNION ALL keyword, it takes over an hour to finish and I found that the CPU Usage in Task Manager is around 100%. However, in our old DB2 server, it takes only 3 seconds.

I have checked the DB2 database and database manager configuration but I could not find any differences in their settings.

Any ideas would be deeply appreciated.

Thank you!


Bson

The first thing I would try is to execute the runstats command on all tables and indexes with distribution on key columns. See the Command Reference for details. If you have any static SQL, you will need to rebind the packages.

Hi Marcus,

I did ran the commands for all of our tables as the sample below

db2 reorg table table_a
db2 runstats on table table_a with distribution and indexes all

Bson

Do you know how to run a visual explain on the SQL statement. That would tell you what access path is being used. If you post the explain output, the table(s) DDL, and the SQL query, someone might be able to help.

Have you compared the configuration of your new and old DBs by issuing the db2 get dbm cfg command?

Improper parameters setting can lead to performance issue.

Hi tkwlaw,

I had already check both the dbm cfg and db cfg and I could not find any difference.

But one thing I did not metion is that our old db2 7.2 server was using Enterprise Edition but in my new db2 server, we are using Workgroup Edition.

Thank all of you!

Bson

What is the fixpak number?

it is v7.1.0.98 - WR21337

I found the problem! The script, which I used for reorg and runstats does not work. Then I used runstats function in DB2 Control Center, the problem solves!

Does anyone know why the commands below causing the problem?

-- runstats command causing the problem
db2 reorg table table_a
db2 runstats on table table_a with distribution and indexes all


-- db2 generated runstats command solving the problem
RUNSTATS ON TABLE DBA.HDR SHRLEVEL CHANGE

Thank a lots guys!

Bson

did you set the correct schema in the script? may be that's the reason

Yes, I had set the schema running the commands. and after running the commands, it said "command run successfully".

The Command Reference manual says regarding the runstats command:

"The fully qualified name or alias in the form: schema.table-name must be used."

The set schema command is for SQL statements, not for DB2 commands like runstats.

bson,

If you copied the script from old computer to new one without any changes,there can be a conclusion the script has never been working, so runstats on old computer was never done with this script.

Hope this helps,
Grofaty

When doing a REORG on DB2 version 7 you should specify the index name or it does not really re-order the table rows (the reorg finishes very quickly). If you have more than one index, chose the index that you want to reorder the rows with (this is a another discussion).

REORG TABLE table-name INDEX index-name

Thanks Marcus_A, could you give me some samplel when running the runstats command and a table is more than one index ?