DB2 --> 2005 Linked Server Mystery Solved

[TechWitch]

I've seen a number of posts from frustrated folks about this topic,
with not much offered as a solution. I actually managed to get this to
work today, so I thought I'd post for the benefit of the community.

My team went through a number of puzzling errors in getting this work,
like the CLI0150E (Driver not capable) error, and a variety of DB2OLEDB
errors. None which were really indicative of the problem. Here's how
I got a read/write connection to function between SQL 2005 and a DB2
UDB database on AIX (v8.2)

1) First I configured a UDL data source as described in this Microsoft
article: http://support.microsoft.com/kb/218590/ This created the
resulting provider string:
Here is the resulting provider string:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=DB2OLE DB;User ID=<masked>;Pas sword=<masked>; Initial
Catalog=<MYDBNA ME>;Network Transport Library=TCP;Hos t CCSID=37;PC Code
Page=1252;Netwo rk Address=myserve r.corp.somethin g.org;Network
Port=50000;Pack age Collection=<TAB SCHEMA>;Default
Schema=<TABSCHE MA>;Process Binary as Character=False ;Units of
Work=RUW;Defaul t Qualifier=<TABS CHEMA>;DBMS Platform=DB2/6000;Defer
Prepare=False;P ersist Security Info=True;Conne ction Pooling=True;De rive
Parameters=Fals e;

2) Then I created the linked server in SQL 2005:
/****** Object: LinkedServer [MYDB2LINKEDSERV ER] Script Date:
01/18/2007 15:10:00 ******/
EXEC master.dbo.sp_a ddlinkedserver @server = N'OLEDB2',
@srvproduct=N'M icrosoft OLE DB Provider for DB2',
@provider=N'DB2 OLEDB', @datasrc=N'<MYS ERVER>',
@provstr=N'Prov ider=DB2OLEDB;I nitial Catalog=<MYDBNA ME>;Network
Transport Library=TCPIP;H ost CCSID=37;PC Code Page=1252;Netwo rk
Address=<myserv er>.corp.nai.or g;Network Port=50000;Pack age
Collection=NULL ID;Process Binary as Character=False ;Units of
Work=RUW;DBMS Platform=DB2/6000;Defer Prepare=False;P ersist Security
Info=False;Conn ection Pooling=True;De rive Parameters=Fals e;',
@catalog=N'<MYD BNAME>'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'coll ation
compatible', @optvalue=N'fal se'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'data
access', @optvalue=N'tru e'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'dist ',
@optvalue=N'fal se'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'pub' ,
@optvalue=N'fal se'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'rpc' ,
@optvalue=N'tru e'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'rpc out',
@optvalue=N'tru e'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'sub' ,
@optvalue=N'fal se'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'conn ect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'coll ation
name', @optvalue=null
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'lazy
schema validation', @optvalue=N'fal se'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'quer y
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_s erveroption @server=N'OLEDB 2', @optname=N'use
remote collation', @optvalue=N'tru e'

3) Then, I had to manually change this option in SQL 2005 (There used
to be a button for it in SQL 2000, so you have to do it manually now)
exec master.dbo.sp_M Sset_oledb_prop 'DB2OLEDB','All owInProcess',1
*** IMPORTANT STEP. IT WON'T WORK WITHOUT IT!!**

4) Then I ran my SQL:

select * from MYDB2LINKEDSERV ER.MYDBNAME.TAB SCHEMA.MY_TABLE

insert into MYDB2LINKEDSERV ER.MYDBNAME.TAB SCHEMA.MY_TABLE
select MY_SQL_COL from MY_SQL_TABLE