Re: Oracle 12 <-> DB2
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 11 Jun 2017 15:21:41 -0400
On 06/08/2017 01:30 PM, rob oraclewizard.com wrote:
Does anyone know of a product that will give bi-directional
communications from Oracle 12.2 to DB2 on System-Z. Oracle is calling
a DB2 stored procedure that returns results. Right now we are using
Start Quest to do this, The shop is upgrading from Oracle 10g -> 12.2,
and can not get the Star Quest product to work. Thanks -Rob
There are freeware products like Tungsten or SymmetricDS that can
replicate a table or two, but are not very good for replicating the
entire schema.
https://www.symmetricds.org/
http://www.continuent.com/
The oldest product is Oracle DB2 gateway which makes DB2 database to
look like Oracle. You can create a database link to the DB2 database and
write triggers or PL/SQL procedures to replicate data from one side to
another. DB2 version 9 and newer support PL/SQL natively. Version 11.1
is very good at that, there is practically no difference. You can also
use newer Oracle features like this:
[mgogala@db2v11 scott]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.1.1.1
SQL authorization ID = MGOGALA
Local database alias = SAMPLE
[mgogala@db2v11 scott]$ db2 'select ename,job,deptno from scott.emp
order by sal fetch first 5 rows only'
ENAME JOB DEPTNO
---------- --------- ------
SMITH CLERK 20
JAMES CLERK 30
ADAMS CLERK 20
WARD SALESMAN 30
MARTIN SALESMAN 30
5 record(s) selected.
If you need some data to play with, you can download Oracle schema
SCOTT, in DB2 format:
http://mgogala.byethost5.com/scott.tar.bz2
Here is what you get:
[mgogala@db2v11 scott]$ db2 set schema scott
DB20000I The SQL command completed successfully.
[mgogala@db2v11 scott]$ db2 select table_name from user_tables
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
BONUS
SALGRADE
JOBHIST
EMP
DEPT
Note that I have used Oracle's "USER_TABLES" view to see the table names.
[mgogala@db2v11 scott]$ db2 describe table emp
Data type Column
Column name schema Data type name Length
Scale Nulls
------------------------------- --------- ------------------- ----------
----- ------
EMPNO SYSIBM INTEGER
4 0 No
ENAME SYSIBM VARCHAR
10 0 Yes
JOB SYSIBM VARCHAR
9 0 Yes
MGR SYSIBM INTEGER
4 0 Yes
HIREDATE SYSIBM DATE
4 0 Yes
SAL SYSIBM DECIMAL
6 2 Yes
COMM SYSIBM DECIMAL
6 2 Yes
DEPTNO SYSIBM SMALLINT
2 0 Yes
8 record(s) selected.
[mgogala@db2v11 scott]$
To load the export, I need to drop the schema first:
[mgogala@db2v11 scott]$ db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA (
'scott',null,'ERRORSCHEMA','ERRORTABLE')"
Value of output parameters
--------------------------
Parameter Name : ERRORTABSCHEMA
Parameter Value : ERRORSCHEMA
Parameter Name : ERRORTAB
Parameter Value : ERRORTABLE
Return Status = 0
[mgogala@db2v11 scott]$
After that, I can import it back:
[mgogala@db2v11 scott]$ clear;cd /media/sf_tmp/scott;db2move sample import
***** DB2MOVE *****
Action: IMPORT
Start time: Sun Jun 11 15:00:46 2017
Connecting to database SAMPLE ... successful! Server : DB2 Common
Server V11.1.1
* IMPORT: table "SCOTT "."BONUS"
-Rows read: 0
-Inserted: 0
-Rejected: 0
-Committed: 0
* IMPORT: table "SCOTT "."DEPT"
-Rows read: 4
-Inserted: 4
-Rejected: 0
-Committed: 4
* IMPORT: table "SCOTT "."EMP"
-Rows read: 14
-Inserted: 14
-Rejected: 0
-Committed: 14
* IMPORT: table "SCOTT "."JOBHIST"
-Rows read: 14
-Inserted: 14
-Rejected: 0
-Committed: 14
* IMPORT: table "SCOTT "."SALGRADE"
-Rows read: 5
-Inserted: 5
-Rejected: 0
-Committed: 5
Disconnecting from database ... successful!
End time: Sun Jun 11 15:00:48 2017
[mgogala@db2v11 scott]$
Voila, your traditional SCOTT/TIGER schema is ready for use .... in a
DB2 database. Here is the DB2 way of listing tables:
[mgogala@db2v11 scott]$ db2 list tables for schema scott
Table/View Schema Type Creation time
------------------------------- --------------- -----
--------------------------
BONUS SCOTT T 2017-06-11-15.00.47.170953
DEPT SCOTT T 2017-06-11-15.00.47.288815
EMP SCOTT T 2017-06-11-15.00.47.502082
JOBHIST SCOTT T 2017-06-11-14.38.04.549969
SALGRADE SCOTT T 2017-06-11-15.00.48.292221
5 record(s) selected.
DB2 is a very sophisticated and complex product. It can do many things,
including replication. DB2 replication product is called "Infosphere":
https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_11.4.0/com.ibm.swg.im.iis.repl.qrepl.doc/topics/iiyrqcncoratgtovu.html
Functionality is similar to Golden Gate, but I would compare the prices
first. GG will also work well with DB2.
DISCLAIMER:
I don't work for IBM, never have been working for them. Moreover, I
don't believe that IBM is doing a particularly good job with DB2 LUW
("Linux, Unix, Windows") marketing. This email was just a reply to an
inquiry.
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Other related posts:
