How to avoid the DB2 Optimizer perfroming the Table scan
as the table data is very large. The table query will be
Select A.C1,A.C2,A.C3, A.C4
From tbl1 A,tbl2 b ,tbl3 C
Where C.REGION= :DECLGEN-region and
B.date <> '00000000'
and( (B.date < :DCLGEN-VAR1)
(B.date < :DCLGEN-VAR1
and A.IND = 'Y')
In the above table columns C1 C2 C3 and C4 are Indexed
The Primary key to all the table is C1 and C2
There will be any performance improvements if we index the coulmns
REGION,IND and DATE to perform fetch faster.
But IND wil have only two values 'y' or 'n'
Date can greater than Current date by 2 months
and less than Current Date by 6 months
In the table tbl1 there 1,38000 records and 69,000 pages to perform the table scan
And the Cardinality for the tbl2 is 209685.
Can you help in performance tuning.
Other way is to get the coulmns statistic by runing the RUNSTATS
Can body give any hints on how will this improve the performance
Can you see the changed query below with join predicate
Plan uses tablespace scan which may take long time.
The Columns C.REGION, A.IND and B.DATE are not indexed
Do indexing this coulmns will have performance imporvement.
Plan also does Sequential prefetch has the coulmns are not indexed.
Do prefetch actualy executes the query before the programs run and keeps the result in the buffer pool?
How DB2 evaluates the cost when Host variables are used in the query?
What is REPOPT?
SELECT A.C1,A.C2,A.C3, A.C4
FFROM TBL1 A, TBL2 b ,TBL3 C
-- Join Predicates the coulmns C1 and C2 are the Primary keys of the
-- three tables and TBL1 is indexed by C1 , C2 , C3 and C4
(A.C1 = B.C1 AND
A.C2 = B.C2 AND
A.C1 = C.C1 AND
A.C2 = C.C2 AND )
AND B.DATE <> '00000000'
AND (( B.DATE < :DCLGEN-VAR1)
(B.DATE < :DCLGEN-VAR1
AND A.IND = 'Y')