The DB2 NSE service crashed my
DB2 server over the weekend... again!! And this time it corrupted one of
my user tablespaces!!! Very annoying. However, in the process of restoring the destroyed database from the most recent
backup, I noticed entries in the db2diag.log file indicated that db2text (the NSE indexing process) was trying to
update indexes for a database that no longer exists - ie a database that I dropped some time ago.
Now, this was not new to me... I'd actually seen this benign error before, but had not found a solution to it yet. You see, to fix the problem, the most obvious thing to do is simply drop the indexes via:
db2text 'DROP INDEX idx FOR TEXT CONNECT TO db USER name USING pass'
But, of course, the you cannot execute that, nor any other db2text command, for a non-existent database!! An odd
scenario - I know. It's one that arises when you drop a database, whilst forgetting to first drop the associated NSE
indexes (something you would think the database would know how to take care of - just as it does for normal (non-NSE)
indexes). Anyway, in the past I'd performed some quick searches of the various NSE-related tables, but to no avail.
So that's as far as I had gone previously.
But this time, while I was waiting for DB2 to recover from backup, I decided to look into it a bit further. So, after a
short bit of googling, I came across
this page
on the IBM support site. It turns out that db2text manages (or should I say mismanages) a scheduling file call
ctedem.dat. This is a plaintext file that, so far as I can tell, is identical in format to a standard
crontab file, except for the obvious codes stored in comment lines. Here's a
link to a copy of my original ctedem.dat file if you're interested.
So sure enough, the ctedem.dat file contained a number of (well, two) references to the non-existent database (the
database in question was called JBH). So removing those entries was simple, and did the trick quite nicely.
However, you know with DB2 NSE, nothing is ever that simple ;) While I was looking at the ctedem.dat file, I soon
realised, that there were too many entries for my remaining NSE indexes... indeed, a couple of the NSE indexes were
listed twice! Now, this might not seem like such a big deal, except that it means that often db2text will try to
update the same index twice simultaneously... and given that concurrent db2text processes seems to be what kills my
DB2 server, this is something well worth fixing!
Now, as part of my software development process, I have some convenient scripts that drop / create / recreate all of my
project's NSE indexes. So, I went ahead an dropped all such indexes. And, to no surprise of mine, I found that several
now-defunct ctedem.dat entries remained. So, I dutifully removed them (in fact, emptied the file completely), and
then created the necessary indexes, after which the ctedem.dat file contained the exact expected entries - that is,
with no duplicates.
I then went a checked another DB2 instance on a different server, and found that it had similar old defunct ctedem.dat
entries too.
So, it seems that db2text is indeed quite poor at managing its own ctedem.dat scheduling file - indeed, doing things
like dropping / recreating databases is guaranteed to cause DB2 NSE's ctedem.dat file to contain erroneous entries.
So, it is my strong suggestion, that anytime you use db2text to create and/or drop indexes, you also manually check
the content of the resulting ctedem.dat file too.
One easy check, that simply looks for duplicates, would be the following:
grep -v '^#' ctedem.dat | cut -d' ' -f9 | sort | uniq -c | sort -n | grep -v '^ *1 '
This will output the names of the indexes that have duplicate entries, preceded with a count of how many times each index occurs in the file. For example, when executed on my original ctedem.dat file, the output looks like this:
2 "DB2INST1"."COMPANIES_NAME_IDX"
2 "DB2INST1"."EMAIL_ATTACHMENTS_TEXT_IDX"
2 "DB2INST1"."JOBS_TITLE_IDX"
2 "DB2INST1"."PEOPLE_FIRSTNAME_IDX"
2 "DB2INST1"."PEOPLE_SURNAME_IDX"
indicating that each of those five indexes are listed twice in the ctedem.dat file. If, however, the ctedem.dat
file contains no duplicates (as should be the case), then the above command won't output any text.
Another simple, yet valuable, check would be simply to verify that the number of non-comment entries in the ctedem.dat
file is equal to the number of indexes you have created. In the case of my project, I happen to know that there are
exactly (for now) 9 NSE indexes. So, it's simply a matter of executing something like:
grep -v '^#' ctedem.dat | wc -l
and ensuring that the result is 9.
Let me just finish by saying, once again, that the IBM DB2 Net Search Extender (NSE) is well and truly, without doubt, the single worst aspect of DB2 (if you hang around me, then you'll hear me say that quite frequently). However, IBM have begun importing the NSE capabilities directly into DB2 under the moniker "DB2 Text Search", so hopefully (fingers crossed tightly, but breath not held) that will resolve this piece of disappointing software... did that sound too harsh? ;)