Microsoft OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 can be downloaded as a part of the SQL Server 2005 Feature Pack located at:
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en

Thanks - it will be good to hear if anyone has comments on their experience with this provider.

Donald

|||It worked great for me to receive data, but when it came to Inserting data or updating data, I could not get it to work. Using the statement Select col1, col2 from LinkedServer.DB2.X.Y works, but using Insert Into LinkedServer.DB2.X.Y values(col1Val, Col2Val) returned an error saying that it was unable to open the table. I checked permissions and it all looked good to me, so I cannot figure it out.|||

Did you use a component from dataflow task (if yes, which one - OLEDBDest, OLEDBCommand?), or you used ExecuteSQLTask to perform the insert/update?

Thanks

Wenyang

|||I used a straight SQL query, Insert Into table values (x, y, z).|||

Did you use SSIS at all? I understand you used a straignt SQL query, but I also want to know where you perform this query?

Thanks

Wenyang

|||

It should work. I have this sample here and you may try it out.

insert into MY_DB2_DATABASE.PERSON (SSN, AGE) values (11, '25')

Be sure that your connection works before you run/test your SQL task. You may start with creating a UDL file to DB2 first to establish a correct connection string. And make sure to use RUW (i.e. Units of Work=RUW). And then create a link-server to verify your connection string.

I doubted the Microsoft DB2OLEDB from feature pack supports distributed transaction. Do not enable transaction.

- Steve

|||I am performing the query using the query editor in the Database Engine inside of the Management Studio.|||

My connection string works when I read from the AS400, but not when I try to insert. My Connection String is below:

Provider=DB2OLEDB;User ID=*****;Password=*****;Initial Catalog=SQ2;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=192.168.212.71;Network Port=446;Package Collection=QGPL;Default Schema=QGPL;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=True;Persist Security Info=True;Data Source=SQ2;Connection Pooling=False;Derive Parameters=True;

The Exact select query that i use is:

Select Top 1 * from SQ4.SQ2.MRPLVOBJ.FOEINTPL

The result is:

IPLACE IPLDSC

JP001 JAPAN 001

The query that doesnt work is:

Insert Into SQ4.SQ2.MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

The result is:

sg 7306, Level 16, State 2, Line 1

Cannot open the table ""SQ2"."MRPLVOBJ"."FOEINTPL"" from OLE DB provider "DB2OLEDB" for linked server "SQ4". Unknown provider error.

|||

I see. then this is not a SSIS issue. You'll probably need to post your question to other SQLServer forums so as to expect a better/faster helps. This forum is for SSIS only.

thanks

wenyang

|||

Alright.

Let me post my connection string so you can compare with yours.

Provider=DB2OLEDB;Password=****;Persist Security Info=True;User

ID=*****;Initial Catalog=CORPI502;Network Transport Library=TCPIP;Host

CCSID=37;PC Code Page=1252;Network Address=CORPI502;Network Port=446;Package

Collection=STEVEDB;Default Schema=STEVEDB;DBMS Platform=DB2/AS400;Process

Binary as Character=False;Connection Pooling=False;Units of Work=RUW

And the insert statement is:

insert into STEVEDB.PERSON (SSN, AGE) values (15, '25').

And that works well.

Back to your insert statement, try this:

Insert Into MRPLVOBJ.FOEINTPL (IPLACE, IPLDSC) values ('JP002', 'Japan 002')

|||I used your connection string and changed my password, library, etc. Once again the select statement worked, but the Insert Statement did not. I cannot figure out how this is possible since I am using the same credentials as the third party driver which can do the insert statement. I will continue to work on it, I appreciate your help.|||

OK, let us back up a little bit and follow a step-by-step approach.

Please start to create a linked server first. Use the connection string (i posted above) to create a linked server. Then run the "Select" query and "insert..." to against your linked server to see if it works.

You may use 4-part query or open query to do that like (note that STEVEDB is the schema name whereas PERSON is the table name):

Select * from MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON

insert into MY_LINKED_SERVER.CORPI502.STEVEDB.PERSON (SSN, AGE) values (101, '23')

OR

SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON')

INSERT into OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM STEVEDB.PERSON') values (999, '999')

NOTE: DO NOT ENABLE and RUN distributed transaction sinnce DB2OLEDB does NOT support it on this feature pack.

Once you get the linked server WORK, then you can try your SSIS package.

One more reminder - you may have to journal your table on DB2/AS400 before you run "insert". Give it a try.

It had worked for me and I used it in my project.

- Steve (from Minnesota)

|||

I gave it another try, now I have the following error: The OLE DB provider "DB2OLEDB" for linked server "SQ4" could not INSERT INTO table "[DB2OLEDB]".

I have not done a thing with the distributed transaction options, the service is disabled in my services tab. As for journaling the table, should I have to do it since I could do the same query from my other OLE provider?

|||

Well, i guess that I have written as specific as possible in the above. The next would be a private hands-on demo for you if needed.

As another thought - I would encourage you to try to download an EVALUATION version of Host Integration Server 2004 to replace DB2OLEDB from the feature pack. Give it a shot to see if DB2OLEDB from HIS 2004 works out. You can find HIS 2004 from Microsoft's website.

- Steve

sql