Hello colleagues,
When you have to support SAP Financial Consolidation (FC), you need to keep in mind that some tips.
Environment:
SAP Financial Consolidation 10.1
Windows Server
SAP HANA 2.0
Tip 1:
CT_LOG table has to be cleaned periodically. If the trace function is on, it grows very quickly and becomes huge.
SAP FC logs
How to clean:
1. (Optional) Create backup table
CREATE COLUMN TABLE "<SCHEMA_NAME>"."save_<date>_ct_log" ("ID" INTEGER CS_INT,
"USER_NAME" NVARCHAR(12),
"ACTION_DATE" NVARCHAR(10),
"ACTION_TIME" NVARCHAR(8),
"MAIN_OBJ_CODE" NVARCHAR(150),
"MAIN_OBJ_MGR" NVARCHAR(50),
"OBJECT1" NVARCHAR(150),
"OBJECT2" NVARCHAR(250),
"ACTION_ID" INTEGER CS_INT,
"IMPACTEDPROPERTY" NVARCHAR(250),
"DETAILS_ID" NVARCHAR(250)) UNLOAD PRIORITY 5 AUTO MERGE
2. (Optional) Save current data to the backup table
INSERT INTO <SCHEMA_NAME>.save_<date>_ct_log SELECT * FROM <SCHEMA_NAME>.CT_LOG
3. Delete old records (e.g. older than 2022-09)
DELETE FROM <SCHEMA_NAME>.CT_LOG WHERE TO_VARCHAR(ACTION_DATE,'YYYY-MM') < '2022-09';
Tip 2.
Check size and delete old records from: CT_HISTORY_DETAIL , CT_EXECUTIONS and CT_HISTORY. These tables can occupy some memory in SAP HANA.
CT tables size in SAP HANA
How to clean:
1. Delete old records in CT_HISTORY_DETAIL (e.g. older than 2022-09)
DELETE FROM <SCHEMA_NAME>.CT_HISTORY_DETAIL WHERE ID IN (SELECT ID FROM <SCHEMA_NAME>.CT_HISTORY WHERE TO_VARCHAR(WLOGDATE,'YYYY-MM') < '2022-09');
2. Delete old records in CT_EXECUTIONS (e.g. older than 2022-09)
DELETE FROM <SCHEMA_NAME>.CT_EXECUTIONS WHERE WHISTORYID IN (SELECT ID FROM <SCHEMA_NAME>.CT_HISTORY WHERE TO_VARCHAR(WLOGDATE,'YYYY-MM') < '2022-09');
3. Delete old records in CT_HISTORY (e.g. older than 2022-09)
DELETE FROM <SCHEMA_NAME>.CT_HISTORY WHERE TO_VARCHAR(WLOGDATE,'YYYY-MM') < '2022-09';
Tip 3 (optional).
Check SAP Notes first or open SAP ticket
If you have some strange problems with SAP FC like performance or blocking situations, you may try to delete records in:
- ct_identifier_pool (the first startup of the FC data sources will be slower)
- ct_recycled_identifier (the first startup of the FC data sources will be slower)
- ct_active_session
- ct_active_server
- ct_user_serial
- ct_md_lock
- ct_object_lock
Update ct_lock filed in CT_MUTEX table
- update <SCHEMA_NAME>.CT_MUTEX set ct_lock=0;
Useful links:
SAP Notes:
1969700 - SQL Statement Collection for SAP HANA
2178324 - How to delete visible logs in Financial Consolidation using SQL script ?-FC
2486273 - What does the function Trace Reports do ? - FC
2511264 - The consolidation task is interrupted randomly - FC
Clean-up of Objects
SAP Financial Consolidation Database Schema Guide