« solidDB beta for MySQL | Main | No Digital Audio for a Month »

August 18, 2006

Log Buffer #6: a Carnival of the Vanities for DBAs

It's time to turn our attention to the sixth edition of Log Buffer, a Carnival of the Vanities for the DBA community.

This week, like every week, DBAs all around the world have been hard at work writing about their experiences, many of them providing detailed instructions on new and interesting ways to use and manage a database.

For folks wondering if it's worth all the work, Eddie Awad's Blog now has the latest results from his Unofficial Oracle Developer/DBA Salary Survey. The results are interesting, and hopefully encouraging. While the data is why we all rush there to see where we stand, there's also a good summary of the process Eddie went through to run and process the results from the survey.

As a DBA you work pretty hard, do you think it's time to take that vacation you keep putting off? Yes, you deserve to get away for awhile. To help in your preparation, Beth Breidenbach offers eight things to think about before heading off.

In some instances a vacation is actually a time to get more work done, as is the case over on Andrew Dunstan's Postgres Blog. It seems that Andrew and his son will attempt to complete a Postgres Enum project while his son is en-route to a working holiday. Sounds like most of the vacation is already filled with work to do. Hopefully your vacation will be less work.

If you do manage to get away, your vacation might be more enjoyable if you go back to Eddie Awad's Blog and consider his observation on comments. Eddie reminds us that including even simple comments can make a huge difference in how easy it will be for other folks to understand what's going on in the database while you're away. Knowing someone else can figure it out should make for a much better vacation. Right?

And while we're talking about understanding one another, Chris Foot's Oracle 10g weblog has an excellent article on Application Design Review Meetings. Chris provides a detailed outline of the things to consider when designing an application. Having representatives from all groups involved in defining, building, and supporting an application makes a huge difference in the outcome of a project.

Now, you might be thinking differently about this whole vacation thing if you're the DBA who gets stuck in the office while everyone else goes away. If you find yourself wanting to make sure everyone gets stung for abandoning you maybe you could learn how to make Oracle send email alerts and overload inboxes for the returning vacationers. Howard Rogers of Dizwell Informatics provides detailed instructions for pulling this together. (While you're there he also has some good thoughts about documentation.)

And if you're using SQL Server you can learn to programatically fill up inboxes by reading Muthusamy Anantha Kumar's instructions for sending mail from SQL Server over at the Database Journal. Whatever your choice, hopefully your non-vacation will be more enjoyable thinking about the growing inboxes across the organization.

Now most of the time being a DBA doesn't mean going on vacation or playing tricks like flooding the mail server. More often it requires wrestling with and debating complex issues, which aren't always easy to win in terms of what's best from the database perspective. Among the issues that tend to get folks riled up is a good discussion on application-level database abstraction layers. Over at Xaprb, Baron Schwartz summarizes what he's seen in his post about database abstraction layers. As expected, the discussion in the post's comments is lively.

How, where, and when to use SQL hints is another issue that can get people going. Tom Kyte provides a nice summary of the key points in Words of Wisdom on The Tom Kyte Blog. Quoting from a recent article from Jonathan Lewis entitled Hinted SQL, Tom emphasizes key points of agreement. Comments on the post are remarkably agreeable.

We'll wrap up today's hot-topics discussion with a pointer to Oracle Musings where Dominic Delmolino writes about his experience playing with MySQL, and ways to convert MySQL databases to Oracle. Dominic has nice manners and is quite friendly when talking about other databases. I like the idea of being able to understand and accept the reasons why different people choose different databases.

While we've got MySQL on the mind, this week the latest beta of solidDB for MySQL was released. Jonathan Cheyer makes the announcement with a link to downloads over at blog.cheyer.biz. For folks who have been waiting to see the new storage engine in action now might be the time to head over and give it a whirl.

Moving on to calmer waters, what would life as a DBA be like without periodically having some work that doesn't seem like work at all? You know, the project that makes you forget you do this for a living. Maybe you're exploring some feature you never knew existed as in the case of Sue Harper over at Sue's Blog... again.... Sue takes time out of a busy Friday to play with a newly found dialog for creating external tables.

Or perhaps you had an clever idea to try to store your database data on a flash drive for ultimate portability. Peter Laursen documents his experience getting it up and running over at Blogck out .. by peterlaursen. Must have been a great feeling when he happened upon the idea and was able to put it into action.

At the core, a DBA is about making data storage and retrieval work accurately and quickly (or as we say in Boston, "wicked fast"). It might be something simple like the inaugural Mini-Tip over at An Expert's Guide to Oracle Technology where Lewis Cunningham offers a solution for Getting rid of spaces after TO_CHAR. Mini-Tip #2 follows close behind with a look at Oracle's nvl2() function.

More often though, the DBA is required to wrap his or her mind around more complex data manipulation and retrieval. Fred, over at DB2 News & Tips demonstrates multi-dimensional clustering and materialized query tables in DB2Express-C. Fred's purpose is not to teach us how to use them, but to prove they can run in the community edition of DB2Express. But it's worth reading for either reason.

Performing year-to-date calculations is another fun problem to tackle. On The Oracle Sponge, David Aldridge compares using a dimension table and a transformation table, concluding that there are significant performance benefits using the transformation table that are probably worth considering.

Pete-s random notes provides some additional discussion on year-to-date calculations and proposes an alternative to the transformation table method from David Aldridge. However, within a day, Pete posts part 2 of the alternative approach confessing that his tests for the alternative approach weren't up to snuff and the alternative isn't actually viable.

Sometimes DBAs get frustrated, tired, and sick of having to be responsible for keeping track of everyone's data. It's at those pivotal points that you wish you could just send the data into a black hole and never have to think about it again. Daniel Schneller got to do just that when he set up MySQL replication using the blackhole storage engine, which discards any and all incoming data. Why would you ever want to do that? Well, besides doing it out of annoyance or frustration, Daniel Schneller's Blog is the place to go to find out how sending data into the deep abyss was just what they needed for building their replication architecture.

And with that we conclude Log Buffer #6. Thanks for listening. As always, tune in next week to see how the database world continues to evolve.

Posted by mike at August 18, 2006 12:00 PM