Hi All,

When I execute a query in version 7.2, I get the cost as 648.
I migrated my database from Db2 v7.2 to v8.1.2.

If I run the same query again, its cost is about 748.

When I check the access plan, then I find that version 8.1 is not using some indexes that exist on the tables which also got migrated from version 8.

Anyone knows why and what to do?

Pls do mail to me ASAP, if u know!

Thanks & Regards,
Priya

Hi Priya

Try running runstats on the tables in question
they optimizer might not have the appropriate
information; - and then run the query again.

Its just a guess but I hope it helps

Cheers

Thanks for the response!
But I have tried RUNSTATS already on the affected tables and also on the systables.

Also, I have converted the Type-1 index to type-2 using REORG indexes.

Both have not helped!

Rgds,
-hp

hmmm... thats not very good

- which optimizing level are you using?

Hi,

Total cost of 748 is very very good. What is total execution time of this query?

Grofaty

I didnot change or specify any optimizing level - its the default

Its about 6 minutes ...since its on a query and there is a huge list of values comapring with the IN clause...
The query is in a stored procedure where the application calls this repetedly and I am afraid of the greater fall on performance.

Hi,

Can you post the SQL? Please also provide index information (on which table and on which columns), is index cluster or regular, etc.

What is your operating system (windows, unix) and version?

Grofaty

Did you runstats with indexes?

RUNSTATS ON TABLE <blah> AND INDEXES ALL

If you have the space/time, this might further help:
RUNSTATS ON TABLE <blah> AND DETAILED INDEXES ALL

If it's static, rebind after...

Check the cardinality on the access plan nodes, too, to see if it's realistic. If it is, then it may not be a stats issue.

The SQL is on join between a view & a table. The SQL is at the end of thsi message!
The index is on the table and its a regular index.The operating system is Windows.

Yes. I had executed the command RUNSTAT on indexes.

What I observe from the access plan is that the index which exists on that table- column was used by version 7 whereas its not being used by 8 and so there is an increase in the cost. I notice whenever there is a NLJOIN on the two cloumns the cost difference is huge between the two versions.


Select view.Col1, cas.c1, cas.c2,view.c3,
view.c4, view.c5, view.c6, view.c7,
view.c8, view.c9, view.c10, view.c11,
view.c11, view.c12, view.c13
from VIEW view,TABLE cas
where view.c1 IN (0, 4)
and view.c2 IN( 12397, 12398, 12399, 12400, 12403, 12404, 12405,
12406, 12407, 12408, 12409, 122910, 122911, 122912, 122913, 122914, 122915,
122916, 122917, 122918, 12420, 12421, 12422, 1229123, 1229124, 1229125, 12426,
12427, 12428, 12429, 12430, 12431, 12432, 12433, 12434, 12435, 12436,
12437, 12438, 12439, 12440, 124291, 12442, 12443, 12444, 12445, 12446,
12447, 12448, 12449, 12450, 12451, 12452, 12453, 12454, 12455, 12456,
12457, 12458, 12459, 12460, 12461, 12462, 12463, 12464, 12465, 12466,
12467, 12468, 12469, 12470, 12471, 12472, 12473, 12474, 12475, 12476,
12477, 12478, 12479, 12480, 12481, 12482, 12483, 12484, 12485, 12486,
12487, 12488, 12489, 12490, 12491, 12492, 12493, 12494, 12495, 12496,
12497, 12498, 12499, 12500, 12501, 12502, 12503, 12504, 12505, 12506,
12507, 12508, 12509, 12510, 12511, 12512, 12513, 12514, 12515, 12516,
12517, 12518, 12519, 12520, 12521, 12522, 29101, 29102, 29103, 29104,
29105, 29106, 29107, 29108, 29109, 29110, 29111, 29112, 29113, 29114,
29115, 29116, 29117, 29118, 29119, 29120, 29121, 29122, 291123, 291124,
291125, 29126, 29127, 29128, 29129, 29130, 29131, 29132, 29133, 29134,
29135, 29136, 29137, 29138, 29139, 29140, 291291, 29142, 29143, 997510)
and view.c2= ?
and view.c3 = ?
and view.c4 = ?
and view.C1 = cas.C1
and view.C2 = cas.c2
and cas.c5 = ?
and cas.c6 IN( 'A', 'D')
and ((cas.c6 <> 'D')
or (cas.c7 > CURRENT DATE))
order by view.c3, view.c4, view.c5, view.c6, view.c7, view.C8

Hi db2priya,

Can you also post the view definition and explain of the sql? Can you please also post the index definitions?

Thanks,
Grofaty

after you run RUNSTATS, try to BIND PLAN or BIND PACKAGE
and run your query