SAP Sybase Replication Server ERROR -99999 Severity 5 Values exceed buffer length – SOLVED

Running SAP Sybase SAP SybaseReplication Server has always been interesting and rather frustrating that in its fragility. Today’s lesson is not exactly that clear. Take the following error message:

ERROR #1027 DSI EXEC(104(1) repdb_svr.rep_db) - /dsiutil.c(390)
    Open Client Client-Library error: Error: -99999, Severity 5 -- 'Values exceed buffer length'.
ERROR #5215 DSI EXEC(104(1) repdb_svr.rep_db) - /dsiutil.c(393)
    The interface function 'SQLPrepare' returns FAIL for database 'repdb_svr.rep_db'. The errors are retryable. The DSI thread will restart automatically. See messages from the interface function for more information.

RANT: While Replication Server says it is retryable, it never actually retries.
It is for the DSI connection but which buffer?? Replication Server doesn’t list any “buffers” for the DSI explicitly. There are a myriad of caches for the DSI connection. In the error message I see two hints to narrow it down: “Values exceed” and “SQLPrepare”. The most likely cache candidates, to me, would be the batch size (dsi_cmd_batch_size) and the dynamic sql cache (dynamic_sql_cache_size).
A simple check would be to disable dynamic SQL and see if we get the same error message:

suspend connection to repdb_svr.rep_db
go
alter connection repdb_svr.rep_db set dynamic_sql to 'off'
go
resume connection to repdb_svr.rep_db
go

Within a few seconds, I received the same message, so that wasn’t the culprit. Before we do anything else, let’s reenable the dynamic sql:

suspend connection to repdb_svr.rep_db
go
alter connection repdb_svr.rep_db set dynamic_sql to 'on'
go

That leaves the batch size as the most likely culprit. So let’s increase that and see what happens:

suspend connection to repdb_svr.rep_db
go
admin who, dsi, repdb_svr, rep_db
go
--  record Cmd_batch_size (default is 8192
--  Increase dsi_cmd_batch_size 
alter connection repdb_svr.rep_db set dsi_cmd_batch_size to '32768'
go
resume connection to repdb_svr.rep_db
go

The error message did not reoccur and I see replication moving by monitoring the admin,sqm in rep server and the rs_lastcommit table in the replicate database to ensure we’re moving.

You may ask what changed that would require increasing the batch size. Well, a very large transaction that was trying to insert data that already existed but since not all the data already existed, we needed to change INSERT into DELETE followed by INSERT:

alter connection to repdb_svr.rep_db set dsi_command_convert to 'i2di'

Why would that cause it to go boom? Well, the dsi_command_convert is applied AFTER replication server slices and dices the transactions into batches.

Share Button

SAP Sybase Replication Server 15.7.1 SP100 manuals fixed to work with tablet/phone PDF readers like Aldiko

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for Sybase Replication Server, I’m unable to load the manuals for say v15.7.1 SP100 as they have the same Title/Author data.  I used the same method from SAP Sybase IQ 15.4 manuals fixed to work with tablet/phone PDF readers like Aldiko

Here are the Replication Server 15.7.1 SP100 manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on sybooks with the exception of the PDF fields I mentioned previously.

No copyright infringement is intended. SAP/Sybase, please feel free to take these and host them.

Replication Server 15.7.1 SP100 – Quick Start Guide for SAP HANA Database – Sybase, Inc_

Replication Server 15.7.1 SP100 – Administration Guide Volume 1 – Sybase, Inc_

Replication Server 15.7.1 SP100 – Release Bulletin for Unix and Linux – Sybase, Inc_

Replication Server 15.7.1 SP100 – Reference Manual – Sybase, Inc_

Replication Server 15.7.1 SP100 – ASE-to-ASE Replication Quick Start Guide – Sybase, Inc_

Replication Server 15.7.1 SP100 – Administration Guide Volume 2 – Sybase, Inc_

Replication Server 15.7.1 SP100 – System Table Diagram – Sybase, Inc_

Replication Server 15.7.1 SP100 – Configuration Guide for Unix – Sybase, Inc_

Replication Server 15.7.1 SP100 – Troubleshooting Guide – Sybase, Inc_

Replication Server 15.7.1 SP100 – Release Bulletin for Windows – Sybase, Inc_

Replication Server 15.7.1 SP100 – Installation Guide for Windows – Sybase, Inc_

Replication Server 15.7.1 SP100 – Design Guide – Sybase, Inc_

Replication Server 15.7.1 SP100 – Getting Started – Sybase, Inc_

Replication Server 15.7.1 SP100 – Configuration Guide for Windows – Sybase, Inc_

Replication Server 15.7.1 SP100 – Heterogeneous Replication Guide – Sybase, Inc_

Replication Server 15.7.1 SP100 – Installation Guide for Unix – Sybase, Inc_

Replication Server 15.7.1 SP100 – New Features Guide – Sybase, Inc_

Share Button

What is with the ISUG Enhancements versus Sybase CR/Bugs on the sybase-product-futures mailing list?

In the MSA and System transactions performedby maint user thread on the sybase-product-futures mailing list, Jeff Tallman mentions thatInternational Sybase Users Group someone needs to create an ISUG enhancement or a Sybase change request (CR).  Mike Harrold let everyone know that even through creating a Sybase CR is put in the Sybase engineering queue, it doesn’t mean that an engineer will actually look at it let alone have product management approve it to be implemented.

Jeff hits the nail on the head.  You have a voice with the ISUG enhancements process. Use it.  And yes, you have to be paid member to vote. Sorry, but we are a non-profit and your membership fees keep us running!  You can vote as a Basic Member – you do not need to join at the higher membership levels (but you should so you can read all the wonderful articles Jeff writes for us).  The more votes, the easier it is for engineering to justify spending staff resources on it when they go to management.  Encourage all of your peers to join and vote as well.  If you work for a business with several Sybase users, convince your boss to pay for a corporate membership and get all your colleagues to vote as well. 🙂
Sybase
Regards,

Mike Harrold
Executive Director

and

Having an “internal enhancement” (aka a CR) doesn’t mean it gets implemented.  It means there’s a CR for it.  It might be a great idea, but without backing (and a user request + an engineer doesn’t mean it has backing; look how long it took for UDFs, mathematical functions, etc.) it doesn’t get allocated any development resources.  Without development resources, it doesn’t get implemented.

Bottom line, a CR doesn’t mean it’s a “planned” feature.

Regards,

Mike Harrold
Executive Director
International Sybase User Group

I would recommend voting for the ISUG enhancements (this week).  I and several other ISUG Board members will be going through the ISUG enhancements this weekend to present a subset to Sybase product management at TechWave next month.

I haven’t seen many ISUG enhancements being requested lately or voted upon for that matter.  The higher the votes for a particular enhancement, the more likely Sybase will pick it up.

So please, everyone go vote on the ISUG enhancements! 🙂

Share Button

How to install and run Sybase Adaptive Server Enterprise 12.5.4 and 15.0x on Ubuntu Linux 8.04 JEOS (Hardy Heron) using chroot environment

Because Sybase has yet to address the GLIBC (LD_POINTER_GUARD) issue with any of their products on Linux, we are forced to find alternative ways of getting Sybase software to run on modern Linux distribution.  In this situation, we will be using the Ubuntu 8.04 Just Enough OS (Ubuntu JEOS) Linux distribution.

After installing Ubuntu 8.04 Server JEOS on the physical or virtual machine, download the Ubuntu 7.04 Server iso image.  Why Ubuntu 7.04?  Ubuntu 7.04 is the last Ubuntu release that has an old enough GLIBC that Sybase software will work with.

% sudo debootstrap feisty /home/sybase file:///media/cdrom0

  • Change the home directory of the sybase user to be /dbms/sybase by editing the /etc/passwd file

% sudo cp /etc/resolv.conf /home/sybase/etc/resolv.conf

% sudo cp /etc/hosts /home/sybase/etc/hosts

% sudo cp /etc/passwd /home/sybase/etc/passwd

% sudo cp /etc/shadow /home/sybase/etc/shadow

% sudo `cat /etc/apt/sources.list | sed -e ‘s/hardy/feisty/g’ > /home/sybase/etc/apt/sources.list`

% sudo mount –bind /dev /home/sybase/dev

% sudo mount –bind /proc /home/sybase/proc

% sudo mount -t devpts none /home/sybase/dev/pts

% sudo mount –bind /tmp /home/sybase/tmp

% sudo aptitude update

% sudo aptitude upgrade

% sudo aptitude install locales dialog wget debconf devscripts gnupg

% sudo aptitude install libstdc++5 libstdc++6 libaio

  • If you’re installing v12.5x software, you will need to install an even older copy of libstdc++ (not needed for v15 or higher software):

% aptitude install libstdc++2.10-glibc2.2

  • Install Sybase software into /dbms/sybase (as root in the chroot environment which you should still be in)

% sudo cp /dbms/sybase/SYBASE.sh /dbms/sybase/SYBASE.env

% sudo echo DSQUERY=MYASE >> /dbms/sybase/.SYBASE.env

% sudo echo sa_password > /dbms/sybase/.sapwd

% sudo chmod go-rwx /dbms/sybase/.sapwd

% sudo chmod u-wx /dbms/sybase/.sapwd

% sudo chown -R sybase /dbms/sybase

  • Install the sybase_chroot script into /usr/local/bin and make it executable by root
  • Install the sybase_ase rc script into /etc/init.d and make it executable by root
  • Exit the chroot environment by typing ‘exit’
  • create a symbolic link outside of the chroot environment to make /home/sybase/dbms appear as /dbms.  This will allow you to access the Sybase software, notably OpenClient, as a normal user outside of the chroot environment.

% sudo ln -s /home/sybase/dbms /dbms

You are now able to start Sybase ASE using sudo /etc/init.d/sybase_ase start and stop Sybase ASE using sudo /etc/init.d/sybase_ase stop.  Starting Sybase Replication Server, OpenServer, or similar Sybase software is simply a matter of copying the /etc/init.d/sybase_ase and tweaking the script copy.

If you feel this is way complicated and Sybase should just fix their software, let them know.  Please point them to this page and refer to CR455393.

UPDATE: The GLIBC issue is fixed in v15.0.2 esd 4.  It doesn’t seem to be working for everyone though.  More information is needed about those systems where esd 4 doesn’t resolve the matter.  No fix is available for 12.5x from Sybase.

UPDATE: Keep in mind, that the Sybase ASE 15.0.2 esd 4 patch is available only to Sybase customers with a current maintenance contract with Sybase.

Share Button

Sybase Sysam License Type Codes

Yuval Malchi over on the ISUG sybase-l mailing list provided the following codes for Sybase’s Sysam (FlexLM):

License Type What the License Type Represents
SS Standalone Seat
DT Development and Testing
MB Mainframe Base
MU Millions of Service Units
CP CPU License
FL Floating License
IC Internet Access License
AP Application License
SR Server License
CL Cluster License
ST Networked Seat
CU Concurrent User License
SF Standby CPU License
SV Standby Server License
SC Standby Concurrent User
OT Other

The following License Types are only available for specific Partner contracts with OEM or embedded license arrangements:

License Type What the License Type Represents
AC Application Deployment CPU
AR Application Deployment Server
AS Application Deployment Seat
AU Application Deployment Concurrent User
AO Application Deployment Other
BC Application Deployment Standby CPU
BR Application Deployment Standby Server
BU Application Deployment Standby Concurrent User
Share Button

Replication Server Exception List Deleter (updated)

As you may remember, my good friend Ken Rearick created an excellent stored procedure, rs_del_all_exception, that safely clears out old exceptions from the RSSD database. Ken has just sent an updated version to me. B-)

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

-- rs_del_exception [xactid] [,xactid]
-- no transaction id just list exceptions
-- single xactid delete that xact
-- range of xactid's delete the full range of xact

create proc rs_del_exception
@xacts int = NULL,
@xacte 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, 0x0000100) = 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_del_exception {Xact ID}'" */
  exec rs_get_msg 20502, @msg output
  print @msg
  print " "
end

if (@xacts = null)
  return 0
  
select @row_cnt = 1

while (@row_cnt > 0)
begin

set rowcount 1

  if (@xacte = null)
  begin
    if (convert(int, 0x0000100) = 65536)
      select @systran = sys_trans_id from #tab1 where convert(int, reverse(substring(sys_trans_id, 5, 8))) = @xacts
    else
      select @systran = sys_trans_id from #tab1 where convert(int, substring(sys_trans_id, 5, 8)) = @xacts
  end
  else
  begin
    if (convert(int, 0x0000100) = 65536)
      select @systran = sys_trans_id from #tab1 where convert(int, reverse(substring(sys_trans_id, 5, 8))) between @xacts and @xacte
    else
      select @systran = sys_trans_id from #tab1 where convert(int, substring(sys_trans_id, 5, 8)) between @xacts and @xacte
  end
  
  select @row_cnt = @@rowcount

  delete #tab1 where @systran = sys_trans_id

  set rowcount 0

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

  if (convert(int, 0x0000100) = 65536)
    select @cxact = convert(char(8), convert(int, reverse(substring(@systran, 5, 8))))
  else
    select @cxact =  convert(char(8), convert(int, substring(@systran, 5, 8)))

  /* 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

  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_exception','unchained'
go
IF OBJECT_ID('dbo.rs_del_exception') IS NOT NULL
    PRINT '< << CREATED PROCEDURE dbo.rs_del_exception >>>'
ELSE
    PRINT '< << FAILED CREATING PROCEDURE dbo.rs_del_exception >>>'
go

Download: rs_del_exception stored procedure

Share Button

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

Sybase Replication Server: ignoring duplicate keys

Sybase’s Replication Server allows you to replicate data entry from one database into another (there can be more than one replicate database).  They don’t necessarily have to be even from the same vendor.

Duplicate rows will occur when an application inserts data into the primary and replicate database(s), if the data being entered in a replicated table.  Replication Server’s DSI connection will stop saying that it has detected a duplicate key and requires a DBA to tell it what to do.  If this duplicate key can be ignored, then the DBA will skip the transaction, which will make a note of the transaction and will skip it (go on to the next transaction).

resume connection to MYSERVER.MYDB skip transaction

The problem with this approach is that if there are a lot of duplicate keys, not only could you be sitting for a while skipping the transactions, you run the risk of skipping a transaction that isn’t a duplicate key.  Say if someone deleted the table on the replicate database..  You could easily make a mess of things if you arbitrarily skip transactions.

Replication Server has a feature called error classes that you can define the course of action if an error occurs with a DSI connection.  The only real issue is that the lowest level of granularity is at the DSI connection level and the highest is all insert dbms type (i.e. ASE) replicated systems.  To create an error class:

create error class ASEallowdupsErrorClass

The error classes can be inherited so if you wanted an error class to ignore duplicate keys and another to stop replication on a duplicate key, you would do something like so:

RSSD> rs_init_erroractions ASEallowdupsErrorClass, rs_sqlserver_error_class

Sybase ASE’s error number for a duplicate key is 2601, but ASE will also raise the 3621 (aborted transaction) error.  We need to set the error class ASEallowdupsErrorClass to ignore duplicate keys:

assign action ignore for ASEallowdupsErrorClass to 2601
assign action ignore for ASEallowdupsErrorClass to 3621

Now that we’ve created the error class and set it to ignore duplicates, we need to do two last things:

  1. alter the DSI connections to use the new error class
  2. suspend and then resume the DSI connections for the DSIs to use the new error class
alter connection to MYSERVER.MYDB
set error class to ASEallowdupsErrorClass
go
suspend connection to MYSERVER.MYDB
go
resume connection to MYSERVER.MYDB
go

Generally, applications should not be performing data entry of the same data across the replicated databases as Replication Server is made for it.

Share Button

Sybase TechWave 2006: Replication Server

I’m in Las Vegas, Nevada for the annual Sybase TechWave Conference at Caesar’s Palace.  The main entertainment will be a private showing of Penn & Teller.  Woohoo!

This year, I’m primarily focusing on Replication Server.  Increasing the performance is of keen interest to me as well as increasing stability of the replication.  There are a number of outstanding issues with Replication Server that I’m hoping to address.  The main one is an issue with firewalls.

This is the environment (very common in large corporations with segmented networks):

ASE (repAgent) tcp/ip connection <-> firewall <-> tcp/ip connection (repagent descriptor) RepServer -> ….

After a period of inactivity on the tcp/ip connection is closed by the firewall

ASE (repAgent) tcp/ip connection <X> firewall X> tcp/ip connection <X> (repagent descriptor) RepServer -> ….

The tcp/ip connection is closed, which will notify both the operating systems containing the primary ASE and replication server.

On the primary Sybase ASE side:

  • os notifies ASE of the disconnected connection. this is mostncommonly reported as a 1608 error (client connection expectedly disappeared).
  • RepAgent is notified within ASE and attempts to reconnect

On the Replication server side:

  • os notifies RepServer of the disconnected connection
  • RepServer either doesn’t handle the message from the os or doesn’t release the repagent descriptor correctly.

When the RepAgent attempts to connect to the Replication Server:

  • RepAgent connection is denied due to Replication Server says that the RepAgent is in the process of disconnecting. So it *appears* to be handling the message from the os but not completely freeing the repagent descriptor for some reason.

This can be easily reproduced with the help of someone that can set up a firewall that will close connections due to both inactivity and maximum time allowed.

Using a heartbeat of say 30secs does reduce the occurances of this issue but there are mandated, from the security group, maximum times that *any* connection can be open regardless of activity. This partial workaround is already in place. Another partial workaround is to have repagent itself disconnect after 20 secs of inactivity but we still run into the maximum connection time limit.

As it is, I’m forced to restart the repserver every 18 to 24 hours. Regardless of how the connection is closed, repserver should release the repagent descriptor fully so that a reconnect from the repagent will go through.

Share Button