Skip to content
Advertisement

DB2 SELECT from UPDATE Options

I am currently trying to do an

However, the version of DB2 I have does not appear to support this

Is there any alternative to this in DB2 that could be return a desired result? Where in one query we can Update and Return the result?

EDIT – The Select statement is supposed to return the values that are to begin processing by a server application. When this happens, a column will be updated to indicate that the Processing of this row has begun. A later command will update the row again when it is completed.

After Optimistic Select/Update Query

This is being called by a .NET Application, so this would be converted into a List of the Table Object.

Advertisement

Answer

While you can’t use SELECT FROM FINAL TABLE(UPDATE ...) currently on Db2 for IBM i…

You can within the context of a transaction do
UPDATE mainTable SET value = 'Y' WHERE value2 = 'N' with RR
SELECT * FROM mainTable WHERE value2 = 'Y'
COMMIT

The use of RR – Repeatable read means that the entire table will be locked until you issue your commit. You may be able to use a lower isolation level if you have knowledge/control of any other processes working with the table.

Or if your willing to do some extra work…the below only locks the rows being returned.
UPDATE mainTable SET value = '*' WHERE value2 = 'N' with CHG
SELECT * FROM mainTable WHERE value2 = '*'
UPDATE mainTable SET value = 'Y' WHERE value2 = '*' with CHG
COMMIT

The straight-forward SQL way to do this is via a cursor and an UPDATE WHERE CURRENT OF CURSOR ....

Lastly, since you are using .NET, I suggest taking a look at the iDB2DataAdapter class in the IBM .NET Provider Technical Reference (part of the IBM ACS Windows Application package)

You may also find some good information in the Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET Redbook.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement