IC73265: THE IMPLICIT CAST IN THE STORED PROCEDURE DOES NOT WORK CORRECT AT THE FIRST TRIGGER CALL.

APAR status

• Closed as program error.

Error description

• The implicit cast in the procedure called from trigger does not
  work at the first call.  It returns NULL instead of casting the
  value but the same casting works at the next trigger call.
  
  Reproduction:
  
  create table t( i int, val int );
  
  create procedure trigtesterr();
      raise exception -746, 0, 'This is an error from trigger!';
     end procedure;
  
   --  procedure to cast varchar to integer
  
  create procedure trigtest1() returning varchar(255) as charint;
      define charint varchar(255);
   let charint = '7';
      return charint;
  end procedure;
  
  create procedure trigtest2( param integer ) returning integer as
  resultnum;
      define resultnum integer;
  
  let resultnum = trigtest1();  -- critical point! NULL is
  returned instead of casting value.
   return resultnum;
  end procedure;
  
  create trigger trig_trigtest update on t
  referencing new as newvalue old as oldvalue
      for each row when ( ( trigtest2( newvalue.i ) != 0 ) )
          ( execute procedure trigtesterr() );
  
  
  insert into t ( i, val ) values ( 1, null );
  update t set ( i, val ) = ( 3, null ) where 1 = 1;
  
   1 row(s) updated.
  =============
  
  If you run another update, then the cast works correct and
  returns error "-746: This is an error from trigger!"
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * triggers calling nested udrs within WHEN clause.             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * In the nested udr call scenario, the sequence of the inner   *
  * udr is idled too early.                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IDS 11.50.xC9 or request a patch.                 *
  ****************************************************************
  

Problem conclusion

• The fix is to idle the outer udr sequence at the end of the
  execution properly. The inner sequence is taken care of as
  usual. Fix is in IDS 11.50.xC9.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP