sqlf write-data-to-db

Inserts data into a database using one or more data XML files (created with sqlf write-data-to-xml), and having the database schema defined in one or more schema XML files (created with sqlf write-schema-to-xml). This command is generally used with a SQLFire cluster to export table data, but it can also be used with other JDBC datasources.

Syntax


To insert all table data from one or more data XML files using the schema specified in one or more schema XML files, use the syntax:

sqlf write-data-to-db -files=<path,path,...> -schema-files=<path,path,...>
     [-alter-identity-columns]
     [-batch-size=<size>]
     [-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>]
     [-ensure-fk-order=<true | false>]
     [-extra-conn-props=<properties>] 
     [-help] [-locators=<addresses>]
     [-mcast-address=<address>]
     [-mcast-port=<port>]
     [-password[=<password>]]
     [-schema-pattern=<pattern>]
     [-url=<url>]
     [-user=<username>]
     [-verbose=<level>]

This table describes options for the sqlf write-data-to-db command. Default values are used if you do not specify an option.

Option Description
-files

The full path to one or more data XML files that contain the data to insert. Use a comma-separated list to specify multiple files. This argument is required.

See sqlf write-data-to-xml.

-alter-identity-columns

This option is provided for tables that have GENERATED ALWAYS identity columns, which prohibit manually inserting identity values even during data import. If one or more tables contains existing GENERATED ALWAYS identity values that you want to preserve, specify -alter-identity-columns with both the sqlf write-schema-to-db and sqlf write-data-to-db commands.

When you include this option with sqlf write-schema-to-db, SQLFire changes an existing GENERATED ALWAYS identity column to a non-identity column. This enables you to import existing data values for the column.

When you include this option with sqlf write-data-to-db, SQLFire alters the column to an identity column (GENERATED ALWAYS AS IDENTITY) after the final column value is imported. Identity values are then automatically generated for new rows, and SQLFire ensures that all new identity values are greater than the last imported value.

As an alternative to using these options, SQLFire supports GENERATED BY DEFAULT identity columns that permit insertion of the identity value. See Identity Columns in the CREATE TABLE reference page.

-schema-files The full path to one or more schema XML files to use when inserting the data. Use a comma-separated list to specify multiple files. This argument is required.

See sqlf write-schema-to-xml.

-batch-size

Specifies the maximum number of insert statements to combine in a single batch. The default batch size is 1000 statements.

-bind-address The address to which this peer binds for receiving peer-to-peer messages. By default sqlf 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 sqlf writes. sqlf 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 SQLFire locator listens for client connections. The default is "localhost."

Use this option with -client-port to attach to a SQLFire cluster as a thin client and perform the command.

-client-port

The port on which a SQLFire locator listens for client connections. The default is 1527.

Use this option with -client-bind-address to attach to a SQLFire 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 sqlf 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, sqlfire, 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, sqlf 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.

-ensure-fk-order

Specifies whether the ordering of foreign keys is honored during the data import. Set this option to "false" if all of the referenced rows come before referencing rows in the data XML file. (Or, change the order for inserting rows to otherwise preserve the foreign key order.)

With the default value of "true," sqlf delays inserting referenced rows until referencing rows are first inserted. This process consumes memory reduces import performance, so large datasets should preserve foreign key ordering in the XML file when possible.

-extra-conn-props

A semicolon-separated list of properties to use when connecting to the datasource.

-help, --help

Display the help message for this sqlf command.

-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 sqlf 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 SQLFire 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 SQLFire 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, sqlf prompts you to enter a password from the console.

-schema-pattern

A string pattern that determines the schema(s) that sqlf writes. sqlf 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.

-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.

Description

See also sqlf write-data-to-xml and sqlf write-schema-to-xml.

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 SQLFire 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 SQLFire 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 SQLFire.

Examples

Note: See also Export, Alter, and Import a Database Schema Using SQLF for a full example of migrating a third-party database to vFabric SQLFire.
This command connects to a SQLFire network server running on localhost:1527, and inserts the data from data.xml:
sqlf write-data-to-db -files=data.xml -schema-files=db-schema.xml
This command connects to a SQLFire network server running on myserver:1234, and inserts data from the data1.xml and data2.xml files:
sqlf write-data-to-db -files=data1.xml,data2.xml -schema-files=db-schema.xml
     -client-bind-address=myserver -client-port=1234
This command connects as a peer client to a SQLFire system running on multicast port 1234, and inserts the data from data.xml:
sqlf write-data-to-db -files=data.xml -schema-files=db-schema.xml -mcast-port=1234
		  -extra-conn-props=host-data=false
		
This command uses MySQL Connector/J to connect to a MySQL server running on the "myserver" host, and inserts data from data.xml into the “test�? database:
sqlf write-data-to-db -files=data.xml -schema-files=db-schema.xml
		  -url=jdbc:mysql://myserver/test -driver-class=com.mysql.jdbc.Driver