vCSA DB2 shenanigans before upgrade

My initial post on upgrading the vCenter appliance to 5.0u1b build 804277 gave some good tips that I found around and figured out myself.
But I'll tell you, dear reader, that the upgrade process kept failing for me. Too much db2 export which I narrowed down to tasks & events.

I had set tasks and events to be kept for 180 days (this might be the default). I bumped this down to 30 days hoping that the rows would be deleted and then my upgrade would work again. This didn't seem to happen. So this post is about clearing your embedded db2 database of history stats, tasks and events before upgrade. Note that it doesn't look like anything in the db2 cli is case sensitive.

I should mention that I am *not* a DBA, that this could hose your installation, it will most definitely hose your stats, events and tasks so this process might not be a good idea in your production environment. The vCSA is a VM: Use snapshots to your advantage here.

• SSH into the appliance as root. Turn off vcenter services:

# service vmware-vpxd stop

• Change your login to db2inst1, get into the db2 cli, connect to the vCenter database:

# su - db2inst1
~> db2
db2 => connect to VCDB

• I couldn't remember if I had bumped the transaction logsize on this appliance, so I did the command anyway: 

db2 => update db configuration FOR VCDB USING logprimary 16 logsecond 112 logfilsiz 8192

• Delete contents of the history tables:

TRUNCATE TABLE vc.vpx_hist_stat1 IMMEDIATE
TRUNCATE TABLE vc.vpx_hist_stat2 IMMEDIATE
TRUNCATE TABLE vc.vpx_hist_stat3 IMMEDIATE
TRUNCATE TABLE vc.vpx_hist_stat4 IMMEDIATE
TRUNCATE TABLE vc.vpx_sample_time1 IMMEDIATE
TRUNCATE TABLE vc.vpx_sample_time2 IMMEDIATE
TRUNCATE TABLE vc.vpx_sample_time3 IMMEDIATE
TRUNCATE TABLE vc.vpx_sample_time4 IMMEDIATE

•  Deleting the contents of the tasks & events is not as straightforward. There are foreign keys associated with these tables that need to be removed before you can delete the rows and then re-create the foreign keys. 
• See how many rows of data: 

select count(*) from vc.vpx_event
select count(*) from vc.vpx_event_arg
select count(*) from vc.vpx_task

• Remove foreign keys: 

alter table vc.VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT
alter table vc.VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_ENTITY
alter table vc.VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT
alter table vc.VPX_EVENT drop constraint FK_VPX_CHANGE_TAG
alter table vc.VPX_EVENT drop constraint FK_VPX_EVENT_REF_COMPUTERES
alter table vc.VPX_TASK drop constraint FK_PARENT_TASK_REF
alter table vc.VPX_TASK drop constraint FK_VPX_TASK_CHANGE_TAG
alter table vc.VPX_TASK drop constraint FK_VPX_TASK_REF_ENTITY

• Delete data: 

truncate table vc.VPX_TASK immediate
truncate table vc.VPX_ENTITY_LAST_EVENT immediate
truncate table vc.VPX_EVENT immediate
truncate table vc.VPX_EVENT_ARG immediate

• Re-add foreign keys: 

alter table vc.VPX_EVENT_ARG add constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references vc.VPX_EVENT (EVENT_ID) on delete cascade
alter table vc.VPX_EVENT_ARG add constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references vc.VPX_OBJECT_TYPE (ID)
alter table vc.VPX_ENTITY_LAST_EVENT add constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references vc.VPX_EVENT (EVENT_ID) on delete cascade
alter table vc.VPX_EVENT add constraint FK_VPX_CHANGE_TAG foreign key(CHANGE_TAG_ID) references vc.VPX_CHANGE_TAG(CHANGE_TAG_ID) on delete set null
alter table vc.VPX_EVENT add constraint FK_VPX_EVENT_REF_COMPUTERES foreign key(COMPUTERESOURCE_TYPE) references vc.VPX_OBJECT_TYPE(ID)
alter table vc.VPX_TASK add constraint FK_PARENT_TASK_REF foreign key(PARENT_TASK_ID) references vc.VPX_TASK(TASK_ID)
alter table vc.VPX_TASK add constraint FK_VPX_TASK_CHANGE_TAG foreign key(CHANGE_TAG_ID) references vc.VPX_CHANGE_TAG(CHANGE_TAG_ID) on delete set null
alter table vc.VPX_TASK add constraint FK_VPX_TASK_REF_ENTITY foreign key(ENTITY_TYPE) references vc.VPX_OBJECT_TYPE(ID)

•  Your tables are now empty:

select count(*) from vc.vpx_event
select count(*) from vc.vpx_event_arg
select count(*) from vc.vpx_task

•  quit from db2 cli, exit from db2inst1 user, restart vcenter services to see if everything powers back up: 

=> quit
~> exit
# service vmware-vpxd start

•  Log into the vSphere Client to check that everything looks good. 
• Try your upgrade again. For me, just truncating the history stats still failed after exporting the data for ~18 hours. Yeah. 18 hours and then I had to revert to snap. Dumping the events/tasks was the only way this was ever going to work. 

Thanks to vfrank, juanma and IBM's db2 documentation.