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

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 don't 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"

Answer 2

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;';