MVSFORUMS.com

[danm]

If a row (by unique index) exists in the table, I want to update the row, otherwise insert a new row. There are 3 ways to accomplish this:

1. a. Update the row
b. If sqlcode = 100 (no row found), insert a new row

2. a. Insert a new row
b. If sqlcode = -803 (duplicate key), update the row

3. a. Select from the table with FOR UPDATE clause
b. If sqlcode = 0, update the row at the cursor
c. If sqlcode = 100 (no row found), insert a new row

Which way will give better performance?

[kolusu]

Danm,

Option 1 & 2 are equally good. I would avoid the third option. Run an explain with option 1 and 2.

Check the access method used and choose the optimal access method. check this link for Using EXPLAIN to improve SQL performance

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/6.4?DT=20010710165542

Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[blitz]

If the table to be updated has triggers defined, and you do not want to 'update' if there isn't going to be any change in data, then option '3' is the best way out as options '1' and '2' might trigger unwantedly if updated blindly.

sorry for digging out skeletons from the graveyard, but I just wanted to put my thoughts in for people using the search facility in these boards

[dbzTHEdinosauer]

Sorry to be a 'mister precision', but danm, you question is worded for failure. What are you doing? Is this a business application where you are posting transactions against a master or are you doing a table load? What are the volumes involved? If you would explain what business process you are attempting to accomplish and some details (such as blitz brought up - are there triggers involved with the table) you will get a better answer.
you cannot choose a methodology if you don't know the process.
_________________
Dick Brenholtz
American living in Varel, Germany

[blitz]