petek, 24. november 2006

DB2 IMPORT

It's been a long time since my last post. It has been a wild roller coaster at my job, as someone said.
During this time I also discovered something about DB2 IMPORT command. It doesn't work as the docs say. What does this mean? Sometimes you get "SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000" and the row is rejected.
Hmmm. How can this be since the docs say that UPDATE should be performed via primary key? So if there were no rows to UPDATE, why does IMPORT not perform an INSERT?
What I found out is that on my target table there was a UNIQUE key defined and removing the UNIQUE key solved the problem.
Does this mean there is a bug ? In the IMPORT command itself or in the docs ? Maybe this is right for a PMR.

ponedeljek, 6. november 2006

Cold

Oh well.. I guess it's just that time in the year. I think I've got cold, termperature and my throat is a bit sore.
I'll have to start drinking tea with honey and lemon. And more vitamins too. :))
I just hope I don't get too sick.

petek, 3. november 2006

DB2: Fast Delete Large Tables

Have you ever wanted to delete a table with millions of rows? Using
DELETE FROM TABLE1 SQL statement? Then you know this is very, very slow and it fills transaction logs. The fastest delete methond I've ever seen goes little like this:
1.) First you export 0 rows from the table:
EXPORT TO TABLE_NAME.IXF OF IXF SELECT * FROM TABLE_NAME WHERE 1 = 2
2.) Then you do a LOAD with REPLACE:
LOAD FROM TABLE_NAME.IXF OF IXF REPLACE INTO TABLE_NAME

I've been able to delete rows from tables with tens of millions of rows in a second.
I hope this little tip is usefull for a lot of DBA's out there.