Support Article

Error running Pega Marketing Setup Utility with externalized IH



User has encountered an error while running Pega Marketing Setup Utility and figured that the issue was due to an (seemingly incomplete) PR_DATA_IH_DIM_CONTEXT table in the separate or standalone Interaction History (IH) schema.

Error Messages

[sql] Failed to execute: CREATE MATERIALIZED VIEW <PegaDATA Schema>.MKT_CAMPAIGN_OFFER_STATS (LAST_REFRESHED, ISSUENAME, GROUPNAME, OFFER, PENDING, IMPRESSION, CLICKED, ACCEPTED) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE NEXT SYSDATE + 30/1440 AS WITH all_stats AS (SELECT ifr.pxfactid AS pxfactid ,ifr.pysubjectid AS customerid ,CASE isr.pyoutcome WHEN 'Pending' THEN 1 ELSE 0 END AS pending ,CASE isr.pyoutcome WHEN 'Impression' THEN 1 ELSE 0 END AS impression ,CASE isr.pyoutcome WHEN 'Clicked' THEN 1 ELSE 0 END AS clicked ,CASE isr.pyoutcome WHEN 'Accepted' THEN 1 ELSE 0 END AS accepted ,irc.pycategory AS pycategory ,action.pyissue AS pyissue ,action.pygroup AS pygroup ,action.pyname AS offer FROM PEGAIHSTORE.pr_data_ih_factifr LEFT OUTER JOIN PEGAIHSTORE.pr_data_ih_dim_contextirc ON (ifr.pzcontextid = irc.pzid) INNER JOIN PEGAIHSTORE.pr_data_ih_dim_outcomeisr ON (ifr.pzoutcomeid = isr.pzid) LEFT OUTER JOIN PEGAIHSTORE.pr_data_ih_dim_action action ON (ifr.pzactionid = action.pzid) WHERE isr.pyoutcome IN ('Pending' ,'Impression' ,'Clicked' ,'Accepted') AND irc.pycategory LIKE 'Data-BatchOutCMPR%' WITH READ ONLY) ,offer_category_customer AS (SELECT MAX (impression) AS impression ,MAX (clicked) AS clicked ,MAX (accepted) AS accepted ,MAX (pending) AS pending ,pyissue ,pygroup ,offer ,pycategory ,customerid FROM all_stats GROUP BY pyissue ,pygroup ,offer ,pycategory ,customerid) SELECT SYSDATE AS last_refreshed ,pyissue AS issuename ,pygroup AS groupname ,offer ,SUM (pending) AS pending ,SUM (impression) AS impression ,SUM (clicked) AS clicked ,SUM (accepted) AS accepted FROM offer_category_customer GROUP BY pyissue, pygroup, offer
[sql] java.sql.SQLSyntaxErrorException: ORA-00904: "IRC"."PYCATEGORY": invalid identifier
[sql] Failed to execute: COMMENT ON MATERIALIZED VIEW <PegaDATA Schema>.MKT_CAMPAIGN_OFFER_STATS IS 'snapshot table for snapshot MKT_CAMPAIGN_OFFER_STATS'
[sql] java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Steps to Reproduce

1. Install Pega 7.2.1 and install Interaction History in a separate schema by running the script available in the product media /Pega721/Additional_Products/DSM/Products/InteractionHistory/schema.
2. Install Pega Marketing 7.21 by importing the archives.
3. Run the Pega Marketing Setup Utility.

Root Cause

User has externalized the Interaction History (IH) tables. However selecting Automatic option in “step 6” of the “Import the Application Bundles” section (page# 12) in the Pega Marketing installation guide (while importing the which contains schema changes, has taken care of the IH tables only in the PegaDATA schema and not in the externalized IH schema.


To overcome the inconsistency related to the IH tables:

  1. Take a backup of the PegaDATA schema as a precaution
  2. Drop all the tables related to Interaction History, that is, table names starting with “PR_DATA_IH_” from the PegaDATA schema with cascade option.
  3. Run the “IH_install_on_oracle.sql” file generated earlier, by pointing to PegaDATA schema, so that IH table structures are in sync in both the schemas, that is PegaDATA and ExternalIH.
  4. Follow the Pega Marketing Installation Guide to import the file and in the “step 6” of the “Import the Application Bundles” section select the Manual option to download the DDL SQL.
  5. Verify the downloaded DDL SQL and run it against the External IH schema to update the necessary IH related tables, it involves Alter table, Grant and Create Index SQLs for the IH tables.
  6. After the IH tables are in-line with the standard structure, execute the “Pega Marketing Setup Utility”.

Published July 21, 2016 - Updated August 23, 2017

Have a question? Get answers now.
