SQL Tips for DB2 LUW

with Tags: trigger

Featured Blog Entries

All posts

Logon triggers in DB2 (kind of)

Visits (8686)

DB2 for LUW is a very extensible database engine and one of things I do love to recommend to customers and IBMers alike is to use these capabilities whenever they encounter mismatches between e.g. Oracle and DB2 which need to be overcome. or, alternatively, perhaps you want to add some "libraries of function" on your own. Let's for example assume you are implementing the INSTRB function. INSTRB is just a synonym for INSTR(VARCHAR, VARCHAR) so perhaps you write the following: CREATE OR REPLACE FUNCTION ORALIB.INSTRB(VARCHAR(),... [More]

Tags: logon trigger connect_proc path

inline SQL pL vs compiled SQL PL (Part1)

Visits (8937)

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 V 2.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... [More]

Tags: function compile procedure sql pl trigger psm inline

Reality Check: SQL PL Profiler and plan explain with actual row counts

Comments (19) Visits (50284)

UPDATE: Feb 11, 2012 I have added a TRACE procedure and renamed ENABLE/DISABLE_EXPLAIN_ACTUALS to ENABLE/DISABLE_ACTIVITY_MONITOR. The TRACE procedure returns a formatted trace of all SQL monitored by the activity monitor. This includes nested compiled triggers, procedure calls and function invocations as well as error conditions. Next on my list: trace-backs for errors and also tracing of parameters. For the past months I have been tinkering on and of with the "new monitoring API" which has been introduced in DB2 9.7. Here are the... [More]

Tags: section function profile db2exfmt actuals procedure explain trigger

SQL PL Profiler - Again, better than ever

Visits (6604)

I spent the time around the July 4th weekend at the IT building a major bank in Jersey City assiting in the "going-live" of their .com website on DB2 after moving their core internet application away from Oracle. Needless to say there were the usual hiccups compounded by the fact that the app is non trivial and has stringent Service Level Agreements (SLA) to fullfill. So, naturally, like any good tradesperson I brought my tools including the SQL PL Profiler I posted about a while ago. When I wrote this profiler however I had left some... [More]

Tags: procedure pl. debugger trigger profiler "sql "pl/sql" function

All in one...multi-action triggers in DB2 9.7.4

Comments (2) Visits (14678)

You may know that I lead the team enhancing DB2 to simplify Oracle application conversion to DB2. A fair number of features we have added, such as VARCHAR2 are not terribly interesting for true blue customers. But there are also quit a few features which add value for everyone. One such feature commonly called multi-action trigger support was added in DB2 9.7.4 released in April 2011. A triggers, as I''m sure you know, consists of procedural logic specified in SQL PL (or PL/SQL) which is executed when the trigger subject table is modified.... [More]

Tags: multi-action 9.7.4 trigger db2

More than a TRACE. Extending the SQL PL Profiler to do tracing

Comment (1) Visits (6244)

Motivation A major DB2 partner acquired a subsidiary a while back which sells an application that needs to me enabled from Oracle to DB2. The team is making good progress, but recently ran into a snag. There was a difference between Oracle and DB2 which caused DB2 to raise a run time error. With deeply nested routines which are being driven by an application homing in on the root cause was not trivial. Tracing This is a prime example for tracing. Given a tracing facility the team could have attached to the application, traced the failing... [More]

Tags: application db2trc tracing sql profile trace routine procedure sql_trace profiling dbms_monitor trigger

Using autonomous transactions

Comments (4) Visits (21657)

When I started leading the project to add Oracle's PL/SQL to DB2 it became quickly clear that perhaps amongst the top five features needed to claim a reasonable level of compatibility were autonomous transactions . An autonomous transaction ("#PRAGMA AUTONOMOUS" in PL/SQL speak) is a transaction which executes in total isolation from its invocation context. While this feature has seen a lot of uptake with the Oracle to DB2 realm I have rarely seen it used in a traditional DB2 environment. The reasons for that may be: Poor... [More]

Tags: sql function savepoint autonomous procedure pragma transaction trigger block auditing

Blogs

SQL Tips for DB2 LUW

About this blog

Links

Tags

Recent tweets