Jason L. Froebe: Ramblings of a Geek

As many of you know, I’ve been working on a free magazine regarding various database systems (dbms) called My Databases.  I hope to have multiple authors in future issues covering all sorts of open source and proprietary databases.

I should have the first issue done Sunday night.  I’m using OpenOffice, Scribus, Gimp, and Inkscape.

Listen to this podcast

ASE implements a subset of SQL 92 and isn’t 100% compliant with the SQL 92 standard (no DBMS on the planet is btw).

SQL99 compliance isn’t seriously being looked at by the major commercial DBMS vendors. Disregarding the fact the the SQL standards aren’t all they are cracked up to be, the vendors have too much invested in their own proprietary SQL variants (and other components) to be 100% compliant. If they were 100% compliant with the SQL92/99/whatever standard, then wholesale migrations from one vendor to another would take place.

I believe as time goes forward the opensource DBMSs (PostgreSQL, MySQL, etc) may become far more compliant with the standards than the commercial vendors as vendor lock in doesn’t mean as much to them.

Look into what the vendors (Oracle, MS, IBM,etc) are saying what constitutes as “compliance”. Ask each vendor what parts of the SQL99 standard they will be implementing and which parts they won’t be. If any vendor says that they are 100% compliant with the SQL92 or SQL99 ANSI standard, then that particular person is lying to you. Granted, that person may have been told their DBMS was 100% compliant and believes it. An honest vendor says that they comply with features X,Y and Z of the SQL 92 or SQL 99 standards.

Personally, I have found no significant movement by any of the commercial DBMS vendors to implement the SQL99 standard. So far it has just been lip service IMHO.

Listen to this podcast

At the dawn of Data Warehouse thinking, primitive decision support systems were poking their collective heads out of the murky depths of the early 1980s. Twenty three years later we have real data warehouses such as Sybase’s IQ. An episode of the Computer Chronicles on the Internet Archive provides a time machine:

Get the Flash Player to see this player.

Decision Support Systems (2/2/1984)

Expert systems software and artificial intelligence applications.
Host: Stewart Cheifet
Guests: Gary Kildall, DRI; Mike Thoma, Mgmt Decisn Sys; Steve Weyl, Syntelligence; Jim Chapman, Human Edge SW
Products/Demos: Strategic Simulations Epidemic, Prospector, CompuStat, Easy Scan, Sales Edge
This item is part of the collection: Computer Chronicles
Keywords: Episode year: 1984

Listen to this podcast

In Chris Brown’s Virtualization and ASE blog post, he brings up the question of whether Sybase’s ASE can be used in a virtual environment (VMWare, Xen, etc) but doesn’t answer it. I’ve been using various databases in virtual environments for several years, here is what I found out:

Running ASE, ASIQ, or SQL Anywhere under virtualization software such as Xen, VMWare, Parallels, etc is very useful under a number of situations:

1. development of new applications - each developer group can have its own “db server” on the same machine
2. testing new ebfs/releases with your applications
3. reproducing problems either in the sybase software or in the application code - a ‘virgin’ instance that can be duplicated at will
4. trying out new operating systems (moving from Windows to Linux or Windows to Solaris x86?) without investing in new hardware

The main caveat is that the performance stinks - databases typically require high disk i/o, memory i/o and cpu responsiveness. The virtualization software currently available, even with hardware help (newer Intel,AMD chips), are not up to the task of running a *production* database.  In a couple years… possibly.

Listen to this podcast

Most database monitoring systems aren’t from the database vendors as you might think, but a hodge-podge of 3rd party vendors that seem to want to charge more than I make in a lifetime for database monitoring software — try finding low cost monitoring software for DB2 on the mainframe.

They typically use standardized, and often deprecated, monitor counters that when used for their product, interfere with any other monitoring products you might use.  For example, if the Operations Department is using Nimbus to monitor the network, VoIP, hosts, tape archival systems, and the database servers to ensure that they are running, what happens when the DBAs want to use DBA Expert? The two products (keep in mind that I chose the products for the example at random) will trip over each other - neither will provide reliable metrics of the databases.

The front ends for the monitoring products always seem to show a fancy GUI full of bright colors, dials, graphs, and the latest and greatest designer kitchen sink. They are very rarely willing to provide any documented API or mechanism for you to obtain the data from their product without a nasty NDA. The premise is that you will use their front end to display and analyze the monitoring metrics.

The database vendors, themselves, are largely to blame. The monitoring APIs that they offer assume that you will only be using a single monitoring system.  For example, in Sybase’s ASE, the new API is to use their MDA tables to obtain performance metrics but the problem comes in when the monitoring software would use multiple methods to obtain additional information that may not be (easily) obtainable from the MDA tables.  sp_sysmon will reset several monitoring counters unless you call it with the ‘noclear’ option.  Unfortunately, the ‘noclear’ is not widely known and rarely used in the monitoring software.  Of course, this is just an example of multiple monitoring APIs from a database vendor.

You know what? I don’t care about the vendors’ fancy front ends. Give me a web service that I can access and use the monitoring metrics in another application, a PDA, etc. A few vendors have tried to offer an API but they are often so damned complicated that you would have had to work at the company to understand the API.  Don’t even get me started on vendors keeping their APIs updated.

Update:  Thanks goes to Peter Dorfman of Sybase to helping clarifying that the MDA tables in ASE ‘clear’ only on a connection basis.  That means if you look at monDeadlocks on connection #1 twice, the first select might show 5 rows and the second 0 rows.  If you ran the select on connection #2 sometime later, you would see the 5 rows plus any other deadlocks that might have occurred since then.  I wasn’t very clear on that as I was (in my head) also including sp_sysmon and other monitoring options that would conflict with the MDA tables.

Listen to this podcast

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.