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.

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.