1

I am using .Net framework 4.5 and Microsoft OleDb Provider For DB2 v6 to connect to db2 and call a stored procedure and read data into data table.

Here is summary of my code which is working fine!

    public DataTable SendQuery(OleDbCommand command)
    {
        DataTable table = null;

        lock (lockObj)
        {
            if (connection == null || connection?.State != ConnectionState.Open)
            {
                connection?.Close();

                try
                {
                    connection = new OleDbConnection();
                    connection.ConnectionString = "Provider=DB2OLEDB.1;Password=MyPass;Persist Security Info=True;User ID=MyUser;Initial Catalog=DSNP;Data Source=DSNP;Network Address=5.5.5.5;Network Port=555;Default Schema=MySchema";

                    connection.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            for (int i = 1; i <= _maxRetry; i++)
            {
                try
                {
                    table = null;
                    command.Connection = connection;

                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    table = new DataTable("ResultTable");

                    adapter.Fill(table);
                    break;
                }
                catch (OleDbException ex)
                {
                    connection?.Close();
                    Connect();

                    if (i == _maxRetry) throw ex;
                }
                catch (Exception ex)
                {
                    throw  ex;
                }
            }
        }
        return (table);
    }

This code works fine but some few times with unknown reason I get this error specially when my service calls from another system in network. And here is my stack trace:

OleDbException: [StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ReportDataAccess.DB2Connection.SendQuery(OleDbCommand command),HResult:-2147217900,other:{"oledbErrors":[{"Message":"A network library error has occured (6): The handle is invalid.\r\n","NativeError":6,"Source":"Microsoft DB2 OLE DB Provider","SQLState":"HY000"}],"ClassName":"System.Data.OleDb.OleDbException","Message":"A network library error has occured (6): The handle is invalid.","Data":null,"InnerException":null,"HelpURL":null,"StackTraceString":" at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)\r\n at ReportDataAccess.DB2Connection.SendQuery(OleDbCommand command)","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":"8\nExecuteCommandTextErrorHandling\nSystem.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Data.OleDb.OleDbCommand\nVoid ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)","HResult":-2147217900,"Source":"Microsoft DB2 OLE DB Provider","WatsonBuckets":null}]|

I tried to reconnect or retry filling data table to solve my problem and some times this solution works but sometimes not! note: this is my first question so if you need more data please comment.

regards

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aida Shenava
  • 21
  • 2
  • odbc tracing may help here, to see what is happening under the covers. If you either have a reproducible scenario, or if you can get control when a retry fails (to allow you do dynamically enable odbc tracing) then tracing may help. If there are any additional sources of diagnostics (from Microsoft tooling, or from Db2-server diagnostics) around the time of the exceptions then they may also give clues. Your is a problem determination question, it is not about programming. – mao Jun 09 '21 at 15:50
  • `OleDbCommand`, `DataTable` and `DataAdapter` are `IDisposable`. You say the code works fine, but how often does it execute a retry? Because you say remote systems suffer worse, I would hazard a guess that the connection gets broken and code quickly "thrashes with a few failed retries" before exiting. `Dispose()` may be a red herring but is always good practice. I would move `if (i == _maxRetry) throw ex;` to the top of the `catch (OleDbException ex)` block and introduce a `Thread.Sleep(1000);//play with values in 500-5000` to give things a chance to recover before trying again. – AlanK Jun 18 '21 at 05:39

0 Answers0