0

How I can copy my schema in db2 database into the oracle database with full table structure, columns, data and etc using Java.

I write java code but it does not select all tables. I do not know how many tables are in db2 database and even do not know the name of the tables. I just wanna store all the information and make the same schema in oracle.

public class automateExport {
    static String value;

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
       // ResultSet rs = null;
        Connection  DB2 = getConnection ();
        String sql = "SELECT * FROM SYSCAT.COLUMNS WHERE TABSCHEMA NOT LIKE 'SYS%'";
       PreparedStatement mainStmt = DB2.prepareStatement(sql);
        ResultSet rs = mainStmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        String tableName = null;
        StringBuilder sb = new StringBuilder( 1024 );
        if ( columnCount > 0 ) { 
          sb.append( "Create table " ).append( rsmd.getTableName( 1 ) ).append( " ( " );
        }
    for ( int i = 1; i <= columnCount; i ++ ) {
    if ( i > 1 ) sb.append( ", " );
    String columnName = rsmd.getColumnLabel( i );
    String columnType = rsmd.getColumnTypeName( i );

    sb.append( columnName ).append( " " ).append( columnType );

    int precision = rsmd.getPrecision( i );
    if ( precision != 0 ) {
        sb.append( "( " ).append( precision ).append( " )" );
    }
     } // for columns
    sb.append( " ) " );
    System.out.println( sb.toString() );       
    }

      private static Connection  getConnection() throws ClassNotFoundException, SQLException
     {
        Class. forName ( "COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver"  );
        Connection  connection =
                DriverManager.getConnection("jdbc:db2://localhost:50000/navid","navid","oracle");
        return connection;
     }
    }
StanislavL
  • 56,971
  • 9
  • 68
  • 98
Omid Rastin
  • 5
  • 5
  • There's a variety of things mentioned [in this recent question](http://stackoverflow.com/questions/27377431/how-to-create-multiple-schema-connections-using-java). Note that it's non-trivial to move from one RDBMS to another, because they all have minor (or not so minor) differences. Oracle, for example, doesn't support in-table auto-generated columns (you have to use `SEQUENCE` objects). Any stored procedures you want to re-create are almost guaranteed to not compile immediately. There are tools for this, don't bother writing it yourself. – Clockwork-Muse Dec 10 '14 at 08:45
  • Thank you for your answer, but I must write it with Java. I just wanna create the same tables in oracle and insert all data in it. auto-generate and keys are not important. could you please help me with java class? – Omid Rastin Dec 10 '14 at 08:56
  • ...why do you have to write it? Why would you want to copy the definitions and (presumably all of) the data, but not other important things? Like, say, indices? What specific problems are you seeing? What errors do you get? What do some of your table creation strings look like? I'm not sure if Oracle supports identically named types to DB2, so even the naïve approach is likely to blow up on you. – Clockwork-Muse Dec 10 '14 at 09:04
  • because data must insert daily not once , because of that I need a java program that run every hour(for example). the tools you suggest is good for one time migration. – Omid Rastin Dec 10 '14 at 09:12
  • ...Then you **really, REALLY** want to look into something called an ETL (Extract, Transform, Load) tool. You do **not** want to write this yourself. If this data is in any way critical, there are _lots_ of ways this can go wrong, really fast. For that matter, DB2 and Oracle probably have ways to talk directly between themselves. What is your final goal here - why are you trying to sync DB2 to Oracle? And I still don't see why you'd need to create the tables each time. – Clockwork-Muse Dec 10 '14 at 09:24
  • you know I write a program for a Bank, I used Oracle database and the bank use db2 database. so changing in data and maybe tables are very rapid in bank so I must write a java program and called it by a procedure from my Oracle every houre for example. – Omid Rastin Dec 10 '14 at 09:32
  • This is the first time **this** account has mentioned writing a program for a bank. You **REALLY** don't want to do this yourself. There's no reason I can think of to copy every table from the DB2 db in this scenario, and every reason for the admins to complain when your installation bloats to ridiculous size. I can guarantee you'd have less trouble changing your program to read from/use the DB2 database, than writing an ETL tool yourself. For that matter, I'm surprised the bank 1) let you use a different db and 2) would give you total read access to their db. – Clockwork-Muse Dec 10 '14 at 09:47

0 Answers0