Discussion
SBT
RU
Last activity: 12 May 2016 15:31 EDT
posibility to truncate db data except system tables
Hello,
I want to insert about 60 Gb newest test information to database (like client_id,name, address, etc).
Is it possible to clean old database data (like truncate table), except system tables that need for pega working? (so after truncate pega must works!)
I need to delete only all information needs for test team, and after deleting will insert new testing data . (so after truncate pega must works!)
Is it real? Where i can find description about this?
Thanks.
using pega 6.1 / oracle 11g
Pegasystems Inc.
US
Great question and most likely to be handled by a database expert. There is currently an Ask the Expert session running this week and next where you can ask DB questions to Jie Long with Pegasystems.
Hope this helps,
Sean
Pega Academy Support Team
Optus
AU
You cannot directly truncate the tables, since pega inserts data in multiple tables for proper functioning. The link PurgingandArchivingWorkObjects might be helpful for your scenario.
Updated: 29 Apr 2016 8:01 EDT
Pegasystems Inc.
US
You can write an Activity with an RDB-Delete step that executes a Connect SQL rule where the Delete tab contains truncate or "delete from" statements.
Do NOT deploy such an Activity -- at the very least comment out the RDB-Delete step when checking in.
You can look at a generated Product Rule (R-A-P) to see what DATA-ADMIN-DB-TABLE instances were picked up by the Packaging wizard.
Example below.
You can write an Activity with an RDB-Delete step that executes a Connect SQL rule where the Delete tab contains truncate or "delete from" statements.
Do NOT deploy such an Activity -- at the very least comment out the RDB-Delete step when checking in.
You can look at a generated Product Rule (R-A-P) to see what DATA-ADMIN-DB-TABLE instances were picked up by the Packaging wizard.
Example below.
Delete From {Class:Assign-Worklist} where pxRefObjectKey like 'GRUN-FW-WARRANTYFW-WORK%';
Delete From {Class:Assign-WorkBasket} where pxRefObjectKey like 'GRUN-FW-WARRANTYFW-WORK%';
Delete From {Class:Data-WorkAttach-} where pxRefObjectKey like 'GRUN-FW-WARRANTYFW-WORK%';
Delete From {Class:Link-Attachment} where pxLinkedRefFrom like 'GRUN-FW-WARRANTYFW-WORK%';
Delete From {Class:Index-WorkPartyUri} where pxInsIndexedKey like 'GRUN-FW-WARRANTYFW-WORK%';
Delete From {Class:History-GRUN-FW-WARRANTYFW-Work};
Delete From {Class:GRUN-FW-WARRANTYFW-Work};
Delete From {Class:Assign-Worklist} where pxRefObjectKey like 'GRUN-NA-WARRANTYNA-WORK%';
Delete From {Class:Assign-WorkBasket} where pxRefObjectKey like 'GRUN-NA-WARRANTYNA-WORK%';
Delete From {Class:Data-WorkAttach-} where pxRefObjectKey like 'GRUN-NA-WARRANTYNA-WORK%';
Delete From {Class:Link-Attachment} where pxLinkedRefFrom like 'GRUN-NA-WARRANTYNA-WORK%';
Delete From {Class:Index-WorkPartyUri} where pxInsIndexedKey like 'GRUN-NA-WARRANTYNA-WORK%';
Delete From {Class:History-GRUN-NA-WARRANTYNA-Work};
Delete From {Class:GRUN-NA-WARRANTYNA-Work};
Delete From {Class:System-Queue-ScheduledTask};
Delete From {Class:System-Queue-ServiceLevel};
Delete From {Class:System-Queue-DefaultEntry};
Delete From {Class:System-Queue-ExecutionRequest-Service-Default};
Delete From {Class:System-Work-Indexer};
Serialized Solutions Inc
CA
Another option is to have an SQL script that you run against the database to truncate the database table or selectively delete the data.
You would need to selectively delete the data (delete from table xx where ......) if you have more than one Pega application running on the environment.
Depending on certain environments you may/may not require specific SQL per environment.
(for example, SLA table...)