Posted by: Serge Rielau

Over the years DB2's SQL Procedure Langiage (SQL PL) has taken on a more and more prominent role and those who know how to use it properly can do some amazing things.
The next couple of posts are dedicated to SQL PL and to giving some advice on the do's and don'ts.

First, in part one, let's start with a history brief lesson to get some perspective:

• DB2 V2.1 for CS
  Supports for inline SQL PL Triggers.
  SQL PL means consists of exactly one construct: BEGIN ATOMIC .... END.
  Inside the compound you can do SIGNAL, INSERT, UPDATE, DELETE, VALUES and SELECT.
  The later two are only interesting for side-effects (such as sending email or executing raise_error().
  It is amazing what one can code with a CASE expression and a where cause.
  Inline means that the trigger is entirely merged into the triggering UPDATE, DELETE, or INSERT statement.
  The result is a very high performance operation.
  
  
• DB2 UDB V7.1
• Support for inilne SQL functions.
  An inline SQL: Function is a function that contains of exactly one(!) statement: RETURN.
  The expression which is returned can be just about any query and it is merged into invoking statement much like a VIEW.
  SQL Functions generally have zero overhead compared with typing in the expression directly into the query.
• Support for compiled SQL Procedures
  SQL PL is based on the ANSI SQL/PSM standard.
  We couldn't call it PSM (Persistent Stored Modules) because we didn't have modules...
  SQL Procedures are cross compiled to C with embedded static SQL.
  That in turn get's turned into a binary for the C code and a package.for the SQL.

• DB2 UDB V7.2
• Support for inline SQL PL functions and triggers
  inline SQL PL is extended to support: Local variables, IF THEN ELSE logic, WHILE loop and FOR loop.
  It is supported for scalar and table functions

• DB2 UDB V8.1
• SQL Procedures are UNFENCED
  This results in a significant performance gain
• INSTEAD of trigger support is added
  (I'm not 100 sure on this release.. it's so long ago...)

• DB2 UDB V8.2
• SQL Procedures get a virtual machine
  No more requirement for a C-compiler.
  The Procedure Virtual Machine (PVM) is platform independent, compiles faster and achieves the same speed.
• inline SQL PL can invoke a CALL statement
  This allows SQL Functions and triggers to drive much more complex logic

• DB2 9.1
• SQL Procedure performance
  The PVM integrates deeply with the SQL Runtime engine

• DB2 9.5
• Global variables
  Global variables allow preservation of data between SQL PL invocation without the need to use tables
• ARRAYs of scalar values support
  ARRAYs allow the passing of datasets between the client and server and between procedures without the need for staging tables

• DB2 9.7
• Module support
  Modules allow grouping of various SQL PL objects much like a C library
• More data types for compiled SQL PL
  Associative arrays, rows, arrays of rows, boolean, cursor type
• Rework of schema evolution
  Auto revalidation, anchored data types, named parameter invocation, default parameters, conditional compilation
• Obfuscation
  Obfuscation is used to hide IP of vendors from customers
• compiled SQL Functions and triggers
  Now you can choose between inline SQL PL and compiled SQL PL allowing for maximum function within triggers and functions.

 The last word on DB2 9.7 has not yet been spoken and you can expect a few more surprises.
In the mean time, and in my next entry I will pick up with the last bullet above:
How to choose between inline SQL PL and compiled SQL PL
#Db2