Hi there,

i hope, someone can help me with the following (tricky!) problem. Sorry for writing so much, but I felt, it's the minimum for properly describing my problem.

I created a stored procedure on my DB2 Express-C v9.7.0.441, WinXP System. The definition looks like this:Myproc.dll is an MFC DLL, that uses the DB2 CLI API to connect with the database that invoked the external procedure. The DLL does not create any kind of UI (which would be blocked by DB2).

DoSomething is an exported function of the DLL with no return value and no parameters. It creates several worker-threads that perform some computations and then insert data into the database.

When invoking the function from a separate .exe it all works well. Unfortunately when the same function is invoked by DB2 through a call of the external procedure (i.e. "call myschema.myproc;") it does not work. Debugging showed that the main thread (which creates the worker-threads) successfully connects to the database and also successfully executes several SELECT statements on it. However, the first time when one of the worker-threads tries to execute a SELECT-statement it fails (SQLExecDirect returns code INVALID_HANDLE). This happens because the statement handle passed to SQLExecDirect indeed is invalid: Although the prior call to SQLAllocHandle returned SQL_SUCCESS, the new handle is negative, while the statement handles allocated in the main thread were all positive.

So it appears to me, that DB2 for some reason accepts queries only from the main thread of the DLL, while rejecting queries from its sub-threads. How can I resolve this? Does it have anything to do with the definition of the procedure (e.g. FENCED vs. NOT FENCED). It's really crucial for me to get this thing to work and thus I'd really appreciate any suggestions.

Best Regards,
Wilfried

Using multiple threads inside a routine (stored procedure or UDF) is not supported. The reason is that your routine runs inside a DB2 process. But DB2 has no control over the threads you may create and, thus, cannot guarantee proper functioning and proper cleanup when the procedure ends. Therefore, using multiple threads in a routine is not supported and DB2 does a few things to prevent you from doing that, e.g. not allowing separate connections from other threads.

p.s: Things are different for DB2 z/OS where a routine runs in a WLM environment and is seen by DB2 just like any other client application (for the most part). But that won't help you much on DB2 LUW, though.

Not actually good news, but at least I can stop trying now and work on a different solution... Anyway, thanks for the quick reply.