CODEWITHSUNDEEP
db2db2-400rpgle
cubswin
cubswin

Reputation: 11

RPG embedded SQL set option commit=*CS

In free form RPG using embedded SQL, I specified this for my SQL options:

exec sql
 set option commit=*CS,
 datfmt=*iso,
 closqlcsr=*endmod;

If I specify commit=*CS, do I need to specify WITH CS on my SQL select statement or is that assumed since I specified it in the set option?

If I specify commit=*none, and then specify WITH CS on my SQL select statement will the WITH CS take effect since on my set option commit I said *none?

Upvotes: 1

Views: 2599

Answers (1)

Charles
Charles

Reputation: 23823

the set option statement sets the default for all statements in the module.

That default can be overridden by the WITH clause on an individual statement.

So with 

exec sql
 set option commit=*CS,
 datfmt=*iso,
 closqlcsr=*endmod;

A statement without a WITH clause will use commitment control and an isolation level of cursor stability. A statement with WITH NC will not use commitment control and a statement with WITH RS will use commitment control and an isolation level read stability.

Note: closqlcsr=*endmod will hurt performance. It's usually used as a band-aid for poorly designed and/or outdated applications.

Upvotes: 1

Related Questions