Log Buffer #43: A Carnival of the Vanities for DBAs
May 4, 2007 / By David Edwards
Welcome to the 43rd edition of Log Buffer, the weekly review of database blogs.
Let’s start with Leon Katsnelson on Database Talk, the first of a series on choosing a free database. He will be writing about SQL Server Express 2005, Oracle Express Edition (XE), MySQL, PostgreSQL, and in this first, DB2 Express-C. That Leon is a program director with IBM will not affect his impartiality I hope. On An Expert’s Guide to DB2 Technology, Chris Eaton enthuses about IBM’s support offering for DB2 Express-C.
On Informix Application Development, Guy Bowerman has a round-up of IDS-related events, including mention of two IBM DB conferences, IDUG and IOD. On
Diary of a Database Geek, scotdb gives his highlights of the IDUG get-together.
Sanjit Chakraborty of IDS Experts offers a table of new reserved words in Informix Dynamic Server version 11.10. “New reserved words might affect migration of an application. Existing application might encounter restricted functionality or unexpected results, if an SPL routine defined with the same name as new reserved words.”
Some aftermath of last week’s MySQL Users Conference now. Antoni Wolski of solid, and Peter Zaitsev of MySQL Performance have made their presentations available.
Xaprb recorded many of the sessions, and offers the recordings. Kevin Burton of the Feed Blog converted the original OGGs to MP3s and made a podcast of them. Kevin also links to Domas Mituzas’s notes from his presentation on the design and architecture of Wikipedia.
Ignacio Correas took part in the quiz show, brought back video and pictures, and published them via his blog, Embracing Open Sources. (Does anyone know who the singer is?) Also there are some pictures from the MySQL staff party at MÃ¥rten Mickos’s little place.
Peter Zaitsev also writes about MySQL’s “countless storage engines”, summarizing his impressions of InnoDB, PBTX, Falcon, Solid, NitroEDB, Infobright, and ScaleDB. “The wide choise (sic) is always good, but Iâ€™m afraid making right decision about Storage Engine will be much harder.”
If you missed the conference (or even if you didn’t), and you crave some kind of communal MySQL experience, Jay Pipes has the answer: MySQL Camp II is coming on Thursday and Friday, August 23-24, in Brooklyn, New York. “I expect it to be a breeding ground for new hacks, ideas, and of course, beer.”
blog.cheyer.biz announces the launch of DorsalSource. “(At) the end of last year MySQL decided that they will no longer commit to releasing binaries for all versions and platforms of their Community Edition. Instead, some versions of their Community Edition are labeled ‘source-only’ releases. For example, 5.0.33 Community Edition was a source-only release. … DorsalSource is a community-focused website whose goal is to provide builds of MySQL and related products. It will be maintained and run by the community.”
Sergey Petrunia reports on MySQL optimizer and prepared statements. He says that the economy of prepared statements will hamper the work of the optimizer, and distills the reasons into the rule, “Optimizations that depend only on query syntax are performed either at PREPARE or at first EXECUTE. Optimizations that also depend on something else are performed at every EXECUTE.” He continues with some speculation on how this might change in future releases and fixes.
On the So What Co-operative, Jeff Hunter has two articles showing his approach to MySQL pseudo-partitioning. Sheeri advises using the merge engine in part one’s comments, which Jeff picks up for the second part.
Turning to Oracle, Laurent Schneider has a useful trick for csv format with select * using regular expressions.
Pythian’s Babette Turner-Underwood wrote about Oracle’s little-known multi-table insert, which Andrew Clarke on Radio Free Tooting expands on with another use for INSERT ALL syntax: “…Babette is correct to describe the multitable insert as ‘little known’. However, there is one use for the syntax, which is slightly arcane but is a much more common situation: creating referenced records for foreign keys on the fly.”
Also looking into Oracle’s shadier corners was Eddie Awad with two Oracle PL/SQL features you probably donâ€™t know about: forward declarations and mutually recursive routines.
Eddie also has the sixth in his back-to-basics series, this one on anti-joins and semi-joins.
Craig Mullins of the DB2Portal Blog also writes about online schema change, or as it is called in DB2v9, database definition on demand.
Andrew Fraser reports that Oracle’s 10.2.0.3 patch fails if database word size had ever been changed in the past: “If you are upgrading or patching to 10.2.0.3 and the word size is different to the word size when the database was created the upgrade will fail with an ora-600. This applies even if the wordsize has been changed in an interim upgrade i.e. 8.1.7 32bit upgraded to 9.2.0 64bit will not upgrade to 10.2.0.3 64 bit, (but will upgrade to 10.2.0.3 32 bit).”
Kevin Closson’s Oracle Blog has an item arguing that the performance of Oracle over NFS is perhaps not as poor as others suggest: “(There) is no shortage of misunderstanding about the model. … when someone tells me the performance is horrible with Oracle over NFS … I canâ€™t call them a bold-faced liar. Iâ€™m sure nay-sayers…saw what they saw, but they likely had a botched test.”
On Jeff Smith’s SQL Server Weblog, Jeff demonstrates using EXCEPT and INTERSECT to compare tables in SQL Server 2005. “Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables. … Well, now in SQL 2005, we have another option: using EXCEPT and INTERSECT. And these are even easier! … Does anyone else have any suggestions for ideas where these operators can make things shorter, quicker or more efficient compared to older (pre SQL 2005) methods?”
Jeff also has a worthwhile piece on the ins and outs of set-based thinking as it applies to SQL.
Over at the SQL Server & .NET Bottom Feeder is a recipe for finding orphaned SQL Server 2000 logins.
Firebird News links to an page on Ohloh.net that “estimates that the cost to hire a team to develop Firebird from scratch would be $37,373,773.” Dig around Ohloh for some more stats on open source projects, it’s quite fascinating.
Finally, Mladen Prajdic has the definitive, normative answer to a question that has bothered us all: it’s “S-Q-L (ess-cue-el)”.
That’s all for now. See you in a week…and in two weeks for LB#45 by Doug Burns, who contributed to the Pythian blog an excellent article on stress, which, as the clock crests 1PM, I can well relate to.
Until next time!