If you are
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 is not plagued
with annoyances and DB2 has its fair share of them. Let’s examine a few.
DB2’s implementation of triggers has some aspects that are quite annoying. The
first trigger annoyance is one that catches every new trigger user – and that is
the requirement to first 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 semi-colon; but the CREATE TRIGGER
statement is also a SQL statement and it needs to be terminated, too. If you use
the same SQL terminator, which defaults to a semi-colon, then DB2 gets
“confused” and will refuse to create the trigger. The usual workaround is to use
a semi-colon in the trigger text and change the SQL terminator for the UOW that
executes the CREATE TRIGGER statement.
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 to terminate the SQL. For example:
--#SET TERMINATOR #
<code of trigger goes here>
the # character is read as the end of the CREATE TRIGGER statement (it would
work the same way for any other SQL statement in the DSNTEP2 input). 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 are 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 little prompt box to the right of the line.
terminating the SQL statement is not the only annoyance when it comes to DB2
triggers. It is also very annoying that you cannot create a trigger specifying
EXPLAIN YES for the SQL in that trigger at the same time. There is no EXPLAIN
option for the CREATE TRIGGER statement, so the package that is created for the
trigger is not explained. The way around this is to immediately follow your
CREATE TRIGGER statement with a REBIND TRIGGER PACKAGE statement specifying
EXPLAIN(YES). It works, but of course, it is annoying.
After dealing with triggers, LOBs are probably the next most annoying part of
managing DB2 databases and applications. LOB (stands for Large OBject) and it
refers to a series of data types (BLOB, CLOB, and DBCLOB) that can be used to
store very large multimedia data in DB2 tables. But the implementation for LOBs
in DB2 seems to me to be a bit incomplete. Why is that?
let’s start with the problem that there is 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 greater
in size 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?
isn’t the only LOB problem. A REORG of a LOB table space does not reclaim
physical space. Reorganizing a LOB table space can help to improve the
effectiveness of prefetch though, because it removed any embedded free space and
will attempt to make the LOB pages contiguous.
and perhaps most annoying in the true sense of the word, 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;
DB2 create it automatically “behind the scenes” when the auxiliary table is
created? There is no reason for DB2 to force the DBA to create this index.
There are several more things that annoy me about DB2, but none are as annoying
as triggers and LOBs. 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 do not utilize the available features as effectively as
they could. For example, consider the simple examples used for CASE statements
and table expressions. Additionally, the DSNZPARMs are not very well-documented.
The best source for DSNZPARM information is in an appendix in the Installation
Guide that includes links to descriptions in the manual. 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.
column we’ve looked at just a few of the DB2 annoyances that drive me crazy. If
I missed your favorite, please share it with me at