Global Data Management Forum

 View Only

 Behavior SIGNAL with message_text from trigger on different drivers

Nick Janssen's profile image
Nick Janssen posted Tue December 30, 2025 08:23 AM

Hello,

We are experiencing unexpected behavior between 2 different connection methods:

  1. Db2 for i using the IBM i Access (ACS) .NET provider (IBM.Data.DB2.iSeries)
  2. IBM Db2 .NET provider for .NET (Core) (IBM.Data.Db2 / Net.IBM.Data.Db2)

The problem occurs when you use a SIGNAL from inside a trigger. Minimum example:

CREATE TRIGGER example_trigger_BTI
BEFORE INSERT
ON example_table
REFERENCING
  NEW ROW AS N --INSERTED 
FOR EACH ROW
MODE DB2ROW
SET OPTION CLOSQLCSR = *ENDMOD 
BEGIN ATOMIC

  --ERROR HANDLER
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    DECLARE V_MSG VARCHAR(9999);
    DECLARE V_SQLCODE VARCHAR(9999);
    GET STACKED DIAGNOSTICS CONDITION 1 V_MSG  = DB2_ORDINAL_TOKEN_5;
    GET STACKED DIAGNOSTICS CONDITION 1 V_SQLCODE  = DB2_RETURNED_SQLCODE;
   RESIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = V_MSG;
  END;

  BEGIN
   signal sqlstate '70001' set message_text = 'example_message';
  END;

END;

When using IBM.Data.DB2.iSeries, the IBM.Data.DB2.iSeries.iDB2Error contains MessageDetails in which the `message_text` can be retrieved.

However, when using the other connection method IBM.Data.Db2 IBM.Data.Db2.DB2Error does not contain anything like MessageDetails. The regular message also does not include the `message_text` that is set in the SIGNAL statement.

Is it possible to somehow retrieve the custom message that is set along with the SIGNAL statement? Would be nice if both connection methods had the same features, because know we have to create a different implementation based on the connection method, which is not really preferred ...

Hope there is a solution for this! Thanks in advance.

Best regards,

Nick Janssen

Related Content