SQL Remote User's Guide
Utilities and Options Reference
The Database Extraction utility
Create a database for reloading (-an) 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 (which should be entered all on one line) creates a new database file named asacopy.db and copies the schema and data for the field_user subscriber of asademo.db into it:
dbxtract -c "uid=dba;pwd=sql;dbf=asademo.db" -an asacopy.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.
Reload the data to an existing database (-ac) 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 (which should be entered all on one line) loads a copy of the data for the field_user subscriber into an existing database file named newdemo.db:
dbxtract -c "uid=dba;pwd=sql;dbf=asademo.db" -ac "uid=dba;pwd=sql;dbf=newdemo.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.
Do not start subscriptions automatically (-b) If this option is selected, 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.
Connection parameters (-c) A set of connection parameters, in a string.
dbxtract 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 (which should be typed on one line) extracts a database for remote user ID joe_remote from the asademo database running on the sample_server server, connecting as user ID DBA with password SQL. The data is unloaded into the c:\unload directory.
ssxtract -c "eng=sample_server;dbn=sademo; 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.
ssxtract connection parameters The following connection parameters are supported:
| Parameter | Description |
|---|---|
| UID | Login ID |
| PWD | Password |
| DBN | (optional) Database name. If this parameter is not supplied, the connection defaults to the default database for the login ID. |
| ENG | Adaptive Server Enterprise name. |
ssxtract cannot extract passwords. It sets passwords to be the same as the user ID.
Unload the data only (-d) If this option is selected, 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.
Use specified locale (-e) This option applies to Adaptive Server Enterprise only.
Specify Adaptive Server Enterprise locale information. The locale string has the following format:
"language_name,charset_name[,sort_order]"
By default, the Message Agent uses the default locale, which is defined in the file sybase\locales\locales.dat.
If language_name and charset_name are not supplied, the Message Agent obtains them from Adaptive Server Enterprise. If sort_order is not supplied, the Message Agent uses a binary sort order (sort by byte value).
Extract fully qualified publications (-f) 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 anyway.
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.
Internal unload, internal load (-ii) 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 takes the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the server.
Internal unload, external load (-ix) 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 takes the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the server.
Iteration count for views (-j) If there are nested views in the consolidated database, this option specifies the maximum number of iterations to use when extracting the views.
Perform extraction at a specified isolation level (-l) 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 (see Extraction utility options) 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.
Unload the schema definition only (-n) 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.
Output messages to file (-o) Outputs the messages from the extraction process to a file for later review.
Escape character (-p) The default escape character (\) can be replaced by another character using this option.
Operate quietly (-q) Display no messages except errors. This option is not available from other environments. This is available only from the command-line utility.
Reload filename (-r) 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.
Output the data unordered (-u) By default the data in each table is ordered by primary key. Unloads are quicker with the -u option, but loading the data into the remote database is slower.
Verbose mode (-v) The name of the table being unloaded and the number of rows unloaded are displayed. The SELECT statement used is also displayed.
Exclude foreign key definitions (-xf) You can use this if the remote database contains a subset of the consolidated database schema, and some foreign key references are not present in the remote database.
External unload, internal load (-xi) 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 at the client.
External operations takes the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the server.
Exclude stored procedure (-xp) Do not extract stored procedures from the database.
Exclude triggers (-xt) Do not extract triggers from the database.
Exclude views (-xv) Do not extract views from the database.
External unload, external load (-xx) 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 takes the path of the data files relative to the current working directory of dbxtract, while internal statements take the path relative to the server.
Operate without confirming actions (-y) Without this option, you are prompted to confirm the replacement of an existing command file.