SOI Database purging & Application cleanup

[Varun kumar]

Hi All,

 

We are planning to do upgrade of SOI Manager, UI and SOI Connectors(Spectrum,UIM) in next few weeks.

Upgrade details : 3.3 cum2 to 4.2

 

We would like to do purging & cleanup of application before upgrade starts. So that upgrade time can be reduced. Can we do this, Is this supported & recommended by CA.

 

And also need clarification on below.

1) Is there any script to do application & Database purging cleanup as part of maintenance before upgrade starts.

2) Will there be any outage while purging the data. 

 

Thanks in advance.

 

Regards

Varun kumar

[Kiet Luong]

Hi Varun,

 

You can use the soitoolbox utility to purge old data from the SOI DB.

Here's the link...

How to Clean Up Data with the CA SOI Toolbox - CA Service Operations Insight - 3.3 - CA Technologies Documentation 

 

1) Is there any script to do application & Database purging cleanup as part of maintenance before upgrade starts.

Yes, the soitoolbox utility.

2) Will there be any outage while purging the data. 

Yes, since you are currently using SOI 3.3, the utility will stop all SOI services.

 

Regards,

Kiet

[Varun kumar]

Hi Kiet,

I have created a SOI Toolbox Configuration file  to run the cleanup on remote database. However we could see below error.

 

Error message :-

===========

D:\CA\SOI\Tools>soitoolbox.exe -x --cleanHistoryData=1415
Started 09/07/2018 at 14:31:05
-> Parsing configuration file soitoolbox.cfg ... Done, 60 lines processed

-> Looking for SOI services on machine <machineName>
-> Connecting to the server <machineName> ... ERROR: Error: can't map '\\<machin
eName>\C$' (< was unexpected at this time.)

 

-> I have checked the user connectivity which is provided in soitoolbox.cfg file, It works from remote machine.

 

Any suggestions here ?

 

Thanks and Regards

Varun kumar

[Britta Hoffner]

Hello Varun, in order to verify the sql server connection credentials please create a file with extention .udl on the server where you run soitoolbox utility. For example test.udl. Double click this file to execute it. This will open a GUI that asks for sql server credentials and test the connection.  See this Knowledge Document for more details.

 

https://comm.support.ca.com/kb/ODBC-Connectivity-Testing-Using-the-Microsoft-Data-Link-API-Tool/KB000056068

 

Kind regards,

Britta Hoffner

CA Support

[Varun kumar]

Hi Britta,

Followed the document. Test was successful.

Attaching the screenshot for reference.

 

Any other leads to resolve this issue ? Could you please suggest.

 

Thanks and Regards

Varun kumar

[Shaheen Ahmed]

Hi Varun,

Either you have misconfigured the soitoolbox.cfg or you are not running it properly. It is difficult to investigate this kind of issue without logging in to your server and for that you'll have to open a case with CA.

Instead of running the soitoolbox the way you are running you can also run it the following way.

- Back up your current SOI DB

- Stop SOI services on SOIUI and SOI manager server

- On the SOI manager open a DOS window and CD to \CA\SOI\Tools folder and run

  soitoolbox -m <db server name> -d <SAMStore> -u <db user name> -p <db user password> --cleanHistoryData=1415

 

NOTE: The value for -m -d -u and -p is located in CA\SOI\tomcat\lib\hibernate.cfg.xml file. The password is encrypted, so use it as is.

Running soitoolbox --help from its location will show you all the options.

 

Thanks

Shaheen

[Varun kumar]

Hi Shaheen,

 

Thanks for the reply, How do we verify that --cleanhistory command has been successfully deleted the data from tables.

 

Can i know what all tables involved in this activity and we need to verify to know whether it has deleted the data or not ?

 

Thanks and Regards

Varun kumar

[Shaheen Ahmed]

Hi Varun,

Here are tables that are affected by the --cleanHistoryData

CIChangeHistory, CIHealth, CIQuality, CIRisk, DbAvailHistory, DbRiskHistory, DbQualityHistory

Thanks

Shaheen

[Varun kumar]

Hi Shaheen,

 

Database purging is completed. However Database size remains same and also i couldn't see any records deleted from SOI Tables(Alerts,CI,CIstagging etc....)

 

Could you please let me know what could be the issue here. Do you need any logs to analyze the issue.Please suggest.

 

Count of records on Tables before & after upgrade is same. Records older than 2 years are still available in SAMSTORE after purging

 

Count of records same after & before purging :-

==================================

Alerts: 3109745

CI : 4931

CIstaging :95920

 

Output of Purging command :-

================

 

D:\CA\SOI\Tools>soitoolbox -m ********* -d SAMStore -u XXXX1 -p EKu83ek1BEPn5bnEoLlmQz5M8xE+mngaf/luLu8LkerD --cleanHistoryData=730
Started 12/07/2018 at 10:25:52

-> Looking for SOI services on machine XXXXXXXXXX
-> Connecting to the server XXXXXXXXXX ... DONE
Service CA SAM UI Server : NOT FOUND
Service CA SAM Store Indexer : NOT FOUND
Service CA SAM Integration Services : NOT FOUND
Service CA Cat. container CatalystConnector : NOT FOUND
Service CA SAM Event Management : NOT FOUND
Service CA UCF Broker : NOT FOUND
Service CA SAM Application Server : NOT FOUND
-> Initializing DB connection to SAMStore@XXXXXXXX(timeouts=20/7200)... -> Initializing DB connection to SAMStore@******
-> Going to access 'SAMStore' database on machine 'XXXXXXX'
Provide password for user 'mdbadmin1':
-> Initializing DB connection to SAMStore@XXXXXXXX(timeouts=20/7200)... DONE
-> Checking DB schema version... DONE

Warning, a destructive DB operation has been selected. Continue? (Yes / [No]) Yes

-> Checking if SOI is running... NO, all SOI services are stopped

-> Deleting History data older than 730 days from database... DONE in 4495.5 seconds
OK

 

Statistical data output :-

=================

D:\CA\SOI\Tools>soitoolbox -m XXXXXXXX -d SAMStore -u XXXXX -p EKu83ek1BEPn5bnEoLlmQz5M8xE+mngaf/luLu8LkerD --getStatisticalData
Started 13/07/2018 at 09:51:53
-> Initializing DB connection to SAMStore@X****** (timeouts=20/30)... -> Initializing DB connection to SAMStore@******
-> Going to access 'SAMStore' database on machine '******'
Provide password for user 'mdbadmin1':
-> Initializing DB connection to SAMStore@XXXXXXXX (timeouts=20/30)... DONE
-> Checking DB schema version... DONE

Database statistics:
Summary statistics for all 6 connectors
Alerts_Acknowledged_in_Catalyst: 0
Alerts_Cleared_in_Catalyst : 0
Alerts_in_Catalyst : 2263425
Alerts_in_SOI : 249
BinRelationships_in_Catalyst : 2435
BinRelationships_in_SOI : 2435
OtherCIs_in_Catalyst : 7486
OtherCIs_in_SOI : 8489
Services_in_Catalyst : 6

 

Thanks and Regards

Varun kumar

 

[Shaheen Ahmed]

Hi Varun,

If you don't have data older then 730 days then nothing will be purged. So what you should do is decrease the number of days gradually to 90 (or 60 days), for example, if you start with data older than 730 days then you should rerun the command by decreasing the number of days to lets say 630 to 530 days to 430 days and so on.

Thanks

Shaheen

[Varun kumar]

Hi Shaheen,

 

I have chosen data that is older than 365 days should be deleted from SAMStore DB. However when queried the alert table, I could see there are alerts in SAMstores DB with logged in time date from 2014-11-24 for records.

 

what exactly command --cleanhistorydata =365 does ? Does it delete all the data which is older than 365 days or does it keep any records ?. Can you please clarify.

 

CA Case no :

01136589

 

Thanks and Regards

Varun kumar

[Brahmaiah Addepalli]

Hi Varun,

 

The best way to check if DB purging completed successfully is, check row count before & after purging activity. As you have confirmed that several lakhs of records have been deleted after this activity, which proves that older data is purged.

 

You may need to shrink SAMStore DB transaction log after purging data, as you have deleted for 4+ years data the transaction log size might be huge.

 

Going forward, we strongly recommend to run this DB purge activity at regular intervals and SOI 4.2 doesn’t stop SOI Manager services when running soitoolbox.exe command to purge older data

 

Thanks

Brahma

[Daniel Blanco]

These are the commands I use to clean up what we can using the utility:

cd C:\Program Files (x86)\CA\SOI\Tools
soitoolbox -x --purgeClearedAlerts 90
soitoolbox -x --cleanHistoryData 90
soitoolbox -x --purgeDBInconsistencies -b 300 -t 1200
soitoolbox -x --rebuildIndexes -b600 -t600

 

I then do a shrink on the SAMStore DB to reclaim the space the old alerts used. So open SQL Studio r-click on SAMStore and Tasks > Shrink > Database