It’s Friday, and time again for some Friday Fixes: selected problems I encountered during the week and their solutions.

Hashes Make for Poor Traces

SQL and parameter tracing  is covered well by most ORMs like Hibernate, but you’re on your own when using JDBC directly.  It’s helpful to funnel JDBC access through one place with a common wrapper or utility class, but if all you get is the PreparedStatement, there are no public methods to get the SQL text and parameter array from it for tracing.  Unbewiebable.  PreparedStatement.toString sometimes includes useful information, but that depends entirely on the JDBC driver.  In my case, all I got was the class name and hash.  So I just went upstream a bit with my calls and tracing hooks so I could pass in the original SQL text and parameter array.

Samy Isn’t My Hero

You’d think URL rewriting went out with the Backstreet Boys, but unfortunately, JSESSIONID is alive and well, and frequenting many address bars.  This week, I’ve been focused on hack-proofing (counting down the OWASP Top Ten) so I should be a bit more sensitive to these things.  Yet I inadvertently gave away my session when emailing a link within the team management system I use (does it count as a stolen session if you give it away?)  Not only does this system include the direct sessionguid in the URL, it also doesn’t validate session references (like by IP address) and it uses long expirations.

At least this system invalidates the session on logout, so I’ve resumed my habit of manually logging out when done.  That, and attention to URL parameters is a burden we must all carry in this insecure web world.  Site owners, update your sites.  Until then, let’s be careful out there.

Virtual Time Zone

While directing offshore developers last year, thinking 9 1/2 or 10 1/2 hours ahead finally became natural.  Having an Additional Clock set in Windows 7 provided a quick backup.  Amazingly, some software still isn’t timezone-aware, causing problems such as missed updates.  I won’t name names or share details, but I opted for a simple solution: keep a VM set in IST and use it for this purpose.  Virtual servers are cheap these days, and it’s easy enough to keep one in the cloud, hovering over the timezone of choice.

SQL1092 Redux

Yes, bugs know no geographical boundaries.  A blog reader from Zoetermeer in the Netherlands dropped me another note this week, this time about some SQL1092 issues he encountered.  Full details are here; the quick fix was the DB2_GRP_LOOKUP change.

This DB2 issue comes up frequently enough that DB2 should either change the default setting or also adopt WebSphere MQ’s strategy of using a separate limited domain ID just for group enumeration.  Those IBM’ers should talk.

Those Mainframe Hippies

I traditionally think of mainframe DB2’ers as belt-and-suspenders codgers who check your grammar and grade your penmanship, while viewing DB2 LUW users as the freewheeling kids who sometimes don’t know better.  That changes some with each new version, as the two platforms become more cross-compatible.

So I was surprised this week to find that DB2 for z/OS was more flexible than DB2 LUW on timestamp formats.  Mainframe DB2 has long accepted spaces and colons as separators, but unlike DB2 LUW, you can mix and match ’em.  For example, DB2 z/OS 9 and higher will take any of the following:

‘2012-03-16 19:20:00’

‘2012-03-16-19.20.00’

‘2012-03-16-19:20:00’

‘2012-03-16 19.20.00’

DB2 LUW (9.7) will reject the last two with SQL0180 errors.

Knowing the limits is important when writing code and scripts that work across multiple DB2 platforms and versions.  The problem could get worse as DB2 LUW adds more Oracle compatibility, but as long as the DB2 kids and codgers stay in sync, we can enjoy some format flexibility there.