DB2 Alter Column
Running DB2 10.5 on Windows server
I am trying to add a default to a column on a large table and every time I get "The current command has been rolled-back due to timeout or deadlock reason code 68".
I have stopped and restarted DB2 and ran the command immediately and still get this.
Here is the command.
ALTER TABLE ABC ALTER COLUMN PHONE_UPDATED_TS SET DEFAULT CURRENT_TIMESTAMP
Any suggestions?
Jim
I am trying to add a default to a column on a large table and every time I get "The current command has been rolled-back due to timeout or deadlock reason code 68".
I have stopped and restarted DB2 and ran the command immediately and still get this.
Here is the command.
ALTER TABLE ABC ALTER COLUMN PHONE_UPDATED_TS SET DEFAULT CURRENT_TIMESTAMP
Any suggestions?
Jim
Hi,
What type of tablespace is the table ABC in ?
If it is SMS then I strongly recommend that you move it to a DMS tablespace.
You could also (if applicable) try to increase LOCKTIMEOUT
Does your table include BLOB columns ?
Make sure that no application is running on the table while you issue the alter especially when table is large.
As ste5an says it may be that your online transaction logs are too small for such large UOW that you are executing if the table is large.
Check the db2diag.log for clues/errors around the time you issue the alter statement and you receive the error
Regards,
Tomas Helgi
What type of tablespace is the table ABC in ?
If it is SMS then I strongly recommend that you move it to a DMS tablespace.
You could also (if applicable) try to increase LOCKTIMEOUT
Does your table include BLOB columns ?
Make sure that no application is running on the table while you issue the alter especially when table is large.
As ste5an says it may be that your online transaction logs are too small for such large UOW that you are executing if the table is large.
Check the db2diag.log for clues/errors around the time you issue the alter statement and you receive the error
Regards,
Tomas Helgi
Hi Jim,
Did you manage to alter the column successfully ?
Regards,
Tomas Helgi
Did you manage to alter the column successfully ?
Regards,
Tomas Helgi
ASKER
Not yet. The table is rather large and very busy so believe the issue is that the Alter statement can't get the lock it needs to add the default. Very frustrating.
ASKER
If I run a stop all applications command and then run the alter immediately after do you think that would work? Or is there someway to take the database offline but still be able to alter the column? The system is 24/7 (of course) but we do have a small window once a month for maintenance.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you do not want downtime, try admin_move_table -- many online operations can be easily done with it.
Not being a DB2 specialist, but such a change should be minimal logged as it is only a schema change and not data changes are involved. But it should require a schema lock, this you must be the only one accessing that table.