DB2 Optimization Service Center: Purging of tables

George Palko

DB2 Optimization Service Center: Purging of tables
Hi List,

We just installed the OSC product as part of our V9 migration. We were wondering if anyone has done the same and if so how are you
purging old rows from the numerous tables that are part of the product. If you could supply SQL that would be great.

Thank you,
George





-----------------------------------------
CONFIDENTIALITY NOTICE: The Ohio Public Employees Retirement System
intends this e-mail message, and any attachments, to be used only
by the person(s) or entity to which it is addressed. This message
may contain confidential and/or legally privileged information. If
the reader is not the intended recipient of this message or an
employee or agent responsible for delivering the message to the
intended recipient, you are hereby notified that you are prohibited
from printing, copying, storing, disseminating or distributing this
communication. If you received this communication in error, please
delete it from your computer and notify the sender by reply e-mail.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Suresh Sane

Re: DB2 Optimization Service Center: Purging of tables
(in response to George Palko)

George,

We deal with this issue in our forthcoming book on Packages. Our opinion is that this purge should be tied to the base plan_table. With is in mind, we suggest a trigger as shown below. The full SQL will be available in the appendix of the book (SG24-7688). Cleaning up on a time basis (e.g. older than 3 months) instead of a trigger is also a possibility.

CREATE TRIGGER PURGE_EXTENDED
AFTER DELETE ON PLAN_TABLE
REFERENCING OLD_TABLE AS OT
FOR EACH STATEMENT MODE DB2SQL
BEGIN ATOMIC
------------------------------------------------------------
-- DSN_FUNCTION_TABLE
------------------------------------------------------------
DELETE FROM DSN_FUNCTION_TABLE
WHERE (COLLID,PROGNAME,EXPLAIN_TIME) IN
(SELECT COLLID,PROGNAME,BIND_TIME FROM OT) ;
------------------------------------------------------------
-- DSN_STATEMNT_TABLE
------------------------------------------------------------
DELETE FROM DSN_STATEMNT_TABLE
WHERE (COLLID,PROGNAME,EXPLAIN_TIME) IN
(SELECT COLLID,PROGNAME,BIND_TIME FROM OT) ;
------------------------------------------------------------
... etc

Hope this helps.

Thx
Suresh



Date: Wed, 7 Jan 2009 10:54:58 -0500From: [login to unmask email]: [DB2-L] DB2 Optimization Service Center: Purging of tablesTo: [login to unmask email]


Hi List,

We just installed the OSC product as part of our V9 migration. We were wondering if anyone has done the same and if so how are you
purging old rows from the numerous tables that are part of the product. If you could supply SQL that would be great.

Thank you,
George






CONFIDENTIALITY NOTICE: The Ohio Public Employees Retirement System intends this e-mail message, and any attachments, to be used only by the person(s) or entity to which it is addressed. This message may contain confidential and/or legally privileged information. If the reader is not the intended recipient of this message or an employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that you are prohibited from printing, copying, storing, disseminating or distributing this communication. If you received this communication in error, please delete it from your computer and notify the sender by reply e-mail.


IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.
_________________________________________________________________
Windows Live™: Keep your life in sync.
http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_012009
______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html