FW Ken Rearick: repserver exception list/deleter

My good friend Ken Rearick wrote a rather clever little stored procedure for managing all the exceptions that are raised in the life of a Sybase Replication Server.  He posted this on sybase.public.rep-server a few days ago:

This is a rewriten rs_delexception script that will list out all exceptions and then step through deleting all of them. As this uses the same procedure for deleting the entries it does not cause any problems with integerity in the RSSD — Ken Rearick

IF OBJECT_ID(‘dbo.rs_del_all_exception’) IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.rs_del_all_exception
    IF OBJECT_ID(‘dbo.rs_del_all_exception’) IS NOT NULL
        PRINT ‘< << FAILED DROPPING PROCEDURE dbo.rs_del_all_exception >>> >>>’
    ELSE
        PRINT ‘< << DROPPED PROCEDURE dbo.rs_del_all_exception >>>’
END
go

create proc rs_del_all_exception
@xact int = NULL

as

declare @systran binary(8)
declare @cnt int, @err int
declare @ccnt char(8)
declare @cxact char(8)
declare @rsname varchar(30)
declare @cmdcount char(9)
declare @msg varchar(255)
declare @tab_name varchar(30)
declare @row_cnt int

set nocount on

/* find RS name */

select @rsname = charvalue
from   rs_config
where  optionname = "oserver"

/* Build temp table #tab1 */

create table #tab1
(    orig_site varchar(30),
    orig_db   varchar(30),
    orig_user varchar(30),
    orig_time datetime,
    error_site varchar(30),
    error_db varchar(30),
    log_time datetime,
    reccount int null,
    sys_trans_id binary(8),
    app_usr varchar(30)
)

insert into #tab1 (orig_site, orig_db,
           orig_user, orig_time,
           error_site, error_db,
           log_time, sys_trans_id,
           app_usr)
select
    orig_site,
    orig_db,
    orig_user,
    orig_time,
    error_site,
    error_db,
    log_time,
    sys_trans_id,
    app_usr
from    rs_exceptshdr exh

/* add logged command counts to table */

update #tab1
  set reccount = (select max(src_cmd_line)
                    from   rs_exceptscmd exc
                      where exc.sys_trans_id = #tab1.sys_trans_id
                    group by exc.sys_trans_id)
/* print summary */

begin
  select @cnt = count(sys_trans_id)
  from rs_exceptshdr

  select @ccnt = convert(char(8), @cnt)

  if (@cnt = 0)
  begin
    print " "
    /* 20500,"         There are 0 Logged Transactions." */
    exec rs_get_msg 20500, @msg output
    print @msg
    print " "
    return
  end

  print " "
  /* 20501,"         Summary of Logged Transactions on ‘%1!’", @rsname
*/
  exec rs_get_msg 20501, @msg output
  print @msg, @rsname
  print " "
  print " "

  if (convert(int, 0×0000100) = 65536)
  begin
    select
      "Xact ID" = convert(int, reverse(substring(sys_trans_id, 5, 8))),
      "Org Site" = substring(rtrim(orig_site)+"."+rtrim(orig_db), 1, 15),
      "Org User" = substring(orig_user, 1, 8),
      "Org Date " = convert(char(11), orig_time),
      "Dest Site" = substring(rtrim(error_site)+"."+rtrim(error_db), 1,
15),
      "# Recs/Xact" = reccount
    from #tab1
  end
  else
  begin
    select
      "Xact ID" = convert(int, substring(sys_trans_id, 5, 8)),
      "Org Site" = substring(rtrim(orig_site)+"."+rtrim(orig_db), 1, 15),
      "Org User" = substring(orig_user, 1, 8),
      "Org Date " = convert(char(11), orig_time),
      "Dest Site" = substring(rtrim(error_site)+"."+rtrim(error_db), 1,
15),
      "# Recs/Xact" = reccount
    from #tab1
  end

  print " "
  print " "
  /* 20502,"         To Delete a Specific Logged Xact., type
‘rs_delexception {Xact ID}’" */
  exec rs_get_msg 20502, @msg output
  print @msg
  print " "
end

select @row_cnt = 1

while (@row_cnt > 0)
begin

set rowcount 1

  select @systran = sys_trans_id from #tab1

  select @row_cnt = @@rowcount

  delete #tab1 where @systran = sys_trans_id

set rowcount 0

  if (@row_cnt = 0)
  begin
    print " "
    print "All exceptions deleted"
    print " "
    return
  end

  if (convert(int, 0×0000100) = 65536)
  begin
    select @cxact = convert(char(8), convert(int,
reverse(substring(@systran, 5, 8))))
  end
  else
  begin
    select @cxact =  convert(char(8), convert(int, substring(@systran, 5, 8)))
  end

  /* if logged transaction exists, delete it. */

  select @cmdcount = rtrim(convert(char(9), reccount))
  from   #tab1
  where  sys_trans_id = @systran

  print " "
  /* 20505," Deleting %1! Commands in Logged Transaction # %2! on ‘%3!’" */
  exec rs_get_msg 20505, @msg output
  print @msg, @cmdcount, @cxact, @rsname
  print " "
  print " "

  begin transaction

  delete  rs_systext
  from    rs_exceptscmd exc,
          rs_exceptshdr exh,
          rs_systext sys
  where   exc.sys_trans_id = exh.sys_trans_id
  and     exc.cmd_id = sys.parentid
  and     sys.texttype = "C"
  and     exh.sys_trans_id  =  @systran

  select @err = @@error

  if (@err != 0)
  begin
    select @tab_name = ‘rs_systext’
    /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
    exec rs_get_msg 20506, @msg output
    print @msg, @tab_name
    rollback transaction
  end
  else
  begin
    delete  rs_exceptscmd
    where   sys_trans_id  =  @systran

    select @err = @@error

    if (@err != 0)
    begin
      select @tab_name = ‘rs_exceptscmd’
      /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
      exec rs_get_msg 20506, @msg output
      print @msg, @tab_name
      rollback transaction
    end
    else
    begin
      delete  rs_exceptshdr
      where   sys_trans_id  =  @systran

      select @err = @@error

      if (@err != 0)
      begin
        select @tab_name = ‘rs_exceptshdr’
        /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
        exec rs_get_msg 20506, @msg output
        print @msg, @tab_name
        rollback transaction
      end
      else
      begin
        commit transaction

        select @err = @@error

        if (@err !=0)
        begin
          /* 20509,"Executing ‘commit transaction’  failed. Transaction
Rolled Back." */
          exec rs_get_msg 20509, @msg output
          print @msg
          rollback transaction
        end
        else
        begin
          print " "
          /* 20510,"         Logged Transaction # %1! Successfully
Deleted. Truncate RSSD Transaction Log if Necessary." */
          exec rs_get_msg 20510, @msg output
          print @msg, @cxact
          print " "
        end
      end
    end
  end
end
go

EXEC sp_procxmode ‘dbo.rs_del_all_exception’,'unchained’
go

IF OBJECT_ID(‘dbo.rs_del_all_exception’) IS NOT NULL
    PRINT ‘< << CREATED PROCEDURE dbo.rs_del_all_exception >>>’
ELSE
    PRINT ‘< << FAILED CREATING PROCEDURE dbo.rs_del_all_exception >>>’
go

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *