DB2 Linked Server
-
I am trying to connect to db2 on mainframe 490 and later zos. I have successfully created the linked server and can query tables no problem using Microsoft's OLE DB ODBC driver.
I am trying to build a new connection that uses Microsoft OLE DB Provider for DB2. I created the provider string from Host Integration Client Sp1 and tested successfully. The linked server however does not.
The reason for trying to create the new linked server is neither linked server is able to do an insert into the db2 table.
Any help someone could lend to this would be sooooo helpful. I have little hair left as I have been fighting this for days!
-
Thought I'd add some info before it was asked. TESTING is the linked server using Microsoft OLE DB ODBC Driver
Here is the insert statement
INSERT INTO TESTING.DB2DSNY.MSQL.TEST_TABLE Values ('ABCDEFGH')
Table MSQL.TEST_TABLE is defined in DB2 as..
VALUE char 8 and INSERT_TIME timestamp ( yyyymmddhhmmss.nnnnnn)
and my account that is connected is sysadm on db2 side and sql
Error when executing SQL is
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'TESTING' does not contain table '"DB2DSNY"."MSQL"."TEST_TABLE"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='TESTING', TableName='"DB2DSNY"."MSQL"."TEST_TABLE"'].
When using the linked server that is connected to the Microsoft DB2 driver I receive the following...(This when trying to display the tables in EM)
Error 7330: Could not fetch a row from OLE DB provider 'UNKNOWN'. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IRowset::GetNextRows returned 0x80040e14].
-
can you run a trace on DB2 Side and see what is exactly being executed ? Below is a KB article which might not be related but does reference an issue with linked server and db2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;296685
-
Maybe i'm wrong but what happens if you modify your query (INSERT INTO TESTING.DB2DSNY.MSQL.TEST_TABLE Values ('ABCDEFGH')) and use the instruction OPENQUERY... ?
-
BOL says you can do an insert with openquery but doesn't go into how to..and haven't been able to dig up an example.
Running the following says incorrect syntax and if I use () instead of quotes around ABCDEFGH it says the provider has no columns...
SELECT * from OPENQUERY (TESTING,'UPDATE MSQL.TEST_TABLE SET VALUE ='ABCDEFGH'')
I'll be able to run a db2 trace when the db2 admin comes in this morning.
-
Just had this problem and was able to solve it.
The problem is the MS OLE DB driver for ODBC does not allow update insert or delete. If you switch to using the IBM OLD DB Driver for DB2 you can insert. Set up linked server with that driver, then use these values:
Product: IBMDADB2
DataSource: DB2 subsystem ID. e.g. the test Db2 is called DB2T, so the value is DB2T
ProviderString: rovider=IBMDADB2;database=DB2T;uid=racfid;pwd=racfpw
racfid is a RACF id
racfpw is a RACF password
-
I set up the linked server using the IBM driver as suggested and am able to do selects no problem...
Can you give an example of the insert to the linked server? As I still get am getting an invalid object name..has to be my insert syntax.
Thanks.
-
These work at my site. Linked server name is DB2TL
DELETE FROM DB2TL..WFMM.ACCOUNT;
INSERT INTO DB2TL..WFMM.ACCOUNT SELECT * FROM ACCOUNT_V4;
If they aren't working, make sure you are not using the OLE Provider for ODBC (I think you are OK there).
Check authority of the Userid on the table in DB2.
Lastly, run a dyn sql trace in DB2.
-
After ten minutes I had to cancel the insert. There is only one row in the source table. Here is where I am at...
insert into DSNYTCP..MSQL.TEST_TABLE SELECT * FROM tempdb.dbo.TEST_TABLE
This query runs > 10 minutes.
This one returns the following.
Insert Into OpenQuery(DSNYTCP, 'Select * From MSQL.TEST_TABLE') Values ('RECDEFGH', 20040623162001.303)
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'IBMDADB2'. The provider could not support an interface required for the UPDATE/DELETE/INSERT statements.
OLE DB error trace [OLE/DB Provider 'IBMDADB2' ICommandText::Execute returned 0x80040e21: Select * From MSQL.TEST_TABLE[PROPID=DBPROP_IRowsetChange VALUE=True STATUS=DBPROPSTATUS_NOTSETTABLE], [PROPID=DBPROP_UPDATABILITY VALUE=4 STATUS=DBPROPSTATUS_NOTSETTABLE]].
-
I got it!Using Microsoft's OLE for DB2 driver...I created my ole provider connection string and tested it before proceeding. The string I created with the Host Integration Client...but here it is in all its glory and 2 weeks in the making..talk about a lot of resource time.
Provider=DB2OLEDB;Password=mypassword;Persist Security Info=True;User ID=myid;Initial Catalog=DB2DSNY;Data Source=MSDSNYTCP;Network Transport Library=TCPIP;Network Address=MYMainframeIP;Network Port=4020;Package Collection=NULLID
From there I built a linked server to DB2.
Provider of Microsoft's OLE DB2
Product is the same
DataSource is blank
Provider String is above
Catalog is SYSIBM but I tried other things that weren't relevant and they worked as well.
My insert string with a variable is
declare @test-2 char (8)
set @test-2 = 'ABCDEFGH'
Insert Into OpenQuery(DEERE, 'Select * From MSQL.TEST_TABLE') Values (@TEST, '2004-06-22 11:22:33.000')
Results are viewed as
select * from DEERE.DB2DSNY.MSQL.TEST_TABLE
The problem I had was on the DB2 side a package called MSCS001 must have rights to be built. The workload manager manager needs RRS or something like that to run a Stored procedure SYSTABLES if that makes sense...DB2 stuff.
-
Hi all,
Still on the subject of creating db2 as a source for linked server, I have tried to create one with no success on a particular server. (works on other servers!). The source is a db2 for vm database. I added a link server with the provider string : provider=IBMDADB2;database=dbname;uid=uid;pwd=pw
The error I am getting is
IBMDADB2 reported an error : Access Denied
This is when I try and access the linked server
Any ideas?
Thanks
Lyn
-
1) Verify the user name and password in the provider string
2) Verify the user name and password on the security context tab..for testing set it to "be made using this context"
-
Hi,
I have verified that the database user and pw is correct as I am able to connect to the database using the same credentials using the db2 client.
I'm thinking that maybe because sql server service is not running as local system account that it gets the access denied error ??
A connection does not even make it to the db2 server for authentication. I think it's a problem with sqlserver trying to access the ddl for db2
Any ideas?
Thanks
Lyn
-
Best practice says not to run as local system account and should have nothing to do with it.
If you have the db2 connect client on the sql server you can create a linked server that simply has read access to the database. Create the odbc setting using the connect client and then call it using a new linked server using odbc sources. This will allow you to test what is being passed from sql.
I'd take it a step further though...if you have other servers running linked servers into db2..are you accessing the same db2 databases with the same accounts and passwords? if not try to access one that you have working elsewhere..this will shed light on where the issue is.
Probably one of the most frustrating things to accomplish...
-
One last thought be sure password is less than 8 Characters long..
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply