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

Where to get the iSeries ADO.NET data provider

The iSeries .NET data provider is the native data provider that allows you to access iSeries data and programs using .NET in a managed way.

This is a .dll (IBM.Data.DB2.iSeries.dll ) that you add in as a reference when creating .Net apps to connect to the iSeries

Once added in you have access to all the classes and methods that simplify writing applications in .Net to leverage iSeries data in the db2 database

The dll is part of iSerices Access for windows which you can download for free (when you select the technology preview). Make sure you check 'Selective Setup' and include the Data and Programmer's toolkit so you get all the data access drivers, help and tools.

1. Download iSeries Access from http://www-03.ibm.com/systems/i/software/access/windows/downloads.html
   Select the technology preview version. You'll have do the register rigmarole.
   If the link doesn't work, google 'iSeries Access windows' to find the latest location.
   


2. Install iSeries Access making sure that you select 'custom install' to include the programmers toolkit (it comes in handy as it has examples etc.) . Once you have installed iSeries Access you will find the dll in C:\Program Files\IBM\Client Access\IBM.Data.DB2.iSeries.dll
   


3. In Visual Studio, open up your project. Click on the Solution Explorer, then references. Right click references and select 'Add Reference'. A dialog box appears. Click 'Browse'. Add in the dll from the location above.
   


4. Add the namespace '
   using IBM.Data.DB2.iSeries;' to your code


5. Start using the iSeries classes! The iDB2Command is what you use to run queries, IDB2Connection to create a connection string to your iSeries db, iDB2DataAdapter gives you a cache to both connect, retrieve and update a set of data.

With the native .NET provider you have support for:

• SQL (INSERT, UPDATE, DELETE, SELECT)
• Stored procedure support
• Commitment control
• Connection pooling
• SQL naming,Unicode, Threads and multiple databases (IASPs)

To call iSeries programs use a stored procedure.