I have a table in DB2 which is having 99 columns and 900,000 records of data.

I want to alter the table with one extra column as below:


However, the query never completes and the status of the table shows locked against my id in the APPLICATION LIST window from where I have to force kill it.

I believe this is because of the huge amount of data present.

Am I missing anything here ? How can I achieve what I am trying to do ?

  • 3
    99 columns is usually an indication that your table is too wide (from denormalization/non-dependant attributes, usually). And I've had dates added to tables with over 48 million records, so it's possible; although it did take multiple hours, and you don't want to do this on a live production table. – Clockwork-Muse Dec 8 '11 at 18:24
  • 1
    Did you try creating a new table with 100 columns, inserting data from the old table (possibly using multiple transactions), and renaming? (There's usually more to it than that, but you get the idea.) – Mike Sherrill 'Cat Recall' Dec 15 '11 at 18:27
  • Is the entire table in memory? How many indexes do you have over the table? – Chris Aldrich Jan 23 '12 at 19:01

Did you check the db2 diag log? what does it say?

The first thing comes into my mind it is that probably, the database does not rollback when the transaction log is full, but instead your updates never finishes.

Check the database logging parameter to see if there is enough log space to record the transaction.

BLK_LOG_DSK_FUL http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0006131.html

| improve this answer | |

Adding a column to a DB2 table shouldn't take a long time; the structural changes aren't applied to the existing rows until the rows are changed through DML, or until the entire table is rebuilt via the REORG TABLE command. Since only a few rows in the system catalog tables are immediately affected by your specific ALTER TABLE statement, it's unlikely that the hang you're encountering is due to an excessive amount of transaction logging.

What might be happening instead is that some other connection may be holding a lock that is interfering with your ALTER TABLE statement. If LOCKTIMEOUT is still set to -1, the connection attempting to add the column won't time out. If you can force off the rest of the users for a brief moment, you'll clear any locks on the table or the system catalog.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy