Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: Migrating to Db2

    Hi

    I ahve a query in oracle that needs to be migrated to db2.

    This a online account system and the account_transactions is partitioned by month and each month has about 2 billion records.

    In oracle they use something like

    select /*+ parallel(t 20) */
    sum(balance) from account_transaction t

    and the result is returned in about 15 minutes.

    How do i optimize the table structure and the query in db2 v8.2.

    Basically what i understood was that parallel 20 hint tells the oracle optimizer to use 20 threads to csan the table and return the results.

    regards
    Hrishy

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    With the information you have provided here, this is what I would do :
    a) CREATE 20 tables - one for each month,eg: tm1,tm2,tm3
    b) Create a UNION ALL View over it - eg. tsales
    [ a and b together give a range partitioned table ]
    c) Enable parallelism by setting INTRA_PRARALLEL to yes.
    Issue
    select sum(sales_am) from tsales.
    This will be executed as 20 different queries and the result set unioned and merged again to produce the single row result set.
    For tables of this size, make sure your bufferpools are well sized and tuned and num io cleaners and num io servers and softmax config parameters in addition to the tablespace extent size and prefetch size.
    You may also consider using MQTs to substantially reduce the load on the historic tables
    HTH
    Sathyaram

    Quote Originally Posted by db2hrishy
    Hi

    I ahve a query in oracle that needs to be migrated to db2.

    This a online account system and the account_transactions is partitioned by month and each month has about 2 billion records.

    In oracle they use something like

    select /*+ parallel(t 20) */
    sum(balance) from account_transaction t

    and the result is returned in about 15 minutes.

    How do i optimize the table structure and the query in db2 v8.2.

    Basically what i understood was that parallel 20 hint tells the oracle optimizer to use 20 threads to csan the table and return the results.

    regards
    Hrishy
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2004
    Posts
    115
    Hi

    How will db2 choose how many parallel process to run per table like if want to use 5 threads to scan tm1 how do i tell that to db2 ?

    regards
    Hrishy

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You do not do it in the query as you would with Oracle ...

    To some extent, you can influence the optimizer to select the number of parallel thread by using the CURRENT DEGREE ...

    But, I would prefer to let the DB2 optimizer select the most efficient access plan and no. of parallel threads to run ... The optimizer makes this decision based on the number of CPUs. the number of concurrent applications, bufferpool space available, prefetch size of the tablespaces, numioservers, you table statistics etc.

    For an example, let's say , you have twnety 2 bllion record tables running on a box with 4 CPUs ... There is no point in spawing twenty threads at the same time as you will not have enough resources to handle the 20 threads ... Effectively, only 4 threads will be doing real work with the remaiing 16 waiting for CPU ...

    Generally, in DB2, the optimizer is allowed to make decisions on most things .. You influence optimizer's decision by means of the parameters ..

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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