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

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

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.

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

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.

I assume it will be documented in SQL Reference

It is documented in the SQL Reference manual.

thanks, i will take a look.

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)");
}
}