MVSFORUMS.com

[Jamylady]

Hi,
I have a requirement for a new program which requires us to select from a cursor and insert into the same table later in the program if the record requirements are correct.
Looks like the cursor picks up the records that are inserted during the program execution. (I wasnt aware of this or I thought this would never happen), Is there any way to prevent this? I am doing the bind with CS (Cursor Stebility)option.
Then my solution has been to create a subscripted temporary table in working storage and then at end of cursor/program insert the records held on the temporary table.

I have come across another solution - Use of Temp DB2 tables, not sure about the performance though

Do you know of a better way of doing this?

Thanks
ja

[jim haire]

I don't know if the records you are inserting have an update timestamp field, but one way this can be done is to capture the current timestamp before you do any inserts and then build your cursor so that it has additional filter where you only retrieve rows that are less than or equal to the current timestamp.

Any new rows inserted would have timestamps greater than the current timestamp. In this way these rows would not get picked up.

[jim haire]

A little clearer version of the solution above:

I don't know if the records you are inserting have an update timestamp field, but one way this can be done is to capture the current timestamp before you do any inserts and then build your cursor so that it has additional filter where you only retrieve rows that are less than or equal to the timestamp you had captured.

Any new rows inserted would have timestamps greater than the timestamp you had captured. In this way these rows would not get picked up.

[Jamylady]

Hi Jim
That is a good idea, but we dont have a time stamp filed on the row to capure the updated time:(
Thank you

[dbzTHEdinosauer]

look at the appl programmers guide and the reference manual for your version of DB2,
keyword: sensitive
_________________
Dick Brenholtz
American living in Varel, Germany

[Jamylady]

Great. Thanks
It worked

[jctgf]

Hi,
I don't think it's a good idea to insert lines in a db2 table in batch mode with embedded Sql.
The problem is that if the program abends, you'll have your db2 table partially populated.
If you restart your program, you'll have to mind about the records already inserted.
I'd simply write all the records to be inserted in a SAM file and then load it into the table (LOAD utility) when the program ends.
Actually it would be even way faster.
Just an opinion.

[Jamylady]

Hi jctgf,

What if I control the program abends and issue a ROLLBACK? I think my program is managing the program abends and rolling back all the table inserts/updates.
Thanks
JA

[papadi]