I am trying to run a stored procedure under a transaction initialized by the client over the iSeries ADO.NET provider.

Here is my client code:
iDB2Connection cn = new iDB2Connection("DataSource=..............");
cn.Open();
iDB2Transaction transaction = cn.BeginTransaction(IsolationLevel.Serializable);
iDB2Command cmd = new iDB2Command("MYLIB/DANITST", CommandType.StoredProcedure, cn, transaction);
cmd.DeriveParameters();
cmd.Parameters[0].Value = "DANITST";
int i = cmd.ExecuteNonQuery();
transaction.Rollback();
cn.Close();

and here is my SP:
CREATE PROCEDURE MFLLPGM.DANITST (IN myParam char(7)) LANGUAGE SQL
BEGIN
DECLARE year INT DEFAULT 0;
Insert into mylib.testFile (number) values (myParam) ;
RETURN year;
END;


It appears that the stored procedure completely disregards my transaction (and the isolation level) and is running as if it was under the *NONE (no commit) activation group . The inserted record is available immediately and the rollback call has no effect.
Do I need to set any options in the stored procedure to get it running under my transaction? Can you please point me in the correct direction

Cheers,
Dan

First thing is to confirm the culprit, the ADO.NET transaction or the stored proc. Run the stored proc through the DB2 CLP and see if you experience the same behavior. Make sure you turn auto-commit off. (update command options using c off). Looking at the stored proc, I'm thinking that the problem is in the ADO.NET transaction, not the stored proc.

hi,
any luck here.i am facing same issue. data is getting inserted into as400 table even after transaction rollback. Any help here is much appreciated.

How is the stored procedure created? Maybe you have an COMMIT ON RETURN YES (as DB2 z/OS supports it)?

Did you get the same results using DB2 CLP?

What version of ADO.NET are you using? I googled and saw that there are some significant differences between V1 and V2. I don't know much of anything about ADO.NET, but it looks like you might need to add the command to the transaction scope. Notice the 'sqlCommand.Transaction = sqlTransaction; ' statement that they used right after creating the command at the following link:
http://www.sql-server-performance.co...ctions_p1.aspx

You might try posting this in the dbforums > Microsoft.NET forum too. Based on the posted CREATE PROCEDURE, I don't think this is a DB issue.

Ok, here is what I have tried:
created the store proc

drop procedure MFLLPGM.DANITS2;
CREATE PROCEDURE MFLLPGM.DANITS2 (
IN branchcode VARCHAR(7) ,
IN surrogate CHAR(7)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN

INSERT INTO ...........;

END ;

and then created another stored proc that calls this sp on a transaction.

drop procedure MFLLPGM.DANITS5;
CREATE PROCEDURE MFLLPGM.DANITS5 (
IN branchcode VARCHAR(7) ,
IN surrogate CHAR(7)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
SET TRANSACTION ISOLATION LEVEL RR;
call MFLLPGM.DANITS2(branchcode, surrogate);
ROLLBACK;
END ;

and then called it

call MFLLPGM.DANITS5('0000', '000');

The rollback worked like a charm, so it obviously does the job. However, it only does it if I leave the SET TRANSACTION ISOLATION LEVEL RR in.
How can I check what the transaction isolation level is when I call it from my client code?
Is there anything I need to add to the connection string or to the stored proc so that it keeps the isolation level initialized in the client?
Surely this should work, otherwise it wouldn't be available in the iseries provider, and it is also stated in the iseries provider book. Please let me know what you think, any idea is appreciated.

Cheers,
Dan

this is ADO.NET 2 but it is using the iSeries provider so it's entirely up to the iSeries api. The code example in the first post ilustrates the call and it does include the transaction.

What exactly do you mean? How do you turn it off?

Cheers,
Dan

When you open DB2 CLP, enter the following command:
list command options

If the current setting for auto-commit is ON, use the following to turn it off:
update command options using c off

Quick question. Is this the only app you're having this problem with? I'm curious if you've developed other ADO.NET 2.0 apps with the iSeries provider calling stored procs where everything is working as expected.

SET TRANSACTION LEVEL seems to be specific to iSeries. I'm reviewing that to see if there's any gotcha's.

No, I have never done anything else with iSeries or AS400. This is the first one. Also, I am sure the issue is around the way I am using the iSeries provider, but it is quite elementary so it should be obvious. Sorry if I am asking stupid questions, but I have no AS400 knowledge at all.
I will try this on the CLP, thanks.

take a look at "DB2 CLI Stored Procedure Commit Behavior"

DB2 Information Center