Can I delete CHANGE_HISTORY records
My question is, is it okay to DELETE records from the CHANGE_HISTORY table if the CHANGE_ID does not match up with any ID on the CHANGE table?
Two years ago we acquired a piece of company who also had Agile. With the help from a consultant from Oracle, we migrated data from their Agile into our Agile. It was a big project. Today, we have discovered CHANGE_HISTORY records from that migration are appearing on our newly created Change Orders and Change Requests. Imagine looking at an ECO you created today, clicking on the History tab, and seeing history records from 6 years ago.
The ID on the CHANGE table increments everytime a new Change is created. Now we have got to that magic number that matches some of the bogus entries that were copied to our system. Can I delete records from CHANGE_HiSTORY where a CHANGE record does not yet exist? Are there any other tables I should worry about?
The short answer is yes. If the value in CHANGE_HISTORY.CHANGE_ID does not match anything in CHANGE (or others, see below), it is an orphan record and can (and really *should*) be removed. Heck, you can even compare the timestamp on the CH record to the create date on the change, and know that it is an old one and so it can be removed as well. I would also look at ITEM_HISTORY and ITEM_HISTORY2 (just in case) as well as SIGNOFF and WORKFLOW_PROCESS (2 other tables intimately linked to changes).
Note that for both of the basic history tables, they are not only history for the ITEM and CHANGE tables. ITEM_HISTORY holds data for non-routable objects (parts, documents, users, mfr parts, manufacturers, sites, customers, etc.) and CHANGE_HISTORY holds data for routable objects (ECO, ECR, MCO, PSR, QCR, Declaration, etc.). Look at CHANGE_HISTORY.CLASS_ID and ITEM_HISTORY.CLASS to see what type of object each record is related to, and therefore which table you should check to make sure that the parent object exists. A bit complicated, but not real hard. Get a list of the class values in each table, find out what they are from NODETABLE (4983, for example, is Customer in the PQM module) and then write a script to check against the correct table for each class ID. Neither SIGNOFF nor WORKFLOW_PROCESS track the class of the object they are related to, so you might have to link through CHANGE_HISTORY or do something else to make sure you do not remove any good records. Or if you ONLY use the PC module, most of the above is all hash anyway.
Good luck.
Thanks for the tips. I studied the CHANGE_HISTORY table a bit more. One thing I noticed is, if you add an attachment to a part, like a URL on the Attachments tab of a part (not a Change), entries are added to the CHANGE_HISTORY table with CLASS_ID = 6159 and the ID is a negative number. The CHANGE_ID has a numeric value in it, but it does not point to a record on the CHANGE table. I would NOT want to delete these records. Any idea what table is linked to these records with CLASS_ID = 6159 ? The NODETABLE where ID = 6159 says ‘File folders’.
