C#: DB2 test available connection first

Question

I have a C# .NET program running an ETL which connects to a DB2 database. Sometimes this database is down, so I'd like to do a health check at the beginning of the application to see if the database is available, without actually calling any stored procedures or pushing any data. Here's an example of the code I'm using now:

OdbcConnection myODBCConnection = new OdbcConnection("DSN=DB2AA;UID=ABCD;PWD=1234;");
OdbcCommand myODBCCommand = new OdbcCommand();
myODBCCommand.CommandType = CommandType.StoredProcedure;
myODBCCommand.CommandText = "{CALL SYSPROC.ABC001(?, ?)}";
myODBCCommand.Parameters.Add("INPUT", OdbcType.VarChar, 500);
myODBCCommand.Parameters["INPUT"] = myString

myODBCCommand.Connection = myODBCConnection
myODBCConnection.Open();

OdbcTransaction myTrans;
myTrans = myODBCConnection.BeginTransaction();
myODBCCommand.Transaction = myTrans;
myTrans.Commit();
myODBCCommand.ExecuteNonQuery();
myODBCConnection.Close();

What's the best way to test this connection without actually pushing any data?

Answer 1

You can simply run some innoccuous select query to check to see if the db is available.

You can try to do something as simple as:

Select 1

Or

Select getdate()

Those simple queries don't even touch any tables but will return only if the rdbms is running.

Note: those examples are for sql server but might work for db2. I haven't had to do a live check on a db2 yet though the similar concept should be doable.

Note 2: after a closer look at your code, all you should really have/need to do is check for success of your odbc connection's .Open() call.