We implemented a data-driven archive functionality for ITSP. This was written by Abydos. This is roughly the strategy we followed:
1. Determine which module to set up the archiving for (we started with Incident Management) 2. Determine which associated forms (WorkLogs, Attachments etc) to carry over 3. Determine the archive criteria (Number of days after closure no associations for example) 4. New field added CC_Chk_ArchiveFlag 5. Archive process enabled on the server and on the relevant forms (Qualification CC_Chk_ArchiveFlag = “Yes”) 6. Table qualifications on the parent archive form are amended to point to new archive forms (e.g. WorkLog table now points to ARC_HPD:WorkLog) Our data driven solution works in the following way: 1. All the matching parent records are updated using an escalation that reads a SQL script and updates the CC_Chk_ArchiveFlag field Example Script: UPDATE HPD_Help_Desk SET CC_Chk_ArchiveFlag = 0 WHERE (NOT EXISTS (SELECT chg.Request_ID01 FROM CHG_Associations chg WHERE chg.Request_ID01 = Incident_Number AND chg.Status < 5) AND NOT EXISTS (SELECT hpd.Request_ID01 FROM HPD_Associations hpd WHERE hpd.Request_ID01 = Incident_Number AND hpd.Status < 5) AND NOT EXISTS (SELECT pke.Request_ID01 FROM PBM_Known_Error_Associations pke WHERE pke.Request_ID01 = Incident_Number AND pke.Status < 5) AND NOT EXISTS (SELECT pia.Request_ID01 FROM PBM_Investigation_Associations pia WHERE pia.Request_ID01 = Incident_Number AND pia.Status < 5) AND NOT EXISTS (SELECT srm.Request_ID01 FROM SRM_Associations srm WHERE srm.Request_ID01 = Incident_Number AND srm.Status < 5)) AND Status = 5 AND Closed_Date < 1321920000 2. All the related child records are updated using an escalation that reads a SQL script and updates the records. Example Script: UPDATE (SELECT secondaryform.CC_Chk_ArchiveFlag as archiveFlag FROM HPD_Help_Desk primaryform, HPD_WorkLog secondaryform WHERE secondaryform.Incident_Number = primaryform.Incident_Number AND primaryform.CC_Chk_ArchiveFlag = 0) SET archiveFlag=0 3. Once all the parent and child records have the CC_Chk_ArchiveFlag set, the normal archive process (defined on the server) takes over and archives the child records first and then the parent records. The process is performed in this order to ensure that the normal HPD:Help Desk filters that fire on delete do not delete the child records. The above solution is easily extendible. The difficult bit is working out the SQL! It works well, and we are looking to roll out to other modules. Hope this helps. I can give more information off list if required. Gavin Coleman Senior Analyst/Programmer Computacenter (UK) Ltd Services & Solutions Hatfield Avenue Hatfield, Hertfordshire, AL10 9TW, United Kingdom T: +44 (0) 1707 631662 E: [email protected]<mailto:[email protected]> W: www.computacenter.com<http://www.computacenter.com> From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Matthew Kunkel Sent: 26 January 2012 22:18 To: [email protected] Subject: Re: Archiving strategy on ITSM 7.6 + ** Two options we have seen from BMC are DSO and custom scripts, both require custom mappings for each of ~1000 regular forms. Additionally, structure changes would need to be maintained in custom mappings when customizations, patches, and upgrades are installed. Since BMC no longer includes the change details in ITSM patch release documentation, both solutions are problematic. There are database level products that allow some mining of the redo logs and should give a hybrid replication/archive functionality. One strategy under development is strip purge job deletes from the redo logs and propagate all other data/structure changes. Two products that we have encountered are Oracle Golden Gate and IBM Change Data Capture (neither is in use yet and this is not an endorsement or recommendation). Matthew Kunkel Systems Engineer Phone 479.277.3392 [email protected] Walmart 805 Moberly Lane, MS32 Bentonville, AR 72716 Save money. Live better. From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of patchsk Sent: Tuesday, January 24, 2012 2:19 PM To: [email protected] Subject: Archiving strategy on ITSM 7.6 + ** We just started to looking at archiving our production data. We are currently in ITSM 7.6.03. With so many related forms to main forms, it would be tricky and take significant effort to properly draw the archiving plan like which forms need to archived in which order so that relationships are carried over so that a closed incident will be moved to a different server or a different form with all of its relationships in tact. Anyone done this already, if so can you provide a few details on the strategy you followed. DSO is another option we are looking at it but have not made decision yet. We can not use DB level replication because if we delete a record in production then it will delete it from replicated database also. Seems like from BMC knowledge base it is not supported and cannot provide any guidelines. But we cannot just keep storing the data in production. _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ ________________________________ This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. *** Walmart Confidential *** _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ ********************************************************************** COMPUTACENTER PLC is registered in England and Wales with the registered number 03110569. Its registered office is at Hatfield Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW COMPUTACENTER (UK) Limited is registered in England and Wales with the registered number 01584718. Its registered office is at Hatfield Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW COMPUTACENTER (Mid-Market) Limited is registered in England and Wales with the registered number 3434654. Its registered office is at Hatfield Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW COMPUTACENTER (FMS) Limited is registered in England and Wales with the registered number 3798091. Its registered office is at Hatfield Business Park, Hatfield Avenue, Hatfield, Hertfordshire AL10 9TW The contents of this email are intended for the named addressee only. It contains information which may be confidential and which may also be privileged. Unless you are the named addressee (or authorised to receive mail for the addressee) you may not copy or use it, or disclose it to anyone else. If you receive it in error please notify us immediately and then destroy it. Computacenter information is available from: http://www.computacenter.com ********************************************************************** _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
