Flush package cache for one sql

[Arun Srinivasan]

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

[db2start]

Do you have an explain plan for this query at the different
optimization levels? Are the statistics current for the tables and
indexes used by the query?

Cheers,
On Feb 6, 6:58 am, Arun Srinivasan <arunro...@gmai l.comwrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

[Serge Rielau]

Arun Srinivasan wrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

There are two ways to invalidate objects in the dyanamic package cache:
Hard invalidation: Shoot something that object needs to live
Soft invalidation: Add/change something that object might like: RUNSTATS

There is no option to purge a specific object from the cache.
Soft invalidation may depend on your version....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

On Feb 5, 11:58 am, Arun Srinivasan <arunro...@gmai l.comwrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

So you've altered the SP to be REOPT = ALWAYS?

[Arun Srinivasan]

On Feb 5, 3:01*pm, jefftyzzer <jefftyz...@sbc global.netwrote :

On Feb 5, 11:58 am, Arun Srinivasan <arunro...@gmai l.comwrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

So you've altered the SP to be REOPT = ALWAYS?

Yes, I used set_routine_opt s ('REOPT ALWAYS EXPLSNAP ALL QUERYOPT 7')
TO increase the optimization level. But the following is a part in the
output from db2exfmt.
Also, I ran the set_routine_opt s and the sql in the same command
window. Thus the options should be valid for the session right?

SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
Please advise.