Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Remote » SQL Remote Utilities and Options Reference » Database Extraction utility

Extracting a remote database in Sybase Central Next Page

Extraction utility


To extract a remote SQL Anywhere database from a consolidated SQL Anywhere database.

Syntax

dbxtract [ options ] [ directory ] subscriber

Extraction utility options
Option Description
@data

Read in options from a configuration file. See @data 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 Using configuration files.

If you want 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; ..."

Connect to the database specified in the connect 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:\newdata.db:

dbxtract -c "UID=DBA;PWD=sql;DBF=c:\olddata.db" 
-ac "UID=DBA;PWD=sql;DBF=c:\newdata.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 filenameSpecify the log file name for the new database.
-an database

Create a database file with the same settings as the database being unloaded and automatically reload 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:\mydatacopy.db and copies the schema and data for the field_user subscriber of c:\mydata.db into it:

dbxtract -c "UID=DBA;PWD=sql;DBF=c:\mydata.db" 
-an c:\mydatacopy.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.

-b

Do 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; ..."

Supply database connection parameters, in a string.

  • connection parameters    The user ID should have DBA authority to ensure that the user has permissions 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 server, connecting as user ID DBA with password sql. The data is unloaded into the c:\extract directory.

    dbxtract -c "ENG=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

Unload 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 for use when a remote database already exists with the proper schema, and needs only to be filled with data.

-ea alg

Specify 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-approved algorithm. AES_FIPS uses a separate library and is not compatible with AES. Algorithm names are case insensitive. If you specify the -ea option, you must also specify -ep or -ek. See Strong encryption.

Separately licensed component required

ECC encryption and FIPS-certified encryption require a separate license. All strong encryption technologies are subject to export regulations.

See Separately licensed components.

-ek key

Specify 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    

Protect your key! Be sure to store a copy of your key in a safe location. A lost key will result in a completely inaccessible database, from which there is no recovery.

-ep

Prompt for the encryption key for the new database. This option specifies that you want to create a strongly encrypted database by inputting the encryption key in a dialog. 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 Strong encryption.

-f

Extract fully qualified publications. In most cases, 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.

-ii

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

-ix

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

-l level

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

-n

Extract the schema definition only. With this definition, none of the data is unloaded. The reload file contains SQL statements to build the database structure only. You can use the SYNCHRONIZE SUBSCRIPTION statement to load the data over the messaging system. Publications, subscriptions, PUBLISH, and SUBSCRIBE permissions are part of the schema.

-o file

Output messages to a file.

-p character

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

-q

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

This option is not available from other environments. This is available only from the command line utility.

-r file

Specify the name of the generated reload Interactive SQL command file.

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

-u

Do 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

Display Verbose messages. The name of the table being unloaded and the number of rows unloaded appear, as well as the SELECT statement used.

-xf

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

-xhExclude procedure hooks.
-xi

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

-xp

Do not extract stored procedures from the database.

-xt

Do not extract triggers from the database.

-xv

Do not extract views from the database.

-xx

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

-y

Overwrite the command file without confirmation. Without this option, you are prompted to confirm the replacement of an existing command file.

directory Specify the directory the files are written to. This is not needed if you specify -an or -ac .
subscriber Specify subscriber for whom the database is being extracted.
Remarks

The Extraction utility creates a command file and a set of associated data files. The command 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 command 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.