Rollback on a DB2 table doesnt rollback the data - VB.NET app

[sreejit7]

Hi Guys,

We have a VB.NET 2005 app that uses IBM.Data.DB2.iSeries provider to connect to our DB2 server.

The connection string for the DB2 connection is set as -
"Data Source=<server>;User ID=<UID>;Password=<pwd>;ConnectionTimeout=120;"

And this is how we are updating a table in the DB2 server (using transactions) -

Dim cnDB2 as new iDB2Connection(<connString>)
Dim TransDB2 as iDB2Transaction = nothing
dim cmdDB2 as new iDB2Command
 
cnDB2.Open
TransDB2 = cnDB2.BeginTransaction
with cmdDB2
    <various command object properties including
     .Connection = cnDB2
     .Transaction = TransDB2 >
end with
 
for I as integer = 0 to 5
     <set cmdDB2 parameter values>
     cmdDB2.executeNonQuery
'if error occurred - do rollback
     if cmdDB2.ReturnCode = 100 then     
          TransDB2.Rollback
          return false
     end if
next I
 
'if no errors, commit the entire batch
TransDB2.Commit
return true
Exit Sub
 

What is happening is, when return code is 100, the Rollback line is executed. But when I check the data in DB2, the table has the new data. Why is this happening and how do I prevent this? It seems with SQL it is pretty straight-forward. But in DB2, I am reading reports of AutoCommit set to ON by default, etc. If I can set it to OFF, will that solve this issue? If so, how do I set it in the app?

Any help will be greatly appreciated. I have been stumped by this for the last 4 hours. Please help.

Thanks,
Sree