IBM DB2 trigger and stored procedures woes

I accept that it is so, but I still find it strange what you can and cannot do in triggers. If anyone knows the answers to my problems with the below trigger code I would be more than happy to hear from them… 🙂

Well it all started with me having to do a simple trigger that calculates a simple search rank when a row is updated. Instead of having this is the Java application I thought this was the perfect job for a trigger. A trigger is code you can have executed at specific times before or after a row of data is inserted. You have much more options than that but you get the point.

The trigger should run after update and end up updating the search_rank column with a calculated integer value. The search rank is quite simple – 1 point for a company name, 3 points for an address, 3 points for an e-mail address etc. After adding the different scores the final store should be written to a column. I immediately thought that a trigger should do the job so I wrote one (not as easy as it sounds since there were some trial and error included).

CREATE TRIGGER CALC_SEARCHRANK
AFTER UPDATE ON COMPANY
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
   -- declare temp variable and reset out variable
   declare calc_rank smallint default 0;
   declare temp_rank smallint default 0;
   declare temp_string varchar(50) default null;

   -- get value for logo
   declare cur_logo cursor for select name_1 from company_logo cl where cl.company_id=o.company_id;
   open cur_logo;
   fetch cur_logo into temp_rank;
   close cur_logo;
   set calc_rank = calc_rank + temp_rank;

   -- get value for products
   select count(*) into temp_rank from company c where c.company_id in (select company_id from company_logo cl where cl.company_id=o.company_id);
   set calc_rank = calc_rank + temp_rank;

   -- get value for company name
   select name_1 into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 1;
   end if;

   -- get value for zip/city
   select zipcode into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 2;
   end if;

   -- get value for address1
   select address_1 into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 2;
   end if;

   -- get value for phone
   select phone into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 3;
   end if;

   -- get value for fax
   select fax into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 2;
   end if;

   -- get value for e-mail
   select email into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 3;
   end if;

   -- get value for web
   select url into temp_string from company where company_id=o.company_id;
   if temp_string != '' then
      set calc_rank = calc_rank + 4;
   end if;

   -- update company table
   update company set search_rank=calc_rank where company_id=o.company_id;
end
@

But that wasn’t good enough. I kept getting errors when trying to add the trigger to the database:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "for" was found following "lare cur_logo
cursor".  Expected tokens may include:
"".  LINE NUMBER=12.  SQLSTATE=42601

After a LOT of trying different stuff out and searching on Google I have found out there is something called dynamic statements and what have you. Apparently you cannot do something like the above – I’ll have to look more deeply into why not. I would like to understand.

Since I hadn’t solved the problem I set out to solve yet I turned to trying my SQL out as a stored procedure. This worked better. It only took me a couple of minutes to convert the SQL into a procedure and load the procedure into my database.

CREATE PROCEDURE CALCSEARCHRANK(IN id INT)
LANGUAGE SQL
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
BEGIN
   -- declare temp variable and reset out variable
   declare calc_rank smallint default 0;
   declare temp_rank smallint default 0;
   declare temp_string varchar(50) default null;

   -- get value for logo
   select count(*) into temp_rank from company_logo cl where cl.company_id=id;
   set calc_rank = calc_rank + temp_rank;

   ... (same as above)
   ... (same as above)
   ... (same as above)

   -- update company table
   update company set search_rank=calc_rank where company_id=id;
END @

The stored procedure worked but I really would like to have the code called automatically hence the need for the trigger. After a couple of hours trying to call my procedure from my trigger I finally gave up. Apparently calling stored procedures from triggers is only supported from DB2 v.8.2 and up and we’re running DB2 v.7.2 due to some legacy stuff. Bummer!

So now I am basically back where I started and I have implemented the updating of the search rank in the application instead in the database. The only thing left to do is the updating of the existing records but that should be simple (famous last words)) using the stored procedure I ended up writing. The only issue there is installing the stored procedure in DB2 on the Linux production box.

I am having an issue with this on Linux where DB2 keeps complaining about not be able to load the db2udp library but I might have found the error. Apparently you need to install the Application Development package in order for you to install stored procedures.

This is a logical prerequisite I think… 🙂