Data Replication with SQL Remote
   PART 4. Reference
     CHAPTER 14. Utilities and Options Reference       

The Database Extraction utility

You can access the remote database extraction utility in the following ways:

• From Sybase Central, for interactive use under Windows 95 or NT.

• From the system command line, using the ssxtract or dbxtract command-line utilities. This is useful for incorporating into batch or command files.

  ssxtract is the extraction utility for Adaptive Server Enterprise, dbxtract is the extraction utility for Adaptive Server Anywhere.

By default, the extraction utility runs at isolation level 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. Running at isolation level 3 may hamper others' turnaround time on the server because of the large number of locks required. It is recommended that you run the extraction utility when the server is not busy, or run it against a copy of the database (see Designing an efficient extraction procedure).

Objects owned by dbo 

The dbo user ID owns a set of Adaptive Server Enterprise-compatible system objects in an Adaptive Server Anywhere database.

For Adaptive Server Anywhere, the extraction utility does 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, and so these objects are preserved.

Adaptive Server Enterprise security issue 

If you extract an Adaptive Server Enterprise database, the resulting Adaptive Server Anywhere database has the default DBA user ID present. If you do not want this user ID present in the remote database, you need to explicitly REVOKE CONNECT for this user.

  Extracting a remote database in Sybase Central

Running the extraction utility from Sybase Central carries out the following tasks related to creating and synchronizing SQL Remote subscriptions:

• Creates a command file to build a remote database containing a copy of the data in a specified publication.

• Creates the necessary SQL Remote objects, such as message types, publisher and remote user IDs, publication and subscription, for the remote database to receive messages from and send messages to the consolidated database.

• Starts the subscription at both the consolidated and remote databases.

  To extract a remote database from a running database:

1. Connect to the database.

2. Right-click the database and click Extract Database in the popup menu.

3. Follow the instructions in the wizard.

  To extract a remote database from a database file or a running database as follows:

1. Open the Database Utilities folder in the left panel.

2. Double-click Extract a Database in the right panel.

3. Follow the instructions in the wizard.

     For full information on extracting a remote database in Sybase Central, see the Sybase Central online Help.

  The extraction command-line utility

Purpose 

To extract a remote Adaptive Server Anywhere database from a consolidated Adaptive Server Enterprise or Adaptive Server Anywhere database.

Syntax 

{ SSXTRACT | DBXTRACT } [ switches ] [ directory ] subscriber

Switch	Description
-an database	Creates a database file with the same settings as the database being unloaded and automatically reloads it.
-ac "keyword=value; ..."	Connect to the database specified in the connect string to do the reload.
-b	Do not start subscriptions
-c "keyword=value; ..."	Supply database connection parameters
-d	Unload data only
-f	Extract fully qualified publications
-l level	Perform all extraction operations at specified isolation level
-k	Close window on completion
-n	Extract schema definition only
-o file	Output messages to file
-p character	Escape character
-q	Operate quietly: do not print messages or show windows
-r file	Specify name of generated reload Interactive SQL command file (default "reload.sql")
-u	Unordered data
-v	Verbose messages
-x	Use external table loads
-xf	Exclude foreign keys
-xp	Exclude stored procedures
-xt	Exclude triggers
-xv	Exclude views
-y	Overwrite command file without confirmation
directory	The directory to which the files are written. This is not needed if you use -an or -ac
subscriber	The subscriber for whom the database is to be extracted.

Description 

ssxtract is the extraction utility for Adaptive Server Enterprise. It is run against a Adaptive Server Enterprise and creates a command file for a remote Adaptive Server Anywhere database.

dbxtract is the extraction utility for Adaptive Server Anywhere. It is run against an Adaptive Server Anywhere database and creates a command file for a remote Adaptive Server Anywhere database.

There is no extraction utility to create remote Adaptive Server Enterprise databases.

The command line extraction utility creates a command file and a set of associated data files. The command file can be run against a newly-initialized Adaptive Server Anywhere database to create the database objects and load the data for the remote database.

By default, the command file is named reload.sql.

SSXtract notes 

Not all Adaptive Server Enterprise objects have corresponding objects in Adaptive Server Anywhere. The ssxtract utility has the following limitations:

• Single database     All extracted objects must be in a single Adaptive Server Enterprise database.

• Passwords     The password for the extracted user IDs are the same as the user ID itself.

• Permissions     The extracted user ID is granted REMOTE DBA authority.

• Named constraints     These are extracted as Adaptive Server Anywhere CHECK constraints.

• System tables     The sp_populate_sql_anywhere SQL Remote procedure builds a set of Adaptive Server Anywhere system tables in TEMPDB from the Adaptive Server Enterprise system tables. The extracted schema comes from these temporary system tables.

     For more information about the command-line switches, see Extraction utility options.

  Extraction utility options

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 on the command line. 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 on the command line. 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.

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.

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

Output the data unordered (-u)     By default the data in each table is ordered by primary key. Unloads are quicker with the -u switch, 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.

Use external loads (-x)     In the reload script, the default is to use the LOAD TABLE statement to load the data into the database. If you choose to use external loads, the Interactive SQL INPUT statement is used instead. The LOAD TABLE statement is faster than INPUT.

INPUT takes the path of the data files relative to the client, while LOAD TABLE takes the path relative to the server.

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.

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.

Operate without confirming actions (-y)     Without this option, you are prompted to confirm the replacement of an existing command file.