Hi All,

I am new to DB2, I have a requiment to tune the below mentioned query.


select a11.SE_ID SE_ID,
sum(a11.D41A_NET_CHRG_AM) WJXBFS1
from GMAPS_SE_BOUNDS_QLY a11
join VMUS00.GMAPS_PRODUCT a12
on a11.GMAPS_PROD_CD = a12.GMAPS_PROD_CD
join SE_CHARACTERISTICS_N a13
on a11.SE_ID = a13.SE_ID and
a11.SE_NO = a13.SE_NO and
a11.SOURCE_SYS_ID = a13.SOURCE_SYS_ID
join M0009_GMAPS_GENSIS_INDUS_SMRY_LVL_15 a14
on a13.SE_INDUS_DS_CD = a14.SE_INDUS_DESC_CD
where (a13.CTRY_ID in ('392')
and a14.GENESIS_ACCT_CD in ('0010-S/E DISCOUNT BUSINESS ', '0020-AMEX DISCOUNT BUSINESS ')
and a11.RPT_PER_DT in ('2008-03-31')
and a12.GMAPS_SUPER_GRP_CD in ('0001'))
group by a11.SE_ID


I am repeatedly getting to my Database same kind of query with small filter changes marked in Bold.
I heard that there is MQT, which will improve the preformce of query. but I am not sure how to approach MQT for this query. Please help me to tune the above mentioned query.

Thanks in advace

Are you using parameter markers on these queries, so that the SQL and access path are cached? Have you explained the SQL to see what the access path looks like and to confirm you are using proper indexes when accessing these tables, tried index advisor? These are a couple of the many items you should look at prior to deciding on a MQT.
As far as the MQT goes, you would want to create it with the same SQL, minus the conditions that are apt to change with each execution. In other words leave out the predicates you have marked in bold.
One last item I wanted to mention was you should really keep the extra items on the joins of the different tables in the ON clause. It won't make much difference in this query due to all tables are INNER JOINed to each other, but if you were to throw in a LEFT OUTER JOIN, you would be negating the use of an OUTER JOIN. As an example, here is your query keeping everything where it belongs ( as I said not very important with inner joins, but very important with outer joins):
select a11.SE_ID SE_ID
,sum(a11.D41A_NET_CHRG_AM) WJXBFS1
from GMAPS_SE_BOUNDS_QLY a11

join VMUS00.GMAPS_PRODUCT a12
on a11.GMAPS_PROD_CD = a12.GMAPS_PROD_CD
and a12.GMAPS_SUPER_GRP_CD in ('0001')

join SE_CHARACTERISTICS_N a13
on a11.SE_ID = a13.SE_ID
and a11.SE_NO = a13.SE_NO
and a11.SOURCE_SYS_ID = a13.SOURCE_SYS_ID
and a13.CTRY_ID in ('392')

join M0009_GMAPS_GENSIS_INDUS_SMRY_LVL_15 a14
on a13.SE_INDUS_DS_CD = a14.SE_INDUS_DESC_CD
and a14.GENESIS_ACCT_CD in ('0010-S/E DISCOUNT BUSINESS ', '0020-AMEX DISCOUNT BUSINESS ')

where a11.RPT_PER_DT in ('2008-03-31')
group by a11.SE_ID

Dave

Hi Dave,

Thanks for your valuable suggestions, So as per MQT creation will it work in my case?

Nag

I don't know. You'll have to tell us if it works or not.
Dave