Inserting into table while open select cursor still active

AlexSalas95 · Posted: Fri May 17, 2024 11:37 pm

(Not sure how relevant this is, but this question is about embedded DB2 in a COBOL program)

I've been told by a senior member that you cannot insert a row into a table where you currently have an open select cursor to. I'm not inclined to doubt them, however I couldn't find any information supporting this.

Is it true you cannot insert into a table when you already have a cursor opened reading the table? Or is it bad practice? Or is it totally fine?

Additionally, assuming it is fine, could the newly inserted row be retrieved using the cursor? Would a commit be required beforehand?

Rohit Umarjikar · Posted: Sun May 19, 2024 1:29 am

Welcome !

The best way to know is to test with different scenarios but ..
you should not be having issues inserting into the the table and also you will see updated row if that qualifies the criteria in the fetch.

If you don’t need inserted row to be part of the fetch or need to have optimized procedures then load the cursor data into temp table or an array and then have another loop just for insert from that.. that will ensure you are not getting into infinite loop situation.