Outline

• SQL Extensions:
  • Transactions
  • Stored Procedures
  • User-defined types
  • Triggers

Introduction

• On Tuesday, we looked at the syntax of the SELECT command, aggregates, and subqueries.
• We then looked at the DCL commands GRANT and REVOKE
• Today, we are going to look at extensions to SQL that DB2 supports.
• To begin we are going to look at transactions.

Transactions

• A transaction is a collection of operations on a database that are executed as a single unit of work.
• The operations of a transaction might look like:
  • Begin transaction
  • Database changes
  • Terminate transaction. i.e., either Commit the whole transaction or Rollback the whole transaction

ACID Properties of Transactions

• There are several properties we would like of any transaction that is done on our database.
• Four basic ones which have been widely considered are the so-called ACID properties of a transaction:
  • Atomicity -- a transaction should either be completely applied to a DB or none of its operations should be applied
  • Consistency -- the operations of the transaction should move the DB from a consistent state to another consistent state. i.e., if all integrity constraints on the DB were satisfied before the transaction, they should still be satisfied after the transaction.
  • Isolation -- The transaction must appear to be executed as if no other transaction is executing at the same time
  • Durability -- After the transaction is committed, its effect is preserved despite system failures

Correct Execution of Transactions

• Begin -- Database is in a consistent state. For example, at the start of a bank transaction I might have a checking account with $300 and a savings account with $20 .
• Changes -- Database might be inconsistent. For example, as a first step in transferring $100 from the checking account to the savings account I deduct $100 from the checking account. So the balances are $200 and $20.
• End -- Database returns to a consistent state (either rolled back or committed). For example, to complete the transaction I add the $100 to savings account and commit.

Transaction Commit

• A transaction commit what occurs when a transaction ends normally.
• It comes in two flavors: implicit or explicit
• At the commit point, changes made to the database are made permanent and made visible to other transactions.
• Due to the deferred write of buffers, the changes most likely are not written out to the disk at commit time.
• However, DB2 has a mechanism to ensure that all those changes are "remembered" in case of a crash even before actual updated data reaches the DASD.

Transaction Rollback

• A transaction rollback occurs when a system or application ends a transaction before a commit. For example, because of exceptions, errors, deadlock, abort.
• When a rollback occurs, changes that have been made to the DB by the transaction are reverted
• The transaction returns to starting point of consistency

Units of Recovery

• Now let's look at things from the DB2 perspective...
• From this viewpoint, an update database transaction begins with the first change to the data after the beginning of the job or following the last point of consistency.
• It ends at a later point of consistency.

Transactions in SQL

• A transaction is a unit of work that consists of multiple SQL statements.
• Transactions in SQL look like:

  begin transaction statement 
  SQL statement SQL statement 
  SQL statement
  COMMIT or ROLLBACK statement
  

More Transaction SQL Syntax

• Sometimes it is useful to be able to rollback a portion of a transaction and have ones program figure out an alternative way to complete.
• This can be done in DB2 by defining a SAVEPOINT and then if need be rolling back to the SAVEPOINT.
• So the three relevant SQL commands in this context are:

  SAVEPOINT savepoint_name 
  ON ROLLBACK RETAIN CURSORS 
  [ON ROLLBACK RETAIN LOCKS]
  

  COMMIT
   -- this will commit all changes and release all savepoints
  

  ROLLBACK [TO SAVEPOINT savepoint_name]
   -- this will rollback changes of the current transaction, 
    or rollback changes to the specified save point.
  

Stored Procedures

• Stored procedures are specialized programs that are executed under the control of the DBMS
• Main reasons to use stored procedures are:
  • Code reuse -- code doesn't need to be replicated to many servers can all reside in the DB server
  • Consistency -- If every user with the same requirements calls the same stored procedures, the DBA can be assured that everyone is running the same code. If each user uses his or her own individual, separate code, there is no assurance that the same logic is being used by everyone. In fact, it is almost certain that inconsistencies will occur.
  • Maintenance -- Because the stored procedure exists in one place, you can make changes quickly without propagating the change to multiple locations.
  • Data integrity -- You can code stored procedures to support database integrity constraints
  • Performance -- Stored procedures can reduce network traffic because multiple SQL statements can be invoked with a single execution of a procedure instead of sending multiple SQL requests across the communication lines
  • Security -- If a given group of users requires access to specific data items, you can develop a stored procedure that returns only those specific data items. You can then grant access to call the stored procedure to those users without giving them any additional authorization to the underlying objects accessed within the body of the stored procedure.

Type of Stored Procedure in DB2

• There are three types of stored procedures in DB2:
  • External -- The procedure is written in a programming language such as C, COBOL, or Java. The external executable is referenced by a procedure defined at the server along with various attributes of the procedure.
  • SQL External -- The procedure is written in the SQL procedural language but is implemented as an external program. The CREATE PROCEDURE statement will register the procedure name, the parameter declarations, and the procedure options at the current server.
  • SQL Native -- The procedure is written exclusively in SQL statements. The body of an SQL procedure is written in the SQL procedural language. The procedure body is defined at the current server along with various attributes of the procedure.

Stored Procedure (External) Example

CREATE PROCEDURE MYPROC (IN INT, OUT INT, OUT DECIMAL(7,2))
LANGUAGE COBOL
EXTERNAL NAME MYMODULE
PARAMETER STYLE GENERAL 
WLM ENVIRONMENT PARTSA 
DYNAMIC RESULT SETS 1;

• An external stored procedure can be implemented in COBOL, C, or Java.
• This type of stored procedure is external to DB2. You need to register this procedure to DB2 by issuing the "CREATE PROCEDURE" DDL statement.
• In the DDL, you specify the language this procedure is to be written in (COBOL in this example), you specify the input/output parameters attributes, and you specify the external name of this stored procedure (MYMODULE in this example) .
• Starting with V8, the external stored procedures can only be run under the WLM environment.
• You also need to create in the z/OS system the body of the COBOL stored procedure, MYMODULE, which includes embedded SQL statements and the control logic.

Stored Procedure (SQL - External) Example

CREATE PROCEDURE MEDIAN (OUT medianSalary DECIMAL(7,2) 
LANGUAGE SQL FENCED 
BEGIN
DECLARE v_numRecords INTEGER DEFAULT 1; 
DECLARE v_counter INTEGER DEFAULT 0; 
DECLARE c1 CURSOR FOR
   SELECT salary FROM staff ORDER BY salary;
SET medianSalary = 0; 
SELECT COUNT(*) INTO v_numRecords 
FROM STAFF; 
OPEN c1; 
WHILE v_counter < (v_numRecords / 2 + 1) 
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1; 
END WHILE; 
CLOSE c1; 
END

• In this example, we create the definition for an external SQL procedure called MEDIAN. This procedure returns the median salary in the output parameter.
• The "FENCED" attribute indicates that this is an external procedure, which is executed outside the DB2 engine.

Stored Procedure (SQL - External) Flow

• You can use the SQL procedural language syntax to code a SQL-External stored procedure.
• Internally DB2 processes it like a C-External stored procedure.
• To generate the external SQL procedure in DB2, you would first enter the "CREATE PROCEDURE" statement into a file. You then input this file, which contains the entire CREATE PROCEDURE statement including the procedure body, to a 2-step pre-compiling process.
• Next you to convert the SQL procedure body to a C program. The "logic control" SQL statements become the "logic control" C statements. The non-logic SQL statements become the embedded SQL statements (EXEC SQL ...) in the C program.
• Then you separate out the embedded SQL statements into a DBRM from the C program. This step is a pre-compiling process that is the same as for an External Procedure.
• The C program is then run through compile and linkedit to create a load module. This load module contains the "logic control" part of the original SQL procedure. The DBRM is bound into a package. The sections inside the package is invoked from the logic control of the C program load module.

Stored Procedure (SQL - Native) Example

CREATE PROCEDURE MEDIAN (OUT medianSalary DECIMAL(7,2)) 
BEGIN
DECLARE v_numRecords INTEGER DEFAULT 1; 
DECLARE v_counter INTEGER DEFAULT 0; 
DECLARE c1 CURSOR FOR
  SELECT salary FROM staff ORDER BY salary;
SET medianSalary = 0; 
SELECT COUNT(*) INTO v_numRecords FROM STAFF; 
OPEN c1; 
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1; 
END WHILE; 
CLOSE c1;
END

• Notice this time we didn't specify the language or that it was fenced.

Stored Procedure (SQL -- Native) Flow

• To generate the native SQL procedure in DB2, you simply enter the "CREATE PROCEDURE" statement as a DDL statement to DB2.
• DB2 will compile and bind the procedure into a package.
• The control logic parts are stored in section One of the package.
• The procedure name of "MEDIAN" is also stored in the catalog table SYSIBM.SYSROUTINES.

External vs. Native SQL Procedures

• External SQL procedures (from V5 on)
  • Preparation: multiple steps, and requires C compiler
  • Execution: the load module for the generated C program requires WLM environment to run.
• Native SQL procedures (from V9)
  • Preparation: single-step DDL
  • Execution: runs entirely within the DB2 engine.

User-defined Types -- Distinct Types

• We are next going to look at user-defined types in DB2.
• A distinct type provides a mechanism for extending the basic types such as INTEGER, CHAR and so on that we learned for SQL.
• After a distinct type is defined it can be used in the same way as the built-in types.
• The syntax for defining a distinct type is:

  CREATE DISTINCT TYPE distinct-type-name AS source-data-type
  

• For example,

  CREATE DISTINCT TYPE SHOESIZE AS INTEGER
  

• Two cast functions will be generated:
  • Function INTEGER(SHOESIZE) returns a value with data type INTEGER.
  • Function SHOESIZE(INTEGER) returns a value with distinct type SHOESIZE.

User-Defined Function (UDF)

• A UDF is a procedural function that users create and that DB2 supports.
• Function types:
  • External scalar -- defined externally returns a value
  • External table -- defined externally computes a table
  • Sourced -- Sourced functions are created from already existing built-in (scalar and column) and user-defined (scalar) functions. The primary reason to create a sourced function is to enable functions for user-defined distinct data types. This is required because DB2 implements strong typing.
  • SQL -- SQL scalar returns a scalar value. The body of an SQL scalar function is written in the SQL procedural language.
• User-defined functions are different from regular application subroutines in that they can be invoked in SQL statements. In fact, the only way that user-defined functions can be executed is from within an SQL statement.
• A user-defined function is not a substitute for an application subroutine, subprogram, or stored procedure. Instead, user-defined functions are used to extend the functionality of the SQL language.

Example of User-Defined Function (Scalar)

CREATE FUNCTION DAYSINMONTH(DATE) 
RETURNS INTEGER
EXTERNAL NAME 'DAYMTH' LANGUAGE COBOL;

SELECT EMPNO, LASTNME, BIRTHDATE,
DAYSINMONTH(BIRTHDATE)
FROM DSN8810.EMP 
WHERE DAYSINMONTH(BIRTHDATE) < 31

• The external name for the function program is DAYMTH and it is coded in COBOL.
• After the user-defined function has been created, and the appropriate authority has been GRANTed, the UDF can be used in an SQL statement.

Example of User-Defined Function (Table)

CREATE FUNCTION FOOTBALL_RESULTS(INTEGER) 
RETURNS TABLE (WEEK INTEGER,
   WINNER CHAR(20), 
   WINNER_SCORE INTEGER, 
   LOSER CHAR(20), 
   LOSER_SCORE INTEGER)
EXTERNAL NAME FOOTBALL 
LANGUAGE C 
CARDINALITY 300;

SELECT WINNER, WINNER_SCORE, LOSER, LOSER_SCORE 
FROM FOOTBALL_RESULTS(5) 
WHERE LOSER_SCORE = 0;

• The input parameter is an INTEGER value coresponding to the week the game was played; if 0 is entered, all weeks are considered.
• The query would return all results where the losing team was shut out (had 0 points) during the fifth week of the season.
• The value supplied for the CARDINALITY parameter is only an estimate. It is provided to help DB2 optimize the statements used the table function. It is possible to return more or fewer rows than is specified in CARDINALITY.

Example of User-Defined Function (Source)

CREATE DISTINCT TYPE SHOESIZE AS INTEGER;

CREATE FUNCTION '+' (SHOESIZE, SHOESIZE) 
RETURNS SHOESIZE
SOURCE SYSIBM. '+' (INTEGER, INTEGER);

• Because of DB2's strong typing mechanism, without this sourced function, it would not be possible to add two SHOESIZE columns using SQL.

Example of User-Defined Function (SQL Scalar)

CREATE FUNCTION TAN (X DOUBLE) 
RETURNS DOUBLE 
LANGUAGE SQL 
CONTAINS SQL
NO EXTERNAL ACTION 
DETERMINISTIC 
RETURN SIN(X)/COS(X);

• DETERMINISTIC means that any time one runs this function on the same inputs it will give the same outputs.
• NO EXTERNAL ACTION means that this function does not change the state of an object that DB2 does not manage.

DB2 Triggers

• A trigger defines a set of actions that are executed when a delete, insert, or update operation occurs on a specified table or view.
• A trigger is event driven, based on INSERT, UPDATE, or DELETE
• Triggers are managed by DB2 and cannot be directly called by applications
• Triggers cannot be directly called or explicitly executed
• Properties
  • Created by DDL (similar to tables and indexes)
  • Similar to check constraints; but more powerful
  • Contain code logic (similar to stored procedures)
  • Atomic

Example Trigger

CREATE TRIGGER TOT_COMP 
AFTER UPDATE OF SALARY, BONUS, COMM 
ON EMP 
REFERENCING 
NEW AS N, 
OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (N.SALARY <> O.SALARY OR N.BONUS<> O.BONUS 
   OR N.COMM <> O.COMM) 
BEGIN ATOMIC UPDATE EMP_SALARY SET
TOT_COMP = N.SALARY + N.BONUS + N.COMM 
WHERE EMP_SALARY.EMPNO = N.EMPNO;
END