Question
Deutsche Bank AG
DE
Last activity: 15 May 2018 12:20 EDT
PMD 6.6.2.0.GA on SLES - object reference not found
Hello together,
in times of GDPR we need to check our data objects and if they can be housekept and/or deleted.
So far we checked the PMD repository and found some tables with no reference in the OBJ_TYPE table.
for example CB*
CB1010_4_11165_30_0
…
CB2081_1_15080_226_0
for example F*
F1080_1_18984_27_0
---
F1218_1_4115_75_0
for example T*
T1075_49_6395_13_3
…
T28_1225_415_37_0
for example OT_TMP*
OT_TMP_CB1773
…
OT_TMP_CB18902
Where can we find a reference table for these objects?
Like SEG_HDR for Segments.
We use PMD Version Proprietary information hidden.GA on SLES
Thanks a lot in advance
Optum
IN
Hi Marcus,
We apologize for the delay in response.
The reference table for the mentioned objects is called VANTAGE_DYN_TAB, which holds information on dynamic entities like CB*, F*, BS*, etc, created by Marketing Director. Note, however, it only contains a reference to these tables, but not the table data itself.
For housekeeping of the CMD schema, kindly go through the attached document on Purge Scripts. Do let us know if you have any doubts/queries regarding the same, we will be happy to assist.
Deutsche Bank
DE
Hello Srinivas, i check the purge document and script.
The procedure purge_CStables only lookup table from cancelled / completed like
Hello Srinivas, i check the purge document and script.
The procedure purge_CStables only lookup table from cancelled / completed like
SELECT db_ent_name
FROM vantage_dyn_tab
WHERE db_ent_name LIKE 'CS%_%_%' ...
and drops the tables from repository.
the obj_type_id = 27 for the CS tables.
All the table we looking for like CB* and OT_TMP_*.
Has the same obj_type_id = 27 like the CS tables.
Could i drop the tables if referenced campaign status is complete or cancelled?
Additional question:
We have 5404 table called like F1080_1_18984_27_0
select * from all_tables where owner='ADVANTAGE' and table_name like 'F%_%_%'
These tables not referenced in vantage_dyn_tab
Could i drop thise tables?
Thanks a lot in advance
Dieter
Pegasystems Inc.
IN
Hi Marcus/Dieter,
Temporary tables beginning with letter like like 'E_%', 'F%', 'T<digit>%' and 'ZZZ%' are created during Campaign run and are dropped at the end for a successful execution. However, they are left behind if the Campaign aborts. If you have any of the these tables present in the database, they can be safely dropped.
Hi Marcus/Dieter,
Temporary tables beginning with letter like like 'E_%', 'F%', 'T<digit>%' and 'ZZZ%' are created during Campaign run and are dropped at the end for a successful execution. However, they are left behind if the Campaign aborts. If you have any of the these tables present in the database, they can be safely dropped.
'CB%' temporary tables are generated during output processing if the delivery channel is 'Combined' type. It has a reference entry in vantage_dyn_tab table. It is a combined segment table created using the CS* tables (of the combined segments) of the campaign. We have tried dropping CB% tables of aborted/failed campaigns and didn’t face any issue when rescheduled. However, as a precaution, it is suggested to drop only the ‘CB%’ tables of campaigns that are completed or cancelled.
The OT_TEMP* tables mentioned doesn't seem to be standard tables generated by CMD so these are either output tables that have been deliberately named like this or are may be tables created outside of CMD. If they are output tables you will need to check if they are no longer needed.
The Dynamic tables are only created when the particular object is executed, for example, segments, derived values, campagins, etc and are recreated during any future execution. Please refer the attached document('Dynamic Table Names' topic of Chapter 8) about standard dynamic tables list, which can be purged using the purge scripts. Other temporary tables need to be manually dropped.
Please note as a safety measure, it is always suggested to back up the respective tables or entire schema before making any changes to the CMD Schema.
Optum
IN
Hello Dieter,
We apologize for the delayed response, and regret the inconvenience caused.
OBJ_TYPE_ID field or Object Type Identifier indicates the type of object to which the dynamic customer table belongs. These are predefined values and the information for this, is present in OBJ_TYPE table. Any dynamic table related to Campaign Segment (CS), for example, will have OBJ_TYPE_ID=27, and so on.
Could you please run the below SQL and send us the output?
select * from obj_type where regexp_like(obj_abbrev, '^F');
Deutsche Bank
DE
Hello Srinivas,
the result of the query
select Count(*) from advantage.obj_type where regexp_like(obj_abbrev, '^F');
is 0
Please contact me if you Need more informations.
Dieter