Copy Tables From DB2 to Oracle – The Free Way

Part of a recent project I was working on involved the decommissioning of an old DB2 database on an IBM z/OS mainframe. As part of the decommissioning process, the business wanted to keep the data available for potential audit reporting. The Oracle Migration Workbench for DB2 sounded like the best option, but it turned out to not be supported on z/OS.

After several attempts at using SQL*Loader to move the 350 tables, a colleague suggested Oracle’s Generic Connectivity. After coordinating with several other groups, this is the process that finally worked:

1. Have a DB2 account created, so that the data can be queried.
2. Install the DB2 Connect client on the UNIX server on which the Oracle database resides.
3. Configure the DB2 Connect client.
   – The DB2 administrator and UNIX administrator coordinated on this, so
   I do not have the specifics.
4. Test the DB2 connection
   . /export/home/db2inst1/sqllib/cfg/db2profile
db2 connect to MYDB2DATABASE user <username>
db2 => select current time as DB2_TIME from sysibm.sysdummy1
db2 => terminate
5. Install the unixODBC package on the Oracle database server.
6. Configure the odbc.ini file (usually located in /usr/local/etc/odbc.ini).
   Example:
   [DB2DATABASE]
Description = DB2 Driver
Driver = /export/home/db2inst1/sqllib/lib/libdb2.so
7. Test the unixODBC connection.
   isql -v MYDB2DATABASE username password
SQL> select current time as DB2_TIME from sysibm.sysdummy1
SQL> quit
8. Create an initialization file for Oracle Generic Connectivity.
   Example:
   cd $ORACLE_HOME/hs/admin
vi initMYDB2DATABASE.ora
#
# HS init parameters
#
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYDB2DATABASE
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
 
#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini
 
#
# Environment variables required for the non-Oracle system
#
set DB2INSTANCE=db2inst1
9. Create a listener entry in the Oracle listener.ora.
   Example:
   (SID_DESC =
(ORACLE_HOME = /path/to/your/oracle/home)
(SID_NAME = MYDB2DATABASE)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/path/to/your/oracle/home/lib:/export/home/db2inst1/sqllib/lib:/u
sr/lib)
)
10. Ensure the listener connection timeout is unlimited in the listener.ora.
    Example:
    INBOUND_CONNECT_TIMEOUT_YOUR_LISTENER=0
11. Ensure the connection timeout is unlimited in the sqlnet.ora.
    Example:
    SQLNET.INBOUND_CONNECT_TIMEOUT = 0
12. Restart the database listener.
    lsnrctl stop listener_name; lsnrctl start listener_name
13. Add a tnsnames.ora entry for the HS listener.
    Example:
    MYDB2DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521>))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB2DATABASE)
)
(HS = OK)
)
14. Log into the Oracle database as a user that has the CREATE DATABASE LINK privilege.
15. Create a database link to the DB2 database.
    CREATE DATABASE LINK "MYDB2DATABASE" CONNECT TO "DB2_USERNAME" IDENTIFIED by "DB2_PASSWORD" USING 'MYDB2DATABASE';
16. Test the database link.
    select current time as DB2_TIME from sysibm.sysdummy1@MYDB2DATABASE;
17. Move as many tables as possible using:
    create table table_name as select * from db2_schema.db2_table_name@MYDB2DATABASE;
18. Some tables will fall out due to “ORA-00997: illegal use of LONG datatype”.
    Workaround:
    SET ARRAYSIZE 1000
SET COPYCOMMIT 1
COPY FROM username/password@ORACLE_SID TO username/password@ORACLE_SID -
CREATE table_name USING SELECT * from db2_schema.db2_table_name@MYDB2DATABASE;

Known Issues:

1. ORA-28511: lost RPC connection to heterogeneous remote agent using
   Solution: Set the connections to not timeout.
   listener.ora: INBOUND_CONNECT_TIMEOUT_YOUR_LISTENER=0
   sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=0
2. ORA-00997: illegal use of LONG datatype
   Solution: Use the SQL*Plus COPY command.
3. Error when running SQL*Plus COPY command.
   ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
   [Generic Connectivity Using ODBC]DRV_BlobRead: DB_ODBC_ENGINE (1489): ;
   [unixODBC][IBM][CLI Driver][DB2] SQL0805N Package
   “MYDB2DATABASE.NULLID.SYSLH203.5359534C564C3031” was not found. SQLSTATE=51002
   (SQL State: 51002; SQL Code: -805)
   Solution: This error is due to packages missing on the DB2 side. I had the DB2 database admin create the missing package.

4. ORA-01400: cannot insert NULL into (“oracle_schema”.”table_name”.”column_name”)
   Solution: Create an empty table and alter the column to accept NULL.
   COPY FROM username/password@ORACLE_SID TO username/password@ORACLE_SID -
CREATE table_name USING SELECT * from db2_schema.db2_table_name@MYDB2DATABASE WHERE 1=2;
ALTER TABLE table_name MODIFY column_name NULL;
COPY FROM username/password@ORACLE_SID TO username/password@ORACLE_SID -
APPEND table_name USING SELECT * from db2_schema.db2_table_name@MYDB2DATABASE;

5. Enable DB2 ODBC driver tracing.
   Solution: Edit the db2cli.ini file.
   [COMMON]
Trace=1
TraceFileName=/tmp/db2cli_trace.log

References:
Metalink Note:375624.1 – How to Configure Generic Connectivity (HSODBC) on Linux 32 bit using DB2Connect