Hi All,

This table EDW_SOR_TX N has totally 30 million rows and the below mentioned query runs so very slow at the rate of 300 rows/second.
Kindly let me know how this can be fine tuned.

SELECT
POSN_DT,
AR_ID,
OU_ID,
TXN_CODE,
CNL_ID,
sum(abs(NET_CASH_FLOW_AMT)) as TOT_TXN_AMT,
SRC_STM_ID ,
'EDW_SOR_TXN' as SRC_TBL,
count(*) as TOT_NBR_OF_TXN
FROM
schema.
where
POSN_DT='2006-09-30'EDW_SOR_TXN
and CNL_ID<>'2'
group by
POSN_DT,
AR_ID,
OU_ID,
TXN_CODE,
CNL_ID,
SRC_STM_ID

Thanks in advance.

Regards,
Priya

Give the details about the indexes built on that table.

Only one index exists on this table,that is the primary key index with (TXN_CODE,POSN_DT)

If the combination of TXN_CODE and POSN_DT is unique,
and the WHERE-clause says ... where POSN_DT='2006-09-30' (one single value ! )
and TXN_CODE is part of the SELECT-clause,
then there is no need to do a GROUP BY !


each value for TXN_CODE can only occur once.
--> GROUP BY TXN_CODE will group each column in a separate group
--> COUNT(*) will always be 1
--> sum(abs(NET_CASH_FLOW_AMT)) will always be abs(NET_CASH_FLOW_AMT)

Use the DB2 Design Advisor with your above query as workload to get a suggestion in indexes from which this query would benefit.

Other than that, I don't think that your query is syntactically correct. Things like "FROM schema." are not valid FROM clauses. Likewise, what kind of predicate is "POSN_DT='2006-09-30'EDW_SOR_TXN"??

I guess you meant that TXN_CODE and POSN_DT are both in the select-list so that each group consists of a single row only

Sorry for the typo.

the query is given below.

SELECT
POSN_DT,
AR_ID,
OU_ID,
TXN_CODE,
CNL_ID,
sum(abs(NET_CASH_FLOW_AMT)) as TOT_TXN_AMT,
SRC_STM_ID ,
'EDW_SOR_TXN' as SRC_TBL,
count(*) as TOT_NBR_OF_TXN
FROM
<schema>.<tablename>
where
POSN_DT='2006-09-30'
and CNL_ID<>'2'
group by
POSN_DT,
AR_ID,
OU_ID,
TXN_CODE,
CNL_ID,
SRC_STM_ID


Umayer ,you are absolutely right.But by mistake I had given the primary key cols as (TXN_CODE,POSN_DT) but it is actually (TXN_ID,POSN_DT)

Either:

1. Reverse the order of the primary key from (TXN_ID,POSN_DT) to (POSN_DT,TXN_ID). This will change the order of the index (assuming you let DB2 create the PK index for you).

or

2. Add a new index on (POSN_DT)

If TXN_ID has less than 20 unique values in the table, I would choose option 1.

@ stolze:


POSN_DT is a constant value due to the WHERE-clause. So there is no need for that column to occur in the SELECT-list. TXN_CODE is unique if POSN_DT is constant

Yes, you are right.

when TXN_CODE is not a part of the key ,how can it be unique just bcoz posn_dt is constant.
Please elaborate.

my statement was a reply to stolzes posting. That ( stolzes post ) was before you corrected the information regarding the primary key.

So if the primary key is on column (TXN_CODE,POSN_DT) and POSN_DT is constant, TXN_CODE must be unique