***SOLVED***

The problem is that there seems to be a bug in the IBM 64bit ODBC driver. We have ODBC connections working properly to this AS/400 database for other applications (MS Access) so I installed Access on this server and setup a 32bit ODBC connection. It worked perfectly. The same ODBC connection params on the 64bit driver does not work. No matter what combination of params I tweaked on the 64bit driver, it does not work. And since SQL is 64bit, I cannot use the 32bit driver.

IBM has a utility called "Data Access Tool" which is basically a wizard to setup an OLEDB provider. I used this tool to create the provider, which also gives me a text provider string which I used to create my linked server with. It works fine now.

Provider=DB2OLEDB;User ID=xxxxxx;Password=xxxxxx;Initial Catalog=xxxxxx;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1208;Network Address=xxxxxx;Network Port=446;Package Collection=xxxxxx;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=True;Mode=Read;Connection Pooling=False;Derive Parameters=False;

(do not need "Provider=DBOLEDB;" in provider string for linked server definition)

EXEC master.dbo.sp_addlinkedserver @server = N'BPCS_OLEDB', @srvproduct=N'BPCS', @provider=N'DB2OLEDB', @datasrc=N'BPCS_OLEDB',

@provstr=N'User ID=xxxxxx;Password=xxxxxx;Initial Catalog=xxxxxx;Network Transport Library=TCP;Host CCSID=37;PC Code Page=850;Network Address=xxxxxx;Network Port=446;Package Collection=xxxxxx;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=True;Mode=Read;Connection Pooling=False;Derive Parameters=False;Force Translate=0;'

GO