Rav Ahuja (an IBM product manager) has just posted a very interesting question over at the IBM DB2 Express Forum titled Who wants DB2 Express-C 9.5.2? As you can see in my reply, I, for one, am very interested in getting my hands on a DB2 Express-C 9.5.2 release, as I live in hope that it will solve some my db2text grievances!! :(
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'
I have a number of DB2 stored procedures that must use the NSE CONTAINS scalar function with optional search
parameters. It turns out, that if you write such stored procedures in the most obvious way, then the performance is
terrible. However, with a little insight, and some resulting tweaks, such stored procedures can be sped up immensely -
eg more than 1,000 times!!
But first, let me explain what I mean by "optional search parameters". Basically, what I mean is stored procedures that return a result set based on a number of potential filter parameters, where the an NSE search string is just one of many possible filters.