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

Using Perl, Mason, mod_perl and DBI: Result sets

I’m new to Mason and am having trouble printing a simple html table pulling data from a table on a database.  I know this has to do with scope but there has to be an easier way other than putting the entire dbms call (including printing the table) within a perl code section.

One thing I have noticed is the apparent lack of any useful information of using Mason with DBI connections.

This is how you do it if you wanted a single file to connect to the dbms and then spit out the result set:

< %shared>
    my %serverDump;

    my $dbh = DBI->connect("dbi:Sybase:server=mydb", ‘info’, ‘infopwd’, { RaiseError => 1, PrintError => 1 } );
    $dbh->do("use sybase_dba");

    my $query = "select serverName, min(dumpdate) from dbLastBackup group by serverName";

    my $sth = $dbh->prepare($query);
    $sth->execute or die "Error: unable to run query! " . $dbh->errstr;
    
<table Border=1>
  % while (my $row = $sth->fetchrow_arrayref ) {

    <tr>
      <td>< % $row->[0] %></td><td>< % $row->[1] %></td>
    </tr>

  % }
</table>

Note, don’t forget to uncomment "PerlSetVar MasonErrorMode fatal".  For some reason, no errors were being sent to the browser. 🙁

 

Share Button

bit wise operations within ASE are still host dependent

On August 11th in the newsgroup sybase.public.ase.general, Tartampion raised the issue that ASE’s bitwise operations were not platform independent.

When we run the statement "select  0 | 0x00000020" on 2 of 
our servers, we obtain different results
1: sun Solaris
Sybase version: Adaptive Server Enterprise/12.5.3/EBF 13398 ESD#6 ONE-OFF/P/Sun_svr4/OS 5.8/ase1253/1947/32-bit/FBO/Tue Mar  7 04:50:16 2006

select 0 | 0x00000020
---
32

2: Linux: 
Sybase version:Adaptive Server Enterprise/12.5.3/EBF 13400
ESD#6 ONE-OFF/P/Linux Intel/Enterprise Linux/ase1253/1947/32-bit/OPT/Mon Mar  6 20:22:17 2006

select 0 | 0x00000020
-------------
536870912

This causes pb in our application behavior.

I am writing to see if this is a normal behavior or we need
to change configurations to obtain similar results.

All help is well appreciated.

Tartampion.

Sybase’s answer up to now has been that this is expected behavior and the following method should be used instead:
For safe conversions between hexadecimal and decimal use the platform independent inttohex() and hextoint():

select @@version
select  0 | 0x00000020, 0 | convert( int, 0x00000020 ), 0 | hextoint('0x00000020' )

The problem with Sybase’s response is that it is inconsistant with how Sybase pushes ASE as being the same running on any supported platform from the client application.

There is quite a bit of os/hardware specific code within ASE.  This just happens to be one such legacy chunk of code where the bitwise operation is performed in a os specific manner rather than an independent manner. Anthony Mandic, myself and others are pushing for Sybase to correct this legacy operation to be platform independent. No bug # (cr#) exists at this time but we should be receiving one within a few days.

Share Button

PowerDesigner 12 and Replication Server

The new version of Power Designer 12 looks promising.  I haven’t had a chance to use it yet but in the base product (no extra $$$), we are now able to create and maintain a replication environment.  That means that we can set up replication definitions, subscriptions and routes as well as all the configuration in modeling software. 

 

The demostration of the software was quite impressive and being able to graphically show management and support staff where and how replication is set up will be very useful.   

 

I’ve ordered a copy of PowerDesigner 12 and have several existing replication environments so a review of the product will appear in the next couple of weeks. 

Share Button

Wireless Access at TechWave

After attending a number of TechWave conferences, I really should expect the wireless to be flakey.  It really isn’t Sybase’s fault because their system administrators don’t often set up a wireless network.  (It is a single cisco 802.11b router so I’m told)  sigh

 

Oh well.  Save often when writing posts or emails.. 

Share Button

Sybase’s Workspace = Eclipse?

The second keynote for today mentioned that the new Sybase product Workspace was a highly modified version of the open source workgroup application Eclipse.  This afternoon, I took a good look at both of them.  Sybase’s Workspace appears to revolve around Sybase’s products (big surprise 😉 but also includes competitors such as Oracle and Microsoft.  For example, using Workspace, you can dynamically add replication definitions and see the result in your ‘management’ data flow.  (You know, that data flow chart that is used by managers and high priced consultants 😉

 

Today’s Development Trends are Driving Tomorrow’s Technologies
Featuring Sybase developers and consultants
Developers know that the only technology that really matters is tomorrow’s technology. Learn how today’s hottest development trends are driving tomorrow’s technologies. Client-server and web development are beginning to intersect, service-oriented architectures (SOAs) are connecting with the world of data and Java, and modeling is taking the lead in solving some of the most complex enterprise development problems. This lively keynote will showcase Sybase’s development tools, including the latest version of Sybase PowerDesigner – our market-leading modeling tool; WorkSpace – our ground-breaking Eclipse-based Java framework for SOA, mobile, and Java developers; and the long-awaited enhancements to PowerBuilder .NET, including WinForms and WebForms technologies. The future is here.  Watch the Video

 

I’m not certain as to the price of the product but if it is $1,000 (USD) or less per seat for corporate use, then it would definitely be worth it.  For small development shops or small companies, you’re probably better off using the free version of Eclipse.

 

If anyone from Sybase is reading this, please please have a small business or developer version in the order of $200 (USD). 

Not everyone can afford to pay big $$. 

 

Update:

I talked to a good friend of mine that just happens to be a Sybase Sales Rep.  Sybase Workspace is $500 – $10,000 (USD) per seat depending on the options.  I think we still need a developer’s version as there is no way that I will be able to convince the wife-to-be (AKA the Boss 😉 to let me spend $500 on Workspace.

 

Share Button

Waiting to enter the exibit hall

tonight dinner is served in the exhibit hall. we haave another 15 minutes before I can enter.

lots of pretty women to look at but when I have the most beautiful woman waiting for me at home, they just don’t matter.

Share Button

Advanced Rep Server Administration- Internals & Tuning

instructor Brian LaFevre Brian is an excellent instructor that livens thing up by telling lots of jokes so you don’t fall asleep in class. I have been both a student and the teacher with Brian. his skills of intructing keep improving. If this was D&D, he would be a level 15 instructor. hehe. The class is straightforward but the age of the PRINTED material is showing. Brian deviates from the course to provide the latest information on repserver v15. there seems to be a lot of misunderstandings about warm standby. I will post on repserver extensively later. I’m typing this on the palm treo that Sybase has been giving out for the duration of the conference. It is a slick toy but ultimately it really isn’t that useful to me on a day to day basis because I’m always near a computer or I’m commuting to work. if I was taking the Metra, that would be different.

Share Button

kosher food :(

When I registered for techwave, I requested kosher meals. Last night I received a proper kosher meal but for today’s lunch I received an iceberg salad with a few pieces of tomato. For this I waited an extra 30 minutes. The wait staff made it sound like I was asking for the Crown Jewels. There were several snide remarks passed between the wait staff regarding the meal that I overheard. sigh update. I finally received the kosher meal after 45 minutes. Chicken leg and thigh with corn and mashed potatoes.

Share Button

Keynotes at Techwave

Sybase’s Mobile Technology was the primary focus of this year’s keynote speeches by John Chen and various executives within the iAnywhere division.  It was quite impressive and made me want to go out an buy a Palm Treo for all the cool new stuff they are working on.

What I didn’t know was that Sybase is the main supplier of the Bluetooth network stack used in most of the new bluetooth enabled phones and PDAs.  I wonder just how much of the total revenue that Sybase brings in can be attributed to iAnywhere.  hmmm

 

They are serving lunch in five minutes so I’ll write more tonight after my RepServer class. 

Share Button