DB link from Oracle to AS/400 - platform migration integrities - AS/400 to oracle

I must say thanks to SVDS (any CNC in Aus will know who this is).  He showed me this wonderful creation and I'm in debt to him for it.

Now, here is something that has saved me a lot of time and a lot of heart ache!  I've used a db link between oracle and AS/400.  This has been invaluable for running integrities between my upgrade / platform migration.  

Essentially you create a DB link on the oracle side and then use the @DBLINK sytax at the end of the table when referring to AS/400 based tables - awesome!

To see information about links, use SQL like:


COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS

http://docs.oracle.com/cd/B19306_01/gateways.102/b16222/using.htm#i20148 is a good place to start

Create the link

CREATE DATABASE LINK myAS400
        CONNECT TO ONEWORLD IDENTIFIED BY NUMPTY99
        USING 'SHANNON.WORLD';

Where your tns_name_entry is defined something like:


SHANNON.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = xx.77.66.44)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = ORAGATE)
    )
    (HS = OK)
  )

What I hear you say, surely it cannot be that simple...  And yet it is.  For simple SQL this is a blast.  Here are some examples:

See if all the custom objects made it over from XE:

select t1.siobnm, t1.sisy, t2.sistce from ol7333.f9860@myAS400 t1, ol7333.f9861@cms t2 

where t1.siobnm = t2.siobnm and t2.sipathcd = 'DV7333'

and t1.siobnm in (select siobnm from ol7333.f9860@myAS400 t3 where not exists

(select 1 from ol910.f9860 t4

where t3.siobnm = t4.siobnm) 

and t3.siobnm like '%55%');

Data count reconciliation between XE and JDE91 with platform migration from AS/400 to Oracle

create table testdta.rowcounts (table_name varchar (20), AS400_count integer, oracle_count integer) ;

/*  Add your table names from 9.1 data */

insert into testdta.rowcounts (select table_name, 0, 0 from all_Tables where owner = 'PRODDTA') ;

commit ;

/*  Add any tables from the 400 that may not already be in the reconciliation table */

insert into testdta.rowcounts (

select table_name, 0, 0

from qsys2.systables@myAS400  t1

where table_schema = 'TESTDTA'

and not exists (select 1 

  FROM all_tables

  WHERE owner = 'PRODDTA'

  AND table_name = t1.table_name)) ;

/*  update the AS/400 row count data  - you need to run the results of this statement*/

select 'UPDATE TESTDTA.ROWCOUNTS set AS400_count = ( SELECT COUNT(1) FROM ' || 'TESTDTA.' || table_name || '@CMS ' || ') WHERE table_name = ''' || table_name || ''';' 

from qsys2.systables@CMS t1

where table_schema = 'TESTDTA'

/*update the oracle row counts - you need to run the results of this statement*/

select distinct 'UPDATE TESTDTA.ROWCOUNTS set oracle_count = ( SELECT COUNT(1) FROM ' || 'PRODDTA.' || table_name || ' ' || ') WHERE table_name = ''' || table_name || ''';' 

from all_tables t1

where owner = 'PRODDTA'

commit ;

/*  here is your data, NICE! */

select * from TESTDTA.rowcounts where (as400_count <>0 or oracle_count <> 0);