Extraction utility (dbxtract)

dbxtract [ options ] [ directory ] subscriber

Option Description

@data

Reads in options from a configuration file. See @data database server option.

Use this option to read in options from the specified environment variable or configuration file. If both exist with the same name, the environment variable is used. See Configuration files.

To protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file. See File Hiding utility (dbfhide).

-ac "keyword=value;..."

Connects to the database specified in the connection string to do the reload.

You can combine the operation of unloading a database and reloading the results into an existing database using this option.

For example, the following command (entered all on one line) loads a copy of the data for the field_user subscriber into an existing database file named c:\field.db User DBA must have the SERVER OPERATOR system privilege.

dbxtract -c "UID=DBA;PWD=sql;DBF=c:\cons.db" -ac "UID=DBA;PWD=sql;DBF=c:\field.db" field_user

If you use this option, no copy of the data is created on disk, so you do not specify an unload directory in the command. This provides greater security for your data, but at some cost for performance.

-al filename Specifies the transaction log file name for the new database if using the -an option.

-an database

Creates a database file with the same settings as the database being extracted and automatically reloads it.

You can combine the operations of unloading a database, creating a new database, and loading the data using this option.

For example, the following command (entered all on one line) creates a new database file named c:\field.db and copies the schema and data for the field_user subscriber of c:\cons.db into it. User DBA must have the SERVER OPERATOR system privilege.

dbxtract -c "UID=DBA;PWD=sql;DBF=c:\cons.db" -an c:\field.db field_user

If you use this option, no copy of the data is created on disk, so you do not specify an unload directory in the command. This provides greater security for your data, but at some cost for performance.

-ap size [ k ] Sets the page size of the new database. This option is ignored unless -an is used. The page size for a database can be (in bytes) 2048, 4096, 8192, 16384, or 32768, with the default being the page size of the original database. Use k to specify units of kilobytes (for example, -ap 4k). If there are already databases running on the database server, the server's page size (set with the -gp option) must be large enough to handle the new page size. See -gp database server option.

-b Does not start subscriptions. If this option is specified, subscriptions at the consolidated database (for the remote database) and at the remote database (for the consolidated database) must be started explicitly using the START SUBSCRIPTION statement for replication to begin. See START SUBSCRIPTION statement [SQL Remote].

-c "keyword=value;..."

Specifies database connection parameters, in a string.

The user ID should have the SYS_RUN_REPLICATION_ROLE system role to ensure that the user has privileges on all the tables in the database.

For example, the following statement (entered all on one line) extracts a database for remote user ID joe_remote from the sample database running on the sample_server database server, connecting as user ID DBA with password sql. User DBA must have the SERVER OPERATOR system privilege. The data is unloaded into the c:\extract directory.

dbxtract -c "Server=sample_server;DBN=demo;UID=DBA;PWD=sql" c:\extract joe_remote

If connection parameters are not specified, connection parameters from the SQLCONNECT environment variable are used, if set.

-d Extracts data only. If this option is specified, the schema definition is not unloaded and publications and subscriptions are not created at the remote database. This option is used when a remote database already exists with the proper schema, and only needs to be filled with data.

-ea alg

Specifies the encryption algorithm for the new database. This option allows you to choose a strong encryption algorithm to encrypt your new database. You can choose either AES (the default) or AES_FIPS for the FIPS-certified algorithm. AES_FIPS uses a separate library and is not compatible with AES.

For greater security, specify AES or AES256 for 128-bit or 256-bit strong encryption, respectively. Specify AES_FIPS or AES256_FIPS for 128-bit or 256-bit FIPS-certified encryption, respectively. For strong encryption, you must also specify the -ek or -ep option. For more information about strong encryption, see Simple encryption and strong encryption.

To create a database that is not encrypted, specify -ea none, or do not include the -ea option (and do not specify -e, -et, -ep, or -et).

If you do not specify the -ea option, the default behavior is as follows:

-ea none, if -ek, -ep, or -et is not specified
-ea AES, if -ek or -ep is specified (with or without -et)
-ea simple, if -et is used without -ek or -ep

Algorithm names are case insensitive.

Remarque

Un composant sous licence distincte est requis.

Le cryptage certifié FIPS exige une licence distincte. Toutes les technologies de cryptage fort sont soumises à des réglementations d'exportation.

Voir Separately licensed components.

-ek key

Specifies the encryption key for the new database. This option allows you to create a strongly encrypted database by specifying an encryption key directly in the command. The algorithm used to encrypt the database is AES or AES_FIPS as specified by the -ea option. If you specify the -ek option without specifying -ea, the AES algorithm is used.

Caution

For strongly encrypted databases, be sure to store a copy of the key in a safe location. If you lose the encryption key, there is no way to access the data, even with the assistance of Technical Support. The database must be discarded and you must create a new database.

-ep

Prompts for the encryption key for the new database. This option specifies that you want to create a strongly encrypted database by typing the encryption key in a window. This provides an extra measure of security by never allowing the encryption key to be seen in clear text.

You must input the encryption key twice to confirm that it was entered correctly. If the keys don't match, the initialization fails. See Simple encryption and strong encryption.

-er

Removes encryption from encrypted tables during an unload procedure.

When extracting from a database that has table encryption enabled, you must specify either -er or -et to indicate whether the new database has table encryption enabled, otherwise you get an error when attempting to load the data into the new database.

The following command (entered all on one line) extracts a database (cons.db) that has encrypted tables, into a new database (field.db) that does not have table encryption enabled, removing encryption from any encrypted tables. User DBA must have the SERVER OPERATOR system privilege.

dbxtract -an c:\field.db -er -c "UID=DBA;PWD=sql;DBF=c:\cons.db;DBKEY=29bN8cj1z field_user"

-et

Enables database table encryption in the new database (-an or -ar must also be specified). If you specify the -et option without the -ea option, the AES algorithm is used. If you specify the -et option, you must also specify -ep or -ek. You can change the table encryption settings for the new database to be different from those of the database you are unloading.

When rebuilding a database that has table encryption enabled, you must specify either -er or -et to indicate whether the new database has table encryption enabled, otherwise you get an error when attempting to load the data into the new database.

The following example (entered all on one line) unloads a database (cons.db) that has tables encrypted with the simple encryption algorithm, into a new database (field.db) that has table encryption enabled, and uses AES_FIPS encryption with the key 34jh. User DBA must have the SERVER OPERATOR system privilege.

dbxtract -an c:\field.db -et -ea AES_FIPS -ek 34jh -c "UID=DBA;PWD=sql;DBF=c:\cons.db field_user"

-f

Extracts fully qualified publications. Usually you do not need to extract fully qualified publication definitions for the remote database, since it typically replicates all rows back to the consolidated database.

However, you may want fully qualified publications for multi-tier setups or for setups where the remote database has rows that are not in the consolidated database.

-g

Materialized views By default, materialized views defined as MANUAL REFRESH are not initialized after a reload. If you want these materialized views to be initialized as part of the reload process, specify the -g option. Specifying -g causes the database server to execute the sa_refresh_materialized_views system procedure. See sa_refresh_materialized_views system procedure.
When deciding whether to use the -g option, consider that initializing all materialized views may cause the reload process to take significantly longer to complete. However, not using the -g option means that the first query that attempts to use an uninitialized materialized view must wait while the database server initializes the view, which may cause an unexpected delay. If you do not use the -g option, you can also manually initialize materialized views after the reload completes. See Initializing a materialized view.
Text indexes By default, text indexes defined as MANUAL REFRESH are not initialized after a reload. If you want the text indexes initialized as part of the reload process, specify the -g option. Specifying -g causes the database server to execute the sa_refresh_text_indexes system procedure. See sa_refresh_text_indexes system procedure.

-ii

Performs an internal unload and internal reload. Using this option forces the reload script to use the internal UNLOAD and LOAD TABLE statements rather than the Interactive SQL OUTPUT and INPUT statements to unload and load data, respectively. This combination of operations is the default behavior.

External operations take the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the database server.

-ix

Performs an internal unload and external reload. Using this option forces the reload script to use the internal UNLOAD statement to unload data, and the Interactive SQL INPUT statement to load the data into the new database.

External operations take the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the database server.

-l level Performs all extraction operations at specified isolation level. The default setting is an isolation level of 0. If you are extracting a database from an active database server, you should run it at isolation level 3 to ensure that data in the extracted database is consistent with data on the database server. Increasing the isolation level may result in large numbers of locks being used by the Extraction utility (dbxtract), and may restrict database use by other users. See Extraction utility (dbxtract).

-n

Extracts the schema definition only. With this definition, none of the data is unloaded. The reload file contains SQL statements to build the database schema only. You can use the SYNCHRONIZE SUBSCRIPTION statement to load the data over the messaging system. See SYNCHRONIZE SUBSCRIPTION statement [SQL Remote]. Publications, subscriptions, PUBLISH, and SUBSCRIBE privileges are part of the schema. In this example, user DBA must have the SERVER OPERATOR system privilege.

dbxtract -c "UID=DBA;PWD=sql;DBF=c:\remote\cons\cons.db" -n "c:\remote\reload.sql" UserName

-nl

Extracts the structure (the same behavior as the -n option), but the resulting reload.sql file also includes LOAD TABLE or INPUT statements for each table. No user data is extracted when this option is used. When you specify -nl, you must also include a data directory so that the LOAD/INPUT statements can be generated, even though no files are written to the directory. This option allows you to generate a reload script without unloading data. You can extract the data by specifying -d. If a database contains a table whose data should not be unloaded, you can avoid unloading the data for that table by using dbxtract -d -e table-name.

-o filename Outputs messages to the specified log file.

-p character Specifies an escape character. The default escape character (\) can be replaced by another character using this option.

-q

Operates quietly: does not display messages or show windows. When this option is specified, -y must also be specified or the operation fails.

This option is available only for the command line utility.

-r file

Specifies the name of the generated reload Interactive SQL script file.

The default name for the reload script file is reload.sql in the current directory. You can specify a different file name with this option.

-u Does not order data during the unload. By default, the data in each table is ordered by primary key. Unloads are faster with the -u option, but loading the data into the remote database is slower.

-v Displays verbose messages. The name of the table being unloaded, the number of rows unloaded, and the SELECT statement used.

-xf Excludes foreign keys. You can use this option if the remote database contains a subset of the consolidated database schema, and some foreign key references are not present in the remote database.

-xh Excludes procedure hooks.

-xi

Performs an external unload and internal reload. The default behavior for unloading the database is to use the UNLOAD statement, which is executed by the database server. If you choose an external unload, dbxtract uses the OUTPUT statement instead. The OUTPUT statement is executed on the client.

External operations take the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the database server.

-xp Does not extract stored procedures from the database.

-xt Does not extract triggers from the database.

-xv Does not extract views from the database.

-xx

Performs an external unload and an external load. Use the OUTPUT statement to unload the data, and the INPUT statement to load the data into the new database.

The default unload behavior is to use the UNLOAD statement, and the default loading behavior is to use the LOAD TABLE statement. The internal UNLOAD and LOAD TABLE statements are faster than OUTPUT and INPUT.

External operations take the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the database server.

y Replace the existing SQL script file without confirmation.

directory Specifies the directory the files are written to. This is not needed if you specify -an or -ac.

subscriber Specifies the subscriber for whom the database is being extracted.

By default, the Extraction utility (dbxtract) runs at isolation level zero. If you are extracting a database from an active database server, you should run it at isolation level 3 to ensure that data in the extracted database is consistent with data on the database server. Running at isolation level 3 may hamper others' turnaround time on the database server because of the large number of locks required. It is recommended that you run the Extraction utility (dbxtract) when the database server is not busy, or run it against a copy of the database.

The Extraction utility (dbxtract) creates a SQL script file and a set of associated data files. The script file can be run against a newly-initialized database to create the database objects and load the data for the remote database.

By default, the SQL script file is named reload.sql.

If the remote user is a group, then all the user IDs that are members of that group are extracted. This allows multiple users on a remote database with different user IDs, without requiring a custom extraction process.

When using the Extraction utility (dbxtract) or the Extract Database Wizard with a version 10.0.0 or later database, the version of dbxtract used must match the version of the database server used to access the database. If an older version of dbxtract is used with a newer database server, or vice versa, an error is reported.

The Extraction utility (dbxtract) and Extract Database Wizard do not unload the objects created for the dbo user ID during database creation. Changes made to these objects, such as redefining a system procedure, are lost when the data is unloaded. Any objects created by the dbo user ID since the initialization of the database are unloaded by the Extraction utility (dbxtract), and so these objects are preserved.

To automatically extract a remote database:

Connect to the consolidated database as a user with the SYS_RUN_REPLICATION_ROLE system role.

Run dbxtract specifying the -ac option to extract to an existing database or the -an option to extract to a new database. You must have the SERVER OPERATOR system privilege.

If you specify the -an option, you must create an empty database before running the Extraction utility (dbxtract). For example, the following command creates an empty database named mydata.db.

dbinit -dba DBA,sql c:\remote\mydata.db

Run the following command to extract a new remote database from a consolidated database located at c:\consolidateddata.db. The new database is for the remote user named field_user and the new database is created at c:\remote\mydata.db. User DBA must have the SERVER OPERATOR system privilege:

dbxtract -c "UID=DBA;PWD=sql;DBF=c:\consolidateddata.db" -an c:\remote\mydata.db field_user

The new remote database, mydata.db, is created with the appropriate schema, remote users, publications, subscriptions, and triggers. By default, the data from the consolidated database is extracted into the remote databases and the subscriptions are started. However, the Extraction utility (dbxtract) does not start the SQL Remote Message Agent, so no messages are exchanged.