Sybase Replication Server: custom built RepAgents with Perl!

Data is entered into the primary database and the replication server were send it to all of the replicate databases.  The process that gathers the data (encapsulated in transactions) and sends it to the Replication Server is called a RepAgent (Replication Agent).

Bernd Dulfer a few years ago wrote the perl module Sybase::RepAgent in order to replicate data from arbitrary DBMS’s.  For example, if your legacy application is built on dBase III+, you could with little effort make a RepAgent to send any changes to the dBase files to Replication Server and then off to some other DBMS that you will eventually migrate to.

In the Sybase::RepAgent Cookbook, Bernd Dulfer describes the setup of a simple RepAgent in order to replicate from a Postgres database to a Sybase ASE database.
 
Even though Sybase::RepAgent uses Sybase’s Openclient, the parser and SQL converter is written in Perl, we can not expect it to keep up with a high load of transactions.  Rewriting the parser and converter in C or some other compiled language may provide a sufficient boost in performance allowing it to be used in high load situations.
 
I have not been able to contact Bernd Dulfer so the Sybase::RepAgent module may indeed be abandoned.  I’ve contacted the CPAN admins regarding this.
Share Button

FW: Sybase vs. Oracle: Users speak out

By Elisa Gabbert, Assistant Editor
15 Aug 2006 | SearchOracle.com

A recent column by Mich Talebzedah touting the merits of Sybase on Linux over Oracle as a database management system (DBMS) has our readers riled up.

Anil Mahadev is a DBA and tech writer whose company, based in India, manages Oracle, SQL Server, DB2 and Sybase databases. Having worked with both Oracle and Sybase systems, Mahadev finds that Sybase is "definitely easier to manage and use." He cited seven reasons to opt for Sybase, including programming advantages, startup time and ease of installation. He also concurs with Talebzedah’s claim that switching from Sybase to Oracle is rarely worth the trouble.  Read more….

 

I have to agree with both sides of the discussion.  Sybase’s (and Microsoft’s for that matter) Transact SQL is very limiting for a developer.  Oracle’s PL/SQL is a far more mature SQL dialect.

Compared to other SQL dialects, TSQL appears to be archaic in both functionality and extendability.  Both vendors have made attempts to developer usability with .NET (Microsoft) and Java (Sybase) with varied success.  When calling in an issue involving either a .NET assembly or a java class, Microsoft’s support staff tend to be better trained than Sybase’s support staff, in so much as Microsoft’s support staff are able to determine whom internally they need to contact for help. 

Sybase’s support of Java in the database is limited to a very small number of tech support people.  There isn’t a whole lot of demand for Sybase’s java in the database due to the high cost of this add on and ongoing stability and performance issues with the JVM being run from within ASE itself.

The extendability of the open source DBMSs MySQL and Postgres far exceed their proprietary counterparts (Oracle, MS SQL Server, Sybase ASE).  Not only are there many interpretors that can be used in lieu of SQL, but you can create your own with little trouble.  The commercial companies should take note of this.

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

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