CODEWITHSUNDEEP
transactionsdb2ibm-midrange
dc-deal
dc-deal

Reputation: 362

IBM db2, transactions in SQL-Scripts

I am searching for an option to operate with transactions in the IBM SQL-Scripts program. For comparison purposes, i added an image on what SQL-Scripts-Program i am talking about.

enter image description here

Further, I also want to achieve those transactions programmatically with RPG(free). How could this look like?

Upvotes: 0

Views: 1482

Answers (2)

Charles
Charles

Reputation: 23823

You mention both the Run SQL Scripts utility, and RPG.

You've got basically two choices in either place.

  1. Change the default isolation level
  2. Add the with clause to individual statements to override the default.

For Run SQL Scripts, the default is configured in the JDBC connection. enter image description here

For SQLRPGLE, the default can be set

  • During compile, via the COMMIT() parameter of the CRTSQLRPGI command
  • by using the SET OPION SQL statement in your source

SET OPTION is preferred, unless you have a CMS that can ensure the CRTSQLRPGI command is always issued properly.

exec SQL
 set option commit = *CHG;

Note that the SET OPTION statement is a compile time, not run-time statement. It must be the physically first SQL statement in your SQLRPGLE module. You can only have one and it is in effect for the entire module.

Regardless of the default isolation/commitment level in effect. You can set the level for a given statement using the WITH clause.

update mytable
  set mycolumn = 1
  with chg;

Lastly, note that in order to use commitment control, your table will have to be journaled.

Upvotes: 2

Paul Vernon
Paul Vernon

Reputation: 3901

See the compound (dynamic) statement

Note that you will need to use a statement terminator other than ; in your SQL Editor. @ is a commonly used one.
Also if you use IBM Data Studio you will get local syntax parsing which will help you spot any syntax errors in your code.

Upvotes: 1

Related Questions