Triggers and Stored Procedures, lets play 20 questions - PowerPoint PPT Presentation

1 / 51
About This Presentation
Title:

Triggers and Stored Procedures, lets play 20 questions

Description:

Triggers are event driven actions, which are defined on specific objects for ... log : is it possible to distinguish wether an operation was done by a trigger or not ? ... – PowerPoint PPT presentation

Number of Views:297
Avg rating:3.0/5.0
Slides: 52
Provided by: roses
Category:

less

Transcript and Presenter's Notes

Title: Triggers and Stored Procedures, lets play 20 questions


1
Triggers and Stored Procedures, lets play20
questions !
DB2 UDB for z/OS and OS/390
Technical Thread
  • Kurt Struyf
  • Consultant/ Competence Partners

2
  • Part I Triggers
  • Theory
  • Hints tips
  • RI using triggers
  • Performance real life questions
  • Part II Stored Procedures
  • Theory
  • Hints tips
  • Performance real life questions

3
Part I Triggers
  • Triggers a definition Triggers are event
    driven actions, which are defined on specific
    objects for each row or for each statement.
  • Trigger name Triggers are associated with a
    schema and the name is limited to 8 characters.
    Default schema name is the authid of creator.

4
Trigger Syntax
  • Create trigger trigger nameno cascade
    before/after insert/update of columnname/deleteo
    n triggering tablename referencing old as
    correlationname, new as correlationname/ old
    table as identifier, new table as identifier
    for each row/for each statement mode db2sql
  • when (search condition)
  • begin atomic triggered sql
    statement(s)end

5
trigger activation time 1
  • Triggers can be fired at 2 moments in time.
  • No cascade before triggers can contain the
    following SQL statements full select, call
    stored procedure, signal sqlstate, values, set
    transition variable. They can NOT contain
    database manipulating SQL statements as insert,
    delete or update!!!
  • After triggers can contain the following SQL
    statements full select, call stored procedure,
    signal sqlstate, values, insert, update,
    delete. They can NOT contain set transition
    variable statements due to the fact that the
    database is already updated

6
trigger activation time 2
Triggering operation S1
error
Determine the set of
ROLLBACK
(INSERT, UPDATE or DELETE)
affected rows
error
Process all
ROLLBACK
BEFORE triggers
error
Apply constraints
ROLLBACK
(CHECK and RI)
error
Apply set of affected
ROLLBACK
rows to target table
CONTINUE
Process all
AFTER triggers
error
ROLLBACK
Triggers belong to the same unit of work as the
original statement. Therefore they get either
committed or rollbacked together!
7
allowable options and available values
8
What are triggers used for ?
  • enforcing transitional business rules
  • validation of input data
  • maintaining summary and audit data
  • when inserting or updating rows generating or
    changing column values
  • initiating external actions to perform all sorts
    of operations outside the DB2 dbms (change
    external files, sending e-mail, maintaining audit
    trail, scheduling batch jobs, )

9
Why should we or shouldnt we use triggers ?
  • Code centralisation Faster application
    development
  • Code reuse
  • Easier maintenance
  • Temporary audit traces become easier
  • More possibilities Update cascade
  • Triggers are not always fired e.g. Load utility
  • Debugging becomes more complex
  • Additional recovery problems
  • The creation order determines the order of
    execution with all other circumstances equal

10
Query the catalog
Additional information can also be found in
SYSIBM.SYSPACKAGE SYSIBM.SYSPACKSTMT
11
Trigger package
  • Is bound at creation of the trigger
  • Collection name schema name
  • Package name trigger name
  • Bind occurs by default with isolation level
    CS release at commit explain
    no
  • Can only be freed or dropped by DROP TRIGGER

12
hints tips (1 of 2)
Create trigger BEDB2M.INSERTTS after insert on
bedb2m.employee referencing new as ins for each
row mode db2sql begin atomic delete from
bedb2m.project where empno ins.empno insert
into bedb2m.project select from employee where
empno ins.empno end Commit
13
hints tips (2 of 2)
  • Host variables and cursor operations are NOT
    allowed in triggers because DB2 does not return
    query output to the trigger!
  • Create trigger bedb2m.deltest after delete
    on bedb2m.employee for each row mode
    db2sql delete from bedb2m.project where empno
    empno

14
Which authorisations do I need to create
triggers ?
Grant TRIGGER/ALTER on table TABLE 1
Grant CREATEIN on schema BEDB2M
  • CREATE TRIGGER BEDB2M.INSERTTB
  • AFTER INSERT ON TABLE1
  • REFERENCING NEW AS INS
  • FOR EACH ROW
  • MODE DB2SQL
  • (WHEN INS.COL1 gt 1000)
  • BEGIN ATOMIC
  • INSERT INTO TABLE2 VALUES (INS.COL1)
  • CALL ROUTINS (INS.COL1)
  • END

Grant SELECT on table TABLE 1
Grant INSERT on table TABLE2
Grant EXECUTE on PROCEDURE ROUTINS
15
Database environment
DEPARTMENT
EMPLOYEE
PROJECT
16
RI by triggers (1 of 4)
  • In case of INSERT or UPDATE, we use triggers to
    check the foreign key, using ONE of the
    following triggers
  • after for each rowor after for each
    statementor before for each row

17
RI by triggers (2 of 4)
  • In case of a DELETE, we simulate a CASCADE
    action, by ONE of the following triggers
    after for each rowor after for each
    statement
  • but before for each row IS NOT POSSIBLE
    here

18
RI by triggers (3 of 4)
19
RI by triggers (4 of 4)
  • CREATE TRIGGER BEDB2M.DELROWAFAFTER DELETE ON
    BEDB2M.EMPLOYEEREFERENCING OLD AS DELFOR EACH
    ROW MODE DB2SQLWHEN (EXISTS (SELECT B.EMPNO
    FROM BEDB2M.PROJECT B WHERE B.EMPNO
    DEL.EMPNO ))BEGIN ATOMIC DELETE FROM
    BEDB2M.PROJECT WHERE EMPNO DEL.EMPNOEND

20
Problem with after statement trigger (1 of 3)
  • CREATE TRIGGER BEDB2M.INSSTMAF
  • AFTER INSERT ON BEDB2M.EMPLOYEE
  • REFERENCING NEW_TABLE AS INSEMP
  • FOR EACH STATEMENT
  • MODE DB2SQL
  • WHEN (0 ltgt
  • (
  • SELECT TAB1.TOTAL-TAB2.TOTAL AS
    TOTAL FROM
  • TABLE (SELECT COUNT() AS TOTAL
    FROM INSEMP)
  • AS TAB1
  • ,TABLE (SELECT COUNT() AS TOTAL
  • FROM BEDB2M.DEPARTMENT A,
    INSEMP B
  • WHERE A.DEPTNO B.DEPTNO)
  • AS TAB2
  • ))
  • BEGIN ATOMIC
  • SIGNAL SQLSTATE '75001' ('INVALID DEPARTMENT ON
    INSERT')
  • END

21
Problem with after statement trigger (2 of 3)
  • Suppose we work with several INSERT statements
    and NO commit between two insert statements,
    example
  • Insert into employee select from testwhere
    empno like 05
  • Insert into employee select from testwhere
    empno not like 05

22
Problem with after statement trigger (3 of 3)
  • Trigger fires only on the first insert, on the
    second insert we receive a 723-723 an error
    occurred in a triggered SQL statement in trigger
    bedb2m.insstmaf section number 1. information
    returned sqlcode -909 the object has been
    deleted
  • DB2 deletes the temporary table for transition
    variables and only recreates it after a commit
  • A commit after every insert solves this.

23
Which trigger should I use, performance wise (1
of 2) ?
  • Setup in a table of 100.000 rows, were doing
    5000 insert of which 650 arent allowed through
    RI.
  • Weve tested
  • DB2 RI
  • RI through an application
  • a before row trigger
  • an after row trigger
  • an after statement trigger.

24
Which trigger should I use, performance wise (2
of 2) ?

Note in case of no RI
25
The real life problems, questions ANSWERS
part I
DB2 log is it possible to distinguish wether an
operation was done by a trigger or not ?
  • DSN1LOGP of a delete cascade trigger action
  • 002E2A6AC09D URID(002E2A6ABD0B)
    LRSN(B7961A8C33D3) DBID(0551)
  • OBID(000F)
    PAGE(00000002) TYPE( UNDO REDO )
  • SUBTYPE(DELETE IN A
    DATA PAGE) CLR(NO)
  • PROCNAME(DSNIDILS)
  • LRH 006C0050 06000001 0E80002E 2A6ABD0B
    002E2A6A C04D0426 002E2A6A C04DB796
  • 1A8C33D3 0000
  • LG 08055100 0F000000 0200002E 2A46FBA4 3900
  • 0000 00342015 00100000 02002C00 1015C1C2
    C3C4C5C6 C1F0F0F5 F5F5F5F5 F500F006
  • 0020 00001999 01010020 00112000 07C4C5D3
    E3C5E2E3
    ANSWER NO but

26
The real life problems, questions ANSWERS
part II
  • Can I explicit bind a trigger package to specify
    non default options ?
  • NO bind package not allowed for tiggers
  • BUT
  • Rebind is allowed with limited options
  • Beware of the syntax
  • Rebind trigger package(collection.packagename)
    currentdata(yes) explain(yes) isolation(UR)
    release(deallocate)

27
The real life problems, questions ANSWERS
part III
How can we add error messages to a trigger?
  • 1. Signal SQLSTATE (only as a part of triggered
    SQL statement and can be used with WHEN clause).
  • ex When (new_emp.salary gt (old_emp.salary
    1.20)) Signal sqlstate 75001(invaldid salary
    increase)
  • 2. RAISE_ERROR, this is a build in FUNCTION can
    appear where expressions appear and can be
    controlled with CASE. Ex Values (case
  • when new_emp.hiredate lt current date
  • then raise_error(85001, hiredate has
    passed)

28
The real life problems, questions ANSWERS
part IV
  • How can we synchronize timestamps when using
    triggers?
  • CREATE TRIGGER BEDB2M.TIMETRIG
  • AFTER INSERT ON BEDB2M.TESTTIME
  • FOR EACH ROW
  • MODE DB2SQL
  • BEGIN ATOMIC
  • INSERT INTO TSTTIME2
  • VALUES (CURRENT TIMESTAMP)
  • END

CREATE TRIGGER BEDB2M.TIMETRIG AFTER
INSERT ON BEDB2M.TESTTIME REFERENCING
NEW AS INS FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO TSTTIME2
VALUES (INS.TIME) END
29
The real life problems, questions ANSWERS
part V (1 of 2)
How can I invoke a stored procedure from a
trigger?
  • CREATE TRIGGER BEDB2M.TRIGSP
  • AFTER UPDATE ON BEDB2M.EMPLOYEE
  • REFERENCING NEW AS UPDT
  • FOR EACH ROW
  • MODE DB2SQL
  • BEGIN ATOMIC
  • CALL SPEC0403('TRIGNAME', UPDT.EMPNO,
    UPDT.LOCID)
  • END
  • Stored procedure body
  • EXEC SQL
  • UPDATE EMPLOYEE
  • SET LOCID 'TESTUPDT'
  • WHERE EMPNO SP1EMPNO
  • END-EXEC.

30
The real life problems, questions ANSWERS
part V (2 of 2)
After 16 levels we receive a sqlcode 724 THE
ACTIVATION OF THE TRIGGER OBJECT TRIGSP WOULD
EXCEED THE MAXIMUM LEVEL OF INDIRECT SQL CASCADING
Application
TRIGGER TRIGSP
UPDATE TABLE1
STO. PROC SPEC0403
CALL
SPEC0403
UPDATE TABLE1
31
The real life problems, questions ANSWERS
part VI
Is it possible to trigger different actions for
each column in a table when the table is
modified, using a single trigger making use of
WHEN clauses?
  • CREATE TRIGGER BEDB2M.COLTRIG NO CASCADE
  • BEFORE UPDATE OF COL1, COL2, COL3, COL4
  • ON BEDB2M.TESTCOL REFERENCING OLD AS OLDROW NEW
    AS NEWROW
  • FOR EACH ROW MODE DB2SQL
  • BEGIN ATOMIC
  • VALUES CASE WHEN (NEWROW.COL1 lt OLDROW.COL1
    AND NEWROW.COL3 lt OLDROW.COL3) THEN
  • RAISE_ERROR('95959','INCOMPATIBLE VALUES COL1
    AND COL3')
  • ELSE 0
  • END
  • VALUES CASE WHEN OLDROW.COL2 lt NEWROW.COL2
    THEN
  • RAISE_ERROR('95000','INCOMPATIBLE VALUE
    COL2')
  • ELSE 0
  • END
  • END

32
  • Part I Triggers
  • Theory
  • Hints tips
  • RI using triggers
  • Performance real life questions
  • Part II Stored Procedures
  • Theory
  • Hints tips
  • Performance real life questions

33
Part II stored procedures
  • Stored Procedure definition
  • A stored procedure is a compiled program at a DB2
    local or remote server, that is invoked using the
    following SQL CALL statement.
  • EXEC SQL CALL proc-name
  • Or
  • EXEC SQL CALL hostvar

34
Typical configuration

35
Should I use SPAS or WLM ?
  • DB2-established
  • One address space possible
  • Difficulty when more then 50 SP
  • First in, first out
  • No 2-phase commit support
  • Security DB2

WLM-established Several address spaces
possible NUMTCB sets maximum SP Priority setting
possible Supports 2-phase commit Security DB2
or USER or DEFINER
36
Stored procedure create statement
CREATE PROCEDURE
procedure
-
name (parameter
-
declaration)
FENCED
LANGUAGE COBOL / PLI / C / ASSEMBLE
NOT DETERMINISTIC / DETERMINISTIC
NO SQL / CONTAINS SQL / READS SQL DATA /
MODIFIES SQLDATA
External-program-
-
name
EXTERNAL NAME
PARAMETER STYLE DB2SQL / GENERAL / GENERAL WITH
NULLS
collection
-
id
NO COLLID / COLLID
NO WLM ENVIRONMENT / WLM ENVIRONMENT
name / (name,)
ASUTIME NO LIMIT / LIMIT
integer
STAY RESIDENT NO / YES
PROGRAM TYPE SUB / MAIN
RUN OPTIONS
run
-
time
-
options
EXTERNAL SECURITY DB2 / USER / DEFINER
NO DBINFO / DBINFO
RESULT SETS integer
COMMIT ON RETURN NO / YES
37
Hints tips (1 of 6)
  • Parameter style general and DBINFO
  • Are mutually exclusive !!
  • When writing, testing and debugging your stored
    procedures, look in the WLM or SPAS address
    space, to see errors inside the stored
    procedures.
  • Stored Procedures written in COBOL, cant be
    compiled with vs cobol II. You need SAA AD/Cycle
    COBOL/370 Version 1 release 1 or later.

38
Hints tips (2 of 6)
How does a COMMIT work within Stored Procedures ?
  • Commit on return is not allowed
  • - for nested stored procedures
  • - if the client is using 2-phase commit
  • HOWEVER explicit commit in a nested stored
    procedure is possible and commits the entire UOW
    up to that point!!!

39
Hints tips (3 of 6)
  • Stored procedure address spacebased SPs have to
    be link-edited using CAF.
  • Example //LKED.SYSIN DD INCLUDE
    SYSLIB(DSNALI) MODE AMODE(31) RMODE(ANY)
  • WLM based stored procedures NEED to be
    link-edited using RRSAF
  • Example //LKED.SYSIN DD INCLUDE
    SYSLIB(DSNRLI) MODE AMODE(31) RMODE(ANY)

40
Hints tips (4 of 6)
  • Symtomes of not using RRSAF with WLM based Stored
    Procedures SQL error -927 the language
    interface (LI) was called
  • when the connecting environment was not
    established. the program
  • should be invoked under the DSN command.
  •  
  • When specifying the wrong precompile parameter
    of the stored procedure "ATTACH(CAF)" . You
    receiveSQL error -430 stored-procedure-name
    has abnormally terminated.

41
Hints tips (5 of 6)
  • Input parameters may NOT be the target or
    destination of an assignment (SET) statement.
  •  
  • CREATE PROCEDURE BEDB2M.PROC0500 (IN SP1NAME
    CHAR(20))....
  •  
  • SET SP1NAME 'THIS IS NOT ALLOWED'
  •  
  • Input parameters may NOT be the target of an INTO
    clause of a SELECT statement.
  •  
  • CREATE PROCEDURE BEDB2M.PROC0500 (IN SP1NAME
    CHAR(20))....
  •  
  •   SELECT NAME INTO SP1NAME FROM EMPLOYEE

42
Hints tips (6 of 6)
  • The WITH RETURN clause will cause the result set
    to be returned to the calling application.
  •  
  • DECLARE C1 CURSOR WITH RETURN FOR SELECT
  • ...
  • OPEN C1
  •  
  • Any declared cursor without a WITH RETURN clause
    will be closed when the stored procedure
    completes. NO result set will be returned to the
    calling application.

43
Performance issues (1 of 2)
  • What is most performant, stored procedures
    running as program type SUB vs MAIN vs a
    subroutine inside an application ?

44
Performance issues (2 of 2)
What should I be aware of using stored procedures
and large strings (gt32 Kb) ?
  • When working with large values we noticed a
    dramatic performance gain using JAVA versus
    COBOL. The larger this value is, the larger the
    benefit of using JAVA.

45
The real life part I
  • What steps do I have to take if I want to work
    with stored procedures ?

DB2 version 5 and up
SPAS
WLM
Link-edit using DSNRLI
Link-edit using DSNALI
Create procedure using NO WLM ENVIRONMENT
Create procedure
-START PROCEDURE
CALL PROCEDURE
46
The real life part II
How to code a simple stored procedure ?
47
The real life part III
How to code SP with a cursor?
48
The real life part IV (1 of 2)
How can we trap errors in stored procedures that
are called from a trigger ?
CREATE TRIGGER BEDB2M.TRIGSP69...........BEGIN
ATOMICCALL SPEC6969('TRIGNAME', INS.EMPNO,
INS.LOCID) END CREATE PROCEDURE SPEC6969 (IN
SP1NAME CHAR(20), IN SP1EMPNO CHAR(6),
OUT SP1LOCID CHAR(10)) LANGUAGE COBOL NOT
DETERMINISTIC MODIFIES SQL DATA EXTERNAL NAME
SPEC6969 PARAMETER STYLE DB2SQL COLLID
SPCO6969 WLM ENVIRONMENT DB2PBE ASUTIME NO LIMIT
STAY RESIDENT NO PROGRAM TYPE MAIN
SECURITY DB2 RESULT SETS
1 COMMIT ON RETURN NO
49
The real life part IV (2 of 2)
LINKAGE SECTION. 01 SP1NAME
PIC X(20). 01 SP1EMPNO PIC
X(06). 01 SP1LOCID PIC X(10).
01 INDSP1NAME PIC S9(4)
COMP. 01 INDSP1EMPNO PIC
S9(4) COMP. 01 INDSP1LOCID
PIC S9(4) COMP. 01 SP-SQLSTATE PIC X(5).
01 SP-PROC.
49
SP-PROC-LEN PIC 9(4) USAGE BINARY.
49 SP-PROC-TEXT PIC X(27).
01 SP-SPEC.
49 SP-SPEC-LEN PIC 9(4) USAGE BINARY.
49 SP-SPEC-TEXT PIC X(18).
01 SP-DIAG.
49 SP-DIAG-LEN PIC
9(4) USAGE BINARY. 49
SP-DIAG-TEXT PIC X(70). PROCEDURE DIVISION USING
SP1NAME, SP1EMPNO, SP1LOCID, INDSP1NAME,
INDSP1EMPNO, INDSP1LOCID, SP-SQLSTATE, SP-PROC,
SP-SPEC, SP-DIAG.
50
Questions ?
51
Kurt StruyfCompetence PartnersKurt.Struyf_at_comp
etence-partners.be
Triggers and Stored Procedures, Lets play 20
Questions ! Session A6
Write a Comment
User Comments (0)
About PowerShow.com