Tag Archives: DB2


Code page issues have become unexpectedly common now that recent versions of DB2 LUW default to UTF-8 / 1208 for XML data type support.  In recent days, two separate projects hit errors like the following:

SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.

The root cause was that, with code page 1208, certain extended ASCII values were each converted from one byte to two: “two bytes for the price of one.”  This stretching data overflowed columns sized to expect one byte per character.

So if you get SQL0302 or similar errors, you can easily check the code page with: get db cfg for DBNAME | grep -i code (or get db cfg for DBNAME | find /i “code”).  The quick fix is to specify a code page like 1252 during database creation: create database DBNAME using codeset ibm-1252 territory us.  I do not recommend changing  the db2codepage registry variable for this problem.

However, code page 1252  prevents you from using XML data types.  So if this is an issue, there are at least two other options:

  • If the data you’re storing is really binary data, define the column with for bit data.  No codepage conversion will occur, and the data will typically come back to your application as raw bytes, not encoded strings.
  • Expand the size of the column to accommodate some “two for one” conversions.  Only a few extended ASCII characters get this conversion, but unless you go at least twice as large, this becomes a managed risk of how many of these you’ll get.

Painted into a Corner

I’ve had such good success with DB2 9.7 LUW under 64-bit Linux that I didn’t hesitate to install it on my new development laptop (Windows XP 32-bit, the corp-rat standard).  I immediately upgraded to Fix Pack 2, of course: never install fix level zero of anything.  I created and loaded a few databases and was on my way.

But it didn’t take long to notice some very bad behaviors.  I saw frequent CPU, memory, and I/O spikes, mostly in db2syscs and db2dasstm. On two occasions, all database operations froze without recovering, and db2syscs suddenly became a diehard: I couldn’t kill it from the Service Control Panel, Task Manager, or even pskill.  Windows shutdown then froze, requiring a hard power off.

This sent me on a fact-finding mission.  First stops: the Windows Event Viewer and db2diag.log.  Since I had not changed diagpath, I had to remind myself that instance information files are no longer under SQLLIB (with Windows, they’re now under Documents and Settings\All Users\Application Data\IBM\DB2). I spotted a huge and growing db2diag.log, at diaglevel 3.  It was flooded with messages like:


2010-07-23- I82554H352         LEVEL: Error
PID     : 260                  TID  : 536         PROC : db2dasstm.exe
INSTANCE: DB2                  NODE : 000
EDUID   : 536
FUNCTION: DB2 Tools, DB2 administration server, SchedulerThread_run, probe:5
DATA #1 : String, 50 bytes
Cannot complete scheduler thread’s initialization!


The db2diag flooding would certainly account for the CPU, I/O, and memory spikes, but I’m not sure about the periodic freezes.  But it’s one thing at a time, and couldn’t stop until I had a clean db2diag.

Fortunately, I found a fellow victim who reported the same issue just yesterday.  The root cause was that the DB2 Admin Server (db2dasstm) did not have required authorities on toolsdb.  This was surprising, since I let db2setup create and configure it.  But I’ve been accustomed to chanting the grant dbadm mantra since the introduction of SECADM, so I typed it out.

But in this case, it wouldn’t work. I couldn’t get into toolsdb with an authorization ID that had SECADM in order to do the grant. I tried stepping into it first via SYSADM/SYSADM_GROUP, but no dice. And toolsdb was unique in that SECADM was only granted to some bogus “SYSTEM” ID.  Thank you, db2setup, for painting me into a corner!

To fix it, I had to drop and re-create the toolsdb, following the proper steps to keep DAS happy.  A couple of db2admin stop/starts later and I had a small and steady diag log.

Time will tell if additional problems remain that contributed to the freeze problem (so far, so good), but I learned an important lesson: never let the DB2 installation program create the toolsdb.

I Ignore Your Puny 32 Bits

Customers these days: they think they’re entitled to 64 bits.  Heck, I remember when we had to do everything with just 32 bits, and only half of those worked.

Since 64-bit Windows and Linux are the new normal, the DB2 LUW folks (particularly with 9.7) have been encouraging migration away from 32 bit apps and libraries.  Sometimes this is more than a gentle nudging, as things break and require intervention.

Colleague Allen Cartright shares a tip for managing 32-bit ODBC data sources when 64-bit Windows just wants to ignore them.  So, if you find yourself running 32-bit DB2 code on a 64-bit OS written by a 2-bit company that can’t stand 1-bit of competition, heed his advice:

The ODBC connection manager in Windows Server 2008 (Administrative Tools -> Data Sources) is 64 bit only.  To get to the 32 bit ODBC connection manager in Windows Server 2008 Rx x64 you must run the executable directly as there aren’t any icons for it.  The executable is “C:\Windows\SysWOW64\odbcad32.exe”.  This is the same executable name as the 64 bit version but is in the legacy directory.  Once you have the 32 bit version of the ODBC Connection Manager open you may add the ODBC connections as normal.

The Microsoft Knowledge Base post on this has additional information and recommends a useful naming convention:

To work around this problem, use the appropriate version of the ODBC Administrator tool. If you build and then run an application as a 32-bit application on a 64-bit operating system, you must create the ODBC data source by using the ODBC Administrator tool in %windir%\SysWOW64\odbcad32.exe. To indicate the type of DSN, you can add “_32” to the 32-bit user DSNs and “_64” to the 64-bit user DSNs.


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.

DML Insurance

In a chat today, we discussed how to protect data when formulating DML, particularly those SQL update and delete statements that can destroy perfectly good data if done incorrectly.  You may find yourself having to run such SQLs against large data sets (and, yes, even production data), so no amount of care is too much.

One option is to hold a transaction open, run the DML, select the (uncommitted) results, and then commit if good or rollback if not.  That’s clever, but it can hold a lot of locks for a long time, is error-prone, and doesn’t work for all cases.  Another is to keep full backups handy and restore the table(s) if something goes wrong.  This can be time consuming and can harm availability during restore if the table(s) are large.

A better approach is to create exports of exactly the data you will be changing or deleting.  That is, run an export command whose where clause is exactly the same as that in your update or delete statement.  That way, you can restore (via import) just the data you changed.  For DB2, I use IXF exports since they’re the richest.  So, for example, before you:

update company.employees set salary=100000000 where name=’Derek’

You should:

export to empderek.ixf of ixf select * from company.employees where name=’Derek’

If you realized maybe you shouldn’t have done that, you can put it back with:

import from empderek.ixf of ixf insert_update into company.employees

This can be used to recover from deletes as well (hence insert_update).  For example, before you:

delete from wikipedia.articles where title=’Malamanteau’

You should:

export to articles.ixf of ixf select * from from wikipedia.articles where title=’Malamanteau’

MySQL has fewer export/import options, but mysqldump can definitely help.  For example, export with:

mysqldump wikipedia articles –compact –where=”title=’Malamanteau'” > articles.sql

And, after the above delete, you can put it back with:

mysql wikipedia < articles.sql

For ease of reference, I often give these “backup” IXF and SQL files unique, descriptive names. It’s helpful to include the date and some tag that identifies what the data is.  In the above example, I could have used articles-Malamanteau-12-May-2010.ixf, although Malamanteau is hard to spell and probably isn’t even a real word.

Perhaps this simple technique of “export first, DML second” may rescue you from the occasional “oops moment.”

Sorting it Out

In prior posts, I described some of the benefits of DB2’s Self-Tuning Memory Manager (STMM), along with a few caveats.  One caution was around automated sort heaps, since sort requirements can often be unpredictable and confusing.

High-volume OLTP systems can, at times, experience heavy loads of small sorts or sort-like activity (hash joins, grpby’s, etc.).  It’s not uncommon for many of these to overflow (spill), or for a sub-optimal access plan to arise, even when more than enough memory is available for sorts.  Like the proverbial bad check writer, you’re left thinking, “I can’t be overflowing, I still have memory left!”

Sort overflow symptoms include high I/O and CPU utilization, poor response times, high sort overflow snapshot counts, long sort times, and large numbers of rows written on read-only SQLs like SELECTs.  Sort heap tuning is often one of the “last frontiers” in performance work (after fixing low-hanging fruit like SQLs, indexes, and bufferpools), and can be a stubborn one.

From my experience, most sort overflow problems in DB2 9.x fall into these categories:

  1. SQL.  In high-volume OLTP systems, it’s important to avoid sorting as much as possible.  Developers usually recognize that order by clauses require sorts, but can miss more subtle uses of the sort heap.  Count, distinct, and group by clauses also often require sort heap space, as do hash joins.  Statement snapshots and explains will reveal these.  The first step should be to try to rewrite problem SQLs to eliminate the sorts.  But wherever that isn’t possible, try step 2:
  2. DDL.  Adding or changing indexes and clustering may eliminate sorts.  If an existing index is used but sorting still occurs, check to see if adding index or include columns will help, and verify that allow reverse scans is specified when needed.  Again, the only way to know for sure is to measure by snapshots and explains.  In some cases, MDCs can help, but check it first by “doing the math” or running db2advis.  Sometimes the problem is as simple as outdated statistics (among other things, outdated statistics can cause DB2 to request too small a sort heap), so make sure your runstats is occurring as frequently as needed.
  3. Configuration.  And here we come to the STMM benefits and caveats.  You should “go automatic” with sort heap sizes, but be prepared to monitor and adjust as needed.

STMM only tunes shared sort memory, so to enable self-tuning sort memory, you must:

  • Set the instance (DBM)-level sheapthres to zero (0).  You can verify this with: db2 get dbm cfg  | grep -i sheap
  • Set the database-level sheapthres_shr (total amount of sort memory) and sortheap (memory available for each sort) to automatic.  You can verify this with: db2 get db cfg for <database> | grep -i sort

In some cases (such as when you’re experimenting and don’t want to wait on STMM’s tuning cycles), you may want to set an initial value for sortheap.  To do this, specify it alongside the automatic parameter, like so: db2 update db cfg using sortheap <size> automatic immediate.   But the initial value matters little after STMM “warms up” and then saves its settings.

When monitoring a database with sort overflow problems, keep an eye on the percentage of overflows (under 3% is a good target) and on the current sortheap and sheapthres_shr sizes.  You can view these with the snapshot monitors (db2 get snapshot for all on <database> | grep -i sort) and database configuration (db2 get db cfg for <database> show detail | grep -i sort).   But you may find, as I often have, that frequent, small overflows are occurring even when the sort memory areas have stretched to more than enough space.

Why this happens is a mystery to me.  A parallel symptom I often see is that the sort heap size reported in explain (db2exfmt) outputs is often far smaller than the current sortheap size.  At this point, the only choices are to leave sort heaps at automatic and accept some overflows, or abandon automatic and set a fixed size.  When setting large fixed sizes, remember that an overflowed sort writes the entire sort heap to tempspace.  So, a very large sort heap may coax the optimizer into relying more on sorts, only to have some of these very large sorts spill everything.

IBM is considering allowing ceiling and/or floor values for certain automatic parameters.  I do hope this gets added in a future DB2, and that sort heaps are among the first that can be configured this way.  Until then, DBAs will be left to sort this out on their own.

Yes You May

I’ve whined a bit lately about having to jump through syntactic hoops to get past new security restrictions.  Yet one of today’s DB2 barriers was a functional change, and not security-related.

I have this long-standing habit of using IXF export and import to quickly copy tables and shuffle data around.  So much so that commands like the following just flow from the subconscious:

db2 import from myfile.ixf of ixf create into mytable

Trouble is, create (and its variants) have been deprecated since DB2 9.5, so this command fails with an SQL3311 error in my DB2 9.7 environments.  The help text for that error (db2 ? sql3311) provides the work-around: add the forcecreate modifier.  That is:

db2 import from myfile.ixf of ixf modified by forcecreate create into mytable

I understand the reasoning for the change: new features like XML, MDC, and table partitioning have outgrown IXF.  But breaking compatibility of frequently-used commands is just plain cruel punishment toward old guys like me.  Yet since “modified by forcecreate” is the new “please”, I’m sure I’ll eventually learn to say it the first time.

Simon Says

There was a bit more dialog today about impersonating the DB2 instance owner.  It’s a quick way to get around controls that newer versions of DB2 and tighter Windows and network security have brought us.  The extra step is annoying, but trying to convince the system you don’t need it is often worse.

Impersonation and elevation have become the “new normal” these days.  I’ve grown so accustomed to opening “run as administrator” shells in UAC Windows (7/Vista/2008), typing runas commands in XP, and using sudo in Ubuntu that these have become second nature.  And that level of user acceptance usually translates into approval to expand the practice, rather than a mandate to remove the inconvenience.  Enhancing security usually includes putting up new barriers.

A former co-worker has often said that what we really need is software that determines whether a user’s intentions are honorable.  Perhaps then security would become seamless.  But it’s more likely that its implementation would also test our manners and fading patience.

Isolation Level Levels

I got a question today about whether CS (cursor stability) really was the default isolation level for dynamic SQLs from a DB2 CLI application.  The short answer is, “yes”, but that can be overridden at many different levels.  So many that I thought of Shrek: onions have layers, ogres have layers, DB2 has layers.  Here are just a few:

  • In db2cli.ini, using the TXNISOLATION keyword.  It does require work to get it to apply to your connections.
  • At the connection level, by sending “set current isolation”, or setting the SQL_TXN_ISOLATION connection attribute (SetConnectAttr API)
  • For static SQLs, in the access plan (the bind).  For the DB2 CLI packages, the package naming convention includes the expected isolation level, and you can verify with: select pkgname, isolation from syscat.packages where pkgname like ‘SYS%’
  • At the statement level, using the with keyword.

There are further settings to tweak the semantics of some isolation levels.  For example, DB2 9.7 offers things like the cur_commit db config parameter and db2_evaluncommitted registry variable to change CS behaviors.

So with all these knobs and overrides, it’d be nice if an application could query its actual effective isolation level.  Running “values(current isolation)” only returns a value if “set current isolation” is used.  Also, it would be nice if dynamic SQL snapshots included the isolation level, or if db2exfmt consistently showed the correct level.  Lacking this, you have to combine these outputs with db2pd results.  That’s a lot of layers to peel back, which could make even an ogre cry.

That No HLL Thing

Probably the worst thing about high level languages (HLL) is that they are so good in what they are doing. Good enough to conquer the entire market on programming and hold it for decades.

Wait! That is a bad thing? How?

It is a bad thing because high level languages are appropriate for a wide range of tasks, but not for every task. Yet it is exactly that that caused them to be used in contexts where they are not appropriate. In the last month alone, my strong recommendation for two different clients was that they need to switch to assembly language because it would greatly simplify the work that they need to do.

That met with some (justified) resistance, predictably. People think that high level languages are the way to write software. I decided to write a series of blog posts about the topic, trying to explain why you might want to work with a low level language.

High level languages have the following properties:

* Standardization
* Simplification
* Optimization
* Ease of trainification

Just about any of the No HLL approaches give up on some of those properties., usually, it gives up on all of those properties. But think about how useful a HLL is, how flexible it can be. Why give it up?

Indeed, the most common reason to want to move from a high level language is running into the HLL limitations. In short, high level languages don’t scale. Actually, let me phrase that a little more strongly, high level languages cannot be made to scale.

The problem is inherit into the basic requirements of a high level language, it must be compiled, to handle things like converting text to machine language, etc. The problem, however, is that trying to scale a high level language system over a set of coding styles. At that point, you run head on into the Abstraction Penalty, which state that if performance is your absolute requirement, you need to give up on either optimization or ease of coding.

In most high scaling environments, it is not feasible to give up on either option, so high level languages are out. That leaves you with the No HLL options, I am going to dedicate a post for each of the following, let me know if I missed something:

* Gas
* Typing machine code into a hex editor
* Typing machine code into notepad or vi

Other languages, namely PL/S and inline assembly, exists. PL/S suffers from the same problem regarding the Abstraction Penalty as high level languages, and inline assembly is basically a special case of No HLL.

But seriously now… this post is, of course, an April Fools Day joke: a spoof on (and transliteration of) That No SQL Thing at Ayende.com.  (You can follow the link to the post, or just Topeka it).  Oren and other NoSQL advocates do make some good points and offer very good advice.  But be careful out there, especially when it comes to such architecture decisions, so that you pick the best tool for the job: SQL or NoSQL.  And don’t get fooled again.

Don’t Get Fooled Again

Surprisingly, I caught a little flack for this comment I made several weeks ago:

“If all you need are LOBs, you probably don’t need a relational database.  And if you need a relational database, you probably shouldn’t use LOBs.”

The surprising part was the question raised: “why would you ever use a relational database these days?”  It was argued from some familiar and simple high-level reasons (trends, having to write SQL, taking the car apart to put it in the garage, etc.), and came from a NoSQL fan.

This objection seemed anachronistic to me, since we just finished migrating one of our large products from a non-relational model to using DB2 for all data storage.  Why would we do such a thing?  Lots of reasons, but it only took one: paying customers wanted it.  Further, data mining has become so popular and successful lately (on the wings of relational databases) that it’s hard to imagine tossing aside that shiny new benefit.

The NoSQL revolution has taken on a reactionary bent (just consider the name), which is odd for a new movement.  Chesterton reminds us that “the business of progressives is to go on making mistakes, and the business of the conservatives is to prevent the mistakes from being corrected.”  NoSQL advertises itself like a progressive movement, but actually falls to the right of conservative.  BigTable, Cassandra, and HBase are no newer concepts than the ancient things grandpa tells war stories about: flat files, hash files, partition stores, CAM, BDAM, ISAM, etc.  So blindly applying NoSQL is making the new mistake of rejecting well-established corrections to old mistakes.

When making architectural decisions like whether to use a relational database or not, I often start with a blank 8 1/2 x 11 sheet of paper, turned landscape.  At the top I write the relevant business requirements (real, not imagined, ones).  I then do a simple left-and-right table of pros and cons.  At the bottom I write areas where architectural prototypes are needed, if any.  This, of course, helps me weigh the options and make a decision, but it also means I “own” the cons.  If I decide to use an RDMS, I accept the costs.  If I decide not to use an RDMS, I willingly reject the benefits they offer with eyes wide open.

Yet the war of words for and against NoSQL rages on, often without fully or correctly acknowledging the cons, nor the simple fact that you don’t do both at the same time.  Many problems are best solved with a relational database and many are best solved without one.

In the early 90s, I would joke that there existed a parallel universe where client/server has fallen out of fashion and the new, cool buzz was mainframes and 3270 terminals. Funny that we soon joined that parallel universe when web apps and cloud computing became our trend.  Andrew Tanenbaum notes that, while recapitulation doesn’t really happen in biology, computer history does rightfully repeat itself.  Old solutions should be applied to new technologies; at the very least it keeps consultants gainfully employed.  Let the pendulum swing, but truly grok all the pros and cons as history repeats itself.

Unlike Ted Dziuba, I don’t want NoSQL to die.  By definition, it can’t, and that’s arguing a strawman anyway.  I just want it to be used where it best fits.  And the same goes for relational databases.  Repeat after me: “no golden hammers; pick the best tool for the job.”  Just don’t be a lemming.  And don’t get fooled again.

Impersonating Better Security

I got a question today from a co-worker who was painted into a corner trying access a database he had restored on his Windows development machine.  He stumbled over DB2 9.7’s new security twists, such as not having dbadm authority by default.  I rattled off my familiar quick fix:

db2 connect to <dbname>
db2 grant dbadm on database to <userid>

However, his default Windows user ID didn’t have secadm or sysadm authority, so that failed with an error.   So, I had him impersonate the one that did:

runas /user:<adminuser> db2cmd

Repeating the grant command from this new command shell did the trick.  It could have also been done with:

db2 connect to <dbname> user <adminuser> using <adminpassword>

And so it goes.  No matter how refined security policies become, they can usually be circumvented with a little impersonation.  For example, think of how many times we quickly and mindlessly sudo under Ubuntu.  In this case, impersonation was a fast route to giving a developer the access he should have had by default anyway.  Today’s technology cannot solve the impersonation problem, but sometimes we consider that more a feature than a bug.

Can’t Get There From Here

I got a question today from a friend who was trying “explain stmtcache all” on DB2 LUW and wondered why it was unrecognized.  He had stumbled across this command while looking up the explain syntax in the DB2 online documentation and was lured in by its promises.  What he didn’t notice was that this was in the DB2 for z/OS documentation, and that this command isn’t available in LUW.

Don’t get me started.

In the olden days, DB2 on the server and DB2 on the mainframe were two very different products, and no-one expected much compatibility.   It took years after the OS/2 EE Database Manager rewrite for DB2 LUW to catch up to DB2 for z/OS’ level of sophistication.  But DB2 LUW has come a very long way very quickly in recent years, and has adopted most of the important features previously only available from the mainframe.  I’ve gotten pretty used to this.

But I’ll often hear of all this cool stuff in new versions of DB2 for z/OS, like in the version 10 beta that rolls out tomorrow.  I expect new features to eventually appear in a subsequent LUW version, but sometimes they never do.  Explain stmtcache all is one such example.  In this case, architectural differences may mean that it will never come down from on-high, but it would be nice to have in LUW.  Yes, I can loop through snap_stmts or top_dynamic_sql and do explain all for on each of the stmt_texts (I have scripts that do this), but this is slow.  And changing the current explain mode is another option, but it’s rarely available and rarely efficient.  It’s a dynamic SQL world now, and the kinds of trends you can spot by mining the snapshot tables (and presumably the dsn_statement_cache_table) are just too useful.  So give us the best tools for it, even if we have to borrow them from the big iron.

Another related example is the stated plan to add access plan stability features for dynamic SQL (I don’t need it for static SQL, so I can skip the current PLANMGMT capabilities).  But, alas, this is currently only being planned for… where?  DB2 for z/OS, of course.

admin_cmd can

The question came up again today: “how do I run a DB2 export through my ODBC connection?”  Before recent versions of DB2, the answer was, “you can’t.”  If you tried just running the command, DB2 would give you the classic SQL0104 “duh” error message: “…an unexpected token was found…”

That’s because administrative commands and utilities require special handling.  And before DB2 8.2, they could only be run through a command line processor session.  Programs could not use their normal database connections for things such as export, runstats, reorg, or “update db cfg.”  The alternatives were often inelegant, such as calling cumbersome utility functions like db2Export/sqluexpr or shelling out to a script.

Fortunately, the new admin_cmd stored procedure lets you run several of these commands through a normal CLI or ODBC connection, much like any SQL statement.  You just pass the command as a parameter; for example:

call admin_cmd(‘export to sales.ixf of ixf select * from sales’)

Even if you’re not writing code, admin_cmd is useful for doing maintenance and data movement directly from your favorite tools.  Since so many programs and tools use ODBC connections, it’s a convenient and portable way of handling your DB2 administrivia.

Don’t Get CLOBbered

The subject of direct I/Os came up in an unexpected phone call today.  This was from a friend who had long ago recovered from CLOB abuse and was concerned that he had somehow fallen off the wagon again.

Often new OO developers are tempted to treat the database as little more than a file system.  With wonderful things like CLOBs, BLOBs, and long varchars, who would actually parse their objects into columns?  Why bother with ORM frameworks?  Why take the car apart to put it in the garage?

The answer, of course, lies in the system requirements.  If all you need are LOBs, you probably don’t need a relational database.  And if you need a relational database, you probably shouldn’t use LOBs.

It’s not only an important design issue, but a big performance issue as well.  In DB2, LOBs and other large objects do not go in the bufferpools.  This means every read and write is physical – DB2 must go directly to disk.  Enabling file system caching on large object tablespaces helps some.  But, even with that, heavy use of LOBs is one of the quickest ways to kill OLTP performance.

There is a place in this world for LOBs and other large objects, used carefully and sparingly.  But far too often they’re used for object storage shortcuts.  I’ve had to tune a few systems where CLOBs factored heavily into the design and were among the most frequently-accessed data in the system.  LOB abuse has become so common that the snapshot counters for direct reads and direct writes are among the first I check when measuring a new system.  Sometimes the fix is simple: convert the CLOB types to varchars, with for bit data, if needed. Sometimes, though, the data is so large or the process so entrenched that deeper surgery is required.

This post-op friend of mine had long ago eradicated direct I/Os in the billions, but was now seeing counts in the few hundred thousands range after several hours of heavy system activity.  A quick check of the tablespace snapshot for syscatspace found the expected smoking gun: nearly all of these were in the system catalog.

Several of the system catalog tables (such as syscat.tables, columns, and indexes) do use LOBS.  They are strategically accessed, but can (especially after changes) cause some direct I/O spikes.  There are some tricks you can play to minimize this, but these direct I/Os are typically unimportant in the grand scheme of things.  Just subtract the syscatspace numbers from the DB-level totals and see if what’s left demands a closer look.  This will help you distinguish true LOB abuse symptoms from flashbacks.


I recently wrote about using DB2 9.x’s Self Tuning Memory Manager (STMM) to automatically configure memory sizes.  If you’re running DB2 9.5 and 9.7, automatic configuration is available for a long list of parameters.  But what if you have to support 9.1, before several settings went automatic?

This question came to me yesterday from a co-worker who was getting database heap “out of memory” errors.  This was under DB2 9.1, where automatic wasn’t yet allowed for dbheap.  As I wrote earlier, several memory areas (like the log buffers) come out of dbheap, so if you increase one of these or set one to automatic, the dbheap needs to grow to accommodate it.  If you have to support 9.1, an automatic dbheap is not an option, so you may have to find a “high enough” fixed ceiling the hard way: trial and error.

You can get DB2 to help you come up with a number, a couple of ways:

  • Run autoconfigure.  Use “apply none”; for example, “db2 autoconfigure apply none”.  See what it recommends.
  • Run the system for awhile (under volume) on a DB2 9.7 install, with STMM enabled and dbheap set to automatic.  Then run “db2 get db cfg show detail” and see what it computed; it’ll be in parentheses after the word automatic.

These same approaches can also be used to determine good optional initial sizes to use along with  automatic settings.  STMM remembers and stores the results of prior self-tuning activities, so once your database has been running awhile under typical volumes (for at least a few 3 minute tuning cycles), the initial sizes don’t matter much.  But, in some cases, you may want to provide initial values so that a new database install is ready to bolt into action.

The Mute Button

Today, Craig Mullins and others took Larry Ellison to task about his sweeping, unfounded claims about Oracle vs. DB2.  Ellison’s comments often invite easy parody (remember the “Fake Larry Ellison”s?).  Apart from that, such remarks aren’t valuable and can safely be ignored.

Both Oracle and DB2 are solid databases and the ongoing TPC-C competition is good for both products.  But stick to facts and evidence; this is not a topic for uninformed, biased opinions from 50,000 feet.  See Craig’s post for some facts.  And Craig refers to our friend, the Viewpointe national check image archive.

Larry did comment on something that he knows and does have direct control over.  Recent history and guidance aside, Ellison said that, rather than laying off any Sun employees, he will be hiring 2,000 more in the next few months.  Great news!  We’ll be watching.

Taking Inventory

Some questions came in today from a customer’s conversion programmer who was exploring uses of various related coded columns in the system’s DB2 tables.  In this case, the quickest way was to “dive in” and run some simple count and group by queries.  For example,

select mycodecolumn1, mycodecolumn2, count(*) from mytable group by mycodecolumn1, mycodecolumn2 with ur

Sometimes it’s helpful to rollup or cube these to get sub-totals:

select mycodecolumn1, mycodecolumn2, count(*) from mytable group by rollup(mycodecolumn1, mycodecolumn2) with ur

A common problem with DB2’s standard cube implementation is that it’s hard to distinguish null values from the summary (“all”) values.  If you don’t want to see null values, simply add that to the where clause:

select mycodecolumn1, mycodecolumn2, count(*) from mytable where mycodecolumn1 is not null and mycodecolumn2 is not null group by rollup(mycodecolumn1, mycodecolumn2) with ur

If you do want to see null values, use the grouping function to distinguish:

select mycodecolumn1, mycodecolumn2, grouping(mycodecolumn1), grouping(mycodecolumn2), count(*) from mytable group by rollup(mycodecolumn1, mycodecolumn2) with ur

Zero (0) means it’s the actual column value (null or otherwise); one (1) means it’s a summary row.

Translating those coded values into meaningful descriptions is often helpful.  This can be done by joining to lookup tables (those tables that map codes to descriptions), or by using case statements.  But there are other, quicker ways.  More on that later.

STeMMing The Tide

The sheer number of DB2’s memory-related parameters can hit like a tidal wave.  Bufferpools, sort heaps, lock lists, package/statement caches, and various other heaps can all harm performance if undersized, and can waste or overcommit memory if oversized.  Further, many of the configuration parameters are interrelated; for example, increase logbufsz and you may also have to increase dbheap to accomodate it.  DBAs and performance tuners can feel overwhelmed by the number of knobs one must turn in each database.  And developers can feel overburdened when providing scripts and configuration tools to right-size their databases for each customer’s unique volume requirements.

Fortunately the latest (9.x) versions of DB2 LUW provide the Self-Tuning Memory Manager (STMM).  This feature significantly simplifies memory management in DB2, letting the database automatically size memory areas based on activity and needs.  For most parameters, you can also specify an optional initial size to avoid the overhead of progressively stretching memory after startup.

To turn on STMM for a database, specify:

update db cfg using self_tuning_mem on

You’ll likely also want database_memory set to automatic:

update db cfg using database_memory automatic

You can fix-size or initial-size database_memory, or use the computed option for back-compatibility.  The publib DB2 documentation has more information.

At this point, you can auto-size many of the memory areas.  For example, for dynamic lock list sizing, specify:

update db cfg using maxlocks automatic
update db cfg using locklist automatic

Depending on DB2 version, you can configure any of the following this way: dbheap, pckcachesz, sortheap, stmtheap, applheapsz, appl_memory, stat_heap_sz, dft_prefetch_sz, num_iocleaners, num_ioservers, maxappls, and avg_appls.   Anyone who has tried to package or build scripts that set these beyond the old (painfully too low) defaults can appreciate the benefits of “going automatic.”

Perhaps the greatest benefit comes when auto-sizing bufferpools.  For that, specify:

alter bufferpool <poolname> size automatic

To specify an initial (startup) size (in pages), place it before the automatic keyword.

In my experience so far, “going automatic” has worked swimmingly, with one exception: sort heaps.  OLTP systems can, at times, experience heavy loads of small sorts or sort-like activity (hash joins, grpby’s, etc.).  It’s not uncommon for too many of these to overflow (spill), or for a sub-optimal access plan to arise, even when more than enough memory is available for sorts.  This is partly because auto-sizing the sort heap requires some extra work and partly because the optimizer can’t predict future sizes.  More on this in a future post.

Just Add Water

I decided to go with Ubuntu 9.10 64-bit this time on my newest development VM.  But when first starting the DB2 9.7 install (db2setup), I got the following errors:

The 32 bit library file libstdc++.so.6 is not found on the system.
32-bit applications may be affected.
The required library file libaio.so.1 is not found on the system.

Those are the standard C++ and asynchronous I/O libraries.  My fingers were already a bit tired from sudo apt-get install‘ing and config’ing the world, but a couple more (with !s:s/… shortcuts) wouldn’t kill me, so:

sudo apt-get install libstdc++6
sudo apt-get install libaio-dev

… and I was off and running.

One of these days, someone will package an “everything you could possibly every need” distro to speed setup.  Why not, disk and bandwidth are cheap.  Until then, maybe I’ll script the whole thing or at least save off my baseline VM.