SETUP REPLICATION PROCESS TO EXPORT ORACLE DATA TO FREEWARE DATABASES.

Setup freewareExport product from fnkits.fnal.gov:
1. ups install freewareExport v1_0 -f NULL -G "c"

2. setup  freewareExport

3. Set JAVA _HOME variable: setenv JAVA_HOME "java-path"/bin

4. cd freewareExport

5. Define REPL_HOME, CLASSPATH and MyPROPERTY variables from freewareExport directory:
   setup  freewareExport -O "property_file_name"


  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;

  Usage of Property Files:
    - MyPROPERTY default value is calibTables.property filename to export calibration tables.
      There are also property files for admin, fileCatalog tables and template.property files with comments.

    - You can split list of tables to load between two or more property files and start the jobs in parallel.
    - To setup $MyPROPERTY variable with another property file do setup freewareExport again as:
      setup freewareExport -O "your-property-file-name"

  Usage dbCreateTables:
    - dbCreateTables distincts "schema" and "merge" words in parameter TableAction:
      With TableAction = "schema" only schemas  will be created in freeware database, no loads.
      With TableAction = "merge" - in freeware database one table will be created with data loaded
      from all source tables with this name but from different schems.
      Blank - create schema and load data from source table into freeware table.

    - Both dbCreateTable and dbUpdateTables have option to exclude number of columns from the source table.

 Usage dbUpdateTables:
    - dbUpdateTables ignores parameter TableAction considering it as "update" only.

    - For DATETIME date type columns use expression with format of your choice in where clause as:
     where to_char(column_name,'yyyy-mm-dd hh24:mi:ss') > 'expression'

    - If schema from source table is changed, dbUpdateTable will stop.
     Then dbCreateTable can be used to create freeware table with modified schema and load data again.


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. Setup property file:
   setup  freewareExport -O "property_file_name"

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

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

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 $REPL_HOME/postgres_batch.sql;
   Or in postgresql:
      start postgresql client:  ups client postgres
      then run:  cdfofprd=# \i /your-path/freewareExport/postgres_batch.sql;

4. Setup property file:
   setup  freewareExport -O "property_file_name"

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

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

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