Export Oracle data to Mysql database.

Import freewareExport from cvs Repository:
There are two products in cvs repository designed for replication oracle data to freeware databases:
freewareRepl and freewareExport. 
The only difference that freewareRepl includes Java(TM) 2 SDK Version 1.4.0 and it takes 65MB; 
freewareExport does not include Java with significantly less size to install. 
freewareRepl is recommended only if user does not have Java SDK on his machine. 
 
 kinit
 setup cvs
 setenv CVSROOT :pserver:anonymous@cdcvs.fnal.gov:/cvs/cd_read_only
 cvs co freewareExport
 cd freewareExport
 Read README file from freewareExport directory or follow these instructions:


Setup freeware replication environment:
  
1.Set JAVA _HOME variable as  
  setenv JAVA_HOME "path-to-your-java"/bin
 
2.Go to freewareExport directory.
  cd freewareExport

  There are following data in freewareExport directory you need to know: 
  Directories:
     1. dbDrivers with jdbc drivers for oracle, mysql and postgresql databases;
     2. cdfExport with java codes 
     3. propertyFiles keeps property files that serve as a set of parameters for java executables.   
  Files: 
     1. README file with instructions how to setup replication process;
     2. mysql_batch.sql - creates cdfofprd database and support tables in Mysql;
     3. postgres_batch.sql - creates cdfofprd database and support tables in PostgreSql;
     4. setenv.csh to define REP_HOME, CLASSPATH and MyPROPERTY variables;
 
    MyPROPERTY default value is calibTables.property filename to export calibration tables.
    There are also property files for admin, fileCatalog tables and template.property file with comments. 

3. Define REP_HOME, CLASSPATH and MyPROPERTY variables from freewareExport directory:   
   source setenv.csh


Replication Oracle tables to Mysql database:
1. Start MySQL server or be sure mysql server is running.  
   setup mysql
   ups start mysql  
 
2. Start mysql client as a root user to create new mysql cdfofprd database and two support tables: 
   ups client mysql  

3. Create mysql database cdfofprd, support tables and mysql user "dbreader" from mysql:
   mysql> source "your-path"/freewareExport/mysql_batch.sql;

4. Check property file to be sure you are going to export data you plan.
   You can update property files with your needs. Also list of tables can be splited 
   between 2 or more property files and exported simultaneously. 
   See template.property file as an example. 

5. Execute Java code:
   Export Tables to freeware database: 
   $JAVA_HOME/java cdfExport.dbCreateTables $MyPROPERTY [debug]

   Update existing freeware tables: 
   $JAVA_HOME/java cdfExport.dbUpdate $MyPROPERTY [debug]

   To compile source code in case of updating the source codes: 
   $JAVA_HOME/javac -d . -classpath $CLASSPATH ./cdfExport/.java


Replication Oracle tables to Postgresql:
1. Start PostgreSql server or be sure the server is running.  
   setup postrges
   ups start postgres

2.Start postgresql client: ups client postgres 
  Login to postgresql as a superuser who has the privileges to create databases and new users. 
   

3. Create postgres database cdfofprd, support tables and postgresql "dbreader".

   One way is to execute following command from shell:
      psql -d cdfofprd -e -f $REP_HOME/postgres_batch.sql;
   Or in postgresql:
      start postgresql client:  ups client postgres
      then run:  cdfofprd=# \i /your-path/freewareExport/postgres_batch.sql;

4. Check property file to be sure you are going to export data you plan.
   You can update property files with your needs. Also list of tables can be splited 
   between 2 or more property files and exported simultaneously. 
   See template.property file as an example. 

5. Execute Java code:
   Export Tables to freeware database: 
   $JAVA_HOME/java cdfExport.dbCreateTables $MyPROPERTY [debug]

   Update existing freeware tables: 
   $JAVA_HOME/java cdfExport.dbUpdate $MyPROPERTY [debug]

   To compile source code in case of updating the source codes: 
   $JAVA_HOME/javac -d . -classpath $CLASSPATH ./cdfExport/.java   

Mail comments to:css-dsg@fnal.gov
Last Modified  October 8, 2003