QueryEngine encountered error:Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. SJIAO.IS_MESSAGE_FACT IS AN UNDEFINED NAME. For more information, please consult DB2 document for error code SQL0204. An error occurred during implicit system action type '2'. Information returned for the error includes SQLCODE '-204', SQLSTATE '42704' and message tokens 'SJIAO.IS_MESSAGE_FACT'. Connection String: ..
CAUSE:The error " *** IS AN UNDEFINED NAME" from DB2 database indicates that table or view *** does not exist. In this case, view SJIAO.IS_MESSAGE_FACT is missing in the database.
ACTION: 1. Run the following SQL script to create view IS_MESSAGE_FACT.
CREATE VIEW IS_MESSAGE_FACT AS
SELECT
DISTINCT MS.DAY_ID AS DAY_ID,
MS.HOUR_ID AS HOUR_ID,
MS.MINUTE_ID AS MINUTE_ID,
MS.MESSAGEINDEX AS IS_MESSAGE_INDEX,
MS.SESSIONID AS IS_SESSION_ID,
MS.HISTORYLISTMESSAGEID AS IS_HL_MESSAGE_ID,
MS.SCHEDULEJOBID AS IS_SCHEDULE_JOB_ID,
MS.DATATYPE AS IS_DATATYPE_ID,
MS.RECIPIENTCONTACTID AS IS_RCPT_CONTACT_ID,
MS.DELIVERYTYPE AS IS_DELIVERY_TYPE_ID,
MS.SUBSINSTID AS IS_SUBS_INST_ID,
CASE WHEN MS.SCHEDULEID IS NULL THEN -1
ELSE SCH.IS_SCHED_ID
END AS IS_SCHEDULE_ID,
MS.SUBINSTNAME AS IS_SUBS_INST_NAME,
MS.DATAID AS IS_DATA_ID,
MS.CONTACTTYPE AS IS_CONTACT_TYPE_ID,
MS.RECIPIENTGROUPID AS IS_RCPT_GROUP_ID,
MS.RECIPIENTCONTACTNAME AS IS_RCPT_CONTACT_NAME,
MS.ISDEFAULTADDRESS AS IS_DFLT_ADDR,
MS.ADDRESSID AS IS_ADDRESS_ID,
CASE WHEN MS.DEVICEID = '00000000000000000000000000000000' THEN 0
WHEN MS.DEVICEID IS NULL THEN -1
ELSE DEV.IS_DEVICE_ID
END AS IS_DEVICE_ID,
MS.ISNOTIFICATIONMESSAGE AS IS_NOTIF_MSG,
MS.NOTIFICATIONADDR AS IS_NOTIF_ADDR,
SER.IS_SERVER_ID AS IS_SERVER_ID,
MS.SERVERMACHINE AS EM_APP_SRV_MACHINE,
CASE WHEN PRJ.IS_PROJ_ID IS NOT NULL THEN PRJ.IS_PROJ_ID
ELSE -1
END AS IS_PROJ_ID,
MS.EXECSTARTTIME AS IS_EXEC_ST_TM_TS,
MS.EXECFINISHTIME AS IS_EXEC_FN_TM_TS,
MS.DELIVERYSTATUS AS IS_DELIVERY_STATUS,
MS.PHYSICALADDRESS AS IS_PHYSICAL_ADD,
MS.BATCHID AS IS_BATCH_ID,
MS.RECORDTIME AS EM_RECORD_TS,
CURRENT_TIMESTAMP AS EM_LOAD_TS
FROM IS_MESSAGE_STATS MS
LEFT JOIN IS_SCHED SCH
ON MS.SCHEDULEID = SCH.IS_SCHED_GUID
LEFT JOIN IS_PROJ PRJ
ON MS.PROJECTID = PRJ.IS_PROJ_GUID
JOIN IS_SERVER SER
ON MS.SERVERID = SER.IS_SERVER_GUID
LEFT JOIN IS_DEVICE DEV
ON MS.DEVICEID = DEV.IS_DEVICE_GUID
2. Configure Enterprise Manager to start logging records for Distribution Services.
NOTES:
- Before taking the action above, the Enterprise Manager warehouse should already have included the following tables.
IS_MESSAGE_STATS
IS_SCHED
IS_PROJ
IS_SERVER
IS_DEVICE
EM_MD
- The SQL script introduced is DB2 specific. For other types of database, users can refer to the em_sql_** files under "Program Files\MicroStrategy\Enterprise Manager" and employ respective CREATE VIEW IS_MESSAGE_FACT sql statements.
