1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Compare heterogeneous database

Discussion in 'General' started by bala0786, Mar 15, 2010.

  1. bala0786

    bala0786 Guest

    Hi ,

    I would require someone to help me in providing the solutiion for the given scenarios using a query and NOT..without any tool.

    1.Compare Source and Target table in 2 different servers ,source being Oracle DB and Target is DB2 DB.

    2.Compare Source and target tables in same server,source being Oracle DB and target is flat file

    3.Compare Source and target tables in same server,both source and target are flat files
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Hello Bala, your question is impractical and meaningless. You cannot use a query to compare tables in flat files to begin with.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Certainly you can provided you make the flat file an external table. As an example (of which many are found on the internet) one can take the alert log and turn it into a table:

    Code (SQL):
    COLUMN a_log new_value ALOG noprint
    COLUMN VALUE new_value bkgd_dmp noprint

    SELECT 'alert_'||instance_name||'.log' a_log
    FROM v$instance;

    SELECT VALUE
    FROM v$parameter
    WHERE name = 'background_dump_dest';

    CREATE OR REPLACE directory data_dir AS '&&bkgd_dmp';

    CREATE TABLE alert_t
    (
    text_line varchar2(255)
    )
    ORGANIZATION EXTERNAL
    (
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY data_dir
        ACCESS PARAMETERS
        (
            records delimited BY newline
            FIELDS
            REJECT ROWS WITH ALL NULL FIELDS
        )
        LOCATION
        (
            '&&ALOG'
        )
    )
    REJECT LIMIT unlimited;

    SELECT *
    FROM alert_t
    WHERE rownum < 20;
     
    An external table could help with question 2; there would be no need for a query in question 3 as a simple diff operation in UNIX could provide the answer. Knowledge of the DB2 data dictionary is necessary to answer question 1 but it could be answered through a qiuery provided Heterogeneous services are installed and a link to the DB2 database is created in the Oracle database.

    The questions are not meaningless when one understands the options available.