Pain with heterogenous services (DG4ODBC)

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 7 Jan 2013 17:33:00 +0000 (UTC)
Message-ID: <pan.2013.01.07.17.33.31_at_gmail.com>

The service is configured correctly but it cannot be used for anything except querying:

SQL> select * from dept_at_db2_sample;

    DEPTNO DNAME LOC
---------- -------------- -------------

	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

Elapsed: 00:00:00.29
SQL> SQL> insert into salgrade_at_db2_sample   2 select * from salgrade;
select * from salgrade

       *
ERROR at line 2:
ORA-02025: all tables in the SQL statement must be at the remote database

Elapsed: 00:00:00.09
SQL> _at_/tmp/1
declare
*
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db2.home.com)(PORT=1521)) (CONNECT_
DATA=(SERVICE_NAME=DB2_SAMPLE)))

ORA-02055: distributed update operation failed; rollback required
ORA-02063: preceding lines from DB2_SAMPLE
ORA-06512: at line 9

Process ID: 2973
Session ID: 54 Serial number: 189

Elapsed: 00:00:00.56

The script that I tried to execute looks like this: declare
type gradetab is table of salgrade%rowtype; grades gradetab;
begin
select * bulk collect into grades from salgrade; commit;
for c in grades.first..grades.last
loop
insert into scott.salgrade_at_db2_sample values(grades(c).grade,grades(c).losal,grades(c).hisal); end loop;
end;
/

I specifically committed after the bulk collect into, to start a new transaction and to make sure that the transaction is not distributed. It didn't work. I know that there is a specific gateway for DB2, but I cannot use it because of $$$. So, ODBC gateway isn't particularly useful. Bummer.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Mon Jan 07 2013 - 18:33:00 CET