Sybase woes – and Jython saves the day

Until now, I've always had a certain respect for Sybase database.

Based on their history, I thought that the product is a sort of MS SQL

without the glitzy features – think Las Vegas without the lights, the cowboy boots and Eiffel Tower. Which gives: huge crowds of fat tourists in tall, dull buildings. But you always have Celine Dion, right ?*

Wrong ! Sybase ASE is a large, enormous, huge piece of steaming …

ummm … code. Ok, ok, I'm a bit over-reacting. In fact, Sybase is a

very good database – if you are still living in the 90s and the only

Linux flavour that you are able to manage is RedHat ASE. Otherwise,

it's a huge … you know.

Let's not talk about the extreme fragility of this … this product.

You never know when it crashes on you without any specific reason.

Sometimes, it's the bad weather. Crash. Restart. Sometimes, you

flushed twice. Crash. Restart. And maybe, yes maybe, you spent more

than 12 minutes in your lunch break. Crash.

Let's just talk about the JDBC drivers – latest version, downloaded

from the site in a temporary moment of insanity. Man, this is cutting

edge. The sharpest cutting edge you'll ever find – the more advanced

JDBC drivers bar none ! Excepting of course the fact that these

classes were compiled in that memorable day of 6 January 2002 [the day

when the last Sybase JDBC drivers were compiled]. But, don't let such

obscure details ruining your enthusiasm. Just download and use them

and you'll be amazed at their unique features – it's the only JDBC

driver which manages to put down DbVisualizer in different and

innovative ways. I'm restarting the poor thing (dbvis) at least 2 or 3

times per day, when working with Sybase.

Also, as a little quasi-undocumented tip, the letter d from

jconn2d.jar does not mean development [drivers] as some of you would be

inclined to think. In fact, it means debug which is the

abbreviation for put me in production poor lousy bastard and I'll

start spewing reams of useless messages through all the logging APIs

known by man and a few yet to be discovered, therefore instantly

slowing down to a crawl your puny little software.

Ermmm … well, all this nice introduction just to humbly confess that

we do have a couple of them Sybase licences floating around here and

some of our production databases are on Sybase ASE. While I can assure

you that this is going to change at least on the web backend, it's

also true that the beast must be alive in order to keep our company up

and running. And, that's part of my job.

Which is of course very strange for an IT management job. But then again:

when your sole DBA is overwhelmed by a horde of Sybase-specific tasks

(like for instance the log configured to truncate at a specific

threshold which naturally [for Sybase] does not truncate, suddenly

throwing all the users into log suspend in the middle of the peak

production time) – you have to enter into the damn kitchen and do some

cooking !

My endeavour was to perform simple data mining in order to migrate

some reports from a legacy system [no, you don't really want to know

what system]. Due to my limited time, starting a mildly complex Java

reporting project was out of the question, so a scripting language was

the natural choice. Python being the first option – unfortunately,

finding a working Sybase driver for Python is a challenge in itself.

But, thank God for Jython and zxJDBC ! In just a few minutes I was

wiring the tables for reporting. Here's a fake code snippet which lists the 'orders' with amounts \< 1000, and you can't go any simpler:

from com.ziclix.python.sql import zxJDBC conn = zxJDBC.connect("", "user","pass","com.sybase.jdbc2.jdbc.SybDriver")cursor = conn.cursor() cursor.execute("select count(orderid) from orders")nb_orders=cursor.fetchone()[0]print '%.0f total orders ...'%(nb_orders,)cursor.execute("select orderid, amount from orders where amount <1000")oids=cursor.fetchall()for o in oids: print 'Order %.0f for amount %.2f'%(o[0],o[1])cursor.close()conn.close()

Easy as pie. And once you got one down, you got'em all. For instance,

yesterday evening I wrote in about an hour a small Jython script which

exports some data. The same export process (running about an order of

magnitude slower) needed previously a couple days of development in

FoxPro. Ah, FoxPro - very juicy subject, but I'll keep it for my next

horror story. Until then, don't forget, when you have a monster to

tame and no time at all: try Jython !

*Let's suppose for a very brief instant that having Celine Dion at a certain location is a positive thing.