Connection String Stinger - Phantom Library List

.Net can look for your tables in a library the same name as your user id  even if you supply a library list. This can cause your SQL 'selects' to fail.
Take a look:
Here's a basic connection string using the IBM supplied iDB2 Classes:
DataSource=192.168.0.1; UserID=colm;Password=xxxx;LibraryList= CusLib,ARlib, QGPL

At first glance everything looks good, right?
So now I want to get at my table MYCUST in CusLib. Should be a no brainer. However if I run the SQL command 'Select * from MYCUST' guess what I get?  - A big fat error like this:
SQL0204 - File MYCSUT in COLM not found
What??. CusLib is on the TOP - I don't even mention library Colm.  What's going on?
IBM says that if you are using the SQL naming convention that it sets the default collection or library to the library with the same name as the user id. I have no idea why they do this! Anyway if you have this issue the simple fix is to specify the default collection. i.e. defaultcollection=cuslib
Here's the new connection string
DataSource=192.168.0.1; UserID=colm;Password=xxxx;LibraryList= CusLib,ARlib, QGPL;DefaultCollection=CUSLIB

How to video: Access data on iSeries using ADO.NET

Here is a super simplified video example of a C# application reading from a table on an iSeries. It uses the IBM .net managed provider (See Different ways to access iSeries data and this) . You have this  if you have IBM Client Access. If not, download the technology preview here. Sound problem? jiggle the player control if the sound goes out (I'm trying out demo utility software let me know what one you use.)
.

To view the video click here.

To download the project used in the video, click here.

Subscribe to DotNetFluke to receive weekly useful tips for integrating .NET with the iSeries.
If you have any iSeries/.NET integrations questions or suggestions for articles email me at cbyrne+blog@AranRock.com

LINQ to DB2 Beta available tomorrow

 

As you probably know the geeks at IBM have been scrambling to put together a LINQ to DB2 Entity framework ever since LINQ was announced - and by jove they've done it! 
You know what LINQ is right? It allows you to query data in C# and refer to columns in tables  by their column names directly just as you would any field in your C# program. Yes I know, this seems like an uber basic requirement for any language but at least it is done.
Initially LINQ had only SQL, XML and in-memory fields were supported but IBM, Oracle and MySQL quickly started getting in on the act to support their dbs.
We've learned here at Dot Net Fluke that IBM will announce tomorrow (May 6th) a beta of the LINQ to DB2 beta. We'll post the link when it comes available.
The bad news is that it is not yet available for the iSeries. So we'll all just have to sit and wait!

Update: Here's the announcement IBM Announces LINQ to DB2 connector

Different ways to access iSeries data

Remember, you have 4 main methods to access DB2 for iSeries from .NET programs:

1. The ADO.NET managed provider for iSeries Access for Windows, IBM.Data.DB2.iSeries. This is probably your best bet for iSeries data connectivity. Get info on it here
   and....
2. ODBC: The Microsoft-supplied ODBC bridge provider using the iSeries Access forWindows ODBC driver for underlying database connectivity. Microsoft.Data.Odbc
3. OLE DB: The Microsoft-supplied OLEDB bridge provider using one of the iSeries Accessfor Windows OLE DB providers for underlying database connectivity. System.Data.OleDb This is an unmanaged method.
4. The DB2 for Linux, UNIX, and Windows (LUW) managed provider implemented by IBM software group.

Managed vs. Unmanaged

The managed provider is superior in terms of controlling actions to the DB from within your application. Managed means managed by .net - the drivers to access the iSeries are compiled into a .Net assembly. Unmanaged means that it is outside the .Net assembly - an .exe for example.
When you use OLE DB you are using an unmanaged provider so there is an extra step that has to occur from .net to the iSeries such as a bridge between the two. You should experience better performance when using the .Net managed provider.

Here's an overview of the IBM .net managed provider (from IBM)

I highly recommend reading the IBM redbook ' Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET' from which the above is summarized. 'You can download it at http://www.redbooks.ibm.com/abstracts/sg246440.html