Asynchronous procedure call with DB2 .NET Data Provider

Question

Is there any way to asychronously call a DB2 stored procedure using DB2 .NET Data Provider?

Answer 1

BeginExecuteNonQuery doesn't work?

Answer 2

The problem is there's no BeginExecuteNonQuery. RichardOD: Could you write something more about how did you do it?

Answer 3

There are a number of ways- which one is appropriate will depend on your scenario.

What I was doing in Oracle was getting back 1000s of items of XML from a DB based on a queue of work item IDs. Getting them out in one go with a datareader didn't work, so I got them out 1 at a time on several different threads (each thread got a work item out of a queue until the queue was empty). As each separate thread got the result back it put the XML into a producer consumer queue- one based on Joseph Albahari's excellent threading tutorial. A separate thread looked at the producer consumer queue, performed XSL transformations on the data and wrote them out to a file- of course I benchmarked doing in single threadedly first and this was significantly slower.

I tried using the threadpool/asynchronous delegates to begin with (as the general advice on threading is to use the threadpool where possible, which delegates do indirectly as well), which may work providing you don't have more than 64 items to do. WaitHandle.WaitAll usually has a limit of 64 work items.. As I had 1000s this wasn't a good option, apart from doing them in batches 64 which seemed kludgy.

From memory I exited the producer threads gracefully when there was no more work for each one to do, then block until the producer consumer queue was also empty.

The most important thing is to make sure you have completely separate DB objects on each thread, otherwise you will stumble upon threading issues.

If you want to just execute 1 stored procedure asynchronous in a Windows form/WPF app you might want to look at using the background worker component.

Let me know if you know if you need any further info.

Answer 4

If DB2 doesn't support BeginExecuteReader, you can use asynchronous delegates to do the trick. Functionally, it would be the same thing, except that you implementing it yourself rather than using something built-in.

Check the example here: sql async query problem…