Triggers and Stored Procedures in DB2

1
Triggers and Stored Proceduresin DB2

• Pam Odden

2
Objectives

• Learn what triggers and stored procedures are
• Learn the benefits of using them
• Learn how DB2 implements triggers and stored
  procedures
• Learn how to create and call a stored procedure1
• 1 To be revisited when we have migrated to v. 7.

3
What is a Trigger?

• A trigger is a specialized program that is not
  called directly, but is event-driven.
• When a data modification statement, such as an
  insert or an update, occurs, a trigger is
  executed, or fired, which may make other
  database updates or call a stored procedure.
• A trigger is not directly called or executed.
  After being created, it is always executed when
  its firing event occurs.
• DB2 version 5 does not support triggers.

4
Why Use Triggers?

• Support data integrity if a change to one
  column dictates a change to another, a trigger
  ensures they always stay in sync.
• Simplify scheduling if an action needs to
  happen every time a particular column is updated,
  the trigger avoids having to schedule it.
• Support complex business rules having business
  rules in the database ensures everyone uses the
  same logic to accomplish the same process.

5
Timing Considerations for Triggers

• DB2 supports BEFORE and AFTER triggers. A
  BEFORE trigger occurs before the database event
  that causes it to fire. An AFTER trigger occurs
  after the database event.
• Trigger timing also depends on any other triggers
  defined for the same database event. Two
  triggers of the same type fire in the order in
  which they were created. This means if the table
  is dropped and recreated for any reason, such as
  making a modification, it is important that the
  triggers are created in the same order each time.
  For this reason, it is best to avoid having more
  than one trigger on the same event.
• A trigger can fire once for a particular database
  event, or once per affected row. These are
  called statement-level triggers or row-level
  triggers and are defined using the FOR EACH
  STATEMENT or FOR EACH ROW clause.

6
Trigger Examples

• CREATE TRIGGER CRITICAL_PROJECT
• AFTER UPDATE OF PROJ_END_DATE ON PROJ
• REFERENCING NEW AS NEWPROJ
• FOR EACH ROW
• WHEN (NEWPROJ.PROJ_END_DATE lt CURRENT DATE 14
  DAYS)
• BEGIN ATOMIC
• CALL CRITPROJ (NEWPROJ.PROJNO)
• END
• Here, when a project end date is updated and is
  within the next 2 weeks, a critical project
  routine is called. This could print an alert for
  management, put a rush on a supply order, or
  whatever is needed for the business.
• Note the project number can be referenced and
  passed to the stored procedure.

7
Another Trigger Example

• CREATE TRIGGER TOT_COMP
• AFTER UPDATE OF SALARY, BONUS, COMM ON EMP
• REFERENCING NEW AS INSERTED, OLD AS DELETED
• FOR EACH ROW
• WHEN (INSERTED.SALARY ltgt DELETED.SALARY
• OR INSERTED.BONUS ltgt DELETED.BONUS
• OR INSERTED.COMM ltgt DELETED.COM)
• BEGIN ATOMIC
• UPDATE EMP_SALARY
• SET TOT_COMP INSERTED.SALARY
  INSERTED.BONUS
• INSERTED COMM
• WHERE EMP_SALARY.EMPNO INSERTED.EMPNO
• END
• Here, when an employees salary, bonus or
  commission is changed on the emp table, his total
  compensation is updated accordingly on the
  emp_salary table.

8
What is a Stored Procedure?

• A stored procedure is a specialized program that
  is stored in the relational database management
  system instead of in an external code library.
• It may access and/or modify data in one or more
  tables, but it is not physically associated with
  a table, or any other object.
• A stored procedure must be invoked, or called,
  before it can be executed. It is not
  event-driven.
• A major motivating reason for using stored
  procedures is to move SQL code from a client to
  the database server. One client request to a
  stored procedure can replace many SQL calls,
  reducing network traffic and speeding up
  processing.

9
Why use Stored Procedures?

• Performance In a client/server or internet
  environment, stored procedures can reduce network
  traffic because multiple SQL statements can be
  invoked with a single stored procedure. Only the
  request and the final results need to be sent
  across the network.
• Reusability Stored procedures allow code to
  reside in one place, the database server.
  Multiple client programs can call the procedures
  as needed, without duplicating code.
• Consistency business rules are implemented only
  one way, not interpreted differently by each
  programmer.
• Maintenance Code changes are only required in
  one place.

10
Why use Stored Procedures, cont.

• Data Integrity Stored procedures can perform
  column validations, so if all applications use
  the same procedure, the data is always validated.
• Security If a given group of users requires
  access to specific data items, you can provide a
  stored procedure that returns just those items.
  You can then grant access to call the stored
  procedure, without giving those users any
  additional authorization to the underlying
  database objects.
• Database protection Stored procedures run in a
  separate address space from the database engine,
  eliminating the possibility of users corrupting
  the DBMS.

11
DB2s Stored Procedure Implementation

• DB2s implementation of stored procedures has
  changed quite a bit from v. 5 to v. 6 and 7.
• Prior to v.6, stored procedures had to be written
  using LE/370 traditional programming languages.
  The supported languages are C, C, COBOL, OO
  COBOL, and PL/I. (Note VS COBOL II is not an
  LE/370 language.)
• Prior to v.6 stored procedures had to be manually
  registered in the DB2 Catalog, as they were
  created like independent programs, rather than
  residing in the database.
• As of v.6, they are created and managed within
  DB2 like any other DB2 object, using CREATE,
  ALTER, AND DROP statements. They can now be
  written in procedural SQL and Java, as well as
  LE/370 languages.
• DB2s stored procedures can issue static and
  dynamic SQL, access flat and VSAM files as well
  as DB2 tables and views, and access resources in
  CICS, IMS, and other MVS address spaces.

12
Parameters

• Parameters allow data to be passed to and
  received from a stored procedure.
• They are used similarly to how they are used to
  call a subroutine. For example, to call the
  DATECALC program, you need to send a date, and a
  few switches to indicate what you want back do
  you want the date converted to another format?
  The day of the week? The program returns the
  results to you in another parameter.
• Three types of parameters may be defined IN
  (input parameter), OUT (output parameter) and
  INOUT (parameter that is used for both input and
  output).

13
Procedural SQL

• IBM created a version of procedural SQL called
  SQL Procedures Language, or SPL. It is based on
  the ANSI Standard SQL/PSM (Persistent Stored
  Modules).
• Other RDBMSs have proprietary procedural dialects
  such as Oracles PL/SQL and MS SQL Servers
  Transact SQL.
• SPL contains procedural constructs like looping
  (WHILE or REPEAT), conditional processing
  (IFTHENELSE) and blocking (BEGINEND), and the
  ability to define and use variables.
• With SPL, a stored procedure can be written
  quickly, without knowledge of COBOL or any other
  LE/370 language.
• SPL stored procedures have their actual code
  defined in the CREATE PROCEDURE statement and
  stored in the database, unlike the external
  stored procedures of DB2 v. 4 and 5.

14
Preparing a Stored Procedure for Execution

• After a stored procedures source statements have
  been created, they are precompiled, compiled,
  link-edited and bound, producing an executable
  program and a DB2 package.
• If the stored procedure is written in SPL, it is
  converted to C source statements and compiled
  into a C executable program.
• Stored procedures are usually written as
  reentrant code that will be started with the
  command START PROCEDURE(procname)
• The procedure will stay resident and available to
  be called until a STOP command is issued.

15
Stored Procedure Example

• CREATE PROCEDURE UPDATE_SALARY
• (IN EMPLOYEE_NUMBER CHAR(10),
• IN RATE DECIMAL(6.2))
• LANGUAGE SQL
• COMMIT ON RETURN YES
• IF RATE lt 0.50
• THEN UPDATE EMP
• SET SALARY SALARY (SALARY RATE)
• WHERE EMPNO EMPLOYEE_NUMBER
• ELSE UPDATE EMP
• SET SALARY SALARY (SALARY 0.50)
• WHERE EMPNO EMPLOYEE_NUMBER
• END IF
• Here, the procedure accepts an employee number
  and rate. The employees salary is increased by
  the rate, and an IF THEN ELSE construct is used
  to ensure the raise is not greater than 50.

16
Tools and Enhancements

• Code/370 is an integrated toolset consisting of
  editing, compilation, and debugging tools
  provided by IBM. Without it, creating and
  managing stored procedures in DB2 v. 4 and 5 can
  be difficult.
• Stored Procedure Builder is a free product for
  developing SPL and Java stored procedures. It
  provides a GUI development environment for
  creating and testing stored procedures, and can
  be launched independently, or from MS Visual
  Studio or Visual Basic, or IBMs Visual Age for
  Java.

17
Summary

• Triggers are blocks of code that are
  automatically executed every time a particular
  database event occurs.
• Stored procedures are blocks of code that are
  stored in the database and can be called by
  triggers, other stored procedures, and external
  programs.
• Using triggers and stored procedures can help
  standardize processing by storing code in only
  one place, and help reduce network traffic by
  consolidating processing on the database server.