Have you ever received the following error Message When using the db2 Adapter for BizTalk?

The adapter "DB2 Adapter" raised an error message. Details "UPDATE or DELETE is not allowed against the specified cursor. SQLSTATE: 42828, SQLCODE: -510"

Well... A not very well documented feature of the adapter is to update all of the rows after you have done a select, for that result set.

So you put your query in the SQL Command Part of the receive location configuration: SELECT * FROM PUBS.AUTHORS

You then insert your update command, which the mini Wizard generates for you: UPDATE PUBS.AUTHORS.AU_LNAME SET AU_COMPLETE = 1  WHERE CURRENT OF <CURSOR>

BANG: Error in the event log:

Event Type: Warning
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5740
Date:  19/02/2007
Time:  03:33:27 PM
User:  N/A
Computer: PAULSO
Description:
The adapter "DB2 Adapter" raised an error message. Details "UPDATE or DELETE is not allowed against the specified cursor. SQLSTATE: 42828, SQLCODE: -510".

And nothing else happens....

Well, after some digging around, and now I feel I'm becoming an expert on DB2...

When the intent is to update records with a server-side cursor, DB2 requires that the SQL SELECT statement also include the FOR UPDATE option. For example, to select all records from the AUTHORS table in the DB2 collection called PUBS with intent to update requires the following SQL syntax:

SELECT * FROM PUBS.AUTHORS FOR UPDATE

If this was not vauge enough, when using DB2 for MVS V4R1 and DB2 for OS/400 V3R2, there are further requirements to indicate the columns that you intend to update. For example, to update the AU_COMPLETE column in the PUBS.AUTHORS table, the following SQL syntax must be used:

SELECT * FROM PUBS.AUTHORS FOR UPDATE OF AU_COMPLETE

On then did the update work and the error go away.

I'd recommend using a db2 package/stored procedure, but for the quick SQL command method this can make it work.