write-schema-to-sql

Writes the schema of a database to a file as SQL commands. You can use the resulting file to recreate the schema in GemFire XD, or in another database management system. This command is generally used with a GemFire XD cluster to export the schema, but it can also be used with other JDBC datasources.

Syntax

To write a database schema as SQL commands to a file, use the syntax:

gfxd write-schema-to-sql -file=<path>
     [-auth-provider=<name>]
     [-bind-address=<address>]
     [-catalog-pattern=<pattern>]
     [-client-bind-address=<address>]
     [-client-port=<port>]
     [-database-type=<db type>]
     [-delimited-identifiers=<true | false>]
     [-driver-class=<class name>]
     [-exclude-table-filter=<filter>] 
     [-exclude-tables=<name,name,...>]
     [-export-all]
     [-extra-conn-props=<properties>] 
     [-generic]
     [-help] 
     [-include-table-filter=<filter>] 
     [-include-tables=<name,name,...>]
     [-isolation-level=<level>]
     [-locators=<addresses>]
     [-mcast-address=<address>]
     [-mcast-port=<port>]
     [-password[=<password>]]
     [-schema-pattern=<pattern>]
     [-to-database-type=<type>]
     [-url=<url>]
     [-user=<username>]
     [-verbose=<level>]
     [-xml-schema-files=<path,path,...>]

This table describes options for the gfxd write-schema-to-sql command. Default values are used if you do not specify an option.

Option	Description
-file	The full path of the file in which to store the SQL commands. This argument is required.
-auth-provider	Sets the authentication provider to use for peer-to-peer connections as well as client-server connections. Valid values are BUILTIN and LDAP. All other members of the GemFire XD distributed system must use the same authentication provider and user definitions. If you omit this option, the connection uses no authentication mechanism. See Configuring Security.
-bind-address	The address to which this peer binds for receiving peer-to-peer messages. By default gfxd uses the hostname, or localhost if the hostname points to a local loopback address.
-catalog-pattern	A string pattern that determines the database catalogs that gfxd writes. gfxd does not use a default catalog pattern. To use a catalog pattern, specify a string value that describes the catalogs that you want to write. Use the "%" character to match any substring of 0 or more characters. Use the "_" character to match any individual character.
-client-bind-address	The hostname or IP address on which a GemFire XD locator listens for client connections. The default is "localhost." Use this option with -client-port to attach to a GemFire XD cluster as a thin client and perform the command.
-client-port	The port on which a GemFire XD locator listens for client connections. The default is 1527. Use this option with -client-bind-address to attach to a GemFire XD cluster as a thin client and perform the command.
-database-type	Specifies the type of database to which you are connecting. Use this option if gfxd cannot determine the type of database from the JDBC driver and JDBC connection URL. Valid values are: axion, cloudscape, db2, derby, firebird, hsqldb, interbase, maxdb, mckoi, mssql, mysql, mysql5, oracle, oracle9, oracle10, postgresql, sapdb, gemfirexd, and sybase.
-delimited-identifiers	Specifies whether to use delimited (quoted) identifiers for table names, column names, and so forth. Most databases convert undelimited identifiers to uppercase letters and ignore any case that you specify in the SQL command. You can set this option to "true" for platforms that support delimited identifiers. However, keep in mind that when you use delimited identifiers, you must always enclose identifiers in double quotes, and you must specify the correct case for the identifier in all subsequent SQL commands. By default, gfxd sets this option to "false."
-driver-class	The JDBC driver class to use for connecting to a datasource. Use this option with -url to connect to a JDBC datasource.
-exclude-table-filter	Specifies a regular expression to use for excluding tables when reading the database. Tables that match the pattern are not included in the output file. For case-insensitive matching (-delimited-identifiers option is false), specify uppercase table names in the pattern.
-exclude-tables	Specifies the name of one or more tables to exclude when reading the database.
-export-all	Include this option to all database objects as SQL statements, including all of the required GemFire XD-specific extensions such as persistence clauses. This option is provided as a way to perform a full GemFire XD schema backup, as it covers all objects such as installed JARs, and so forth. Note: In order to execute this utility, you must connect to the GemFire XD distributed system as a system user.
-extra-conn-props	A semicolon-separated list of properties to use when connecting to the datasource.
-generic	By default, when you export a schema from GemFireXD, gfxd exports all DDLs as SQL statements that include GemFire XD-specific extensions such as partitioning, persistence, and so forth. Include the `-generic` option to override this default behaviour and export only generic schema objects as visible to the JDBC driver.
-help, --help	Display the help message for this gfxd command.
-include-table-filter	Specifies a regular expression to use for including tables when reading the database. Tables that match the pattern are included in the output file. For case-insensitive matching (-delimited-identifiers option is false), specify uppercase table names in the pattern.
-include-tables	Specifies the name of one or more tables to include when reading the database.
-isolation-level	Sets the ANSI standard transaction isolation level for database operations. Valid values are READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE (case insensitive).
-locators	The list of locators as comma-separated host[port] values, used to discover other members of the distributed system. Using -locators creates a peer client member to execute the gfxd command.
-mcast-address	The multicast address used to discover other members of the distributed system. This value is used only when the -locators option is not specified. The default multicast address is 239.192.81.1. Use this option with -mcast-port to attach to a GemFire XD cluster as a peer client and perform the command.
-mcast-port	The multicast port used to communicate with other members of the distributed system. If zero, multicast is not used for member discovery (specify -locators instead). This value is used only if the -locators option is not specified. Valid values are in the range 0–65535, with a default value of 10334. Use this option with -mcast-address to attach to a GemFire XD cluster as a peer client and perform the command.
-password	If the servers or locators have been configured to use authentication, this option specifies the password for the user (specified with the -user option) to use for booting the server and joining the distributed system. The password value is optional. If you omit the password, gfxd prompts you to enter a password from the console.
-schema-pattern	A string pattern that determines the schema(s) that gfxd writes. gfxd does not use a default schema pattern. However, with certain databases you may be required to use a schema pattern to exclude system tables that contain data types that are incompatible with the DdlUtils 1.1 API. To use a schema pattern, specify a string value that describes the catalogs that you want to write. Use the "%" character to match any substring of 0 or more characters. Use the "_" character to match any individual character.
-to-database-type	Specifies the target database type to use when formatting SQL statements for the output file. Valid values are: axion, cloudscape, db2, derby, firebird, hsqldb, interbase, maxdb, mckoi, mssql, mysql, mysql5, oracle, oracle9, oracle10, postgresql, sapdb, gemfirexd, and sybase. Use this option when you want to read a schema from one database and create a SQL file in a format that is compatible with another database.
-url	The JDBC URL to use for connecting to a datasource. Use this option with -driver-class to connect to a JDBC datasource.
-user	If the servers or locators have been configured to use authentication, this option specifies the user name to use for booting the server and joining the distributed system.
-verbose	Sets the DdlUtils verbosity level to one of FATAL, ERROR, WARN, INFO, or DEBUG, in increasing order of logging. The default level is INFO.
-xml-schema-files	Specifies one or more XML schema files to use as the source for reading the schema. Use this option to generate a SQL file from an XML file that was created using write-schema-to-xml, rather than from a database connection.

Description

Specify one of these pairs of options to connect to a data source with this command:

Use both -client-bind-address and -client-port to connect to a GemFire XD cluster as a thin client and perform the command.
Use both mcast-port and -mcast-address, or use the -locators property to connect to a GemFire XD cluster as a peer client and perform the command.
Use both -url and -driver-class to connect to a datasource using a JDBC URL and driver. You can use this option to connect to a data source other than GemFire XD.

Examples

Note: See also Export, Alter, and Import a Database Schema Using GFXD for a full example of migrating a third-party database to GemFire XD.

This command connects using a system user account a GemFire XD locator running on localhost:1527, and backups up all database objects (using GemFire XD-specific extensions) to the file, db-schema.sql:

gfxd write-schema-to-sql -user=serveradmin -password=serverpassword -export-all 
-file=db-schema.sql -client-bind-address=localhost -client-port=1527

Note that the distributed system in the above example must have already defined the "serveradmin" system user and password. See Creating System Users and Starting GemFire XD Members for more information.

This command uses MySQL Connector/J to connect to a MySQL server running on the "myserver" host, and writes the "test" database schema to a file name db-schema.sql. The SQL file is formatted for use with GemFire XD:

gfxd write-schema-to-sql -file=db-schema.sql -to-database-type=gemfirexd
		  -url=jdbc:mysql://myserver/test -driver-class=com.mysql.jdbc.Driver