Monthly Archives: January 2010

Firmly Volatile

Yesterday, Scott Hayes described when it’s useful to mark a DB2 table as volatile.  It should be considered when table data changes so frequently and wildly that it’s hard to keep statistics current enough and applicable.  Such as with those “flush and fill” tables often present in OLTP systems.

Because the DB2 optimizer looks only at statistics (not current table data) when generating an access plan, outdated statistics can cause it to choose a costly tablescan even when there’s a perfectly good index available to cover one or more sargs.  It’s one of those things that’s easy to explain but hard to justify.  Sometimes we developers will bring this on ourselves through excessive use of literal values, rather than markers and bound parameters.  The volatile property prevents that: it tells the optimizer to ignore statistics and just grab whatever indexes are there.

Yet volatile can send you back into the weeds again when there are multiple indexes on the table, especially multi-column indexes.  When several index candidates can satisfy a query, the optimizer really does need the statistics to wisely choose between them.  I’ve seen some really bizarre and costly access plans when it doesn’t.  That’s why I’ve un-marked (not volatile) about as many tables as I’ve marked volatile. Volatile is great for single-index tables, but often not for others.

So I usually prefer good old-fashioned runstats scripts instead.  And by using multiple scripts tailored to match the workload and data flow, one can significantly reduce the runstats overhead.  Besides, cron jobs and scheduled tasks are often more fruitful than betting against the optimizer.

I expect that soon enough that DB2’s auto runstats will be rich enough to eliminate scheduled scripts altogether, but it’s just not there yet.  Among other things, the two hour wait is an eternity for some systems.  I’d like to see timing/scheduling settings added to runstats profiles and more frequent polling.  Here’s hoping that’ll be in a future DB2.

DB2 Snippets

Today a DBA at one of our customers reported deadlocks and wanted recommendations.  I usually start with the SQLs involved in the deadlock for which I need the deadlock event monitor output.  Fortunately, all modern versions of DB2 have the db2detaildeadlock event monitor installed and one just needs to run db2evmon to get it to talk.  This DBA wasn’t clear on how to provide this so I copied and pasted some simple instructions from my file of “common DB2 snippets”:

Getting deadlock event output – Windows

Open a DB2 Command Window (db2cmd)
cd to the “Default database path”.  If not the default instance path, you can determine this by running: db2 get dbm cfg | find “DFTDBPATH”
Run: dir db2detaildeadlock /s
Look for paths with recent timestamps
For each of these paths, cd to that path and run the following.  Change the output filename each time; for example: dl1.txt, dl2.txt, dl3.txt…
db2evmon -path . > dl1.txt
Send each of the dl*.txt files

I can then browse and grep the dl*.txt files for SQLs and other information.

I thought I’d paste in some others, if for no other reason than ease of reference (“send it off in a letter to yourself”).  That is, we have many of these in our products’ scripts and utilities, but often a DBA just wants easy access to a quick script.  Some of the other commonly-sent snippets are:

Gather snapshots remotely – Windows

: Check if DBM-level snapshot monitors are on:db2 get dbm cfg | find “DFT”
: Turn on those that are off, restart DB2 afterward:
db2 update dbm cfg using dft_mon_bufpool on dft_mon_table on dft_mon_uow on
db2 update dbm cfg using dft_mon_sort on dft_mon_stmt on dft_mon_lock on
db2stop & db2start
: Collect snapshots:
echo Current snapshots for %date% – %time%. >>%SnapshotFilename%
db2 get snapshot for db manager >>%SnapshotFilename%

db2 get snapshot for all on %DbName% >>%SnapshotFilename%

Collect DDL and mimic statistics (to do explains remotely):

db2look -d %DbName% -a -o db2look-%DbName%.ddl -e -l -m -f -x 2>&1

Get explain output (for remote analysis):

db2expln -g -i -d %DbName% -o explain1.txt -q “%SqlText%”

Get more detailed explain output (for remote analysis):

db2 connect to %DbName%
db2 set schema %DbSchema%
db2 “explain all with snapshot set querytag = ‘DWEXPLN’ for %SqlText%”
db2exfmt -d %DbName% -n % -s % -# 0 -w -1 -o explain1.txt

Bound by autobind

It’s a lesser-known trait of DB2 CLI that if it doesn’t find the packages it needs, it’ll attempt to bind them for you.   This can be a nice favor except when it’s unwelcome.  The CLI package names change periodically with new releases of DB2, so it’s too easy for someone at a different DB2 client level to unknowingly do unwanted binds.

This pops up once a year or so on our customer support line.  Someone will be unable to connect because the packages were bound under the wrong authorization ID.  Or, as happened today, sysplan and other catalog locks are held indefinitely because a package is missing and an attempted autobind has gone off into the weeds.  Browsing sysibm.sysplan can sometimes reveal the smoking gun.

This can usually be avoided by carefully migrating each database as required for new versions and fixpacks (creating and migrating databases will do proactive automatic rebinds).  If unsure, have the DBA do all the CLI rebinds himself/herself from the database server for all supported client versions before turning the database back over to the users.

I vaguely remember hearing of an option to turn off autobind in mainframe DB2, but I can’t find such a thing in DB2 LUW, and googling it was a dead end.  If you know of one, please post a comment or email.

Gigabyte Catch-22

The family surprised me with a new system for Christmas, including that Intel Q8400 I’ve wanted (4 true cores for parallel programming work, of course).  The combo included a Gigabyte EP45-UD3P motherboard whose bells and whistles impressed this Asus fan.

But after putting it together and starting it up, I found that the USB keyboard and mouse were dead.  I really didn’t want to RMA this thing, so I experimented.  I could get the BIOS startup keys (F12, Del, etc.) to work, although sluggishly.  I could bring up the setup screen, but couldn’t do anything in it.

Fortunately, I had an old dusty PS/2 keyboard and mouse that I could plug into those ports, and they worked great.  Once into BIOS setup, I found the settings for USB keyboard and mouse, and they were disabled.  By enabling them and restarting, I was able to switch back to my USB devices.

So if all you have is a USB keyboard, you’re stuck: you can’t use your keyboard to enable the keyboard!  A little googling after all this indicates this is just how it is with many Gigabyte mobos.

Moral of the story: hang onto that old PS/2 keyboard or adapter plug; you never know when you might need it.

This New stuff really is Old

One might think it’s a lesser known communicable attribute that we drop hints.  If asked to keep a secret too long, fragments of it will sometimes percolate out of us until the gig is up.  Let my wife buy you a Christmas or birthday present early enough and, by the time that gift-giving day arrives, you will have probably pieced together enough “slips” to at least take a really good guess at what it is.  Just ask her about woks some time.

Redemption and resurrection are such well defined New Testament concepts that we often don’t look for them in the Old.  And God, who is outside of time and space, wasn’t impatient getting to them.  Yet that news was just too good to keep hidden during Israel’s trials and discipline.

So redemption and resurrection are clearly communicated in the Old Testament.  The clues were often missed, but they certainly weren’t subtle.  They were so common and so not-subtle that Israel developed idioms and “shortcuts” for them.  Something like those two friends who numbered their jokes to save time retelling them.

“Redeemed from the pit” (or Sheol) is one of these. Perhaps the most familiar is in Psalm 103; others include 1 Sam 2:6, Psalm 16, and Psalm 30.

“Light of light” is another.  To suffer or go down into the pit and then “see the light of life” meant to be risen from the dead. My favorite use of this is in Isaiah 53:11, because it’s such a clear prophecy of Christ’s resurrection, but also because it was just too good to keep hidden.  Some manuscripts had the verb for “see” with the required object missing, causing early NASB translators (and others) to add it in italics.  The “it” was “the light” (of resurrection) and it was there all along, with the Dead Sea Scrolls providing the final proofs.

In today’s sermon, Pastor Dave Stephens covered Job 33, and verses 28-30 lept out.  I’m not sure how I previously missed the weight of these two foreshadows being so clearly and beautifully presented, but they were certainly there all along. In this most ancient of works, the very future concepts of resurrection and redemption are exceptionally clear.  As Dave points out, Job contains some advanced Christology (and, of course, very beautiful language), so we should discard any idea that ancient man was primitive.  Listen to his series on Job for more on that.

Warfield said described the Old Testament as a room “fully furnished but dimly lit” and Augustine said that what is “concealed in the Old Testament is revealed in the New”.  Indeed.

777 – An Unlucky Number

Fool me twice, shame on… insecurity.

Far too often, Step 1 in deploying a file-manipulating script on Apache is setting wide-open (777) permissions on working directories.  This seems wrong on a few levels, but is often unavoidable.  The security risks of this are often overstated, since most real hosts chroot Apache, or use suEXEC or something like it.  So, hey, chmod -R 777 , why not?  The world may be able to write to my folders but they’ll never get there.

Well, one reason is the ghosts of hacks past have many web hosts configured (mod_php, mod_perl, whatever) to refuse to run scripts that have 777 permissions.  The result is usually an internal server error (500 exception) and, of course, no output from the script itself, since it never even got to run.

I had encountered this before when deploying and customizing a large CiviCRM system, and with some Q&D PHP scripts of my own.  But it was certainly not the first thing on my mind last night.

My wife wanted a new site to display and possibly sell some of her photography.  Her needs matched WordPress’ capabilities well, so I went that route – a WordPress install with a nice FotoFolio template.  But when it came time to generate image thumbnails – nada – just blank and black boxes.  By viewing the HTML source in the browser, I could see that the img tag src’s were calling timthumb.php.  Pasting this into a new browser address bar revealed the source of the problem – the internal server (500) errors.

I rechecked the FotoFolio setup instructions (including the overzealous chmod’s for TimThumb’s sake), and verified the script source.  777, 755, whatever it takes.   This process was made a bit more challenging since I had again lost ssh access to the host, and had to resort to using cpanel’s File Manager.  In frustration, I finally went back to square one and found that it wasn’t the temporary cache directory, parent directory, or template directory that had the offensive 777 permissions, but one at a higher-level.  Apache was refusing to run the script if any parent folder (between itself and document root) had world execute or write permissions.  I can understand the thinking, but wow.  755’ing that puppy fixed the problem and I was off and running at 1:00 am.

While searching for a solution, I saw that others had stumbled into this and found a few FotoFolio-based sites with black box images – telltale signs of the same struggles.  So, I would offer the following suggestions to others who might hit this:

  1. Set the 777 permissions as directed by the instructions, but be prepared to back off if needed.  The FotoFolio instructions included setting 777 on the scripts directory, not just the cache directory.  Doing this doesn’t work and isn’t necessary.
  2. If you see incorrect thumbnails and preview images, view the source in your browser, find the img src bit with the timthumb.php call, and paste it into a browser.  This will reveal if it’s failing.
  3. If you have upload or resize problems with large images, check the error_log file.  If you see memory allocation errors (“allowed memory size exhausted”), you may have to modify the ini_set call in timthumb.php to be under your memory limit.  A phpinfo script will show you your memory limit.  Of course, uploading only reasonably-sized images helps, too.

The site is basically up, so check it out: reflectionsofHismercies.com.  FotoFolio is a nice theme and, if things continue to go well, I’ll stick with it, although I will need to customize it for a shopping cart, etc.

Perhaps the moral of this story is that insecurity can be costly. Too often, fear (insecurity) of an exploit can lead to ineffective (insecure) security measures.  In this environment, the 777 restrictions added costs but no benefits.  Hosts and developers should take precise, effective security measures, rather than using a shotgun approach.

Building bridges

One of my New Years Weekend projects was starting that long-overdue bridge spanning the creek separating us from the “back 3” of our property.  Tina worked with me on it during the bitter weather, including cold, high winds, and even sleet.

Tina’s involvement is great, but can be a double-edged sword.  She’s a tremendous help, but also an informed critic.  As the daughter of a talented custom home builder, she has the genes and exposure to spot a mistake at 500 paces.

So, for example, while setting one of the 16′ 2×12 joists across the creek, she wouldn’t let me stop at almost level.  Her words were “I’ll know it’s 1/4 inch out of level when I’m walking across it,” while mine were, “it’s a bridge in the woods!”  We had some lively discussions, but all in fun.  At least I think so.

Working together against a real challenge: that’s how we build the real bridges.  And we’ve learned a lot through the years with projects like this: like not to hang wallpaper together.

We got the 6×6 posts cemented in and the joists hung.  Watch for more updates.

New Decade, New Blog

Welcome to DerekWilliams.us, my new blog site for 2010 and hopefully following.

Current interests include parallel programming, databases (especially DB2), family fun, web development, running, Biblical worldviews, hiking, kayaking, and, of course, HomeStarRunner.  D.V., I’ll cover these things and whatever else pops into my streams of consciousness.

So perhaps WriteStreams of Consciousness is a fitting title for this blog, since WriteStreams are written to, never read.  Waka waka waka.