Updating the Dump Sequence Number

Like dump database, quiesce database updates the dump sequence numbers if there have been unlogged writes. This prevents you from using an earlier database dump or external copy as an improper foundation for a dump sequence.

For example, in the warm standby method described below, archives are produced by dump database (D1), dump transaction (T1), quiesce database, dump transaction (T2), and dump transaction (T3):

Warm Standby Dump Sequence
Graphic describes a dump sequence in a warm standby. Image shows a primary server for which there are a number of dump and load databases. However, a user runs an unlogged transaction occurs (select into), after which you cannot run a dump transaction. The graphic shows a quiesce database operation occuring, after which you can run a dump transaction again.

Typically, in an environment with logged updates and no dump tran with truncate_only, you could load D1, T1, T2, and T3 in turn, bypassing any quiesce database hold. This approach is used in a warm standby situation, where succeeding database dumps on the primary server simplify media failure recovery scenarios. On the secondary, or standby server, which is used for decision-support systems, you may prefer continuous incremental applications of load transaction instead of interruptions from external copy operations.

However, if an unlogged operation occurs (for example, a select into, as happens above) after the dump transaction that produces T1, a subsequent dump transaction to archive is not allowed, and you must either create another dump of the database, or issue quiesce database for external copy and then make a new external copy of the database. Issuing either of these commands updates the dump sequence number and clears the mark that blocks the dump transaction to archive.

Whether or not you use the for external dump clause depends on how you want recovery to treat the quiescent database that would be marked as in quiesce.

quiesce database hold

If you issue quiesce database and do not use the for external dump clause, during the external copy operation that creates the secondary set of databases, the secondary server is not running, and recovery under -q does not see any copied database as “in quiesce.”

It recovers each server in the normal fashion during start-up recovery; it does not recover them as for load database as was previously described. Subsequently, any attempt to perform a load tran to any of these databases is disallowed with error 4306, "There was activity on database since last load ...", or with error 4305, "Specified file '%.*s' is out of sequence ..."

Whether or not there been unlogged activity in the primary database, the dump sequence number does not incremented by quiesce database hold, and the unlogged-writes bits are not cleared by quiesce database release.

If you attempt to run a query against a database that is quisced, SAP ASE issues error message 880:
Your query is blocked because it tried to write and database '%.*s' is in quiesce state. Your query will proceed after the DBA performs QUIESCE DATABASE RELEASE

The query is run once the database is no longer in a quiescent state.

quiesce database hold for external dump

When you issue quiesce database for external dump, the external copy of the database “remembers” that it was made during a quiescent interval, so that -q recovery can recover it, as happens for load database.

quiesce database release clears this information from the primary database. If unlogged writes have prevented dump tran to archive on the primary server, dump tran to archive is now enabled.

If unlogged writes have occurred since the previous dump database or quiesce database hold for external dump for any database in quiesce database’s list, the dump sequence number is updated by quiesce database hold for external dump, and the unlogged write information is cleared by quiesce database release. The updated sequence number causes load tran to fail if it is applied to a target other than the external copy created under the quiesce database that updated it. This resembles the behavior for dump database of a database with unlogged writes status.

Warning!   quiesce database for external dump clears the internal flag that prevents you from performing dump transaction to archive_device whether or not you actually make an external copy or perform a database dump. quiesce database has no way of knowing whether or not you have made an external copy. It is incumbent upon you to perform this duty. If you use quiesce database hold for external dump to effect a transient write protection rather than to actually perform a copy that serves as the foundation for a new dump sequence, and your application includes occasional unlogged writes, SAP ASE may allow you to create transaction log dumps that cannot be used. In this situation, dump transaction to archive_device initially succeeds, but future load transaction commands may reject these archives because they are out of sequence.