Product: | DBConnectivity Products for IBM Db2 for i (HiT ODBC/400, HiT OLEDB/400, Ritmo/i, HiT JDBC/400) |
Version: | All |
ID: | 1177 |
Summary: | Overview of using transactions from data connectors with IBM Db2 for i |
A transaction can be considered as a set of SQL statements, which must all be executed without errors. If any of these statements produces an error, all the other statements are not valid and the changes made on the database must be restored.
In order to use transactions, the Db2 server and the PC must be configured to use them.
IBM Db2 for i Configuration
The IBM i uses a Journal to keep track of events that are happening on the system, and write them to a receiver (a library file). The receiver contains all the operations made on the database, and is used to restore a previous state.
In order to use transaction with a table, the table must first be journalized.
PC Configuration
To use transactions, an Isolation Level must be specified in the connection parameters. The isolation level determines how the objects on the Db2 server are locked until the transaction is committed or rolled back. Locking data prevents other processes from accessing or modifying the same records that are under transaction.
Descriptions of the values supported by HiT data connectors:
0 - None - No commitment control. This is the default value. Every time a statement has been executed it is automatically committed, the changes cannot be rolled back, and the records are locked only for the time necessary to execute the operation.
1 - All - Records are locked until commit/rollback is performed. The other processes cannot access the entire table until commit/rollback.
2 - Change – The other processes cannot modify the block of data that is under transaction. The records can be read.
3 - Cursor - Records are locked while they are being fetched. The block of data under transaction cannot be accessed by any other process (read or modify).
4 - Repeatable Read – In the same work unit, if you read a record more than once without modifying it, you retrieve the same values for all fields (even if other users are modifying fields) until the work unit is closed.
Transactions with VB Applications
This is an example of how to use transactions with VB code. The connection parameters are stored in a file created with the HiT Toolbox.
Dim AdoCn As ADODB.Connection
Set AdoCn = New adodb.Connection
Set rS = New adodb.Recordset
strConn = "Provider=HiTOLEDB400;User ID=xxx;Password=xxx;Data Source=myconnection.udl;"
'Establish connection
AdoCn.Open strConn
'Setting RecordSet properties
rS.CursorLocation = adUseClient
SQL = "Select * from My_Table"
rS.Open SQL, AdoCn, adOpenKeyset, adLockOptimistic
AdoCn.BeginTrans -> START TRANSACTION FROM HERE
'Changing the value of the first field of the current record
rS(1)="new value"
'Update value
rS.Update
AdoCn.CommitTrans -> COMMIT TRANSACTION
AdoCn.BeginTrans -> START A NEW TRANSACTION FROM HERE
'Changing the value of the first field of the current record
rS(1)="wrong value"
'Update value
rS.Update
AdoCn.RollbackTrans -> ROLLBACK TRANSACTION: CHANGES ARE DISCARDED
'Close the connection
rS.Close
AdoCn.Close