Set default library for Microsoft OLEDB Provider for DB2 connection to AS400

• nmccormick

  Say Hey Kid

  Points: 700

  More actions

  January 26, 2012 at 10:26 am

  #254244

  We use SQL Server 2008 R2. In August 2011 per Microsoft recommendations we switched to the Microsoft OLEDB Provider for DB2 provider (DB2OLEDB) version 8.0.4294.0 for connections to AS400 DB2 to resolve some connectivity and performance issues. As part of the switch we had to qualify all the DB2 table references with the library name. For example we changed select * from tablename; to select * from libraryname.tablename;. The switch was successful and we've had no repeat of the issues since then.

  Now we need to duplicate our processing on another SQL Server 2008 R2 server. In the duplicate processing the AS400 DB2 connection will be pointed to the same AS400/DB2 server but a different library. We would like to be able to set a default library in the DB2OLEDB connection and remove the qualifier from the table name in the SQL. However I haven't been able to make this work. I'm not sure I'm setting the parameters in the connection string correctly but haven't yet found definitions of what each parameter means.

  Have any of you been able to make the DB2OLEDB provider work with default library? If so, can you share your solution with me? If not, can someone point me to documentation of the meaning of each parameter in the DB2OLEDB connection string? It may be that I'm just not setting them correctly.

  Thanks,

  Nancy

  Update: The official Microsoft version of the DB2OLEDB provider is 3.0