RimuDB Logo
Database Access Framework for Java

DBCopy

DBCopy is a tool that allows a set of tables to be copied from one database to another. It was added to RimuDB in version 1.3.3. Previous builds do not include the tool.

Usage

org.rimudb.dbcopy.DBCopy -cconfiguration.xml

The XML configuration file name must be passed as a argument to the program.

How it Works

DBCopy uses the XML configuration file to determine how to establish a connection with the source database and the target database.

It then iterates through the tables in the order they are described in the tables element.

For each table, the meta data from the source table is examined to determine column names, types and sizes for the CREATE TABLE statement to be used on the target database.

If a primary key is defined for the source database table, the primary key will be created on the target table. No other indexes are copied, so if there is no primary key found, no indexes will be copied.

If the truncate_target attribute is set to true then the target table will be truncated before the data is copied.

The max_records attribute can be used to limit the number of records copied from the source table.

If the source records need to be filtered, then the where_clause attribute can be set. The where clause must be specified in the syntax used by the source database.

XML Configuration File

The XML configuration file is validated against the XML Schema http://www.rimudb.org/schemas/dbcopy-1.0.xsd.

There are three sections in the document

  • source-connection -
    • driver - The JDBC driver class name for the source database.
    • url - The URL to connect to the source database.
    • user - UserID to connect to the database. This can be included in the URL for some databases.
    • password - Password to connect to the database. This can be included in the URL for some databases.
    • use_quotes - Use quotes to build the SQL statements. Some databases default to either upper or lower case names unless quotes surround the names.
  • target-connection -
    • driver - The JDBC driver class name for the target database.
    • url - The URL to connect to the target database.
    • user - UserID to connect to the database. This can be included in the URL for some databases.
    • password - Password to connect to the database. This can be included in the URL for some databases.
    • use_quotes - Use quotes to build the SQL statements. Some databases default to either upper or lower case names unless quotes surround the names.
  • copy-tables -
    • table
      • source_catalog - The catalog for the source table (optional - some databases don't support a JDBC catalog).
      • source_schema - The schema for the source table (optional - some databases don't support a JDBC Schema).
      • source_name - The name of the source table.
      • target_catalog - The catalog for the target table (optional - some databases don't support a JDBC catalog).
      • target_schema - The schema for the target table (optional - some databases don't support a JDBC Schema).
      • target_name - The name of the target table.
      • truncate_target - Truncate the table before copying (default=true).
      • comment - This comment is not used by the program but may be useful for notes in the configuration file (optional).
      • max_records - The maximum number of records to copy (optional).
      • where_clause - A where clause that can be used to filter records to copy. The syntax must comply with the source database's syntax rules (optional).

Example:


<?xml version="1.0" encoding="UTF-8"?>
<dbcopy xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.rimudb.org/schemas http://www.rimudb.org/schemas/dbcopy-1.0.xsd" xmlns="http://www.rimudb.org/schemas">
	<source-connection driver="com.ibm.as400.access.AS400JDBCDriver" 
	                   url="jdbc:as400://mars.rimudb.org;naming=system;libraries=LIB1,LIB2,LIB3;prompt=false;access=read only" 
	                   user="xxxxxxxx" password="xxxxxxxxx" use_quotes="false"/>
	<target-connection driver="com.mysql.jdbc.Driver" 
	                   url="jdbc:mysql://jupiter.rimudb.org/db2_backup" 
	                   user="xxxxxx" password="xxxxxx" use_quotes="false"/>
	<copy-tables>
		<table source_catalog="systemname" source_schema="LIB1" source_name="ORDHDRF1" target_catalog="db2_backup" target_name="OrderHeader" truncate_target="true" comment="" where_clause="WHERE ORDNBR > 123456"/>
		<table source_catalog="systemname" source_schema="LIB1" source_name="ORDDTLF1" target_catalog="db2_backup" target_name="OrderDetail" truncate_target="true" where_clause="WHERE ORDNBR &gt; 123456"/>
		<table source_catalog="systemname" source_schema="LIB1" source_name="ORDPAYF1" target_catalog="db2_backup" target_name="OrderPayment" truncate_target="true" where_clause="WHERE ORDNBR &gt; 123456"/>
		<table source_catalog="systemname" source_schema="LIB1" source_name="ITMMSTF1" target_catalog="db2_backup" target_name="ItemMaster" truncate_target="true" max_records="100"/>
		<table source_catalog="systemname" source_schema="LIB1" source_name="ITMSKUF1" target_catalog="db2_backup" target_name="ItemSku" truncate_target="true"/>
	</copy-tables>
</dbcopy>

Database Support

While this tool most likely works on all the platforms that RimuDB is supported on, it has so far only been tested copying from a DB2 for iSeries database to a MySQL database.