Reconcile data between world, oneworld and oracle for retiring JD Edwards.

What I’ve done here is for me, for next time I need to do this task, and there will be a next time.  This is some sample scripts from an JD Edwards retirement that we’re completing (the could be used for direct migration reconciliation too).  This involves doing an upgrade on a world system (direct migration) and then parking the 9.1 equivalent in the cloud.

There are a lot of advantages of this approach to retiring JD Edwards, primarily that you do not have to run an AS/400 until the statute of limitations winds down.

We’ve put JD Edwards into the cloud for a number of very large organisations and it’s essential to get the reconciliation correct.

The general process of below is:

1. create reconciliation table
2. insert base records for all 9.1 tables
3. insert records for any tables in the World data library used in the direct migration
4. Insert records for any tables in the OneWorld data library used in the direct migration (note that this is a lot less than the #3)
5. The create the “count SQL statements”
6. run the count statements

This is all run from oracle using the oracle database gateway for ODBC.  I’ve been able to move over 1.1TB of data (pure data, non-unicode) to oracle from AS/400 in about 48 – 72 hours of pure processing.

[sourcecode language='sql'  padlinenumbers='true']
create table fbadta.datacounts2
(AS400WORLDLIB VARCHAR(12), 
AS400E1LIB VARCHAR(12), 
TABLENAME VARCHAR(20),
AS400WORLDCOUNT INTEGER,
AS400E1COUNT INTEGER,
ORACLECOUNT INTEGER,
ORACLE_OWNER varchar(16),
TABLE_DESCRIPTION VARCHAR(256),
ONEWORLD_TABLE VARCHAR(10)) ;

insert into fbadta.datacounts2 (select '','',siobnm, 0,0,0,'',simd, '91Table' FROM OL910.F9860 where siobnm like 'F%');

insert into fbadta.datacounts2 (
select table_schema, '', table_name, 0,0,0,'', TABLE_TEXT, 'WORLD ONLY' 
FROM QSYS2.SYSTABLES@FONDEV01
where TABLE_SCHEMA = 'A73CCODTA3' AND TABLE_TYPE in ('P','T')
AND NOT EXISTS (SELECT 1 
  from fbadta.datacounts2 
  where trim(tablename) = trim(table_name)));

commit;

insert into fbadta.datacounts2 (
select '', table_schema, table_name, 0,0,0,'', TABLE_TEXT, 'WORLD ONLY' 
FROM QSYS2.SYSTABLES@FONDEV01
where TABLE_SCHEMA = 'FBAPRODDTA' AND TABLE_TYPE in ('P','T')
AND NOT EXISTS (SELECT 1 
  from fbadta.datacounts2 
  where trim(tablename) = trim(table_name)));
  update FBAdta.datacounts2 set tablename=trim(tablename) ;

  commit;
  
select 'UPDATE FBADTA.DATACOUNTS2 SET   AS400WORLDCOUNT = (SELECT COUNT(*) FROM A73CCODTA3.' || trim(TABLENAME) || '@FONDEV01) WHERE TABLENAME = ' || '''' || trim(tablename) || ''';' FROM FBADTA.DATACOUNTS2 ;
select 'UPDATE FBADTA.DATACOUNTS2 SET   AS400E1COUNT = (SELECT COUNT(*) FROM FBAPRODDTA.' || trim(TABLENAME) || '@FONDEV01) WHERE TABLENAME = ' || '''' || trim(tablename) || ''';' FROM FBADTA.DATACOUNTS2 ;
select 'UPDATE FBADTA.DATACOUNTS2 SET   ORACLECOUNT = (SELECT COUNT(*) FROM FBADTA.' || trim(TABLENAME) || ') WHERE TABLENAME = ' || '''' || trim(tablename) || ''';' FROM FBADTA.DATACOUNTS2 ;
[/sourcecode]