Wednesday, February 14, 2007

DB2 the Hard Way...

I'm having to get to grips with DB2. Here is a list of things I've learned the hard way so far...
  1. You don't put semicolons at the end of DB2 statements
  2. When doing lots of INSERTs put a COMMIT every 1000 or so. (As pointed out here)
  3. If you're uploading a lot of data consider using the DB2 LOAD utility. [More info on how to use it when I get it working]
  4. To get a nice graphical view of your data use something like SQuirreL or DBVisualizer (Update: don't try and use IBM's DB2 Connect, it leaves locks all over the place and locks in DB2 are pretty pervasive...)
  5. Don't try and do bulk uploads with triggers switched on
  6. Remember to create the statistics for your database tables (RUNSTATS SQL command)
  7. Find out what an error code means with db2= ? SQL-[xxx] NOTE: The angle brackets are part of the command
  8. Don't delete a load of rows all at once using "DELETE FROM..." - it'll go very slowly. Instead do in bunches of 10,000 rows. E.g. db2= DELETE FROM things WHERE things_id 900000; DELETE FROM things WHERE things_id 890000; etc; etc
  9. Or, if you can bear it, drop everything and start again from scratch...