Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Wimbledon
    Posts
    10

    Smile Unanswered: db2 udb datawareshousing

    Hi

    I've just registered and am not a db2 dba, but hopefully someone here will be able to give me some advice on how we can improve the performance of our datawarehouse.

    Just to give you some back ground:

    We currently use DB2 UDB to store a data warehouse, which can be queried by standard users via Business Objects (a reporting software tool).
    We have noticed a lot of performance problems here and are looking at ways we can improve this.

    For example, I recently collected over 100 different user SQL statements and am in the process of running these through index advisor to see if any additional indexes could be added to improve things.
    We have also noticed that performance is considerably worse when multiple SQL statements are running. This makes sense since the machine is doing more work, but if anyone has any experience with data warehouse and db2, so suggestions on parameters or settings which we could try changing would be helpful.

    Hopefully that has given you all something to think about, and hope I havent bored you too much

    Any help would be appreciated
    thanks
    brendan

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: db2 udb datawareshousing

    Brenden

    You have taken a right step by looking at the indexes ...

    In addition to indexes, you can look at increasing the SORTHEAPSZ and INTRA_PARALLELISM ...

    Others: Use DMS Containers and keep index and data on different tablespaces .... Trying to spread over the data/indexes over multiple disks/containers ...

    Increase the size of the bufferpool ... In DW, it is a genral recommendation to have one large BP and one small BP

    Sure, there are many other parms ..

    This is good for a beginner :

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

    Cheers

    Sathyaram

    Originally posted by brendan
    Hi

    I've just registered and am not a db2 dba, but hopefully someone here will be able to give me some advice on how we can improve the performance of our datawarehouse.

    Just to give you some back ground:

    We currently use DB2 UDB to store a data warehouse, which can be queried by standard users via Business Objects (a reporting software tool).
    We have noticed a lot of performance problems here and are looking at ways we can improve this.

    For example, I recently collected over 100 different user SQL statements and am in the process of running these through index advisor to see if any additional indexes could be added to improve things.
    We have also noticed that performance is considerably worse when multiple SQL statements are running. This makes sense since the machine is doing more work, but if anyone has any experience with data warehouse and db2, so suggestions on parameters or settings which we could try changing would be helpful.

    Hopefully that has given you all something to think about, and hope I havent bored you too much

    Any help would be appreciated
    thanks
    brendan
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Hello Brendan,

    I am in a similar situation like you with datamarts in DB2 that are accessed by Access and Business Objects. I have recently started to look into the use of keeping statistics up to date for all tables involved and occasionaly reorganizing the larger facttables when DB2 suggests this.

    There are a couple of command line commands to check upon these things:

    reorgchk update statistics on table <schema>.<tablename>

    or

    reorgchk update statistics on table all

    will give some insight. I have seen some dramatic performance improvement with just keeping things up to date, WITHOUT the need to add indices !
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In addition to the very good suggestions above, I would increase the size of the system temporary tablespace to at least 3 times as large as the largest table. Make sure it has multiple containers spread over multiple physical drives (except if you use RAID 5).

    Usually the best way to do this to create a 2nd system temporary tablespace and then drop the default one.

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

    Something about performance has already been answered in this forum, look at:
    http://dbforums.com/showthread.php?threadid=782441

    If somebody has any tip please write it to above link theme

    Hope this helps,
    Grofaty

Posting Permissions

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