Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2007
    Posts
    50

    Question Unanswered: Run a stored procedure in a transaction

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  3. #3
    Join Date
    Sep 2007
    Posts
    1

    moggie

    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How is the stored procedure created? Maybe you have an COMMIT ON RETURN YES (as DB2 z/OS supports it)?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  6. #6
    Join Date
    Sep 2007
    Posts
    50
    Quote Originally Posted by jsharon1248
    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.
    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

  7. #7
    Join Date
    Sep 2007
    Posts
    50
    Quote Originally Posted by jsharon1248
    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.
    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.

  8. #8
    Join Date
    Sep 2007
    Posts
    50
    Quote Originally Posted by jsharon1248
    Make sure you turn auto-commit off.
    What exactly do you mean? How do you turn it off?

    Cheers,
    Dan

  9. #9
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  10. #10
    Join Date
    Sep 2007
    Posts
    50
    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.

  11. #11
    Join Date
    Apr 2009
    Posts
    1

    Lightbulb

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

    DB2 Information Center

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •