Brief history:
I have been using Microsoft SQL Server since about 1995, when version 6.0 was the first version designed for Windows NT and was no longer a Sybase product. Over the years, SQL Server has grown into an enterprise-worthy RDMS. It’s tools are top-notch compared to other RDMS systems that rely on Java for its administrative tools.
I have slowly been forced coerced to using IBM’s DB2 over Microsoft SQL Server at my job. Not that DB2 is a better product. Far be it… It’s primarily due to company politics and the fact that our clients can install DB2 Express for free rather than having to pay for a SQL Server license. Yes, I know there is SQL Server Express – but that’s not the point…. Bummer for me.
Since this blog post is about DB2, I wanted to share with you my recent experience with trying to talk to DB2 using .NET and C#. I have scoured the web trying to find decent examples that would work for me. Something simple. Unfortunately, there doesn’t seem to many folks using DB2 that I can find – at least not the hobbyist. Maybe using DB2 is a bit of a black art where only the elite few that work in big businesses know how to use. Even IBM’s website seems to be outdated with its DB2 examples, only providing samples using Visual Basic 6.0 with some outdated from of DB2 that I don’t even remember or care about….
Similar to Microsoft SQL Server Express line, IBM has its own version of DB2 called DB2 Express. As of this writing, the latest version of DB2 is DB2 Express-C 9.7.5. It is available in both 32-bit and 64-bit form for Windows, Linux, and in beta for Mac OS X. The Mac? Are you serious? Whatever… You will need to create an account on IBM’s website to download, unfortunately.
In addition to downloading the core bits, you will likely want to download the “Data Studio Administration Client”. This is another free tool that kind of replaced the Administrator Center…
IBM’s Control Center:
IBM’s Data Studio:
You should also download the Visual Studio Add-on tools as well. I personally haven’t used these, but they should provide you with direct access to DB2 from within the Visual Studio IDE.
Installation of DB2 is as simple as running the installer and then agreeing to all the default prompts. It should install the SAMPLE database which is very small compared to Microsoft’s Northwind or AdventureWorks, by the way.
There appears to be two kinds of providers you can use with DB2. Either
- DB2OLEDB
- IBMDADB2
From my understanding, the DB2OLEDB is Microsoft’s .NET Provider. I was able to get this provider to work, but I kept getting binding errors every time I tried to fill a DataSet from the query. Switching to the IBMDADB2 provider seems to remedy all the other oddities I ran into.
An example connection string using both providers is below:
DB2OLEDB:
Provider=DB2OLEDB;User ID=your_username;Password=your_password;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=your_host_IP;Network Port=50000;Package Collection=<collection>;Default Schema=<schema>;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;
IBMDADB2
Provider=IBMDADB2;Database=SAMPLE;HostName=your_host_IP;Protocol=TCPIP;Port=50000;Uid=your_user;Pwd=your_password
For the DB2OLEDB provider above, I’m not 100% certain if you need all those parameters, but with my limited experience with DB2, it would seem to throw errors when I tried to remove other parameters… The ConnectionStrings.com here website seems to have almost everything imaginable for any data source (Access, RDMS, Azure, etc.) I encourage you to check them out.
OK, with all the introduction out the way, assuming you have downloaded DB2, installed the SAMPLE default database and can successfully access and log into DB2 using the Control Center, then the below code should work for you. Note that for the IBMDADB2 provider, you will need to add a using statement for IBM.Data.DB2 namespace and a reference to your project.
The code sample below will execute a simple query against the SAMPLE database to a DataTable. It will then export the rows from the DataTable to a .csv file within the C:\TEMP folder.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
using IBM.Data.DB2;
namespace DB2TestConsole
{
class Program
{
static void Main(string[] args)
{
string connStringLocal = "Provider=IBMDADB2;Database=SAMPLE;HostName=your_host_IP;Protocol=TCPIP;Port=50000;Uid=your_user;Pwd=your_password;";
OleDbConnection connLocal = new OleDbConnection(connStringLocal);
OleDbDataAdapter da_local = new OleDbDataAdapter(@"SELECT * FROM DMOHORN.EMPLOYEE", connStringLocal);
DataTable td = new DataTable();
try
{
da_local.Fill(td);
}
catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
}
// save to CSV
ExportDatasetToCsvFile(td, @"c:\temp\EMPLOYEE.csv");
}
static void ExportDatasetToCsvFile(DataTable table, string path)
{
StringBuilder str = new StringBuilder();
foreach (DataRow dr in table.Rows)
{
foreach (object field in dr.ItemArray)
{
str.Append(field.ToString() + ",");
}
str.Replace(",", Environment.NewLine, str.Length - 1, 1);
}
try
{
System.IO.File.WriteAllText(path, str.ToString(), Encoding.UTF8);
}
catch (Exception ex)
{
Console.WriteLine("Exception: {0}", ex.Message);
}
}
}
}
In the future, I plan to blog about other simple utility apps I’m working on requiring DB2 access for both automation, auditing, and change control.
Hope this helps!
Pingback: Using Powershell to access DB2 | myblog4fun