7 posts tagged with "db2"

Sometimes you just want to extract a single BLOB from a DB2 database... you don't want to write any software... you don't want to pull out some scripting language with a true DB2 database driver binding... you just want to get a BLOB from the database, and write it to a file - using nothing but the command line.

Well, there are two ways, that I know of, to do so. The first one, is the officially "correct" method, and should always work. The second method is even easier, but only works for very small BLOBs (which is all you need sometimes).

During the past three years, I've often been amazed at just how difficult it can be to get IBM's DB2 to successfully restore from its own backups. Even in the most simple case ( offline backups, with circular logging) you can run into difficulty, but it can be incredibly difficult if the database has been configured to use log shipping, and especially if the backups were taken "online" - that, it seems, can be most the difficult DB2 backup / restore scenario. And, of course, DB's error messages usually offer very little (to the untrained eye) to explain what's going wrong.

However, over time, I have finally figured out most of the problems, and so most restorations work quite smoothly for me now. So, I thought I'd quickly share the most useful (for me) backup and restore commands.

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!! :(

So if, like me, you're just hanging out for the next DB2 Express-C release, then jump over to this thread, and post a reply :)

I had a really frustrating time yesterday, trying to sort out some bugs in IBM's ibm_db DB2 driver for Python. Unfortunately I don't know Python well enough to know whether the bugs are in the ibm_db driver, Python, or my misuse if either or both :( But what I can tell you, is that the following Python / ibm_db code results in a definite memory leak.

sql = "SELECT filename, data FROM files WHERE id = ... ";
stmt = ibm_db.exec_immediate(conn,sql);
row = ibm_db.fetch_assoc(stmt);
row.clear(); # Just to be sure.
ibm_db.free_result(stmt);

I've started a discussion on the memory leak issue over at the ibm_db group (on Google Groups), so it will be interesting to see what transpires :) But all I can say at this stage is this: IBM's ibm_db DB2 driver for Python seems only half baked, currently. To be fair though, it is still officially in Beta.

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.

Most computers cannot generate truly random numbers. For this reason, many computer programming languages include pseudo-random number generators. These pseudo-random generators usually need to be seeded so that they don't always reproduce the same sequence of numbers.