Hi.

I have problem with threads and DB2. My program fetch rows from table
A, validates them and at the end inserts rows to table B. This
processing takes too much time so I've tried to use threads (pthread.h
library). Pseudo code of thread's function (executed by each thread)
looks like below:

**********************************************
pthread_mutex_lock(&prepareMuteks);
declaration of some host variables;
preparing, declaring and openings cursor on table A;
pthread_mutex_unlock(&prepareMuteks);

loop start
pthread_mutex_lock(&fetchMuteks);
fetching from cursor;
pthread_mutex_unlock(&fetchMuteks);

validating record;

if record is ok then
pthread_mutex_lock(&fetchMuteks);
insert record to table B;
pthread_mutex_unlock(&fetchMuteks);
end if
end loop;

closing cursor;
************************************************

I pass some parameters to above function and use them in select
statement of cursor so each thread operate on its own set of rows from
table A. I simply use appropriate 'where' clause. So each thread
process its own and separate set of rows When I start only one thread
everything is ok but when more than one thread execute (parallel)
above code then SQL errors occurs. These errors are:

- SQLCODE=-501, SQLSTATE=24501 - cursor not open
- SQLCODE=-514, SQLSTATE=26501 - prepared statement not found
- SQLCODE=-901, SQLSTATE=58004 - SQL system error

Additionally sometimes program doesn't fail and all rows are processed
but sometimes above errors occurs. It's lottery. From IBM support I
know that each thread operate on its own cursor (in my program these
cursors have the same name). Quotation: "Scope of a cursor: The scope
of cursor-name is the source program in which it is defined; that is,
the program submitted to the precompiler. Thus, a cursor can only be
referenced by statements that are precompiled with the cursor
declaration. For example, a program called from another separately
compiled program cannot use a cursor that was opened by the calling
program. The scope of cursor-name is also limited to the thread in
which the program that contains the cursor is running. For example, if
the same program is running in two separate threads in the same job,
the second thread cannot use a cursor that was opened by the first
thread."
So a thread can't close cursor which belong to other thread. Also a
scope of host variables is also limited to the thread. IBM suggested
to install some PTFs but it didn't help. I've tried to use mutexes in
different places of my program but without result. Maybe someone has
any idea or had the same problem.

P.S. Sorry for my English