Tag Archives: Database

Never Don’t Buy Version 4.0 of Anything

As I’ve mentioned before, TOAD is one of my regular tools for database work.  At least it used to be.

I’ve had to keep upgrading TOAD for DB2 to stay current with DB2 releases, but the 4.0 release is so buggy that it’s unusable.  The 4.7 Commercial Version has been out for awhile now, but there’s no sign of those fixes and enhancements coming to the freeware version anytime soon.  Folks have been asking in forums, but there’s no meaningful response.

It’s seems that since the TOAD first donned that Quest T-shirt, this formerly independent tool has become increasingly more constrained.  Ever more menu options are grayed out with the “(Commercial Feature)” taunt.  Simple things that worked in prior releases are broken in the 4.0 freeware versions.  Perhaps they work just fine in the paid commercial version.

I’ve purchased Quest tools in the past, so cost isn’t the issue: availability is.  TOAD is one of those tools that I’ve frequently recommended largely because it was just one free download away.  If freedom and ubiquity weren’t so very important, we might all be coding in CFScript.

Quest is a great company, but TOAD has not done well lately under its ownership (don’t get me started on the lack of a *ix version).  I only wish there was a TOra for DB2.

This happens with some companies that take over a free, independent, or open source project: they take it in under their wing, lock it down, and look for improper ways to capitalize on it.  It can make money in the short run, but let’s be up front about it: state your intentions and either spin off a community version or kill off the freeware version altogether.  This “slippery slope” behavior is right down there with snake oil salesman and Oracle.


There are many great tools for running DB2 commands; I often use TOAD, Control Center (db2ce), and a CLI-based tool I wrote.  And with DB2 9.7, I’ve enjoyed experimenting with the new CLPPlus, IBM’s answer to SQL*Plus for Oracle refugees.  But for those quick commands, I usually just pop open a DB2 Command Window (Windows) or bash shell (Linux) and type “db2” followed by the command.  It works great nearly all the time.  Emphasis on nearly.

Today, Wayne reported how this command taken verbatim from the DB2 documentation (the “File type modifiers for export” section) choked:

db2 export to delfile2 of del modified by timestampformat=”yyyy.mm.dd hh:mm tt” select * from schedule

It failed with the error: SQL3192N  In the filetmod a user specified format “TIMESTAMPFORMAT” beginning with the string “yyyy” is not valid.  And no value for timestampformat worked.

This is a case where bugs in CLP’s command-line parsing (particularly with double quotes) get in the way.  The command works from inside CLP, Command Editor, and other tools, so you can just type db2 or db2ce and hit enter and then run the command (without “db2” in front) from there.  Using admin_cmd from a CLI/ODBC tool also works, like so:

call admin_cmd(‘ export to delfile2 of del modified by timestampformat=”yyyy.mm.dd hh:mm tt” select * from schedule’)

Bugs like this have survived in the db2 command line for awhile (this fails even in 9.7).  I’ll report it, but since CLPPlus is the new kid in town, the old db2 command line probably won’t get as much attention.

Guilt By Association

Anyone who has done a little data mining knows that simple association rules (a.k.a., market basket analysis) and decision trees can reveal some of the most strange and wondrous things.  Often the results are intuitive, which builds confidence in the techniques.  But then let it run loose and you’ll usually find some (strongly correlated) wild surprises.

Folks who fold their underwear tend to make their bed daily.  I’ll buy that.  But people who like The Count on Sesame Street tend to support legalizing marijuana – are you kidding?

Those are some of the conclusions reached at hunch.com.  This site will happily make recommendations for you on all your life decisions, big or small.  There’s no real wisdom here – it just collects data and mines it to build decision trees.  So, as with most data mining, the results are based on pragmatics and association, and they never answer the question, “why?”  Yet “just because” is usually good enough for things like marketing, politics, and all your important life decisions.

In school they made me work through many of these data mining algorithms by hand: classifiers, associations, clusters, and nets using Apriori, OneR, Bayes, PRISM, k-means, and the like.  When it got too rote, we could use tools like Weka and DMX SQL extensions.  It was, of course, somewhat time-consuming and pedantic, but it made me realize that most of these “complex data mining techniques” that seem to mystify folks are actually quite simple.  The real value is in the data itself, and having it stored in such a way that it can be easily sliced and diced into countless permutations.  (NoSQL fans: that typically means a relational database.  Oh the horror.)

Yet simple associations can be valuable and entertaining.  I’ve run enough DMX and SQLs against large database tables (housing contact management, payment, and contribution data) to find some surprising ways to “predict” things like risk and likely contributors.  But since “past performance is no guarantee of future results”, these outputs must be used carefully.  It’s one thing to use them to lay out products in a store, quite another to deny credit or insurance coverage.

American Express, Visa, and others have caught a some flack lately for their overuse of these results.  “OK, so I bought something from PleaseRipMeOff.com and you’ve found that other cardholders who shop there have trouble paying their bills.  But that doesn’t mean I won’t pay my bill!  Don’t associate me with those guys!”  Well, associate is what data mining does best.  And, like actuarial science, it’s surprisingly accurate: numbers don’t lie.  But companies must acknowledge and accommodate exceptions to the rules.

Meanwhile, data mining will continue to turn wheels of business, so get used to it.  Just don’t let anyone know that you like The Count.