Category: SQL

Posted in Amazon, DBA, EC2, Economy & Business, IT Issues, Linux, MySQL, RDBMS, RDS, Red Hat, Solaris, SQL, SUN, Sybase

A Cloud based Sybase

Posted on by rwjordan

Over the past week I found myself in a situation as follows, during a migration, conversion from a Sybase Production server to a MySQL based version, I was required to ‘expedite’ a Sybase 15 ASE installation into an Amazon (EC2) instance, The Cloud!

The company has been in the position of seeking less expensive IT infrastructure over the past few years, moving from Sun Enterprise servers with ASE clustering to commodity Intel based Redhat Sybase servers with poor mans replication. The final goal became a decision to convert the expensive Sybase ASE (read inflexible licensing), to MySQL, and generally into the Amazon RDS (cloud).

The move of a Sybase ASE into the Cloud was the result of an urgent desire to terminate a data-center contract early by management. The shrinking time line for the conversion of the Sybase schema to MySQL could not be guaranteed so a Plan B had to be created. Hence, the Cloud based Sybase production edition of a production server.

To my surprise, it works! after a bit of twisting, the Redhat ASE developer installation came off more or less just like any other Sybase install. There are irregularities from a normal Linux install, but functional. Being a bit of a spindle jockey, I was surprised (happily) at the overall performance of the storage systems of the EC2 instance. And the production server is now operating in the instance. (having previously moved the app and web servers into the EC2)

This post needing a point to make, is this, while working this issue, I did considerable Googling for anyone using Sybase ASE in the cloud, and nothing! or nearly nothing. What I did find first, a press release from Sybase corporate that they were now in the Amazon Cloud, dated in 2009, and not a peep since. Nothing, no product, no advertising, no options. What a missed opportunity, it’s now easy to see why Sybase has been loosing so much market to a ‘free’ RDBMS like MySQL.

Posted in RDBMS, SQL, Sybase

MySQL Triggers weak support for referential integrity

Posted on by rwjordan

Even though my workplace and reduced costs by migrating from Sybase on Solaris to Sybase on redhat. We are now in a move from Sybase to MySQL to further reduce the license costs of the applications. This has brought to forefront one of the reasons to use Sybase. Triggers and stored procedures. And while MySQL now provides both, and transactions in InnoDB engines, Sybase triggers and Procedure are far more advanced. This forms a type of trap, they are very useful, but create serious obstacles to convert triggers that perform Referential Integrity. Just to highlight two types of these issues, take the following;

You can not use ‘select’ criteria in a trigger that returns a value. This prevents executing a select on a table to check for the existence of record prior to an insert/update. This was a major issue with a trigger used to produce a history file via a ‘update’ that had to both insert and update the history table. My workaround was to create a MySQL procedure that had no such restriction, and to call the procedure from the trigger. This worked very well, but of course meant that there was additional overhead and more coding involved.

The second example was, in my opinion, was a far more severe omission of Referential Integrity, ‘Rollback’! You cannot perform a rollback in a trigger. What! Further more, you have use ‘set autocommit=0’ either as a database default, which it is not, by default, set. Or set it during the batch or client operation that you are performing. Twice bad! Again, my solution was to call a procedure that could perform a ‘rollback’ and to ensure that either the developer set autocommit off or to insure that it was set as a database wide option.

As easy as these solutions were, they highlight an omission of functionality that requires addressing in the future development of MySQL.

Posted in Oracle DBMS, RDBMS, SQL, Sybase

Row level Locking

Posted on by rwjordan

In the Sybase vs Oracle discussion there is always one issue that Oracle used to win. Row level locking. In the early days of Sybase, page level locking was the lowest level of granularity that locks attained. Much of this was due to a conscious choice of increasing the performance of the transaction, rather than ensuring unique row locking in Sybase. After row level locking was added to Sybase at version 11, this was proved to be a false ‘feature’ as most Sybase installations rarely implemented it.

In retrospect most issues relating to row level being a requirement can be traced back to badly implemented transaction clients. which makes this even more evident, is that in many cases, when the client transaction were ‘repaired’ to implement true ACID transactions, the Oracle databases performed better, and the row level locking issues vanished as a requirement.

UPDATE:

One other difference between Oracle and Sybase which doesn’t get much visibility is the business of isolation levels. Oracle allows the ‘dirty’ read and Sybase does not. This is changeable in both databases, but Oracle used this to their advantage in the locking wars as shared, and shared intent locking did not cause the same kind of issues like the ones you would encounter in Sybase.

Posted in IT Issues, Oracle DBMS, Personal, Solaris, SQL, Sybase

Sybase vs Oracle

Posted on by rwjordan

I’ve seen some discussion around the internet about the age old argument about which is best Sybase or Oracle. I have been reading Mr. Talebzedah article on Sybase vs. Oracle: 10 reasons to use Sybase on Linux and I would agree with many of his statements. Having installed and used both over the years, I would pick Sybase. Why more companies don’t use Sybase more, has always been a mystery to me. Sybase is cheaper to install, and operate both in hardware resource usage, and manpower utilization. As an example, which can be repeated ad finite, one of the companies I worked at required 20 DBA’s to support a single Oracle Financials system in version 7 Oracle. And they were terrified to upgrade to Oracle 8 as Oracle does not supply a migration path. Sybase in this same company was used extensively in 7/24 environments, and only had 18 DBA’s supporting more than 800 Sybase databases, with more than 15,000 logins. In Mr Talebzedah article he mentions 2.5 Oracle DBA’s to one Sybase DBA, and since you and I know that 0.5 DBA’s don’t really exist (see mythical man month) I would put that, rounding factors aside, to 3+ Oracle DBA’s to one Sybase DBA.

Oracle supporters always mention the ‘sophistication’ and maturity of Oracle features, and in the same breath include the requirements for learning more, and of it’s complexity. This complexity extends to it’s high requirements in installation, and operating Oracle databases. Ask an Oracle DBA why they have to maintain backup configuration files three or more times to feel safe? As an example of this heavy requirement for installation, Oracle makes more money consulting on how to install it, than selling the actual RDBMS product. As another cost Oracle DBA’s are higher paid than Sybase DBA’s you do have to pay for all this sophisticated knowledge. That’s not a fault of the DBA, but is is still a business expense, and adds to the TCO.

As for the maturity and sophistication, Oracle may win here, but I am not comparing ASE 15 as I have not used this, but in almost every environment I’ve ever worked in, this sophistication is never utilized. This is not a lack of programming skills in the staff. It has to do with not being locked into any particular RDBMS feature. Many companies are completely heterogeneous with regards to Databases, probably due to corporate merger mania. And database transportability is prized much more than any particular database feature. In some cases junior programmers utilize databases more as file systems than RDBMS systems and hence, gain nothing from the mature, sophisticated features Oracle might provide.

Ultimately Oracle grants nothing in the benefits column when the costs are taken into perspective. Oracle is more expensive, fragile and harder to develop applications for. Sybase is cheaper faster, more stable, and requires less hardware and manpower to operate.