Hello,

I'm having a strange issue with a particular use case of the DB2 Informix (DRDA) connection.  Looking for suggestions on how to fix or work around.

Our code base is C# .Net and we moved to the DB2 driver to avoid some SQLI issues we were having post .Net 4.5 or thereabouts.  The application is calling a stored procedure to post some data in a transaction and then call the system command to print some reports based on the committed transaction data.

Here is the pseudo code.

C#

Create non-transacted connection

Call the Post stored procedure.

Dispose the connection.

----------------------------------------------------------------

Post Procedure

begin work

post data

commit work

system "print report"

----------------------------------------------------------------

The issue is that when begin work in the procedure is called I get a -535 "Already in transaction" error.  The DB2Connection in the application was created without a DB2Transaction so seems as though an implicit transaction was created "behind the scenes" or something like that.

Of note is that we were previously call a shell script to launch the stored procedure on the database host server.  The connection being created in that case was a SQLI connection.  Everything worked fine until we code to the C# application.

Appreciate any help that can be provided.  Also curious if there is a new SQLI (Informix Client SDK 4.10.TC12) driver for Windows that supports beyond .Net 4.5 in case that might be the solution.

Additional environment information.

Database version 14.10.FC6 (RHEL 7.1)
Windows 11 Client OS
IBM Data Server Driver Package 11.1.3031.68

Thank you,

Hi,

Run some quick test with the latest DSD driver against IDS and it worked (tx created inside the procedure) fine:

/*
C:\Program Files\ibm\IBM DATA SERVER DRIVER\bin\netf40>dsd

Unhandled Exception: IBM.Data.DB2.DB2Exception: ERROR [58005] [IBM][IDS/NT64] SQL0998N  Error occurred during transaction or heuristic processing.  Reason Code = "16". Subcode = "2-FFFFFFFF8004D026".
   at IBM.Data.DB2.DB2ConnPool.Open(DB2Connection connection, String& szConnectionString, DB2ConnSettings& ppSettings, Object& ppConn)
   at IBM.Data.DB2.DB2Connection.Open()
   at simple.Main(String[] args)

C:\Program Files\ibm\IBM DATA SERVER DRIVER\bin\netf40>
C:\Program Files\ibm\IBM DATA SERVER DRIVER\bin\netf40>csc dsd.cs /R:IBM.Data.DB2.dll
Microsoft (R) Visual C# Compiler version 3.4.1-beta4-20120-08 (fa984e02)
Copyright (C) Microsoft Corporation. All rights reserved.


C:\Program Files\ibm\IBM DATA SERVER DRIVER\bin\netf40>dsd
after p2():  commited


C:\Program Files\ibm\IBM DATA SERVER DRIVER\bin\netf40>

*/
using System;                
using IBM.Data.DB2;
using System.Data;
using System.Transactions;

class simple
      {
            static void Main(string[] args) 
            {
			DB2Connection conn;	
	        DB2Command cmd,cmd3;
			string connstring = "Database=stores7;Server=192.168.56.1:19493;Persist Security Info=True;uid=informix;pwd=ximrofni;";
	
         //   using (TransactionScope scope = new TransactionScope())
            {
                conn = new DB2Connection(connstring);
                conn.Open();
                cmd = conn.CreateCommand();
                // create table t1(c1 char(10));

                cmd.CommandText = "truncate table t1;";
                cmd.ExecuteNonQuery();
                
                // create procedure p2(); begin work; insert into t1 values("commited"); commit work; end procedure;
                cmd.CommandText = "execute procedure p2()";
                try {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e) {
                    Console.WriteLine(e.Message);
                }
            
          //      scope.Complete();
            }
             
            cmd3 = conn.CreateCommand();
            cmd3.CommandText = "SELECT first 1 * from t1;";;
            DB2DataReader drdr3 = cmd3.ExecuteReader();
            drdr3.Read();
            Console.WriteLine("after p2():  "+drdr3.GetString(0).Trim());
            
			Console.ReadLine(); 
			conn.Close();
          }
}

I can get the error if the connection is part of a transaction scope (look at the commented lines) as that start a transaction, but just having a connection (without a  BeginTransaction() ) it runs fine.

Regarding latest .NET Provider.  Latest CSDK for the 4.10 family is 4.10.xC12.  CSDK 4.50.FC11 will also work against 12.10 IDS engines, but there are no major changes between then two in terms of .NET Provider. Both should work with .NET SDK 4.5.