rs_dumptran

Initiates a coordinated transaction dump.

Examples

Example 1

Creates an rs_dumptran function string to execute a stored procedure named dumptran_proc. The stored procedure manages the dump devices and then executes the rs_update_lastcommit stored procedure, passing it the <rs_origin>, <rs_origin_qid>, <-rs_secondary_qid>, and <rs_origin_commit_time> parameters.

create function string rs_dumptran
 for sqlserver_derived_class
 output rpc
 'execute dumptran_proc
     ?rs_dump_dbname!sys?,
     ?rs_dump_label!sys?,
     ?rs_dump_timestamp!sys?,
     ?rs_dump_status!sys?,
     ?rs_destination_db!sys?,
     ?rs_origin!sys?,
     ?rs_origin_qid!sys?,
     ?rs_secondary_qid!sys?
     ?rs_origin_commit_time!sys?'

If the server crashes after the dump is complete but before the <rs_lastcommit> system table is updated, Replication Server restarts the backup.

Note

There is no guarantee that the dump and the rs_update_lastcommit procedure will be executed atomically, because Adaptive Server does not allow the dump command to be included in a transaction with other commands. If the <rs_lastcommit> system table is not updated successfully, an additional dump may be performed.

In the following sample text of the dumptran_proc stored procedure, the dump devices are hard-coded. In a production environment, it is better to manage them in a table:

create proc dumptran_proc
   @dump_dbname varchar(30),
   @dump_label varchar(30),
   @dump_timestamp varbinary(16),
   @dump_status int,
   @destination_dbname varchar(30),
   @origin int,
   @origin_qid binary(36),
   @secondary_qid binary(36),
   @origin_time datetime
as
   print 'Received a dump transaction command from Replication Server:'
   declare @message varchar(255)
   if @dump_status = 0
   begin
      select @message = 'dump transaction ' + @dump_dbname + '. Label= '''
         + @dump_label + '''' + '. Dest.db = ''' + @destination_dbname + ''''
   end
   else if @dump_status = 1
   begin
      select @message = 'dump transaction standby ' 
         + @dump_dbname + '. Label= ''' +
         @dump_label + '''' + '. Dest.db = ''' + @destination_dbname + ''''
   end
   print @message
   if @destination_dbname = 'pubs2'
   begin
      print 'issuing ''dump transaction pubs2.'''
      if @dump_status = 0
      begin
         dump transaction pubs2 to pubs2_dmplog
      end
      else if @dump_status = 1
      begin
         dump transaction pubs2 to pubs2_dmplog with standby_access
      end
      update dmp_count set d_count = d_count + 1
      exec pubs2.dbo.rs_update_lastcommit
         @origin, @origin_qid, @secondary_qid,
         @origin_time
   end
   else if @destination_dbname = 'pubs3'
   begin
      print 'issuing ''dump transaction pubs3.'''
      if @dump_status = 0
      begin
         dump transaction pubs3 to pubs3_dmplog
      end
      else if @dump_status = 1
      begin
         dump transaction pubs3 to pubs3_dmplog with standby_access
      end
      update dmp_count set d_count = d_count + 1
      exec pubs3.dbo.rs_update_lastcommit
         @origin, @origin_qid, @secondary_qid,
         @origin_time
   end

Example 2

Alters the rs_dumptran function string that you created in the first example to execute as a remote procedure call.

alter function string rs_dumptran
 for sqlserver_derived_class
 output rpc
 'execute dumptran_proc
     ?rs_dump_dbname!sys?,
     ?rs_dump_label!sys?,
     ?rs_dump_timestamp!sys?,
     ?rs_dump_status!sys?,
     ?rs_destination_db!sys?,
     ?rs_origin!sys?,
     ?rs_origin_qid!sys?,
     ?rs_secondary_qid!sys?,
     ?rs_origin_commit_time!sys?!'

Usage

Replication Server coordinates transaction dumps by inserting an rs_dumptran function call at the same place in the stream of transactions it distributes to all replicate Replication Servers.
rs_dumptran has function-string-class scope.

Note
Replication Server does not initialize or generate rs_dumptran function strings for the system-provided function-string classes. You must create a function string before using a coordinated dump with Adaptive Server.
Create an rs_dumptran function string at the Replication Server that is the primary site for the class.
The <rs_lastcommit> system table should be updated when the rs_dumptran function string executes so that a restarted Replication Server does not perform duplicate dumps. See "rs_commit" for information about <rs_lastcommit>.
To account for different dump devices at multiple replicate sites, create a stored procedure in each replicate database that performs a transaction dump, then write the rs_dumptran function string to execute the stored procedure.

Table 1: System Variables for rs_dumptran Function Strings
Variable Name	Datatype	Description
`<rs_destination_db>`	`<varchar(30)>`	Name of the database where a transaction was sent.
`<rs_dump_dbname>`	`<varchar(30)>`	The name of the database where the dump originated.
`<rs_dump_label>`	`<varchar(30)>`	Label information for the dump. For Adaptive Server, this variable contains a `<datetime>` value for the time the dump began.
`<rs_dump_status>`	`<int(4)>`	Dump status indicator: 0 – denotes that the dump transaction command does not contain the parameter with standby_access 1 – denotes that the dump transaction command contains the parameter with standby_access
`<rs_dump_timestamp>`	`<varbinary(16)>`	An Adaptive Server database timestamp taken when the dump was started at the origin. The variable is used for informational purposes only.
`<rs_origin>`	`<int(4)>`	ID of the originating database for a transaction.
`<rs_origin_commit_time>`	`<datetime>`	The time that a transaction was committed at the origin. Note If you execute select getdate() while ASE is still processing user database recovery, the returned value of select getdate() may be different from the value of rs_origin_begin_time.
`<rs_origin_qid>`	`<varbinary(36)>`	Origin queue ID of the first command in a transaction.
`<rs_secondary_qid>`	`<varbinary(36)>`	Queue ID of a transaction in a subscription materialization or dematerialization queue.