Flush package cache for one sql

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_opts 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!!

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...@gmail.comwrote:

Quote:

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_opts 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!!

Arun Srinivasan wrote:

Quote:

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_opts 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

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

Quote:

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_opts 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?

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

Quote:

On Feb 5, 11:58 am, Arun Srinivasan <arunro...@gmail.comwrote:
>

Quote:

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_opts 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_opts ('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_opts 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.

Flush package cache for one sql

re: Flush package cache for one sql

re: Flush package cache for one sql

re: Flush package cache for one sql

re: Flush package cache for one sql

Similar DB2 Database bytes

/bytes/development

/bytes/it

/bytes/about