Feb 1 ’05

z/Data Perspectives: DB2 Annoyances

by Editor in z/Journal

If you’re a regular user of any type of software, you know what I mean by an “annoyance.” Annoyances are those little things software does to drive intelligent people crazy. There isn’t a piece of commercial software out there that isn’t plagued with annoyances, and DB2 has its fair share of them. Let’s examine a few.


DB2’s implementation of triggers has annoying aspects. The first trigger annoyance is one that catches every new trigger user: the requirement to change the SQL terminator before you try to issue the CREATE TRIGGER statement. This is required because a trigger is composed of SQL statements, each of which is terminated by a semicolon; but the CREATE TRIGGER statement is also an SQL statement and it needs to be terminated, too. If you use the same SQL terminator, which defaults to a semicolon, then DB2 gets “confused” and will refuse to create the trigger. The usual workaround is to use a semicolon in the trigger text and change the SQL terminator for the UOW that executes the CREATE TRIGGER statement.

The way you specify a different SQL termination character depends on how you are issuing your SQL. For example, using DSNTEP2, you can specify SET TERMINATOR TO in a comment to use a different character (such as the # sign) to terminate the SQL.

If you are working with DB2 on Linux, Unix or Windows platforms, you can use the command -td# in the Command Line Processor (CLP) to change the termination character. Or, if you’re using DB2 Control Center, you can select the “Tools Settings” option from the “Tools” pull-down menu. Once there, check the box next to the line labeled “Use statement termination character” and supply the character you wish to use in the prompt box to the right of the line.

However, terminating the SQL statement isn’t the only annoyance when it comes to DB2 triggers. It’s also very annoying that you can’t create a trigger specifying EXPLAIN YES for the SQL in that trigger at the same time. There’s no EXPLAIN option for the CREATE TRIGGER statement, so the package that’s created for the trigger isn’t explained. You can work around this by immediately following your CREATE TRIGGER statement with a REBIND TRIGGER PACKAGE statement that specifies EXPLAIN(YES). It works, but of course, it’s annoying.


After dealing with triggers, LOBs (Large OBjects) are probably the next most annoying part of managing DB2 databases and applications. LOB refers to a series of data types (BLOB, CLOB, and DBCLOB) that can be used to store very large multi-media data in DB2 tables. But the implementation for LOBs in DB2 seems, to me, to be a bit incomplete. Why is that?

Well, let’s start with the fact there’s no easy way to LOAD them. If the total length of the LOB column and the base table row is less than 32KB, you can use the LOAD utility to populate the data into DB2. If the LOB column is larger, you must use INSERT or UPDATE statements. That means you have to write a program to load LOBs into the database, and who wants to do that?

Additionally, reorganizing a LOB table space doesn’t reclaim physical space, even though reorganizing a LOB table space can help to improve the effectiveness of prefetch because it removes any embedded free space and will attempt to make the LOB pages contiguous.

Finally, and perhaps the most annoying, is the need to create a unique index on the auxiliary table. The auxiliary table is the table in the LOB table space that holds the LOB data. The index is required. But why does the DBA have to create that index? No columns are specified and the index is mandatory. The CREATE statement will look something like this:

CREATE UNIQUE INDEX indexxname ON auxtab;

Couldn’t DB2 create it automatically “behind the scenes” when the auxiliary table is created?

Other Annoyances

There are several additional things that annoy me about DB2, but triggers and LOBs top the list. A somewhat annoying documentation aspect is that the SQL examples in the DB2 manuals are far too simple. In some cases, the examples are so simple that users don’t use the available features as effectively as they could. For example, consider the simple examples used for CASE statements and table expressions. Additionally, the DSNZPARMs aren’t very well- documented. The best source for DSNZPARM information is an appendix in the Installation Guidethat includes links to descriptions. Of course, additional information is spread throughout the rest of the DB2 manuals, too. I’d really like to see a separate, comprehensive DSNZPARM manual.

These are just a few of the DB2 annoyances that drive me crazy. If I missed your favorite, please share it with me at Craig_Mullins@BMC.com.