Sybase Replication Server - Practical Handbook, tips and tricks

Mode quiesced

RS> suspend log transfer from {data_server.database | all}
RS> admin quiesce_force_rsi
RS> admin quiesce_check

When the quiesced mode is successful, the message "Replication <RS> is quiesced" is displayed.

To remove the quiesced mode :

RS> resume log transfer from {dataserver.database | all}

Bulk remove exceptions in a RSSD Adaptive Server Enterprise database

The replication server engine is put in quiesced mode.

Tables rs_excepts% in the RSSD database are truncated :

RSSD> truncate table rs_exceptshdr
RSSD> truncate table rs_exceptslast
RSSD> truncate table rs_exceptscmd

The table rs_systext is purged :

RSSD> select * into #rs_systext
      from rs_systext
      where texttype != 'C'
RSSD> truncate table rs_systext
RSSD> insert into rs_systext
      select * from #rs_systext

Don’t forget to remove the quiesced mode after tables purge.

Listing and extracting SQL commands in an exception

To list the exceptions in the RSSD database :

RSSD> rs_helpexception

To display the details of an exception XactID :

RSSD> rs_helpexception XActID

To retrieve the SQL commands in an exception XactID:

• Create the stored procedure sp_dba_dumpexception which returns the columns XActID, sequence and textval for an XActID exception given as a parameter.
• Create the proxy table v_dba_dumpexception with the stored procedure sp_dba_dumexception as its data source.

RSSD> create procedure sp_dba_dumpexception
                      @xactid int=NULL
as
select
   @xactid,
   sequence,
   textval
from rs_systext,
     rs_exceptscmd
where cmd_id = parentid
      and texttype='C'
      and cmd_type='L'
      and
(case (select convert(int, 0x0000100))
  when 65536 then
    convert(int,reverse(substring(sys_trans_id,5,8)))
  else
    convert(int,substring(sys_trans_id, 5, 8))
 end
) = @xactid
order by src_cmd_line,sequence
go

create existing table v_dba_dumpexception (
   xactid int not null,
   sequence int not null,
   textval varchar(255) not null
)
external procedure at
'loopback.<RSSD>.dbo.sp_dba_dump

• Create a view v_xactid querying the view v_dba_dumpexception for the XActID exception to be fetched.

RSSD> create view v_xactid as
       select * from v_dba_exception
       where xactid=<XActID>

• Retrieve the data from the view v_xactid in a flat file using the bcp utility. The specified column separator TSEP should not exist in the SQL commands of the exception.

UX> bcp <RSSD>..v_xactid out v_xactid_tmp.sql –Usa
     –P<password> -S<RSSD_SERVERNAME> -t'TSEP' -c

• Use the awk program below to concatenate the SQL commands splitted in several sequences.

UX> cat v_xactid_tmp.sql | awk -F"TSEP" 'BEGIN
     { vTextVal="" } \
     { if ($2==1) { print vTextVal; vTextVal=$3; } else
     { vTextVal = vTextVal$3 } } \
     END { print vTextVal; }' > v_xactid.sql

Moving partitions without changing logical names

The replication is first put in quiesced mode and then switched off with the shutdown command.

RS> shutdown

Partitions are physically moved.

The new partition paths are modified in the RSSD database by directly updating the table rs_diskpartitions :

RSSD> update rs_diskpartitions
      set name='<nouveau chemin>'
      where logical_name='<nom logique de la partition>'

The replication server is restarted and the quiesced mode removed.

Assigning a custom error class to a connection (DirectConnect for MSSQL etc…)

Create the error class custom_errorclass :

RS> create error class <custom_erroclass>

The new error class custom_errorclass inherits from a parent error class to predefine actions on errors :

RSSD> rs_init_erroractions <custom_erroclass>,
                           <parent_errorclass>

RSSD> rs_init_erroractions dcmssql_error_class,
                           rs_sqlserver_error_class

The class dcmssql_error_class inherits from the system class rs_sqlserver_error_class in the above example.

Assign a specific action for an error number encountered on a target database :

RS> assign action { ignore | warn | retry_log |
                    log | retry_stop | stop_replication }
                    for error_class
                    to data_server_error [, data_server_error ]

RS> assign action retry_stop for
            dcmssql_error_class to 30291

Apply the custom error class custom_errorclass to a target database :

RS> alter connection to <dataserver>.<database>
      set error class to <custom_errorclass>

The connection to the target is then suspended and resumed :

RS> suspend connection to <dataserver>.<database>
RS> resume connection to <dataserver>.<database>

Overriding the function rs_usedb with a function string

Create the custom function class custom_function_class specifying the parent function class for inheritance :

RS> create function string class
    <custom_function_class> set parent to
    <parent_function_class>

RS> create function string class
    rtds_function_class set parent to
    rs_default_function_class

Override the function rs_usedb :

RS> create function string rs_usedb for
    <custom_function_class>
     with overwrite output language
      ′use ?rs_destination_db!sys_raw?;
         Commandes TSQL de surcharge
      ′

RS> create function string rs_usedb for
    rtds_function_class
      with overwrite output language
       ′use ?rs_destination_db!sys_raw?;
          set transactional messaging FULL;
       ′
          

Apply the new function class custom_function_class to the target database :

RS> alter connection to <dataserver>.<database>
   set function string class <custom_function_class>

The connection to the target is then suspended and resumed :

RS> suspend connection to <dataserver>.<database>
RS> resume connection to <dataserver>.<database>