Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    May 2003
    Posts
    37

    Unanswered: How can I improve DB2 database performance?

    Hi,
    How can I improve DB2 database performance?
    Can I improve it by setting some configurable parameters?
    Suggest me with some tips.

    Looking for your precious response?


    Thanks,
    Narender
    Thanks,
    Narender,
    Satyam Computers,
    HYD,
    INDIA

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    1. If using Linux,Windows,Unix try using the Performance wizard from Control Center.
    2. Indexes on tables. Use index wizard in Control Center.
    3. Runstats on tables (command: runstats on table table_name with distribution and detailed indexes all)

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    May 2003
    Posts
    37
    Thanks Grofaty,
    It is fine.
    But the problem is: There is no GUI tool available to me.
    I am working on SOLARIS. Are there any other paramaters Like 'MaxAppls' so that if we configure them, then will the performance will be improved?

    And how can I execute runstats command on all tables at once?


    Thanks,
    Narender


    Originally posted by grofaty
    Hi,

    1. If using Linux,Windows,Unix try using the Performance wizard from Control Center.
    2. Indexes on tables. Use index wizard in Control Center.
    3. Runstats on tables (command: runstats on table table_name with distribution and detailed indexes all)

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Mar 2003
    Posts
    33

    Re: How can I improve DB2 database performance?

    Here is one link which you should check:

    http://www.db2mag.com/db_area/archiv...q1/hayes.shtml


    (... sorry that I can give you only a link)
    -r-

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Run statistics on all tables (including system tables):

    reorgchk update statistics on table all

    But be careful using this command, I think it locks the tables. Not sure, try it. I have data warehouse where I have time to do this, so nobody access the data when I execute this command.

    Hope it helps,
    Grofaty

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    INDEXES
    On Unix/Linux there is a command db2advis for advising the indexes.

    Before you can use this tool you have to create the some tables. The tables in Linux are in sqllib/misc directory. The file name is EXPLAIN.DDL. The notes on how to execute this DDL commands are included in this file itself.

    Sintax:
    db2advis -d database_name -t 10 -i input_file -o output file

    t: 10 minutes
    input file is: SQL statements (execute db2advis without parameters and you will get the EXAMPLE on form of file)
    output file: the result from adviser is put in this file

    GUI tools
    I don't know the Solaris. But I user DB2 for Linux S/390 and there is no GUI. So I use Windows GUI (DB2 Control Center) to access Linux S/390. It works fine.

    Perhaps you can use GUI from Windows environment to control the Solaris database. Try this. It is worth the effort. I manage the Linux database on mainframe like it was on Windows. So simple!

    Hope this helps,
    Grofaty

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    With an XWindows client you can access DB2 control Centre

    Originally posted by pnarender
    Thanks Grofaty,
    It is fine.
    But the problem is: There is no GUI tool available to me.
    I am working on SOLARIS. Are there any other paramaters Like 'MaxAppls' so that if we configure them, then will the performance will be improved?

    And how can I execute runstats command on all tables at once?


    Thanks,
    Narender

  8. #8
    Join Date
    May 2003
    Posts
    37
    Hi grofaty,
    Thanks a lot.
    It is useful information only.
    You have given nice info.
    Thanks once again.


    Thanks,
    Narender



    Originally posted by grofaty
    Hi,

    Run statistics on all tables (including system tables):

    reorgchk update statistics on table all

    But be careful using this command, I think it locks the tables. Not sure, try it. I have data warehouse where I have time to do this, so nobody access the data when I execute this command.

    Hope it helps,
    Grofaty

  9. #9
    Join Date
    May 2003
    Posts
    37

    Re: How can I improve DB2 database performance?

    Hi raisa,
    Thanks a lot.
    It is useful to me.

    Narender

    Originally posted by raisa
    Here is one link which you should check:

    http://www.db2mag.com/db_area/archiv...q1/hayes.shtml


    (... sorry that I can give you only a link)
    Thanks,
    Narender,
    Satyam Computers,
    HYD,
    INDIA

  10. #10
    Join Date
    May 2003
    Posts
    37

    Re: How can I improve DB2 database performance?

    Hi Raisa,
    Thanks a lot.
    It is useful to me.

    Thanks,
    Narender


    Originally posted by raisa
    Here is one link which you should check:

    http://www.db2mag.com/db_area/archiv...q1/hayes.shtml


    (... sorry that I can give you only a link)

  11. #11
    Join Date
    May 2003
    Posts
    37
    Hi,
    Thanks a lot once again.
    I will try this.

    Regards,
    Narender



    Originally posted by grofaty
    Hi,

    Run statistics on all tables (including system tables):

    reorgchk update statistics on table all

    But be careful using this command, I think it locks the tables. Not sure, try it. I have data warehouse where I have time to do this, so nobody access the data when I execute this command.

    Hope it helps,
    Grofaty

  12. #12
    Join Date
    May 2003
    Posts
    37
    Grofaty,
    Thank you very much.

    Thanks,
    Narender




    Originally posted by grofaty
    Hi,

    INDEXES
    On Unix/Linux there is a command db2advis for advising the indexes.

    Before you can use this tool you have to create the some tables. The tables in Linux are in sqllib/misc directory. The file name is EXPLAIN.DDL. The notes on how to execute this DDL commands are included in this file itself.

    Sintax:
    db2advis -d database_name -t 10 -i input_file -o output file

    t: 10 minutes
    input file is: SQL statements (execute db2advis without parameters and you will get the EXAMPLE on form of file)
    output file: the result from adviser is put in this file

    GUI tools
    I don't know the Solaris. But I user DB2 for Linux S/390 and there is no GUI. So I use Windows GUI (DB2 Control Center) to access Linux S/390. It works fine.

    Perhaps you can use GUI from Windows environment to control the Solaris database. Try this. It is worth the effort. I manage the Linux database on mainframe like it was on Windows. So simple!

    Hope this helps,
    Grofaty

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    You can also use two books from IBM page:
    http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main

    The books are:
    - Administration Guide: Performance
    - Visual Explain Tutorial

    Hope this helps,
    Grofaty

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Buffer pool size is one of the most important performance tuning parameters. DB2 always reads data from, and writes data to, the buffer pool. If the page it needs to read or write to is not already in the buffer pool, DB2 puts it in the buffer pool first. After data is updated in the buffer pool, the updates of the tablespaces to disk are asynchronous (deferred), except that when commits are issued there is a synchronous update to the log on disk. Obviously, reading and writing to memory is much faster than to disk, so buffer pool size is very important for performance.

    The buffer pools are specified as the number of 4K pages, so 250 4K pages equals 1MB. You should increase this dramatically depending on how much memory is on your machine and what other tasks the machine is running. On a machine with 1GB of memory, and no other applications other than DB2, it is not unreasonable to have buffer pools that total up to 512MB of memory for all databases open concurrently. That would be a total of 128000 4K pages. If you used larger pages for tablespaces (4K 8K, 16K, 32K) adjust your calculations accordingly.

    Do not exceed the amount of actual real memory available for the machine when calculating buffer pools (and taking into account other open databases, applications, OS, and other system memory requirements). After you set the buffer pools for a database, and the system has been running for awhile, run a OS memory monitor to see how much free memory is available.

    Although there is no use in making the buffer pool larger than the total amount of data in the database (including indexes and system catalogue), making the buffer pools larger will usually dramatically increase performance.

    As others have mentioned, make sure you reorg data on a regular basis, run statistics on all objects, and check to ensure that the proper indexes are created and used by the applications accessing the database.
    Last edited by Marcus_A; 05-13-03 at 23:05.

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    More on DB2 buffer pools:

    If you have an very large database (relative to the amount of memory you have available for DB2 buffer pools), one strategy is to have multiple buffer pools. In some situations, I would suggest using two buffer pools as follows:

    The first buffer pool would be used for all objects (tablespaces and indexes) that are part of the system catalogue, all indexes for user tables, all small user tables, and maybe some important intermediate size tables. The objects (and buffer pool size) for this buffer pool should be chosen so that objects assigned to this buffer pool can all fit in the buffer pool at one time (or at least 80% of them can fit at the same time).

    Then second buffer pool would be reserved for all large user tables. Usually this buffer pool is not large enough to hold all the objects in the buffer pool at one time.

    Using the above scenario, you would only have to create one new buffer pool and then redefine the buffer pool assignment for the large user tables (leaving all other objects using the original buffer pool).

Posting Permissions

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