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