Microsoft OLE DB Provider for DB2

Microsoft OLE DB Provider for DB2

• Neo the 1

  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

  
  
  

• kcon

  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.
  

• Arshadje

  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

  
  
  

• eggie5

  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)

  
  
  

• MJackson

  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

  
  
  

• RAMPRAKASH

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

• Bojo

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

• SocratesBrighton

  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.
  

• Milo123

  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

  
  

• ijemiD

  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

  
  
  

• ColinLondon

  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.

  
  

• taogeh

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

  Donald

  
  
  

• Divya

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

  
  
  

• AjitGadge

  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

  
  
  
• Microsoft OLE DB Provider for DB2