Export oracle data to Postgres database on ncdf141 machine.

Set Java environment:  source setenv.csh 
   Example of setenv.csh file on ncdf141 machine: 
   **********************************************
   setenv CLASSPATH /cdf/postgres:
                   /cdf/postgres/postgres-drivers/oracle:
                  /cdf/postgres/postgres-drivers/mysql-connector:
                 /cdf/postgres/postgres-drivers/jdbc7.1-1.2.jar 
   setenv JAVA_HOME /usr/products/prd/java/v1_4_0/Linux-2/bin 
   setenv MyPROPERTY /cdf/postgres/property-files/.property


On PostgreSQL side:
 
1) Set enable TCP/IP networking for Postgres server.
   In postgres datadir uncomment line with tcpip_socket in postgresql.conf file as: 
   tcpip_socket = 1  
   Usually this line looks like: 
   #tcpip_socket = false;

2) Set access permission for machine connecting with Postgres server: 
   In postgres datadir update pg_hba.conf file as: 
   host       all         "machine IP address"  255.255.255.255  trust 
   To get your IP address use: shell> cat /etc/hosts

1) Create PostgreSql database: CREATE DATABASE cdfofprd;

2) Create PostgreSql support tables. 
   In PostgreSql execute: cdfofdev=# \i /cdf/postgres/scripts/createTables.psql 

   Example of createTables.psql file:
   **********************************
   drop table myTables;
   DROP SEQUENCE mytables_mytablesid_seq;
 
   CREATE TABLE myTables (
        myTablesID  serial NOT NULL,
        TableName varchar(100) default NULL,
        TableType varchar(10)
        CHECK (TableType='admin' OR TableType='calib' OR TableType='user'OR TableType='view')
        default NULL,
        CreateDate TIMESTAMP DEFAULT now(),
        UpdateDate TIMESTAMP DEFAULT now(),
        NumRows smallint default NULL,
        PRIMARY KEY  (myTablesID) );
 
  drop table myUpdates;
  DROP SEQUENCE myupdates_myupdatesid_seq;
 
  CREATE TABLE myUpdates (
       myUpdatesID SERIAL NOT NULL,
       myTablesID real default NULL,
       UpdateDate TIMESTAMP DEFAULT now() NOT NULL,
       NumRows smallint default NULL,
       PRIMARY KEY  (myUpdatesID)  );
 
  GRANT ALL PRIVILEGES ON  myTables TO dbreader;
  GRANT ALL PRIVILEGES ON  myUpdates TO dbreader;
 
  GRANT ALL ON mytables_mytablesid_seq  TO dbreader;
  GRANT ALL ON myupdates_myupdatesid_seq TO dbreader;


To execute Java code
1. Create Tables in Mysql database: 
   $JAVA_HOME/java cdfExport.dbCreateTables $MyPROPERTY [debug]

2. Add records to existing Mysql tables: 
   $JAVA_HOME/java cdfExport.dbUpdate $MyPROPERTY [debug]

3. 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  March 3, 2003