I have written a few blog posts recently related to DB2. They are listed below:
Connecting to a DB2 database using C#
A while back, I was experimenting with DB2 and trying to access DB2 using Powershell. Having Googled around, there weren’t any real examples and the ones I did find, were kind of out-dated and mentioned needing a bunch of other components installed just to get the data access providers I need. I never did figure out what all was required…
The script I initially used and experimented with is posted below; along with the error message. I later posted this problem on Stack Overflow and the error I was receiving, hoping to receive some guidance or insight into the problem. If you’re not familiar with Stack Overflow, it is for programmers and other computer professionals that write code to post questions related to problems they are having, various tools they may be using, software algorithms, or practical questions to problems unique to the programming profession. To date, my post has never answered. I’ll probably answer it myself and include a link back to this article. The Stack Overflow question is here.
The code I originally used was using the DB2OLEDB provider provider my Microsoft. When trying to fill the DataSet, I kept receiving an exception error. However, query results still worked.
Here is the code using DB2OLEDB provider:
$cn = new-object system.data.OleDb.OleDbConnection("Provider=DB2OLEDB;User ID=DB2ADMIN;Password=mypassword;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=localhost;Network Port=50000;Package Collection=;Default Schema=;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Defer Prepare=False;Persist Security Info=True;Connection Pooling=False;");
$ds = new-object "System.Data.DataSet" "dsEmployee"
$q = "SELECT EMPNO"
$q = $q + ",FIRSTNME"
$q = $q + ",LASTNAME"
$q = $q + " FROM DMOHORN.EMPLOYEE"
$da = new-object "System.Data.OleDb.OleDbDataAdapter" ($q, $cn)
$da.Fill($ds)
$dtPerson = new-object "System.Data.DataTable" "dtEmployee"
$dtPerson = $ds.Tables[0]
$dtPerson | FOREACH-OBJECT { " " + $_.EMPNO + ": " + $_.FIRSTNME + ", " + $_.LASTNAME }
Powershell output was the following:
For those that cannot read the exception, it says:
Exception calling “Fill” with “1″ argument(s): “The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open. SQLSTATE: 24501, SQLCODE: -501″
At line:12 char:9
+ $da.Fill <<<< ($ds)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Later I changed the script to use the IBMDADB2 provider provided by IBM. It is included as part of the DB2 Express installation. I’m not 100% sure, but I believe these drivers also get installed by installing the DB2 Client and Drivers downloadable package.
$cn = new-object system.data.OleDb.OleDbConnection("Provider=IBMDADB2;Database=SAMPLE;HostName=localhost;Protocol=TCPIP;Port=50000;Uid=DB2ADMIN;Pwd=mypassword;");
$ds = new-object "System.Data.DataSet" "dsEmployee"
$q = "SELECT EMPNO"
$q = $q + ",FIRSTNME"
$q = $q + ",LASTNAME"
$q = $q + " FROM DMOHORN.EMPLOYEE"
$da = new-object "System.Data.OleDb.OleDbDataAdapter" ($q, $cn)
$da.Fill($ds)
$dtPerson = new-object "System.Data.DataTable" "dtEmployee"
$dtPerson = $ds.Tables[0]
$dtPerson | FOREACH-OBJECT { " " + $_.EMPNO + ": " + $_.FIRSTNME + ", " + $_.LASTNAME }
Powershell output was the following:
I’m happy that I finally got this simple example working using Powershell. I’m still uncertain why the code is failing using Microsoft’s DB2OLEDB provider both using Powershell and C#. Maybe there is a bug with Microsoft’s driver using a DataSet? I never did try using a DataTable. I’ll save that little exercise to you, dear reader.
I’m still not a huge fan of DB2. I think it is ackward to use and its tooling needs a lot of improvements. Maybe I’m just too used to the quality of Microsoft’s SQL Server Management Studio. Even Enterprise Manager and Query Analyzer runs laps around IBM’s clunking Control Center and newer Data Studio administrative tools.
I have a few more upcoming DB2 code samples to provide soon using Powershell’s hosting feature which allows me to run my scripts to create nice tabular reporting. Stay tuned!
Hope you find this post useful.
Pingback: Using Powershell to access SQLCE 4.0 | myblog4fun