z/OS Db2 V8.1 CM Invalid Packages.

Wayne Campbell

z/OS Db2 V8.1 CM Invalid Packages.
Quick Question,



Can Creating a Trigger on a table invalidate packages? We had a bunch
of auto rebinds yesterday; the only commonality I could see is the ISS
table that I had created a couple of triggers for that morning. Here is
the code for the triggers.





CREATE TRIGGER ISSAIR01


AFTER insert ON ISS

REFERENCING new as N

for each row mode DB2SQL

WHEN (exists(SELECT ETX_ETK_KY

FROM ETX

WHERE ETX_CS_TK = N.ISS_CS_TK))

INSERT INTO ELI

(ELI_CRE_TS, ELI_ISS_CRT_ITL_NU, ELI_ISS_CS_TK, ELI_ISS_SEQ_NU)

values(TIMESTAMP(GENERATE_UNIQUE()), N.ISS_CRT_ITL_NU, N.

ISS_CS_TK, N.ISS_SEQ_NU);





CREATE TRIGGER ISSAUR01

AFTER update OF ISS_REF_SEQ_NU, ISS_DIS_CD, ISS_DIS_DT

ON ISS

REFERENCING new as N

for each row mode DB2SQL

WHEN (exists(SELECT ETX_ETK_KY

FROM ETX

WHERE ETX_CS_TK = N.ISS_CS_TK))

INSERT INTO ELI

(ELI_CRE_TS, ELI_ISS_CRT_ITL_NU, ELI_ISS_CS_TK, ELI_ISS_SEQ_NU)

values(TIMESTAMP(GENERATE_UNIQUE()), N.ISS_CRT_ITL_NU, N.

ISS_CS_TK, N.ISS_SEQ_NU);





Thanks



Wayne Campbell

DB2 DBA

Administrative Office of the Courts

(360) 705-5268

Email: [login to unmask email]




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: z/OS Db2 V8.1 CM Invalid Packages.
(in response to Wayne Campbell)




I would imagine so, yes



Creating a trigger changes the work that a statement is doing, so it
does seem reasonable that DB2 will need to make some access path changes
to take account of the triggered statement.....



Phil Grainger

CA

Product Manager

Phone: +44 (0)161 929 9334

Fax: +44 (0)161 941 3775

Mobile: +44 (0)7970 125 752

eMail: [login to unmask email]



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Campbell, Wayne
Sent: 21 December 2006 23:12
To: [login to unmask email]
Subject: [DB2-L] z/OS Db2 V8.1 CM Invalid Packages.



Quick Question,



Can Creating a Trigger on a table invalidate packages? We had a bunch
of auto rebinds yesterday; the only commonality I could see is the ISS
table that I had created a couple of triggers for that morning. Here is
the code for the triggers.







------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sushanta Dash

Re: z/OS Db2 V8.1 CM Invalid Packages.
(in response to Phil Grainger)
Hi Wayne,



When you created the trigger the trigger packages are created with the
name of trigger. The packages will not be invalidated unless you do the
following like dropping the reference table or alias or adding another
index or column to the same table or played with any grants. As you had
not done any such above listed activity which would have made the
triggers invalid, the auto rebind is the result of choosing the access
paths for the first time as a result of first attempt to invoke the
trigger after these triggers are created. This is the reason the trigger
packages are some times rebind explicitly to avoid delay during prime
time. I hope this helps.







Thanks

Sushant Dash

Lead DBA
Fidelity Business Services India Pvt. Ltd.,
Embassy Golf Links Business Park,
Off Intermediate Ring Road,
Bangalore - 560 071.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Campbell, Wayne
Sent: Thursday, December 21, 2006 6:12 PM
To: [login to unmask email]
Subject: [DB2-L] z/OS Db2 V8.1 CM Invalid Packages.



Quick Question,



Can Creating a Trigger on a table invalidate packages? We had a bunch
of auto rebinds yesterday; the only commonality I could see is the ISS
table that I had created a couple of triggers for that morning. Here is
the code for the triggers.





=

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm