Sybase DC38133-01-0902-01 manual An efficient approach to extracting many databases

An efficient approach to extracting many databases

☞For performance tips for Adaptive Server Enterprise users using a subscription-listcolumn, see “Tuning extraction performance” on page 155 and “Tuning extraction performance for shared rows” on page 162 .

There are several potential causes of inefficiency in a large-scale extraction process:

♦The extraction utility extracts one database at a time, including the schema and data for each user. Commonly, many users share a common schema, and only the data differs. The brute force method of running the extraction utility for each user repeats large amounts of work unnecessarily. Extracting schema and data separately can help with this problem.

♦Running from Sybase Central, the extraction utility creates a new database for each user. If subscribers share a common schema, you could create a single database, with schema but no data, and copy the file.

♦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” on page 306 ) 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.

One approach that avoids these problems is as follows:

1.Make a copy of the consolidated database, and at the same time start the subscriptions from the live database. Messages will now start being sent to subscribers, even though they have no database and will not receive them yet.

To start several subscriptions within a single transaction, use the REMOTE RESET statement (Adaptive Server Anywhere ) or sp_remote procedure (Adaptive Server Enterprise).

2.Extract the remote databases from the copy of the database. As the database is a copy, there are no locking and concurrency problems. For a large number of remote databases, this process may take several days.

3.As each remote database is created, it is out of date, but its user can receive and apply messages that have been being sent from the live consolidated database, to bring themselves up to date.

This solution interferes with the production database only during the first step. The copy must be made at isolation level three if the database is in use,

194