Highlighted
Respected Contributor.. Respected Contributor..
Respected Contributor..
185 views

(SM) Support Tip Proactive Forum: How to purge SM data from back end SQL DB

 

Generally purging the data from DB is faster than from SM application.

Note: It is strongly suggested to purge the data from application for steps please refer knowledge article 

KM02857475 : How to purge and archive Incidents, Interactions and Change data from SM application

Purging from DB should always be the last resort also with appropriate db backup and it should be strictly carried out by DBA at own risk.

To delete the data from the SQL backend DB, below queries can be used as reference the select statements can be used to verify the data before purging.

In order to overcome data dependencies, it’s recommended to delete the data from the related tables (containing Foreign Key) first followed by the primary table (containing Primary Key).

For Sysattachment table, not to use “select * from” on the table which has IMAGE type field, as this will impact a lot for the select query when there are lot of return records.

Also, in SYSATTACHMEM1 table there is a big data field “DATA” which is IMAGE type, so this field should be excluded such when the select query is used.

For better performance, it is important to not run the queries against too many rows, so it may be necessary to start with incidents older than 5 years, then 4 years, then 3 years, then 2 years, then 1 year and run it in separate statements when archiving for the first time.

 

Date Format used in the queries: MM/DD/YY

 

INCIDENTS: SERVICE DESK

Tables: incidents, screlations, incdepends, ACTIVITYM1, SYSATTACHMENT, slaactive, sloresponse

 

SELECT * FROM SCRELATIONM1 WHERE (DEPEND_FILENAME='incidents' and DEPEND IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' )) )

DELETE FROM SCRELATIONM1 WHERE (DEPEND_FILENAME='incidents' and DEPEND IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' )) )

SELECT * from ACTIVITYM1 WHERE (number IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM ACTIVITYM1 WHERE (number IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT APPLICATION,TOPIC,TYPE,SEGMENT,SYSMODCOUNT,SYSMODUSER,SYSMODTIME,MIMETYPE,SIZE,FILENAME,UID,COMPRESSED,COMPRESSED_SIZE,UPLOAD_BY,UPLOAD_DATE

from SYSATTACHMEM1 WHERE ( TOPIC IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

DELETE FROM SYSATTACHMEM1 WHERE ( TOPIC IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from slaactiveM1 WHERE (FOREIGN_KEY IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM slaactiveM1 WHERE (FOREIGN_KEY IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from SLORESPONSEM1 WHERE (FOREIGN_KEY IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM SLORESPONSEM1 WHERE (FOREIGN_KEY IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17')

DELETE FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17')

 

 Incident Management

Tables: probsummary, (problem), screlation, activity, SYSATTACHMENT, slaactive, sloresponse

 

SELECT * FROM SCRELATIONM1 WHERE (DEPEND_FILENAME='problem' and DEPEND IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' )) )

DELETE FROM SCRELATIONM1 WHERE (DEPEND_FILENAME='problem' and DEPEND IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' )) )

SELECT * from ACTIVITYM1 WHERE (NUMBER IN ( SELECT NUMBER FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM ACTIVITYM1 WHERE (NUMBER IN ( SELECT NUMBER FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT APPLICATION,TOPIC,TYPE,SEGMENT,SYSMODCOUNT,SYSMODUSER,SYSMODTIME,MIMETYPE,SIZE,FILENAME,UID,COMPRESSED,COMPRESSED_SIZE,UPLOAD_BY,UPLOAD_DATE

from SYSATTACHMEM1 WHERE ( TOPIC IN ( SELECT number FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM SYSATTACHMEM1 WHERE ( TOPIC IN ( SELECT number FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from slaactiveM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM slaactiveM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from SLORESPONSEM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM SLORESPONSEM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17')

DELETE FROM PROBSUMMARYM1 WHERE (CLOSE_TIME < '07/03/17')

 

Change Management:

Tables: cm3r, cm3t, screlation, activitycm3r, SYSATTACHMENT, slaactive, sloresponse, ApprovalLog, (Approval not included, since there should be no active approvals on closed changes), AlertLog, dataModEvent (if unplanned changes and change verification are used)

 

SELECT * FROM SCRELATIONM1 WHERE (DEPEND_FILENAME='cm3r' and DEPEND IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' )) )

DELETE FROM SCRELATIONM1 WHERE (DEPEND_FILENAME='cm3r' and DEPEND IN ( SELECT INCIDENT_ID FROM INCIDENTSM1 WHERE (CLOSE_TIME < '07/03/17' )) )

 

Select * from CM3tm1 where( parent_change in (SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE from CM3tm1 where( parent_change in (SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from ACTIVITYM1 WHERE (NUMBER IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM ACTIVITYM1 WHERE (NUMBER IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT APPLICATION,TOPIC,TYPE,SEGMENT,SYSMODCOUNT,SYSMODUSER,SYSMODTIME,MIMETYPE,SIZE,FILENAME,UID,COMPRESSED,COMPRESSED_SIZE,UPLOAD_BY,UPLOAD_DATE

from SYSATTACHMEM1 WHERE ( TOPIC IN ( SELECT number FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM SYSATTACHMEM1 WHERE ( TOPIC IN ( SELECT number FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from APPROVALLOGM1 WHERE (UNIQUE_KEY IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE * from APPROVALLOGM1 WHERE (UNIQUE_KEY IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from slaactiveM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM slaactiveM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from SLORESPONSEM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

DELETE FROM SLORESPONSEM1 WHERE (FOREIGN_KEY IN ( SELECT NUMBER FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17' ) ) )

 

SELECT * from CM3RM1 WHERE (CLOSE_TIME < '07/03/17')

DELETE FROM CM3RM1 WHERE (CLOSE_TIME < '07/03/17')

 

Similarly, it can be followed for below modules:

Problem Management

Tables: rootcause, rootcausetask, knownerror, knownerrortask, screlation, activityproblem, activityproblemtask, activityknownerror, SYSATTACHMENT, slaactive, sloresponse, AlertLog

Request Management

Tables: ocmq, ocml, ocmo, screlation, ApprovalLog

Knowledge Management

Tables: kmdocument, kmadaptivelearning, kmfeedback, cm3r, SYSATTACHMENT

Labels (1)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.