Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2005
    Posts
    9

    Unanswered: DB2 Performance Problem

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    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
    Feb 2005
    Posts
    9
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2005
    Posts
    3
    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.

  6. #6
    Join Date
    Feb 2005
    Posts
    9
    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

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is the fixpak number?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Feb 2005
    Posts
    9
    it is v7.1.0.98 - WR21337

  9. #9
    Join Date
    Feb 2005
    Posts
    9
    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

  10. #10
    Join Date
    Aug 2004
    Posts
    138
    did you set the correct schema in the script? may be that's the reason

  11. #11
    Join Date
    Feb 2005
    Posts
    9
    Yes, I had set the schema running the commands. and after running the commands, it said "command run successfully".

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    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

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    Feb 2005
    Posts
    9
    Thanks Marcus_A, could you give me some samplel when running the runstats command and a table is more than one index ?

Posting Permissions

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