DB2Connection Object Looping Open then Close Memory Exceptions

Question

I am using IBM.Data.DB2.DB2DataAdapter object to make multiple connections to different databases on different servers.
My basic loop and connection structure looks like this:

foreach (MyDBObject db in allDBs)
{
    //Database Call here for current DB...//Get SQL, then pass it to DB call
    QueryCurrentDB(command);
}

Then...

DB2Connection _connection;    

Public DataTable QueryCurrentDB(DB2Command command)
{

    _connection = new DB2Connection();
    DB2DataAdapter adapter = new DB2DataAdapter();

    _connection.ConnectionString = string.Format("Server={0};Database={1};UID={2};PWD={3};", _currentDB.DBServer, _currentDB.DBName, _currentDB.UserCode, _currentDB.Password);
    command.CommandTimeout = 20;
    command.Connection = _connection;
    adapter.SelectCommand = command;                
    _connection.Open();

    adapter.Fill(dataTable);

    _connection.Close();
    _connection.Dispose();

    return dataTable;
}

If I have around 20 or so databases on different servers I end up eventually getting this exception. I cannot control the memory allocation for each db instance either.

ERROR [57019] [IBM] SQL1084C The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached. SQLSTATE=57019

The only way I have been able to get around this is to put a thread sleep before each db call, such as:

System.Threading.Thread.Sleep(3000);

I hate this, any suggestions would be appreciated.

Answer 1

In the code posted, the Connection, Command and DataAdapter are all IDisposable indicating they need to be disposed to free allocated resources. But only the DBConnection object is actually disposed. Particularly in a loop such as you have, it is important to dispose of those to prevent leaks.

I dont have the DB2 providers, but they all work pretty much the same, especially in this regard. I'd start by refactoring the code starting with MyDBObject. Rather than just holding onto connection string params, have it create the connection(s) for you:

class MyDBObject 
{ 
    private const string fmt = "Server={0};Database={1};UID={2};PWD={3};";
    ...
    public DB2Connection GetConnection()
    {
        return new DB2Connection(string.Format(fmt,
                    DBServer,DBName,UserCode,Password));
    }
}

Then the loop method:

// this also could be a method in MyDbObject
public DataTable QueryCurrentDB(string SQL)
{
    DataTable dt = new DataTable();
    using (DB2Connection dbcon = currentDB.GetConnection())
    using (DB2Command cmd = new DB2Command(SQL, dbcon))
    {
        cmd.CommandTimeout = 20;
        dbcon.Open();
        dt.Load(cmd.ExecuteReader());
    }
    return dt;
}

• Most importantly, note that the IDisposable objects are all enclosed in a using block. This will dispose (and close) the target and release any resources allocated.
• You dont need a DataAdapter to fill a table. Omitting it means one less IDisposable thing created.
• Rather than passing in the command, pass in the SQL. This allows you to also create, use and dispose of the DBCommand object.
• If there is a chance of 2 tables in the same DB getting polled, I'd refactor further to make it possible to fill both tables on the same connection.

Before: 2 out of 3 objects were not being disposed (per iteration!)
After: 2 out of 2 objects are disposed.

I suspect the culprit was the DBCommand object (similar to this question), but it could be a combination of them.

Putting the thread to sleep (probably) works because it gives GC a chance to catch up on cleanup. You are probably not out of the woods yet. The link above was running into problems at 400 iterations; 20 or even 40 (20*2 objects) seems like a very small number to exhaust resources.

So, I suspect other parts of the code are also failing to dispose properly and that loop is just the straw which breaks the camel's back. Look for other loops and DB objects being used and be sure to dispose of them. Basically, anything which has a Dispose() method ought to be used in a using block.