IBM Support

IC73627: BREAKING CLP OUTPUT PIPE ON STORED PROC CAUSES DB2BP TO RUN AWAY, FLOODING DB2DIAG.LOG WITH ERRORS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • 1. create stored proc with large result set in returned cursor:
    
    CREATE OR REPLACE PROCEDURE foo
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    
        DECLARE stmt_str varchar(16384);
    
        DECLARE output_cursor cursor with return to caller for
    output_Stmt;
    
    
        set stmt_str = 'select 1 from
    syscat.tables,syscat.tables,syscat.tables';
        prepare output_Stmt from stmt_str;
        open output_cursor;
    
    END @
    
    2. db2 "call foo" | less
    3. page down a couple of pages, and then quit out of 'less'.
    This breaks the output pipe of CLP frontend
    4. db2bp will start to spin, consuming a whole CPU and flooding
    db2diag.log with messages
    2010-10-11-13.10.12.544776-240 E13460809A708      LEVEL: Error
    (OS)
    PID     : 778276               TID  : 1           PROC : db2bp
    INSTANCE: srees                NODE : 000
    APPID   : *LOCAL.srees.101011170952
    EDUID   : 1
    FUNCTION: DB2 UDB, oper system services, sqlowqueInternal,
    probe:40
    MESSAGE : ZRC=0x870F003E=-2029060034=SQLO_QUE_BAD_HANDLE "Bad
    Queue Handle"
              DIA8555C An invalid message queue handle was
    encountered.
    CALLED  : OS, -, msgsnd
    OSERR   : EINVAL (22) "A system call received a parameter that
    is not valid."
    DATA #1 : system V message queue identifier.,
    PD_TYPE_SYSV_QUEUE_ID, 4 bytes
    0x0730000A
    DATA #2 : Pointer, 8 bytes
    0x0000000110050738
    DATA #3 : unsigned integer, 8 bytes
    31
    
    No database connection is possible after that, from the window
    where we were doing the pipe into less.   Even after killing
    db2bp, we still can't connect.   Either db2stop force or closing
    the shell where the connect was attempted will fix it.
    
    And also note that unless you kill db2bp, it will just keep
    running, flooding db2diag.log - even after db2stop force - until
    db2bp is killed or the containing shell is closed.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * db2 CLP users                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When a SP returning a huge result set is executed and output *
    * via pipe, db2diag.log is heavily dumped with messages due to *
    * bad queue handle. No database connection is possible after   *
    * that.                                                        *
    * unless b2bp is killed, it will just keep running, flooding   *
    * db2diag.log - even after db2stop force - until db2bp is      *
    * killed or the containing shell is closed.                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2_v97fp4                                        *
    ****************************************************************
    

Problem conclusion

  • Problem fixed at the current fixpack and applied to future
    releases too.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC73627

  • 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-01-04

  • Closed date

    2011-04-28

  • Last modified date

    2011-04-28

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

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

    IC73631 IC87548

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC73627

Modified date: 28 April 2011