Did we just create1024 triggers?

Mark Harris,Steve Heichelheim

Sprint

Session C5

Tuesday, May 11th, 10:00am

Highlights

• Converting Oracle PL/SQL to DB2 Triggers• Trigger Versioning• Trigger Fires and Misfires• Performance Considerations• Calling External Code with Triggers

Converting Oracle PL/SQL to DB2Triggers – Current Systems

Business Application• Midrange-based• Some Business Rules Enforced

with Database Triggers and RI• Triggers That Automatically

Populated Some of the Data

Consumer Application• Mainframe-based• Business Rules Not Enforced• RI Not Available

ADABAS Versant Oracle

Converting Oracle PL/SQL to DB2Triggers - Goals

• Re-write of Existing Application

• Consolidation of Redundant Applications

• Decommission of existing DBMS

• Enforce Data Integrity

Converting Oracle PL/SQL to DB2Triggers - Challenges

• Compressed Time Frame– September 2002 through May

2003

• Evolution of the Model/Project

• Experience Level

Converting Oracle PL/SQL to DB2Triggers - Challenges

• Project Scope

• Conflicting Information– Technical Publications– Consultations– Conclusions

Converting Oracle PL/SQL to DB2Triggers - Approach

• Why did we create so many triggers?

• What we learned.

Converting Oracle PL/SQL to DB2Triggers – Statistics

31,1549,878Lines of Code

1,114147Triggers

765,576Lines of Code

126Stored Procedures

13884Constraints

11Functions

187104Foreign Keys

13882Tables

DB2OracleObject Type

Converting Oracle PL/SQL to DB2Triggers - ExamplesPL/SQL Example Code

IF OLD.FROM_NODE_TYP_CD = 'IDN'

THEN

IF OLD.ROUTG_COND_TYP_CD = 'INFO-DIG'

THEN

DELETE FROM SPECD_INFODIGIT

WHERE VERSION_ID = OLD.VERSION_ID;

END IF;

ELSEIF OLD.FROM_NODE_TYPE_CD = 'CON'

THEN

IF OLD.ROUTG_COND_TYP_CD = 'CTRY'

THEN

DELETE FROM SPECD_COUNTRY

WHERE VERSION_ID = OLD.VERSION_ID;

Trigger GO58AU03

Trigger GO58AU04

Converting Oracle PL/SQL to DB2Triggers - Examples

Resulting DB2 Triggers CREATE TRIGGER CS9CI01.TO58AU04

AFTER

UPDATE OF ROUTG_COND_TYP_CD

ON CS9CI01.TCI_GRPH_ROUTG_LIN

REFERENCING OLD AS OLD

NEW AS NEW

FOR EACH ROW MODE DB2SQL

WHEN (OLD.FRM_GRPH_ND_TYP_CD = 'CON'

AND OLD.ROUTG_COND_TYP_CD = 'CTRY')

BEGIN ATOMIC

DELETE FROM CS9CI01.SPECD_COUNTRY

WHERE VERSION_ID = OLD.VERSION_ID;

END

IF OLD.FROM_NODE_TYP_CD = 'IDN'

THEN

IF OLD.ROUTG_COND_TYP_CD = 'INFO-DIG'

THEN

DELETE FROM SPECD_INFODIGIT

WHERE VERSION_ID = OLD.VERSION_ID;

END IF;

ELSE IF OLD.FROM_NODE_TYPE_CD = 'CON'

THEN

IF OLD.ROUTG_COND_TYP_CD = 'CTRY'

THEN

DELETE FROM SPECD_COUNTRY

WHERE VERSION_ID = OLD.VERSION_ID;

Trigger Versioning - Approach

• Tracking Versions

• Multiple Environments

• Coordinate with Application Developers

Trigger Versioning – Approach

• Testing the Triggers

• Trigger Documentation– Version– Date– Type– Description– Who Coded

Trigger Versioning – Approach

Naming Standard

$*&&%@## TO01AU04

$ - Reflects the object type* - Application indicator&& - Table/Tablespace name indicator% - Before or After@ - Insert, Update or Delete## - Sequence Number (order of firing)

Trigger Versioning – Identifying Patterns

• Why Classify Triggers?– Re-Use of Trigger Code– Rapid Development

• Trigger Classifications– Data Verification – Transition Variables– Complex Business Rules– Database-Maintained Data– Verifying Data Outside of DB2– “Current SQLID” Detection

Trigger Versioning – PatternsData Verification – Transition VariablesCREATE TRIGGER CSYCI01.TO01BU06 NO CASCADE BEFORE UPDATE OF STATUS_CD ON CSYCI01.TCI_TF_V REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL WHEN (NEW.STATUS_CD IN (1,2)(NEW.STATUS_CD IN (1,2) AND OLD.STATUS_CD NOT IN (9,8)) AND OLD.STATUS_CD NOT IN (9,8)) BEGIN ATOMIC SIGNAL SQLSTATE 'I0107'SIGNAL SQLSTATE 'I0107' ('TO01BU06 STUS CAN ONLY BE CHANGED TO 1 or 2 ONLY FROM 9 or 8') ('TO01BU06 STUS CAN ONLY BE CHANGED TO 1 or 2 ONLY FROM 9 or 8') ; END $

Trigger Versioning – PatternsComplex Business RulesCREATE TRIGGER CSYCI01.TO01BU17 NO CASCADE BEFORE UPDATE OF STATUS_CD ON CSYCI01.TCI_TF_V REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW MODE DB2SQL WHEN (NEW.STATUS_CD <> OLD.STATUS_CD AND NEW.STATUS_CD IN (1,0)) BEGIN ATOMIC SET NEW.ALT_KEY_ID = NEW.STATUS_CDSET NEW.ALT_KEY_ID = NEW.STATUS_CD ; END $

Trigger Versioning – PatternsDatabase-Maintained DataCREATE TRIGGER CS9CI01.TO46AI02AFTER INSERT ON CS9CI01.ROUTING_NODEREFERENCING NEW AS NEWFOR EACH ROW MODE DB2SQL WHEN (NEW.INITIAL_FLAG = 'Y')(NEW.INITIAL_FLAG = 'Y')BEGIN ATOMICINSERT INTO CS9CI01.INITIAL_NODEINSERT INTO CS9CI01.INITIAL_NODE (SET_ID, (SET_ID, NODE_ID, NODE_ID, NODE_TYPE_CD, NODE_TYPE_CD, STATUS_CD) STATUS_CD) VALUES VALUES (NEW.SET_ID, (NEW.SET_ID, NEW.NODE_ID, NEW.NODE_ID, NEW.NODE_TYPE_CD, NEW.NODE_TYPE_CD, NEW.STATUS_CD); NEW.STATUS_CD);END$

Trigger Versioning – PatternsVerifying Data Outside of DB2 CREATE TRIGGER CS9CI01.TO40BI01 NO CASCADE BEFORE INSERT ON CS9CI01.TRUNK_GROUP REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL WHEN(EXISTS (SELECT O8DVOLCB(NEW.SWITCH_ID,'VALIDATE-SWITCH')O8DVOLCB(NEW.SWITCH_ID,'VALIDATE-SWITCH') O8_OUT FROM SYSIBM.SYSDUMMY1) AS A WHERE A.O8_OUT = 'SWITCH/TRUNK NOT FOUND ON CIS-EQUIP') ) BEGIN ATOMIC SIGNAL SQLSTATE 'I4001' ('TO40BI01 SWITCH/TRUNK NOT FOUND ON CIS-EQUIP') ; END $

Trigger Versioning – Patterns“Current SQLID” Detection CREATE TRIGGER CS9CI01.TO27BU05 NO CASCADE BEFORE UPDATE ON CS9CI01.TCI_INBD_UIFN_FT_V REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW MODE DB2SQL WHEN (NEW.STATUS_CD = -1 AND OLD.STATUS_CD = 0 AND CURRENT SQLIDCURRENT SQLID NOT IN (SELECT DB2_SCNDY_AUTHN_ID(SELECT DB2_SCNDY_AUTHN_ID FROM CS9CI01.TCI_DB2_SCNDY_AUTH) FROM CS9CI01.TCI_DB2_SCNDY_AUTH)) BEGIN ATOMIC SIGNAL SQLSTATE 'I2706' ('TO27BU05 USER CANNOT CHANGE STATUS FROM 0 to -1'); END $

Trigger Versioning – Dependencies

• Why Track Trigger Dependencies?

Trigger Versioning – Dependencies

• Track Manually

• Query the Catalog– SYSTRIGGERS– SYSPACKDEP– SYSCOLAUTH

• Third Party Tools

Trigger Fires and Misfires - Cascading

Trigger 2Trigger 1

Table A

Table B

Table A

Table B

Error!

Trigger 1

Trigger 2

Table QTrigger 16

Trigger 17

. . .. . .. . .

Table P

Trigger Fires and Misfires - Cascading

Table A

Column A

Table B

Column BTrigger1 updates Column B

Trigger2 updates Column A

WHEN OLD.Column A <> NEW.Column A

User can updatevalue in Table A orTable B

Trigger Fires and Misfires – SQL Errors

-724 THE ACTIVATION OF THE object-type OBJECT object-name WOULD EXCEEDTHE MAXIMUM LEVEL OF INDIRECT SQL CASCADINGExplanation: Cascading of indirect SQL occurs when a trigger,user-defined function or stored procedure invokes another trigger,user-defined function or stored procedure which in turn invokes another.The activation of some of the triggers in this chain might be due tothe enforcement of referential constraint delete rules. Thedepth of this cascading is limited to 16.

Trigger Fires and Misfires – SQL Errors

-723 AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN trigger-name.# INFORMATION RETURNED: SQLCODE: sqlerror, SQLSTATE: sqlstate,# MESSAGE TOKENS token-list, SECTION NUMBER section-numberExplanation: During execution of an UPDATE, INSERT, or DELETEstatement, a trigger was activated. One of the triggered SQLstatements received an SQL error condition.

Trigger Fires and Misfires – SQL Errors

-438 APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT: textExplanation: This error occurred as a result of execution of theRAISE_ERROR function or as a result of the SIGNAL SQLSTATE statement.text Diagnostic text provided by the invocation of theRAISE_ERROR function or the SIGNAL SQLSTATE statement.

•Cross Reference Table

•Trigger Name in the Message Text

•Documentation in the Trigger Source

Trigger Fires and Misfires – SQL Errors

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN

UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF RESOURCE00000304,

AND RESOURCE NAME DCS1CI01.SCSPCI51.X'000053' '.X'26'SQLSTATE = 57011 SQLSTATE RETURN CODESQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERRORSQLERRD = 101 13172746 0 13228501 -404549624 0 SQLDIAGNOSTIC INFORMATIONSQLERRD = X'00000065' X'00C9000A' X'00000000' X'00C9D9D5'X'E7E31008' X'00000000' SQL DIAGNOSTIC INFORMATION

Trigger Fires and Misfires – SQL Errors

00C90096

Explanation: The page, row or LOB lock on the page or subpage identifiedby NAME in message DSNT500I or DSNT501I caused the total number of page,row or LOB locks concurrently held to reach the installation maximumnumber of page, row or LOB locks (NUMLKUS) allowed for a single agent inthe system.

This reason code is issued by the following CSECT: DSNILMCL

Trigger Fires and Misfires -Considerations

• Tracking Errors– Incomplete Error Information– Investigative Tactics

• Associating Errors with RootCause– Original SQL Statement– Host Variable Values

Trigger Fires and Misfires – Considerations

• Using SPUFI to create triggers

• SQL terminator

• Create at the Table Level

• Rebind Triggers

Performance Considerations – Approach

• Order of magnitude / Predicting Performance– Volumetrics– Benchmarks– Dependencies

• Choosing Index Usage– Performance– Concurrency

Trigger Fires and Misfires - Performance

• Inefficient Query SyntaxSELECT . . .FROM CS1CI01.TRUNK_GROUP AWHERE (GROUP_ID,(GROUP_ID, VERSION_ID) VERSION_ID) IN ( SELECT GROUP_ID, VERSION_ID FROM CS1CI01.TRUNK B WHERE . . . )

• “WHEN” – clause executionWHEN (NEW.transition_variable = 1ANDAND EXISTS (SELECT . . . {Complex SQL statement}

Performance Considerations

• Initial Data Conversion

• Load Utility

• Programmatic Inserts– Needed Triggers– Adjust Lock Level During Conversion– Reorg/Runstats/Rebind

Performance Considerations - Deadlocks

• Row Level vs. Page Locks– Availability vs. Performance

• Avoiding Locks– Query Optimization– Rebind Triggers– Locksize

Performance Considerations - Alternatives

• Check Constraints

• Column Security

Performance Considerations

• Trigger Rebind

• DBD Size

• Unit of Work

Calling External Code with Triggers

• Trigger accessing ADABAS via UDF– User Defined Function

• Workload Managed Address Space (WLM)

Calling External Code with Triggers

Procedure based upon recursive codeCREATE PROCEDURE MAX_DEEP(IN_SET_ID,IN_FROM_NODE_ID)

BEGINFOR CSRIN IN (SELECT * FROM LINE

WHERE SET_ID = IN_SET_IDAND FROM_NODE_ID = IN_FROM_NODE_ID)

LOOPUPDATE NODESET MAX_DEPTH = (SELECT MAX(MAX_DEPTH) + 1

FROM NODE WHERE SET_ID = IN_SET_ID AND NODE_ID = CSRIN.FROM_NODE_ID GROUP BY SET_ID, NODE_ID) WHERE SET_ID = IN_SET_ID AND NODE_ID = CSRIN.TO_NODE_ID;

/* CALL MAX_DEEP PROCEDURE */

MAX_DEEP(IN_SET_ID,CSRIN.TO_NODE_ID);

END LOOP;

NodeA

NodeB

NodeC

NodeE

NodeD

1

2

2

3

4

Calling External Code with TriggersSQL Stored Procedure

SET TO_NODE_DEPTH = 1;

B_LOOP: LOOP

SET MAX_NODE_DEPTH = TO_NODE_DEPTH + 1;

UPDATE NODE XSET MAX_NODE_DEPTH_CNT = MAX_NODE_DEPTHWHERE X.SET_ID = IN_SET_IDAND EXISTS (SELECT 1 FROM NODE A , LINE B , NODE C WHERE A.SET_ID = B.SET_ID AND B.SET_ID = C.SET_ID AND B.FROM_NODE_ID = A.NODE_ID AND B.TO_NODE_ID = C.NODE_ID GROUP BY C.SET_ID, C.NODE_ID HAVING MAX(A.MAX_NODE_DEPTH_CNT) = TO_NODE_DEPTH);

IF SQLCODE = 100 THEN LEAVE B_LOOP; END IF;

IF SQLCODE = 0 THEN SET TO_NODE_DEPTH = TO_NODE_DEPTH+ 1; END IF;

END LOOP;

NodeA

NodeB

NodeC

NodeE

NodeD

1

2

2

3

34

Calling External Code with TriggersCalling SQL Stored Procedure with a Trigger

CREATE TRIGGER CS1CI01.TO31AU20AFTER UPDATE OF NW_STUS_CD , RS_V_STUS_CD ON CS1CI01.ROUTG_SET REFERENCING OLD AS OLD NEW AS NEWFOR EACH ROW MODE DB2SQL WHEN (NEW.STATUS_CD IN (1,2) AND NEW.NW_STATUS_CD IN ('A','B','I') AND OLD.STATUS_CD = 9)BEGIN ATOMIC CALL CS1CI01.CIMXS1RG(NEW. SET_ID);END

Wrap-up

• Current Performance

• What We Learned

Questions?

Did we just create1024 triggers?

Session C5

Mark Harris, Steve Heichelheim

Sprint

Mark.K.Harris@mail.sprint.com

Stephen.J.Heichelheim@mail.sprint.com