How do indicate the SQL default library in an IBM iSeries 2 connection string to an AS/400?

Question

I'm connecting to an AS/400 stored procedure layer using the IBM iSeries Access for Windows package. This provides a .NET dll with classes similar to those in the System.Data namespace. As such we use their implementation of the connection class and provide it with a connection string.

Does anyone know how I can amend the connection string to indicate the default library it should use?

Answer 1

Are you using the Catalog Library List parameter? This is what my connection string typically looks like:

[add name="AS400ConnectionString" connectionString="Data Source=DEVL820;Initial Catalog=Q1A_DATABASE_SRVR;Persist Security Info=False;User ID=BLAH;Password=BLAHBLAH;Provider=IBMDASQL.DataSource.1;Catalog Library List="HTSUTST, HTEUSRJ, HTEDTA"" providerName="System.Data.OleDb" /]

Note: swapped the < and > characters for [ and ] so that it would show up in the post, but you probably already knew that 8^D

Answer 2

Wow! That connection string has a lot more parameters in it than ours, which is just

DataSource=10.99.61.16;UserId=IORDODBC;Password=IORDODBC

I will try using the suggested parameter and let you know how it goes. Thanks for the quick response.

Answer 3

Snippet from some Delphi source code using the Client Access Express Driver. Probably not exactly what you are looking for, but it may help others that stumble upon this post. The DBQ part is the default library, and the System part is the AS400/DB2 host name.

ConnectionString :=
  'Driver={Client Access ODBC Driver (32-bit)};' +
  'System=' + System + ';' +
  'DBQ=' + Lib + ';' +
  'TRANSLATE=1;' +
  'CMT=0;' +
  //'DESC=Client Access Express ODBC data source;' +
  'QAQQINILIB=;' +
  'PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;' +      
  'SORTTABLE=;' +
  'LANGUAGEID=ENU;' +
  'XLATEDLL=;' +
  'DFTPKGLIB=QGPL;';

Answer 4

If you are connecting through .Net:

Provider=IBMDA400;Data Source=as400.com;User Id=user;Password=password;Default Collection=yourLibrary;

Default Collection is the parameter that sets the library where your programs should start executing.

And if you are connecting through ODBC from windows (like setting up a driver in the control panel):

DRIVER=Client Access ODBC Driver(32-bit);SYSTEM=as400.com;EXTCOLINFO=1;UID=user;PWD=password;LibraryList=yourLibrary

In this case LibraryList is the parameter to set, remember this is for ODBC connection.

There are two drivers from IBM to connect to the AS400, the older one uses the above connection string, if you have the newest version of the client software from IBM called "System i Access for windows" then you should use this connection string:

DRIVER=iSeries Access ODBC Driver;SYSTEM=as400.com;EXTCOLINFO=1;UID=user;PWD=password;LibraryList=yourLibrary

The last is pretty much the same, only the DRIVER parameter value changes

If you are using this in a .Net application dont forget to add the providerName parameter to your XML tag and define the API used for connecting which would be OleDb in this case:

providerName="System.Data.OleDb"