IBM Support

IC84048: SQLCODE -952 DOES NOT INTERRUPT AN SQL ROUTINE IN PROGRESS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When an SQL routine executes a statement that completes with
    SQLCODE -952 (Processing was cancelled due to an interrupt),
    DB2 incorrectly treats the -952 error as a regular exception
    condition.  If your SQL routine contains an appropriate general
    condition handler for SQLEXCEPTION, or a specific condition
    handler for SQLSTATE '57014', the condition handler will
    activate.  It is therefore possible for your SQL routine to
    continue execution after the interrupt has occurred.
    Subsequent SQL statements may fail with SQLCODE -20139:
    
    SQL20139N  SQL statements may not be issued in routine
    "<routine-name>" (specific name "<specific-name>") because of a
    previous statement failed or was interrupted.
    
    The intended behaviour is that DB2 will interrupt the currently
    executing SQL routine when SQLCODE -431 or SQLCODE -952 is
    detected.  These errors will be treated as unhandled exception
    conditions, even if an appropriate condition handler exists.
    The exception condition for the interrupt will be propagated to
    the caller.
    
    Example:
    
    create procedure test
    language sql
    begin
      declare continue handler for sqlexception
      begin
        insert into errlog values sqlstate, sqlcode;
      end;
    
      -- Let's say that this called routine fails with SQLCODE -952:
      call some_other_routine();
    
      insert into T1 values 42;
    end
    
    Here we have a simple SQL procedure that calls another
    procedure.  If the nested procedure call is interrupted with
    SQLCODE -952, instead of terminating routine TEST, DB2 will
    incorrectly proceed to dispatch the continue handler for the
    exception condition.  In this case, it will attempt an INSERT
    from the handler, which will fail with SQLCODE -20139; we will
    never reach the INSERT into table T1.
    
    This is incorrect.
    
    What should happen here is that immediately after the call to
    SOME_OTHER_ROUTINE, procedure TEST should terminate with
    SQLCODE -952; the condition handler should not be executed.
    

Local fix

  • When coding condition handlers for SQLEXCEPTION, be mindful
    that this may possibly include SQLCODE -952, until a fix is
    available.  Because condition handlers for specific SQLSTATEs
    always take precedence over general condition handlers
    (SQLWARNING, SQLEXCEPTION, NOT FOUND), you can add a specific
    condition handler for SQLSTATE '57014', which corresponds to
    SQLCODE -952.  Because this interrupt will not be catchable by
    condition handlers in the future, your handler should do
    nothing but propagate SQLSTATE '57014' to the caller.  We
    recommend an EXIT handler in the outermost scope of your
    routine.  For example:
    
    create procedure test
    language sql
    begin
      declare exit handler for sqlstate '57014'
        resignal; -- propagate SQLSTATE '57014' to the caller
    
      -- Your statements here...
    end
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of version 9.7 on Linux, Unix and Windows          *
    * platforms.                                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to DB2 LUW Version 9.7 Fix Pack 7 or higher levels.   *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 LUW Version 9.7 Fix Pack 7.
    

Temporary fix

  • When coding condition handlers for SQLEXCEPTION, be mindful
    that this may possibly include SQLCODE -952, until a fix is
    available.  Because condition handlers for specific SQLSTATEs
    always take precedence over general condition handlers
    (SQLWARNING, SQLEXCEPTION, NOT FOUND), you can add a specific
    condition handler for SQLSTATE '57014', which corresponds to
    SQLCODE -952.  Because this interrupt will not be catchable by
    condition handlers in the future, your handler should do
    nothing but propagate SQLSTATE '57014' to the caller.  We
    recommend an EXIT handler in the outermost scope of your
    routine.  For example:
    
    create procedure test
    language sql
    begin
      declare exit handler for sqlstate '57014'
        resignal; -- propagate SQLSTATE '57014' to the caller
    
      -- Your statements here...
    end
    

Comments

APAR Information

  • APAR number

    IC84048

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-06-11

  • Closed date

    2012-10-20

  • Last modified date

    2012-10-20

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

    IC83909

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
20 October 2012