Performance of loading data from DB2 to Oracle

Question:

The question is complex and ambiguous, but I hope for any advice (or maybe my experience will help someone):

  1. There is a server with Oracle 11.2.0.3 x32 on Windows Server 2008R2 x64 (4 cores / 8 threads, 8GB of RAM, one HDD without raid).
  2. ODBCx32 sources configured to connect to DB2 via CLI DRIVER named db2
  3. The target DB2 server is on a mainframe that I don't know the settings for, version 9fix15

  4. An additional listener is raised:

     SID_LIST_LISTENERdb2 = (SID_LIST = (SID_DESC= (SID_NAME=db2) (ORACLE_HOME=C:\app\product\11.2.0\dbhome_1) (PROGRAM=dg4odbc)) ) LISTENERdb2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523)) ) )

    In tnsnames.ora:

     db2 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523)) (CONNECT_DATA=(SID=db2)) (HS=OK) )

  5. Public dblink created:

     CREATE PUBLIC DATABASE LINK db2 CONNECT TO "login" IDENTIFIED BY "pwd" using 'db2';`

  6. Procedure created:

     CREATE OR REPLACE PROCEDURE P_LOAD_DATA IS BEGIN insert into data_table select * from data_table@db2 d where d.date_op between TRUNC(SYSDATE - 4/24, 'HH24') + 1/24/60 AND TRUNC(SYSDATE - 2/24, 'HH24')); END;/

  7. A job has been created to start the procedure every 2 hours (or 12 jobs for each start).

The target table is about 13 million records in 50 columns, my table collects an archive from the target and has a size of about 150 million records of the same 50 columns.

Transferring ~ 600k records (150-200MB) takes an hour at best, periodically it takes 4-5 hours. Plus, it would be nice to keep indexes on the archive table so that you can then pull something out of it in a reasonable time, but this is optional. Is the method of filling the archive chosen correct or are there better alternatives? The problem is that periodically jobs are interrupted with the reason "Job slave process was terminated", and in the logs

ORA-00603: ORACLE server session terminated by fatal error
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003 {08003,NativeErr = -99999}
ORA-02063: preceding 2 line

It is likely that the mainframe timed out the connection. The reason for Orakla's bitness is that none of the found version of the DB2 driver worked on x64.

Answer:

Well, obviously, it's worth starting to poke around with an analysis of where the hour and 4 hours go – i.e. turn on tracing for the job session and analyze the resulting trace. Further, in order to work out the version that slows down DB2 on the mainframe, I would simply select the required range, but without writing to the table – i.e., as they say in the unix world, to / dev / null. But at the same time, for IBM to give all the records and they go through the network and db-link – i.e. just SELECT, but with COUNT or grouping + play with the hint + driving_site – so that all records will come to us in Oracle first. Well, then – the classic options for writing a large amount in Oracle – NOLOGGING, + APPEND, options with a temporary table (partition) and DBMS_REDEFINITION / EXCHANGE PARTITION, dropping indexes before / re-creating after, BULK INSERT, etc., etc.

The main thing is to start and figure out what to optimize or where the plug is. And then it's a matter of technology.

If there is such a physical / legal opportunity + the need for the proposed one – if you can provide remote access – I will be happy to help solve (or at least "examine externally" / apply the above recommendations) the current problem "physically", for an interesting case + I have previous experience in similar situations where data transfer in heterogeneous environments does not behave as expected.

This proposal, by the way – to connect and help remotely – applies to everyone else who sooner or later gets into this thread / discussion (through a search or something else).

Post Views: 5
Scroll to Top