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?
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.
closqlcsr=*endmod might or might not hurt performance. It might be best to specify it, but we can't tell from the OP yet. It's very often best not to specify it.