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 Section 2.8, “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 8.4. ddlscan Command-line Options
Option | Description |
---|---|
-conf path | Path to a static-{svc}.properties file to read JDBC connection address and credentials |
-db db | Database to use (will substitute ${DBNAME} in the URL, if needed) |
-opt opt val | Option(s) to pass to template, try: -opt help me |
-out file | Render to file (print to stdout if not specified) |
-pass secret | JDBC password |
-path path | Add additional search path for loading Velocity templates |
-rename file | Definitions file for renaming schemas, tables and columns |
-service name | Name of a replication service instead of path to config |
-tableFile file | New-line separated definitions file of tables to find |
-tables regex | Comma-separated list of tables to find |
-template file | Specify template file to render |
-url jdbcUrl | JDBC connection string (use single quotes to escape) |
-user user | JDBC 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 8.5, “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.