inline SQL pL vs compiled SQL PL (Part1)

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 CSSupports 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 triggersinline 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 machineNo 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 statementThis allows SQL Functions and triggers to drive much more complex logic

compiled SQL Functions and triggersNow 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