9.10. The ddlscan Command

The ddlscan command scans the existing schema for a database or table and then generates a schema or file in a target database environment. For example, ddlscan is used in MySQL to Oracle heterogeneous deployments to translate the schema definitions within MySQL to the Oracle format. For more information on heterogeneous deployments, see Understanding Heterogeneous Deployments.

For example, to generate Oracle DDL from an existing MySQL database:

shell> ddlscan -user tungsten -url 'jdbc:mysql:thin://tr-source:13306/test' -pass password \
    -template ddl-mysql-oracle.vm -db test

SQL generated on Thu Sep 11 15:39:06 BST 2019 by ./ddlscan utility of Tungsten

url = jdbc:mysql:thin://tr-source:13306/test
user = tungsten
dbName = test
*/

DROP TABLE test.sales;
CREATE TABLE test.sales
(
  id NUMBER(10, 0) NOT NULL,
  salesman CHAR,
  planet CHAR,
  value FLOAT,
  PRIMARY KEY (id)
);

The format of the command is:

ddlscan [ -conf path ] [ -db db ] [ -opt opt val ] [ -out file ] [ -pass secret ] [ -path path ] [ -rename file ] [ -service name ] [ -tableFile file ] [ -tables regex ] [ -template file ] [ -url jdbcUrl ] [ -user user ]

The available options are as follows:

Table 9.21. ddlscan Command-line Options

OptionDescription
-conf pathPath to a static-{svc}.properties file to read JDBC connection address and credentials
-db dbDatabase to use (will substitute ${DBNAME} in the URL, if needed)
-opt opt valOption(s) to pass to template, try: -opt help me
-out fileRender to file (print to stdout if not specified)
-pass secretJDBC password
-path pathAdd additional search path for loading Velocity templates
-rename fileDefinitions file for renaming schemas, tables and columns
-service nameName of a replication service instead of path to config
-tableFile fileNew-line separated definitions file of tables to find
-tables regexComma-separated list of tables to find
-template fileSpecify template file to render
-url jdbcUrlJDBC connection string (use single quotes to escape)
-user userJDBC username

ddlscan supports three different methods for execution:

  • Using an explicit JDBC URL, username and password:

    shell> ddlscan -user tungsten -url 'jdbc:mysql:thin://tr-hadoop1:13306/test' -user user \
        -pass password ...

    This is useful when a deployment has not already been installed.

  • By specifying an explicit configuration file:

    shell> ddlscan -conf /opt/continuent/tungsten/tungsten-replicator/conf/static-alpha.properties ...
  • When an existing deployment has been installed, by specifying one of the active services:

    shell> ddlscan -service alpha ...

In addition, the following two options must be specified on the command-line:

  • The template to be used (using the -template option) for the DDL translation must be specified on the command-line. A list of the support templates and their operation are available in Table 9.22, “ddlscan Supported Templates”.

  • The -db parameter, which defines the database or schema that should be scanned. All tables are translated unless an explicit list, regex, or table file has been specified.

For example, to translate MySQL DDL to Oracle for all tables within the schema test using the connection to MySQL defined in the service alpha:

shell> ddlscan -service alpha -template ddl-mysql-oracle.vm -db test

ddlscan provides a series of additional command-line options, and a full list of the available templates.