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. |
****************************************************************
* 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 *
**************************************************************** |
