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

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

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

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