Re: Purging the HPSM data via Database (175 Views)
Reply
Advisor
Sood
Posts: 25
Registered: ‎09-12-2013
Message 1 of 7 (219 Views)

Purging the HPSM data via Database

Hi,

 

I need to do the purge of probsummary,incidents,ocmq,ocml and cm3r tables via Database directly.

 

We have Oracle DB.

 

Can someone let me know if you have queries to perform the purge via DB directly.

 

Regards,

Gourav Sood

Please use plain text.
Honored Contributor
Abhijit K
Posts: 888
Registered: ‎10-14-2010
Message 2 of 7 (202 Views)

Re: Purging the HPSM data via Database

Hi,

 

Try to purge data from application side and not from Database directly if you dont know which table store what kind of data.

 

From application go to System Definition--> Select Table --> You will find option "Purge data".

 

this will also purge related data.

 

Re,

Abhijit K

Please use plain text.
Honored Contributor
Jacob Heubner
Posts: 4,174
Registered: ‎07-21-2008
Message 3 of 7 (194 Views)

Re: Purging the HPSM data via Database

I would agree that you probably want to do this via the tool itself.

 

However, if for some reason you cannot purge data through the application itself, I think you'd want something as simple as

 

delete from <tablename>

 

For example

delete from incidentsm1

 

To see which tables you need to purge, use dbdict to check the mapping of the Service Manager objects to the underlying database.  For example, use dbdict to look at the incidents table, and check the SQL Tables tab.  All the tablenames listed there hold the records from the Interactions. (ie, incidentsm1, incidentsm2, incidentsa1)

Please use plain text.
Advisor
Sood
Posts: 25
Registered: ‎09-12-2013
Message 4 of 7 (183 Views)

Re: Purging the HPSM data via Database

I have tried from application (Purge/Archive) but it is very slow from application.

 

So that is why i wanted to do it from DB.

 

Also can we schedule it from application so that it runs and stops at some particular time.

Please use plain text.
Honored Contributor
John Stagaman
Posts: 3,097
Registered: ‎07-13-2007
Message 5 of 7 (175 Views)

Re: Purging the HPSM data via Database

The advantage of doing the purge from within the application is that the related records to transactional tickets will be deleted also (so not only will an incident be deleted, but its associated activities, attachments, clocks, etc. will all be deleted). 

If you purge directly from the database, none of the related records will be cleaned up. You would need to do that manually.

----------------------------------------------------
Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
Please use plain text.
Esteemed Contributor
Jas1
Posts: 361
Registered: ‎04-05-2010
Message 6 of 7 (167 Views)

Re: Purging the HPSM data via Database

You can schedule the purge to start but there's no option to schedule the purge to stop. I don't think it's a good idea to kill the purge manually in the middle of its task.

One other option is to use System Administration, OnGoing Maintenance, System, Purge/Archive. Set up a query to select records based on a date range to purge so that you can do them in blocks . You can also test out your query by choosing the archive instead of purge and schedule. This will help you work out whether you query will pick the right records and how long the archive took. If you set the data map option, you can see the related records from other tables being selected too.

Always remember plan B, backup your database before your actual purge. If something unexpected goes wrong, you have a recovery method.

Please use plain text.
Honored Contributor
Piku
Posts: 3,611
Registered: ‎06-17-2010
Message 7 of 7 (154 Views)

Re: Purging the HPSM data via Database

Hi Gourav,

Why you need to delete all data ? you should create new system and may take unload of operator profiles,form and other customized objects but have pro and cons.

As John said app will delete related records too so in case of manual you have to identify each associated table first.

And yes from application side it will be very slow and if you have tens of thousands of records in system for each table then application will take weeks or months to delete data.
Also as Jas suggested, you can filter the records to make it fit in delete time.
From DB end it will take seconds to delete data by truncate command.


hth,
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation