Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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:

ALTER TABLE MYSCHEMA.PRODUCT ADD UPDATE_DT DATE

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 ?

share|improve this question
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 'Catcall' 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

migrated from stackoverflow.com Dec 15 '11 at 17:01

2 Answers

up vote 3 down vote

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

share|improve this answer
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.