IBM Support

IC80560: DB2 MAY CRASH WHEN CREATING SQL STORED PROCEDURES FROM A SCRIPT.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • In the db2diag.log, you may notice something similar to this:
    
    2011-11-29-16.04.23.737762-300 I3121781E331        LEVEL: Event
    PID     : 18473                TID  : 46912689169632PROC :
    db2vend (PD Vendor Process - 1)
    INSTANCE: db2inst1             NODE : 000
    FUNCTION: DB2 UDB, trace services, pdInvokeCalloutScript,
    probe:20
    STOP    : Completed invoking
    /home/db2inst1/sqllib/bin/db2cos_trap
    
    2011-11-29-16.04.23.737918-300 E3122113E1567       LEVEL:
    Critical
    PID     : 18466                TID  : 46913906796864PROC :
    db2sysc 0
    INSTANCE: db2inst1             NODE : 000          DB   : SAMPLE
    APPHDL  : 0-80                 APPID:
    *LOCAL.db2inst1.111129210341
    AUTHID  : db2inst1
    EDUID   : 157                  EDUNAME: db2agent (SAMPLE) 0
    FUNCTION: DB2 UDB, oper system services, sqloEDUCodeTrapHandler,
    probe:90
    MESSAGE : ADM14011C  A critical failure has caused the following
    type of error:
              "Trap". The DB2 database manager cannot recover from
    the failure.
              First Occurrence Data Capture (FODC) was invoked in
    the following
              mode: "Automatic". FODC diagnostic information is
    located in the
              following directory:
    
    "/eodb/diaglog/db2inst1/FODC_Trap_2011-11-29-16.04.14.424617_000
    0/".
    DATA #1 : Signal Number Recieved, 4 bytes
    11
    DATA #2 : Siginfo, 128 bytes
    0x00002AAAFE80FD70 : 0B00 0000 0000 0000 0100 0000 0000 0000
    ................
    0x00002AAAFE80FD80 : 0000 0200 0000 0000 0000 0000 0000 0000
    ................
    0x00002AAAFE80FD90 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AAAFE80FDA0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AAAFE80FDB0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AAAFE80FDC0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AAAFE80FDD0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    0x00002AAAFE80FDE0 : 0000 0000 0000 0000 0000 0000 0000 0000
    ................
    
    2011-11-29-16.04.27.841158-300 I3123681E481        LEVEL: Error
    PID     : 18473                TID  : 46912689169632PROC :
    db2vend (PD Vendor Process - 1)
    INSTANCE: db2inst1             NODE : 000
    FUNCTION: DB2 UDB, oper system services, DB2VEND_main,
    probe:1040
    MESSAGE : ZRC=0x870F0009=-2029060087=SQLO_EOF "the data does not
    exist"
              DIA8506C Unexpected end of file was reached.
    DATA #1 : String, 63 bytes
    Fenced vendor process pipe failure, terminating vendor process.
    
    2011-11-29-16.04.27.843463-300 E3124163E549        LEVEL: Severe
    PID     : 18464                TID  : 46912698837312PROC :
    db2wdog 0
    INSTANCE: db2inst1             NODE : 000
    EDUID   : 2                    EDUNAME: db2wdog 0
    FUNCTION: DB2 UDB, base sys utilities, sqleWatchDog, probe:20
    MESSAGE : ADM0503C  An unexpected internal processing error has
    occurred. All
              DB2 processes associated with this instance have been
    shutdown.
              Diagnostic information has been recorded. Contact IBM
    Support for
              further assistance.
    
    ===
    
    Also, this problem will only occur when there's XML query
    involved.
    
    An example of the Stored Procedure to create is:
    
    CREATE PROCEDURE GET_FAULT_DETAIL (
        INOUT IFAULT        XML,
        OUT OFAULT_DETAIL    XML,
        OUT OERROR_CODE    INTEGER )
      SPECIFIC SQL090617134025600
      LANGUAGE SQL
      NOT DETERMINISTIC
      EXTERNAL ACTION
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      INHERIT SPECIAL REGISTERS
    ----------------------------------------------------------------
    --------
    -- SQL Stored Procedure
    ----------------------------------------------------------------
    --------
    P1: BEGIN ATOMIC
        DECLARE vTIN VARCHAR(32);
        DECLARE vFaultID BIGINT;
        DECLARE vCounter BIGINT;
        DECLARE vOrderID BIGINT;
        DECLARE cStatus VARCHAR(32) DEFAULT 'Active';
        DECLARE vFaultDetailXML XML;
    
    
        SELECT  T.TIN, T.FAULT_ID
        INTO      vTIN, vFaultID
            FROM  xmltable('$XML_DOC' passing IFAULT AS "XML_DOC"
    COLUMNS
                   TIN             VARCHAR(32)    PATH
    '/*:GetServiceOrderFaultRequest/*:DataArea/*:ServiceOrderFault/*
    :TIN',
                   FAULT_ID    BIGINT        PATH
    '/*:GetServiceOrderFaultRequest/*:DataArea/*:ServiceOrderFault/*
    :Id'
        ) AS T;
    
        IF (vTIN is not null) AND (LENGTH(vFaultID) > 0) THEN
            CALL GET_FAULT_DETAIL_BY_ID(vFaultID, OFAULT_DETAIL,
    OERROR_CODE);
        ELSE
            SET OERROR_CODE = -1;
    
        RETURN OERROR_CODE;
    
    END P1@
    

Local fix

  • Split the script to multiple smaller ones to create only a small
    number (about 10) of Stored Procedure at a time.
       Or, create the Stored Procedure at a time from a Command
    Line.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Fix Pack 6 or higher                              *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 V9.7 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC80560

  • 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

    2011-12-21

  • Closed date

    2012-07-11

  • Last modified date

    2012-07-11

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

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

    IC84404

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC80560

Modified date: 11 July 2012