Posted by: Serge Rielau

First off: Happy New Year to everyone. I hope your holiday season was as pleasant as mine.

In the first post of this "mini-series" I introduced inline SQL PL and compiled SQL PL; when they were introduced along with a time line of added capabilities.
In general most of us believe choice is good. Oftentimes however choice can cause confusion.
Do I buy the red shoes or the black shoes? Life is so much easier when there is less choice, or at least when it's clear which choice is better.

So in this post I'll make an attempt to describe which form of SQL PL to choose over which, when and why.

• Choose inline SQL PL when it is the only choice you have
  Doh! Of course, but when is that?
• An SQL PL table function
  DB2 9.7.3 does not support compiled table functions.
  So if you want to build some result set in SQL and pass it back into a FROM clause your choice is clear.
  In the not so distant future you may be able to pass an ARRAY OF ROWS via a scalar function to the FROM clause, but right here right now table functions means inline SQL PL or use a host language such as Java or C.
• A FOR EACH statement trigger
  DB2 9.7.3 does not support compiled statement triggers. This may change over time, but if you need NEW TABLE and/or OLD TABLE access you must use inline SQL PL
• You need the logic to share the same special variable content. Most importantly CURRENT TIMESTAMP.
  I have a hard time imagining why that is. After all you can just store your CURRENT TIMESTAMP in a variable, but then again perhaps you need to share the CURRENT TIMESTAMP of the invoking statement. Inline SQL PL completely lives in the time of it's invoker.
• You need to use SQL PL in the WHERE clause of a multinode data warehouse.
  Currently DB2 does not support invocation of compiled SQL PL from a "non-coordinator node".

• Choose compiled SQL PL when it is the only choice you have
• You write an SQL PL Procedure
• You have compose SQL on the fly and execute it dynamically.
  That is you need to use PREPARE/EXECUTE or EXECUTE IMMEDIATE
• You need to handle SQL exceptions
  Since inline SQL PL is macro expanded into an SQL statement it cannot recover from any error.
  Any error must e handled by the invoker.
• You need to rollback to a savepoint
  Same as above for SQL exception. You cannot unravel parts of macro expanded logic.
• You need to use ARRAY or ROW or cursor parameters or variables
• You need to use cursors
  inline SQL PL does not have the concept of cursor declaration, open, fetch and close.
  It can do FOR loop however, which is often just as good or better.
• You need to write PL/SQL rather than SQL PL
  PL/SQL is entirely compiled logic. If you want to exploit inlining you need to redefine your PL/SQL as SQL PL.
• You need to write a routine within a MODULE
  Within a MODULE all SQL PL is compiled, independent of the syntax you use.
• You need recursion
  The only way to get recursive invocations in routines is to use dynamic SQL.
• Choose inline SQL PL when speed is critical
  Inline SQL PL has zero initialization and tear-down cost.
  Better yet since it's macro expanded the optimizer can do some pretty nifty things to melt away codepath.
  For example if you invoke foo('hello') then any SQL statement within foo which uses the argument will see the 'hello' literal string when it gets compiled.
  This can do wonders branch elimination of MDC or range partitioned tables or simply to exploit distribution statistics.
  The ideal inline SQL PL statement has no BEGIN ATOMIC. It only has RETURN.
  Performance improvements can reach multiple orders of magnitudes between a compiled function and simple RETURN.
• Choose compiled SQL PL for complex stuff
  Very fussy I know... My thumb rule is this: If you need more lines than fit on your shell screen (80x25) you probably want to go compiled.
  This is because you likely:
• Have some non trivial logic
• Multiple SQL statements which each are not free
  So the cost of initialization and tear down becomes secondary to the execution time.
• Use this logic in many places and replicating it into many invoking statements will bloat you package cache.
• Maintennatce will become an issue if you try to twist it into something inline SQL PL can digest
  (dense SQL is not always a good thing)
• Choose compiled SQL PL for recursive triggers
  Did I say inline SQL PL is macro expanded? I'm sure I did. Now imagine you have an update trigger.
  You compile an UPDATE statement which causes expansion of this update trigger.
  The update trigger itself contains to UPDATE statements against the same table your trigger is defined against.
  Noe that I chose "contains" rather than "executes". We are still compiling. Nothing is executing just yet!.
  Each update statement causes the same update trigger to be expanded yet again and so it goes 16 level deep.
  After 16 levels if Db2 ever reaches the bottom of recursion you will get a runtime error.
  Luckily long before that you will have received a -101 "statement too long or too complex" error because you just expanded the trigger
  1+2+4+8+....  so about 64000 times!
  Note that such a recursion does not have to be direct. It can be indirect involving say another table or an RI constraint.
  Also, even if you do not have a "fan-out", that is each nesting only expands one other trigger, you still wnat to avoid the situation because compile time will simply become too high. This is especially true in OLTP (it's a trigger!) and  when statement concentrator is not used.
  I call this: "death by compile"
• Choose inline SQL PL for before triggers or triggers that simply raise errors
  In the vast majority of cases before triggers either set default values for columns or they check conditions and raise errors.
  A well written inline before trigger evaporates in the optimizer plan to something as simple as a FILTER.
  The cost of such a trigger will exactly the same as if you had added the RAISE_ERROR() function in the INSERT/UPDATE/DELETE statement itself or provided the column values directly.
  In OLTP trigger cost dominates execution cost for the statement. Ideally the cost for the statement should be the sum cos of the statement without trigger plus the content of the triggers.

OK, these are pretty much the rules I live buy when giving advise on using SQL PL. There will be at least one more part in this miniseries where I will describe how to rewrite normal SQL PL into fast inilne SQL PL for those extra critical functions or triggers.

Again, Happy New year
#Db2