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

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don’t want the NULLS at the beginning.Oracle has this syntax:

ORDER BY ColumnName NULLS LAST;

SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

Read more at SQL Server Code,Tips and Tricks, Performance Tuning. These two methods will also work on Sybase ASE if you use a derived table or temp table instead of the memory table.

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

I’m not trying to raise any ire in anyone, so please bear with me….

The issue of using isql or some other tool (such as Perl or Java) was brought up in the sybase-l mailing list

A benefit of handling each batch separate entity instead of all the batches as a whole in isql, we have the ability to isolate each batch. That means, the we can place logic around each batch or run them in parallel or a number of other reasons. This doesn’t actually require Perl and you could definitely do this in Java, but you would probably want to shy away from using isql for executing each batch. You may have hundreds or thousands of batches and starting isql for each batch would be very time consuming.

I have no problems with using awk/sed/whatever for maintenance scripts. I do have a problem of relying too heavily on the isql program from Sybase or Microsoft. Why? isql does not handle errors very well and often can fail due to bugs in the isql/openclient or network issues. sqsh is a bit better but I wouldn’t trust either in a production environment.

Yeah, I know lots of people run isql/sqsh in production environments but when isql fails, are you able to identify the problem and workaround it without having your script fail?

For example, if you were using isql to import a SQL file of say 10,000 batches (table ddls, etc), would you really want to restart the whole job if the 998th batch failed because of a deadlock?  Wouldn’t you rather just retry the 998th batch?

In many cases, you might not be able to restart the whole job, so you would have to create a new SQL file copy/paste it from the original.

Sure, you could pipe the output of isql/sqsh and parse it but they don’t show all the errors and a general disconnect error message doesn’t really help.

I’m lazy by nature, so doing something manually when it can be scripted to be have more error handling is pretty much a good thing.

Listen to this podcast

Erica Sadun wrote an application, written in Perl, to convert your iPhone backup files into a SQLite3 database:

Here’s a nice way to recover notes from your iPhone without having to mail them to yourself–although it’s not for the faint of heart. Read more at The Unofficial Apple Weblog…

It would be a simple matter to have the data imported in to your favorite DBMS if you prefer Sybase ASE, MS SQL Server, dBASE / xBase, etc.

Listen to this podcast

Say you have a query that easily fills up the transaction log of the Sybase ASE server / Microsoft SQL Server:

How would you break up the transaction so it doesn’t fill up the log? There are several ways to do it, but I’ll just cover two of them:

1. Break up the transaction based on the actual data.
   DELETE FROM rep_queues_archive WHERE sample_date < DATEADD(wk, -52, GETDATE()
   DELETE FROM rep_queues_archive WHERE sample_date < DATEADD(wk, -51, GETDATE())
2. Break up the transaction based on the number of rows affected.

Of course, if you are performing incremental backups, you will want to modify the dump tran @dbName with truncate only line to read something like dump tran @dbName to “/sybdumps/mydb_tran.dmp”. Modify to suit your naming standard.

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

Does anyone know the syntax for the dbcc dbrepair commands writepage and readpage for Sybase ASE?  These are being used by Whitesands’s ProActive DBA.

The equivalent on Microsoft SQL Server are:

dbcc readpage ({ dbid, ‘dbname’ }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
dbcc writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)

UPDATE!

I’ve figured out readtext:

dbcc dbrepair(<database name|dbid>, readpage, <page number>)

Read the rest of this entry »

Listen to this podcast

In case you're looking for reasonably priced Visual Studio .Nethttp://www.empowerforisv.com/

You need to have a company web site, and you need to agree to:

Develop one packaged and resalable software application that supports at least one of these Microsoft technologies:

• Windows Server 2003
• Windows XP
• Microsoft SQL Server 2000
• Microsoft Dynamics, formerly known as Microsoft Business Solutions
• Microsoft Mobility platform (Microsoft Windows Mobile software for Pocket PCs or Microsoft Windows Mobile software for Smartphones)

Thanks goes to Bruce Armstrong for pointing this out!  

Listen to this podcast

There are lots of variations on this myth that I have heard/read over the years, most amusing of which is probably “Microsoft has not had time to mess up the Sybase code it bought yet so SQL Server is still a pretty solid product”

 

Lets start with a history lesson, you can read a much more in depth (and insightful) version of this in the book Inside SQL Server by Microsoft Press (at this time I can only check the 7.0 and 2000 versions) or any of the historical posts/articles by Hal Berenson (former Architect, PUM and GM of SQL Server during 6.5/7.0/2000).

Read more on Euan Garden’s Blog.

Euan’s explanation of what happened is pretty close to what I was told by people that were involved in the deal while I was at Sybase.  There are a few differences regarding how the relationship broke down but that appears more of hearsay and point of view issue.  IMHO, I believe that both Microsoft and Sybase were guilty of screwing each other over.  However, that is so far in the past, that it really doesn’t matter.

Interestingly, Sybase still owns the trademark on SQL Server.  Make of that as you will but it is little more than a trivia answer.

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.