With support for most major databases, SymmetricDS is the ideal tool for migrating data from one database platform to another. Data can be exported to files and imported using formats that are independent of the database being used. The dbexport and dbimport utilities provide a powerful set of options for extracting and loading data.
Connecting to the Database
In order for dbexport and dbimport to connect to the database, the user must provide a properties file with settings for the database. If you’ve already setup SymmetricDS to connect to the database using a property file, those settings are used by default. Or you can specify a different property file to use. Read how to Connect to the Database to learn how to setup properties files and specify which ones to use.
Data Formats
Structure and data can be exported and imported using different file formats. Some formats support both structure and data, while other formats only support data. Structure is the definition of the table, including its column names, column types, indexes, and constraints. Data are the rows of values stored in the table. The following formats are supported:
| Format | Description |
|---|---|
| SQL | Structured Query Language (SQL) is the language of the database used to create tables and insert, update, and delete data. This is the default format if one isn’t specified. For data, the SQL generated should be ANSI compliant and work on any database. For structure, the SQL generated will be compatible with the current database. Use the “–compatible” option to change the SQL generation to target a different database. |
| CSV | Comma separated value format supports only data. Each line is a row of data, with each column value separated by a comma. The first line of the file is a comment that includes the column names. This file can be opened in a desktop spreadsheet tool for easy editing. There is not a way to distinguish between multiple tables in the file, so only one table should be exported to a file. |
| XML | The eXtensible Markup Language is used to describe both table structure and data. The structure is written as Apache Torque XML, while data uses something similar to mysqldump. |
| SYM_XML | This format is also XML, but only supports data. The data is written using the same XML format used by the message publishing extension. |
Exporting Data
The dbexport command line utility can export the structure and data of tables from the database to files. When run without any arguments, it will export all tables from the current schema using the SQL format, which is sent to standard output. The catalog and schema can be specified with options and table names can be listed as arguments. Below is an example of running dbexport for a couple tables and writing the output to a file.
dbexport –catalog mycatalog –schema myschema mytable1 mytable2 > output.sql
The SQL output for table create statements is specific to the current database, but you can specify a different database to target.
dbexport –format=sql –compatible=oracle mytable1
Change the output format to one of SQL, CSV, XML, or SYM_XML. Keep in mind the limitations for certain formats, such as one table per file with CSV, and support for only data with CSV and SYM_XML.
dbexport –format=xml mytable1
The output can contain both structure and data, or you can turn one of them off.
dbexport –no-data mytable1 > create.sql
dbexport –no-create-info mytable1 > data.sql
By default, all rows will be written as output. You can specify a SQL query to use instead, which changes the output to the columns and rows you want.
dbexport –sql=”select column1 from table1 where column1 is not null” mytable1
Importing Data
The dbimport command line utility can import data files into the database. It expects to be passed the names of files as arguments. The catalog and schema can be specified with options. Below is an example of running dbimport to apply a SQL file to the database.
dbimport output.sql
The SQL format is the default format expected, so you should specify a different format for other files.
dbexport –format=xml file1.xml
When importing a CSV file, since it doesn’t contain structure information about the table name, you can specify the table name with an option.
dbimport –catalog mycatalog –schema myschema –table mytable1 –format=csv file1.csv file2.csv
If an error is encountered while processing the file, the import will display the error and stop. To force processing of all import data, use the “–force” option.
dbimport –force data.sql
When loading data, a commit is run for every 10,000 rows. You can change the commit rate using an option.
dbimport –commit 1000 data.sql
More Help
Both the dbexport and dbimport commands have plenty of options to change how data is exported and imported. Run the command with an option of “–help” to see a full list of options and how to use them.
,/p>