Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    30

    Unanswered: SQL Export of Records

    Hi,

    i need a SQL export of table records, like this:

    Insert into ....
    Insert into ....

    In the ControlCenter of DB2 i only see options like delimited, IXF and WSF. Is there a chance to do a SQL data export to a file?

    I recently saw a third party tool for db2 who does it, but i think this should be allready on board.

    Thanks for Infos.

    Marc

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL Export of Records

    You should be able to generate manually ...
    Example :

    db2 "select 'insert into table1 values('||col1||','||col2||');'

    or export the table to a de file
    and
    cat table1.del | nawk '{print "insert into table1 values\("$0"\);"'

    HTH

    Sathyaram


    Originally posted by loge
    Hi,

    i need a SQL export of table records, like this:

    Insert into ....
    Insert into ....

    In the ControlCenter of DB2 i only see options like delimited, IXF and WSF. Is there a chance to do a SQL data export to a file?

    I recently saw a third party tool for db2 who does it, but i think this should be allready on board.

    Thanks for Infos.

    Marc
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2003
    Posts
    30
    thanks for your hints. I hoped for an secret onboard solution ;-) But i will do one of your proposed ways. Perhaps its worth to wrap it in some nice gui and roll out an open source db2 tool. Lets see if there is time...

    BTW the second solution is clear, but:

    db2 "select 'insert into table1 values('||col1||','||col2||');'

    seems a little bit weird to me. Can you point me to some documentation link, i dont understand the syntax and the bahavior. Where do i define which table to export for instance.
    Last edited by loge; 11-25-03 at 10:46.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The second one is a concatenation of the following strings ...

    insert into table1 values(
    col1
    ,
    col1
    ) ;


    Eg :

    select 'runstats on table '||rtrim(tabschema)||'.'||rtrim(tabname)||' with distribution ;' from syscat.tables

    will give me

    runstats on table sysibm.systables with distribution ;
    runstats on table sysibm.syspackages with distribution ;


    etc ...

    HTH

    Sathyaram



    Originally posted by loge
    thanks for your hints. I hoped for an secret onboard solution ;-) But i will do one of your proposed ways. Perhaps its worth to wrap it in some nice gui and roll out an open source db2 tool. Lets see if there is time...

    BTW the second solution is clear, but:

    db2 "select 'insert into table1 values('||col1||','||col2||');'

    seems a little bit weird to me. Can you point me to some documentation link, i dont understand the syntax and the bahavior. Where do i define which table to export for instance.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2003
    Posts
    30
    wow this is really nice. Is this concatenation feature documented somewhere? Of course your example is enough to make things work, but i am feeling better if i can study this in some redbook.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I assume it will be documented in SQL Reference



    Originally posted by loge
    wow this is really nice. Is this concatenation feature documented somewhere? Of course your example is enough to make things work, but i am feeling better if i can study this in some redbook.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is documented in the SQL Reference manual.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2003
    Posts
    30
    thanks, i will take a look.

  9. #9
    Join Date
    Nov 2003
    Posts
    30
    created a small java app which exports an entire database without too much hassle. Its certainly only a hack and will most likely fail in some situations. You can get it at:

    http://www.logemann.org/divs/Db2ExportSQL.zip

    If people are interessted in code, here it comes:

    import java.sql.*;

    class Db2ExportSQL {
    public static void main(String[] args) throws SQLException {
    if (args.length != 6)
    argumentError();
    else {
    System.out.println(" == Start SQL Export ==");

    // Register the driver. You must register the driver before you can use it.
    DriverManager.registerDriver(new com.ibm.db2.jcc.DB2Driver());

    String url = "jdbc:db2://" + args[0] + ":" + args[1] + "/" + args[3];

    String pw;
    if (args[5].equals("!EMPTY!"))
    pw = " ";
    else
    pw = args[5];

    Connection con = DriverManager.getConnection(url, args[4], pw);
    Statement statement = con.createStatement();

    // retrieve all tables for the specified schema
    String getAllTablesSQL = "SELECT tabname from syscat.tables where tabschema='" + args[2] + "'";
    ResultSet rs = statement.executeQuery(getAllTablesSQL);
    while (rs.next()) {
    String sql = "select * from " + rs.getString("tabname");
    Statement st2 = con.createStatement();
    ResultSet rs2 = st2.executeQuery(sql);

    // we create nice SQL inserts so we retrieve column names in order to place them into insert STMT
    ResultSetMetaData metaData = rs2.getMetaData();

    int columnCount = metaData.getColumnCount();
    StringBuffer insertSQL = new StringBuffer("insert into " + rs.getString("tabname") + " (");

    for (int i = 0; i < columnCount; i++) {
    if (i != 0) insertSQL.append(",");
    insertSQL.append(metaData.getColumnName(i + 1));
    }
    insertSQL.append(") values (");

    while (rs2.next()) {
    StringBuffer insertSQLfinal = new StringBuffer(insertSQL.toString());
    for (int i = 0; i < columnCount; i++) {
    if (i != 0) insertSQLfinal.append(",");
    int columnType = metaData.getColumnType(i + 1);
    if (columnType == java.sql.Types.CHAR || columnType == java.sql.Types.VARCHAR)
    insertSQLfinal.append("'" + rs2.getString(i + 1).trim() + "'");
    if (columnType == java.sql.Types.INTEGER || columnType == java.sql.Types.DECIMAL)
    insertSQLfinal.append(rs2.getInt(i + 1));
    else
    insertSQLfinal.append(rs2.getString(i + 1).trim());
    }
    insertSQLfinal.append(");");
    System.out.println(insertSQLfinal);
    }
    }
    System.out.println(" == DONE ==");


    // Finally but importantly close the statement and connection
    statement.close();
    con.close();
    }
    }

    private static void argumentError() {
    System.out.println("DB2 Data SQL Export Hack - by Logentis e.K.");
    System.out.println("============================== ===============");
    System.out.println("Usage: java Db2ExportSQL Hostname Port Schemaname Databasename User Password");
    System.out.println("#");
    System.out.println("Hostname = Hostname or IP Address where DB2 runs");
    System.out.println("Port = TCPIP port where DB2 runs (try 50000)");
    System.out.println("Schemaname = Schemaname of Tables");
    System.out.println("Databasename = Database where you want to export from");
    System.out.println("User = User for authentication");
    System.out.println("Password = Password for authentication (use !EMPTY! for no password)");
    }
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •