Technical Blog Post
Abstract
How to Handle DBASE Maintenance on Control Center
Body
If the customer has enabled nightly purging in CC, then CC will purge the partitions for the following tables as part of the purge process:
EVENT_COMMENTS
CD_STATS_LOG
CE_STATS_LOG
CX_STATS_LOG
QF_STATS_LOG
MQ_STATS_LOG
AF_STATS_LOG
BP_STATS_LOG
FG_STATS_LOG
AUDIT_LOG
EVENTS_EXT
CC_ALERT
If the client does not enable the nightly purge then the DBA is responsible for cleaning these up using the following links:
they are listed in the doc link:
https://www.ibm.com/support/knowledgecenter/SS4Q96_6.1.0/com.ibm.help.scc.configure.doc/scc_partition_overview.html
Sample commands to drop the partition can be found here:
https://www.ibm.com/support/knowledgecenter/SS4Q96_6.1.0/com.ibm.help.scc.configure.doc/scc_archiving_data_in_partitioning_environment.html
The DBA should also refer the appropriate database documentation to manage the partitions.
This link provides the details of CC database partitioning.
https://www.ibm.com/support/knowledgecenter/SS4Q96_6.1.0/com.ibm.help.scc.configure.doc/scc_data_base_partitioning.html
Maintaining IBM Control Center Summarization Table Data
There are 5 tables that hold summarization data for IBM Control Center (ICC) that you must "maintain" because if you do not, they will grow indefinitely. And know that besides removing data from them on a periodic basis, you must also ensure their indices are maintained regularly, as with all the ICC database tables, to keep performance of ICC as good as possible.
The 5 tables are:
ROLL_UP
CC_PROCESS
CC_PROCESS_DVG
CC_FILE_TRANSFER
CC_FILE_TRANSFER_DVG
If you're not using DVGs (data visibility groups) in ICC, no data will be written to CC_PROCESS_DVG or CC_FILE_TRANSFER_DVG, which leaves only three tables for you to maintain but I'll cover what you need to do though as if you had DVGs...
First pick a date that represents the oldest summarization data you want to keep. Say it was May 19th, 2017
ROLL_UP is pretty straight forward to "truncate" because DATE_TIME, the column you need to reference in SQL, is just a "varchar" kind of column (as opposed to a "date" type, so SQL to truncate it would just be, for example:
DELETE FROM ROLL_UP WHERE DATE_TIME < '2017-05-20'
NOTE: the ROLL_UP delete changes based on the level of Control Center.
Pre-upgrade 6102 iFix06 rows: DATE_TIME = yyyy-mm-dd hh:mm:ss
Post-upgrade 6121 iFix02 rows: DATE_TIME = yyyy/mm/dd hh:mm:ss 0000
The other SQL you'll need to run is slightly "trickier" as you need to convert a string to a date in either Oracle, DB2, or MSSQL SQL, which I must leave to you... But other than that, the SQL is very straight forward
Perform this SQL in this order
DELETE FROM CC_PROCESS_DVG WHERE PROCESS_ID IN (SELECT PROCESS_ID FROM CC_PROCESS WHERE STARTED < your date)
DELETE FROM CC_PROCESS WHERE STARTED < your date
DELETE FROM CC_FILE_TRANSFER_DVG WHERE FILE_TRANSFER_ID IN (SELECT FILE_TRANSFER_ID FROM CC_FILE_TRANSFER WHERE STARTED < your date)
DELETE FROM CC_FILE_TRANSFER WHERE STARTED < your date
Rebuild indexes periodically.
Rebuild these indexes at least every week for the following main tables:
a) EVENTS
b) CD_STATS_LOG
c) CE_STATS_LOG
d) CC_PROCESS
e) CC_PROCESS_DVG
f) CC_FILE_TRANSFER
g) CC_FILE_TRANSFER_DVG
h) AF_STATS_LOG
i) BP_STATS_LOG
j) CD_STATS_LOG
k) CE_STATS_LOG
l) CX_STATS_LOG
m) FG_STATS_LOG
n) MQ_STATS_LOG
o) QF_STATS_LOG
UID
ibm11123353