Procedure - Detailed

About this task

All files and executables related to GDPR are available at the <COLLABORATE_HOME>/tools/GDPR.

Procedure

  1. Set up the gdpr.properties before running the gdpr_purge utility.

    The user is required to fill all the information listed underneath for every Audience in their Unica Collaborate application. For the Default Audience Level - Customer.

    Note:
    # Name of the audience.
    # This is case sensitive.
    Collaborate.Audience.Name=Customer
    
    # Database type for Collaborate System tables.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.DBType
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.DBType=MSSQL
    # This property can have on of the MSSQL, DB2, ORACLE, MARIADB as value
    Collaborate.Customer.DBType=DB2
    
    
    # uacc_lists table mapped in collaborate for the audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Uacc_List
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Uacc_List=Uacc_List
    Collaborate.Customer.Uacc_List=uacc_lists
    
    # Uacc_Corporate_Permanent table mapped in collaborate for the audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Uacc_Corporate_Permanent
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Uacc_Corporate_Permanent=Uacc_Corporate_Permanent
    Collaborate.Customer.Uacc_Corporate_Permanent=Uacc_Corporate_Permanent
    
    # Uacc_Corporate_Lists table mapped in collaborate for the audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Uacc_Corporate_Lists
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Uacc_Corporate_Lists=Uacc_Corporate_Lists
    Collaborate.Customer.Uacc_Corporate_Lists=Uacc_Corporate_Lists
    # uacc_ondemand_lists table mapped in collaborate for the audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.uacc_ondemand_lists
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.uacc_ondemand_lists=uacc_ondemand_lists
    Collaborate.Customer.uacc_ondemand_lists=uacc_ondemand_lists
    
    # uacc_ondemand_permanent used in collaborate for the audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.uacc_ondemand_permanent
    # This can be blank if not used.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.uacc_ondemand_permanent=uacc_ondemand_permanent
    Collaborate.Customer.uacc_ondemand_permanent=uacc_ondemand_permanent
    Collaborate.Customer.uacc_permanent=uacc_permanent
    # Schema name used in collaborate for collaborate system tables.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Db.Schema.Name
    # This can be blank if no DB schema is used.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Db.Schema.Name=dbo
    Collaborate.Customer.Db.Schema.Name=
    
    # Audience ID field for audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Field
    # If composite key is present define multiple fields.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Field=CustomerID
    Collaborate.Customer.Field=Customer_ID
    
    # Data type for the Audience fields for the audience level Customer.
    # Name of this property should have the audience name and field name.
    # Format used here is Collaborate.<Audience>.<FieldName>.Datatype
    # for composite Audience level, define multiple field data types
    # Valid values for these properties are number/string.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.CustomerID.Datatype=number
    Collaborate.Customer.Customer_ID.Datatype=number
    # Absolute path to the input CSV file which has values for different columns defined for audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Csv
    # Note: must use forward slashes (/) or double-backslashes (\\)
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Csv=/sample/Customer.csv
    # Note: Here <GDPR_HOME> need to be replaced with actual path.
    Collaborate.Customer.Csv=/docker/unica/Collaborate/tools/GDPR/sample/Customer.csv
    
    # Absolute path to the output SQL file which will be generated by GDPR tool for audience level Customer.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Output
    # Note: must use forward slashes (/) or double-backslashes (\\)
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Output=/Customer.sql
    # Note: Here <GDPR_HOME> need to be replaced with actual path.
    Collaborate.Customer.Output=/docker/unica/Collaborate/tools/GDPR/Customer.sql
    
    # Maximum size of the output file in megabytes. If value of this property is nonzero
    # then output files will be split if file size is going beyond the below given limit.
    # Output file could be bit larger than the size specified by below property.
    # Name of this property should have the audience name.
    # Format used here is Collaborate.<Audience>.Output.FileSizeLimit
    # Only 0(zero) or positive values are supported. If 0 is specified then single output file will be created.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Output.FileSizeLimit=10
    Collaborate.Customer.Output.FileSizeLimit=10
    #####################################################################
    # Query separator character to be used for separating the queries.
    QuerySeparator=;
    
    # Nationalized string prefix to be used while generating the DB queries.
    # If your audience name or value specified in csv file has non English characters then N prefix should be used for MSSQL.
    NLS.String.Prefix=N
    For additional audience level (for example: Composite)
    Note: For additional audience levels, for example composite audience level, the users must replace the values with the names they use for additional audience levels in their Campaign application.
    Copy the following section for every audience for all the additional audience levels defined in the Campaign.
    # Name of the audience.
    # This is case sensitive.
    Collaborate.Audience.Name=CompositeAudience
    
    # Database type for Collaborate System tables.
    # Name of this property should have the audience name. 
    # Format used here is Collaborate.<Audience>.DBType
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.DBType=MSSQL
    # This property can have on of the MSSQL, DB2, ORACLE, MARIADB as value
    Collaborate.CompositeAudience.DBType=MARIADB
    # Uacc_List table mapped in collaborate for the audience level CompositeAudience.
    # Name of this property should have the audience name. 
    # Format used here is Collaborate.<Audience>.Uacc_List
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience. Uacc_List =UA_ContactHistory_CompositeAudience
    Collaborate.CompositeAudience.Uacc_List=uacc_lists
    Uacc_Corporate_Permanent table mapped in collaborate for the audience level CompositeAudience
    # Name of this property must have the audience name. 
    # Format used here is Collaborate.<Audience>.Uacc_Corporate_Permanent
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Uacc_Corporate_Permanent =UA_ResponseHistory_CompositeAudience
    Collaborate.CompositeAudience.Uacc_Corporate_Permanent=Uacc_Corporate_Permanent
    
    # Uacc_Corporate_Lists table mapped in collaborate for the audience level CompositeAudience
    # Name of this property should have the audience name. 
    # Format used here is Collaborate.<Audience>.Uacc_Corporate_Lists
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience. Uacc_Corporate_Lists = Uacc_Corporate_Lists_CompositeAudience
    Collaborate.CompositeAudience.Uacc_Corporate_Lists=Uacc_Corporate_Lists
    
    # uacc_ondemand_lists  table mapped in collaborate for the audience level CompositeAudience
    # Name of this property must have the audience name. 
    # Format used here is Collaborate.<Audience>.uacc_ondemand_lists
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience. uacc_ondemand_lists = uacc_ondemand_lists_CompositeAudience
    Collaborate.CompositeAudience.uacc_ondemand_lists=uacc_ondemand_lists
    # uacc_ondemand_permanent used in collaborate for the audience level CompositeAudience
    # Name of this property must have the audience name.
    # Format used here is Collaborate.<Audience>.ActionTable
    This can be blank if not used.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.uacc_ondemand_permanent = uacc_ondemand_permanent_CompositeAudience
    Collaborate.CompositeAudience.uacc_ondemand_permanent=uacc_ondemand_permanent
    
    Collaborate.CompositeAudience.uacc_permanent=uacc_permanent
    # Schema name used in collaborate for collaborate system tables.
    # Name of this property must have the audience name. 
    # Format used here is Collaborate.<Audience>.Db.Schema.Name
    # This can be blank if no DB schema is used.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Db.Schema.Name=dbo
    Collaborate.CompositeAudience.Db.Schema.Name=
    
    # Audience ID field for audience level CompositeAudience
    # Name of this property must have the audience name. 
    # Format used here is Collaborate.<Audience>.Field
    # For composite Audience level , define multiple fields.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Field=Field1PK
    Collaborate.CompositeAudience.Field=Field1PK
    Collaborate.CompositeAudience.Field=Field2PK
    Collaborate.CompositeAudience.Field=Field3PK
    # Data type for the Audience fields for the audience level CompositeAudience.
    # Name of this property should have the audience name and field name. 
    # Format used here is Collaborate.<Audience>.<FieldName>.Datatype
    # For composite Audience level, define multiple field data types
    # Valid values for these properties are number/string.
    # If there is space in audience name then escape space with single backslash
    For example Collaborate.Composite\ Audience.Field1PK.Datatype=number
    Collaborate.CompositeAudience.Field1PK.Datatype=number
    Collaborate.CompositeAudience.Field2PK.Datatype=string
    Collaborate.CompositeAudience.Field3PK.Datatype=string
    
    # Absolute path to the input CSV file which has values for different columns defined for audience level CompositeAudience
    # Name of this property should have the audience name. 
    # Format used here is Collaborate.<Audience>.Csv
    # Note: must use forward slashes (/) or double-backslashes (\\)
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Csv=/sample/CompositeAudience.csv
    # Note: Here <GDPR_HOME> need to be replaced with actual path.
    Collaborate.CompositeAudience.Csv=/docker/unica/Collaborate/tools/GDPR/sample/CompositeAudience.csv
    
    
    # Absolute path to the output SQL file which will be generated by GDPR tool for audience level CompositeAudience
    
    # Name of this property should have the audience name. 
    # Format used here is Collaborate.<Audience>.Output
    # Note: must use forward slashes (/) or double-backslashes (\\)
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Output=/CompositeAudience.sql
    # Note: Here <GDPR_HOME> need to be replaced with actual path.
    Collaborate.CompositeAudience.Output=D:/Deliver/HCL_GDPR/dist/collaborate/CompositeAudience.sql
    
    # Maximum size of the output file in megabytes. If value of this property is nonzero 
    # then output files will be split if file size is going beyond the below given limit.
    Output file could be bit larger than the size specified by below property.
    # Name of this property should have the audience name. 
    # Format used here is Collaborate.<Audience>.Output.FileSizeLimit
    # Only 0(zero) or positive values are supported. If 0 is specified then single output file will be created.
    # If there is space in audience name then escape space with single backslash
    # For example Collaborate.Composite\ Audience.Output.FileSizeLimit=10
    Collaborate.CompositeAudience.Output.FileSizeLimit=10
    
    ####################################################################
    # Query separator character to be used for separating the queries.
    QuerySeparator=;
    
    # Nationalized string prefix to be used while generating the DB queries.
    # If your audience name or value specified in csv file has non English characters then N prefix should be used for MSSQL.
    NLS.String.Prefix=N
    
    Note: The input CSV file including customer information to be deleted from the Collaborate system tables can be generated through Campaign using the select and snapshot process box; output only the audienceId field into the CSV file.
  2. Run the gdpr_purge utility.
    1. Launch the command prompt and navigate to <COLLABORATE_HOME>/tools/GDPR.
    2. Run the gdpr_purge.bat / sh.
      Note: In case of any errors, check the gdpr.log file generated at <COLLABORATE_HOME>\tools\GDPR\logs.Logging for gdpr_purge.sh/bat utility. In case of errors in the GDPR utility execution, users can use the log4j.properties to change the logging of GDPR utility.log4j.properties is available in <COLLABORATE_HOME>\tools\GDPR\.
  3. Review and execute SQL.
    The Output sql file containing delete statements gets generated at the following locations:
    • Output = <COLLABORATE_HOME>/tools/GDPR/Customer_0.sql.
    • Output= <COLLABORATE_HOME>/tools/GDPR/CompositeAudience_0.sql
    All the SQL files created for all the audiences configured in Collaborate system must be executed. The GDPR utility may generate multiple files of delete statements of customer records to be erased, depending on output file size specified in the properties file: Collaborate.CompositeAudience.Output.FileSizeLimit. The Collaborate user or Collaborate database administrator must execute all these files. If there are multiple partitions, the SQLs must be executed on each database schema for each partition defined in Campaign.