Wednesday, January 26, 2011

A Note About IBM's DB2 .NET Data Provider

A lot of people like to use DB2 (on whatever platform -- mainframe, Linux/UNIX/Windows, IBM i) as a data server for .NET applications running on Windows servers. IBM facilitates this architecture with the DB2 .NET Data Provider, which extends DB2 support for the ADO.NET interface. The DB2 .NET Data Provider is included with several of IBM's data server client and driver offerings (more on this momentarily). A DB2 for z/OS DBA friend of mine recently asked, "How can I tell if the DB2 .NET Data Provider is 'there' on an application server?" As it turns out, there's an app for that, and I'll tell you about it in this post (many thanks to Brent Gross, a senior member of IBM's DB2 development organization, who told ME about this).

The app to which I refer is called testconn. It's a .NET application that ships with all IBM client packages. testconn will actually drive a DB2 connection though the .NET layer. There are versions of testconn for each .NET Framework: 1.1, 2.0, and 4.0. To run testconn for a database (e.g., the sample database) that is local to the app server, you'd type the following (if you were looking to verify that the IBM DB2 Data Provider for .NET Framework 2.0 is installed on the server):

testconn20 database=sample


What you specify after testconn20 (or testconn40 or whatever) is a .NET connection string. The testconn tool will use that string to connect to the target database. If all you want to do is check to see if the driver is there, you can use any name for the database. If it's not a valid database name, testconn will report an error indicating that it cannot connect to the database, but it will first print out all of the driver information. Here is an example of a testconn execution for a remote database:

testconn20 database=robdb;server=myserver.com:50000;userid=robert;password=freddy


Here is some sample output from an execution of testconn:

E:\>testconn20 database=sample

Step 1: Printing version info
        .NET Framework version: 2.0.50727.3615
        DB2 .NET provider version: 9.0.0.2
        DB2 .NET file version: 9.7.3.2
        Capability bits: ALLDEFINED
        Build: 20100823
        Factory for invariant name IBM.Data.DB2 verified
        Factory for invariant name IBM.Data.Informix verified
        IDS.NET from DbFactory is Common IDS.NET
        VSAI assembly version: 9.1.0.0
        VSAI file version: 9.7.0.489
        Elapsed: 0.5


Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file
        Elapsed: 0.015625

Step 3: Connecting using "database=sample"
        Server type and version: DB2/NT 09.07.0003
        Elapsed: 1.890625

Step 4: Selecting rows from SYSIBM.SYSTABLES to validate existance of packages
   SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
        Elapsed: 0.21875

Step 5: Calling GetSchema for tables to validate existance of schema functions
        Elapsed: 0.40625


Test passed.


And there you have it. So, if in doubt: testconn.

Now, I mentioned that the DB2 .NET Data Provider is included with a number of IBM's data server clients and drivers. You can get more information about these offerings at this url:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.install.doc/doc/c0023452.html

I'll tell you that the preferred client package for you is likely to be the IBM Data Server Driver Package (also known as the ds driver). It's lightweight and easy to distribute.

Got DB2? Got .NET apps? They go great together. Check it out, if you haven't already.

5 comments:

  1. Hey Robert

    Thanks for the post. I have a client asking me about connecting their .NET stuff to DB2 without using Microsoft Host Integration Services (which is like their DB2 Connect I'm led to believe). So can they use IBM's DB2 .NET Data Provider to connect to DB2 for z/OS data, without requiring a DB2 Connect license (or a MS HIS license)?

    Thanks,
    Craig Mullins
    Mullins Consulting, Inc.
    craig@craigsmullins.com

    ReplyDelete
    Replies
    1. Hello, Craig.

      We do recommend use of the IBM Data Server Driver Package for access to DB2 for z/OS data from a Windows application server. The Data Server Driver Package includes .NET drivers that enable direct connection from a Windows application server to a DB2 for z/OS system, with no DB2 Connect "gateway" server in-between.

      Now, you do need a DB2 Connect license to use the IBM Data Server Driver package, because that's how we handle licensing for this software: instead of having separate licensing procedures for DB2 Connect and our Data Server Drivers, we have DB2 Connect licensing, and if you're licensed for DB2 Connect then you can use the IBM Data Server Driver (and in fact we recommend use of the Data Server Driver versus the DB2 Connect client, largely because it's a lighter-weight client and is easier to maintain and upgrade versus the DB2 Connect client). More information about the IBM Data Server Driver Package for Windows can be found in the IBM Knowledge Center, at http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.install.doc/doc/c0054554.html.

      Note that there are several different DB2 Connect licensing options, generally based on how widely an organization wants to deploy the DB2 client software (at one end there are "unlimited" editions, and at the other end there are "workgroup" editions). IBM software sales representatives have the details about these options and can help an organization make the choice that best matches its requirements.

      Hope this helps.

      Robert

      Delete
    2. Perfect. Thanks for the response.

      Delete
  2. For a client machine that already has a set of working DB2 drivers and connections, what all do I need to deploy to make this work? I'm in the middle of pulling out all my hair trying to get it working on a co-worker's machine.

    He has a valid ODBC set up and is using AQT just like me to get to the same database. We can also see his ODBC connection, alias, and driver are set up in the ODBC configuration screens.

    Is it more than the couple of IBM.DATA.*.DLL files?

    ReplyDelete
    Replies
    1. I'm not going to be able to help you with this situation, Orion. If you've consulted the relevant IBM documentation for the .NET data provider (available online at http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.adonet.doc/doc/c0010960.html?lang=en) and you have not been able to get it to work as needed, your best source of help would be the IBM Support Center.

      Robert

      Delete