Truncating tables in DB2

If you have a table full of data and want to just drop the lot you can truncate it, just as you can in Oracle. The command is:

db2= alter table [tablename] activate not logged initially with empty table

Read More

DB2: Finding the maximum / minimum / average value in a given column

db2= SELECT MAX ([your column to query]) FROM [your table to query]

Read More

Showing a Table's Foreign Keys with DB2 Control Centre

Might be obvious to some. Not to me...

1. Start Control Centre and connect to your database
2. Select "Tables" for your database from the tree in the left hand panel
3. Right click on the table you are interested in in the tables panel on the right hand side and select the "Alter Table" option
4. Select the "Keys" tab in the new window and voila.

Read More

DB2 Import Commands Failing With "transaction log for db is full" Errors

Trying to upload test data with the DB2 Import Utility. I have a .CSV file with 100,000 records in it. It used to work; then I added another column and now it fails with "transaction log for database is full" errors.

:-(

It turns out that it wasn't committing enough. In fact I didn;t know when I was committing at all. I discovered that the "sensible" defaults can sometimes not be so sensible. In order to explicitly set a commit count you need to add the following to your import command (highlighted in red):

db2 "import from [your_csv_file_name] of del commitcount 1000 insert into [table_name]([column A], [column B], [column C], ...)"

Read More

Using DB2 Load to Batch Upload Data (DRAFT)

UPDATE 1: I've since found out that although DB2 LOAD is quick, it's more complicated than the documentation implies. It also gets you into an inconsistent state nightmare really easily. I'm investigating DB2 IMPORT as an (slower but more reliable) alternative.

UPDATE 2: A DBA friend has just given me a command to use to list all the tables in DB2 which are in an inconsistent state. It is as follows:

select substr(tabname,1,30),status from syscat.tables where tabschema='[your schema owner]' and status='C'

On our current project we have the need to load large volumes of data into DB2. We also dont want to sit around for years while we do it with SQL files. We used DB2 Loader to speed things up.

Firstly we needed to create a csv file for each table where we were to load our data. Luckily we had a set of java classes which we'd used to generate our SQL files so we simply added some additional code to write the same information comma seperated. I'll not provide any more detail. It's kind of obvious what this means.

Next, we scripted our calls to the "db2 load" command so that all our table data could be loaded with a single command. An example script follows:

echo "Connecting to the database..."
db2 "connect to [your_database] user [db_user] using [db_password]
LOG="load.log"

echo "Loading data into mytable..."
db2 "load from [your_data.csv] of del replace into [mytable] (c1, c2, c3)" > $LOG
db2 "set integrity for mytable immediate checked" > $LOG

NOTE: It assumes that we are loading into a table called mytable as follows:

CREATE TABLE mytable (c1 VARCHAR(30), c2 INT, c3 CHAR(1))

and that the csv file looks like this:

Hello world, 1, Y
Hi again, 2, N

Read More

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...

Read More

Blog Entries I Would Write, Had I the Time...

I keep thinking of blogs entries I would like to write. Get things out of my brain and down on a page (of sorts). If I ever get round to it, my current output would include:

• What I'd do with an Open Source Second Life Universe - There are a million uses I can think of for a virtual Second Life. None of which I'd ever get the chance to try out myself. Maybe I could write about them instead...
• Tips for a First-Time-Java-Project - If I knew then what I do now, I wouldn't have made a bunch of mistakes.
  
  
• Building JRuby with Netbeans and Testing it against Oracle - I have barely fiddled with it, let along built and ran test suites against it but then this is a wish list...
• A Java Mobile version of my Expenses Tracker app (and get a decent phone to run it on)
  
  

Problem is, I never seem to have the time... Perhaps I should cut out all the dodgy Italian gore films I keep getting sent from LoveFilm?...

powered by performancing firefox

Read More