Re: DB2-style INS/UPD/DEL RETURNING

On Sun, 12 Mar 2006, Jonah H. Harris wrote:

> I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING stuff,
> and he recommended looking into the way DB2 handles similar functionality.
> After looking into it a bit, it's more inline with what Tom's suggestion was
> regarding a query from the operation rather than returning the values in the
> manner currently required.
>
> Here's DB2's syntax... does anyone have any familiarity with it?
>
> Simply put, it's sort-of like:
>
> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)
>
> I'd like to hear from anyone that's used it to see if it really is better...
> logically it seems nicer, but I've never used it.

It works well for cases where you want to pass the result of an
insert/delete/update to another query. There was a paper on IBM developer
works on how they got the 7 or so queries in an order transaction in TPC-C
down to 3 queries and increased throughput impressively.

This doesn't solve the generated keys problem that the Java and probably
.NET interfaces have. Mind, RETURNING doesn't solve anything either.

I prefer this syntax to RETURNING. Then again, Oracle is a bigger target
than DB2 so... I'm not sure.

Thanks,

Gavin