IT Process Automation

PAM 4.2SP2

Can someone point me to anything related to PAM DB size? My DB is over 100GB. I've tried steps outlined in

https://communities.ca.com/message/241760483 and it won't let me drop or truncate the C2OServiceOperationInstance table and it's over 60GB by itself. I don't need reporting data and set the OasisConfig.properties file as outlined in the thread but it looks like it's still putting data in this table. I also tried using the option to purge reporting data from the GUI but not sure it's doing anything since the DB and tables look the same and the same size.

You have to run a db_shrink command to reclaim the space after you clear the tables as the database assume that much room will be needed in the future and continues to reserve the space for the particular database. 
This KB is a bit old, but the concepts are still valid:

TEC556200  Why is my ITPAM database so large?

If you found my above response to your question sufficient to  address your concern, could I please ask you to click on the ‘Mark Correct’ button

To mark your community posting as having been answered. 

Thank you,

Michael Niebuhr

Any idea about how to shrink the C2OServiceOperationInstance table specifically? Can I delete all the data in there if I don't need reporting data?

Here you go:

-- Reporting tables

use CHANGE_TO_REPORTING_DATABASE_NAME;
TRUNCATE TABLE C2OConfigComponents;
TRUNCATE TABLE C2OFlowchartInstanceObject;
TRUNCATE TABLE C2OReferenceObject;
TRUNCATE TABLE C2OServiceOperationInstance;
TRUNCATE TABLE FileTransferServiceOperation;
TRUNCATE TABLE MapiAlertServiceOperation;
TRUNCATE TABLE PlayWaveformAlertSVC;
TRUNCATE TABLE PromptUserServiceOperation;
TRUNCATE TABLE SOAPClientCall;
TRUNCATE TABLE StartProcessServiceOperation;
TRUNCATE TABLE TapiAlertServiceOperation;

That will completely clear all reporting tables.

Ryan,

To disable the internal Process Automation reporting.

This should only be performed if you do not use the CA Process Automation reporting – Reports tab.

Please make sure to backup your database before making any modifications.

Stop all CA Process Automation services.

Edit OasisConfig.properties (for all orchestrators) located in <PAM>/server/c2o/.config; add the following line:

oasis.disable.reporting.manager=true

In the database, you need to clean up the reporting tables. These statements are for Sql Server. If you are using Oracle, add   ‘DROP STORAGE;’ to the end of each statement as

TRUNCATE TABLE filetransferserviceoperation DROP STORAGE;

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

TRUNCATE TABLE filetransferserviceoperation
TRUNCATE TABLE mapialertserviceoperation
TRUNCATE TABLE playwaveformalertsvc
TRUNCATE TABLE promptuserserviceoperation
TRUNCATE TABLE soapclientcall
TRUNCATE TABLE startprocessserviceoperation
TRUNCATE TABLE tapialertserviceoperation
TRUNCATE TABLE c2oflowchartinstanceobject
TRUNCATE TABLE c2oreferenceobject
TRUNCATE TABLE c2oconfigcomponents
TRUNCATE TABLE c2oserviceoperationinstance

TRUNCATE is used rather than DELETE as it is faster, automatically cleans up and releases free space, and there is minimal impact to the transaction log.

The minimum permission required is ALTER on table_name for SQL Server.

For ORACLE the table must be in your schema or you must have DROP ANY TABLE system privilege.

Once this has completed, restart the CA Process Automation orchestrator services. 

This information is also available as a knowledge document here:

How to disable the reporting feature in CA Process Automation 

If you found my above response to your question sufficient to address your concern, could I please ask you to click on the "Mark Correct" button to mark your community posting as having been answered.

Thank you.

Jennifer Jessup

I'm unable to truncate it. I'm using SQL 2008R2

Cannot truncate table 'C2OServiceOperationInstance' because it is being referenced by a FOREIGN KEY constraint.

Ryan,

With Process Automation stopped, you can just delete the tables under the reporting database. Leave the database, but drop the tables. 

Thanks! I ended up dropping the tables and saving the C2OServiceOperationInstance table for last. Trying to drop this table before any of the others resulted in the Foreign Key constraint issue.

Awesome, glad you were able to get this taken care of!