Load DWH Job Fails Intermittently After Stuck on DWH_INV_PROJECT
Article ID: 245531
Updated On:
Products
Issue/Introduction
Run either the full or incremental Load DWH job.
Actual: The job gets stuck processing.
Expected: The should complete within 30 minutes
The job intermittently fails after 100+ minutes, where typically it runs in under 30 minutes.
BG-DWH log shows stuck on:
Present?]
INFO 2022-07-06 12:45:14,957 [Thread-13387] dwh.event (none:none:none:none) dwh_dim_load_pg - Starting entry [Track Deletions]
INFO 2022-07-06 12:45:15,176 [Thread-13387] dwh.event (none:none:none:none) dwh_dim_load_pg - Starting entry [Clear Deleted Records ]
INFO 2022-07-06 12:45:15,503 [Thread-13387] dwh.event (none:none:none:none) dwh_dim_load_pg - Starting entry [Merge - Insert/Update ]
Eventually it will fail with
FATAL: terminating connection due to administrator command
Where: SQL statement "INSERT INTO DWH_INV_PROJECT (TD_EXPORT_WHY_REJECT, CLARITY_CREATED_DATE, TIER_2_A, EV_RPT_PERIOD, SPONSORED_BY, PR_USERTEXT1, PR_USERTEXT2, PR_USERTEXT3, PR_USERTEXT4, PR_USERTEXT5, PR_USERTEXT6, PR_USERTEXT7, AS_OF_DATE, EV_RPT_PERIOD_KEY, INVESTMENT_KEY, RISK_OBJECTIVES_KEY, RISK_SPONSORSHIP_KEY, RISK_FUNDING_KEY, RISK_RESOURCE_AVAIL_KEY, RISK_INTERDEPENDENCY_KEY, RISK_TECHNICAL_KEY, RISK_HUMAN_INTERFACE_KEY, RISK_ORG_CULTURE_KEY, RISK_SUPPORTABILITY_KEY, RISK_IMPLEMENTATION_KEY, RISK_FLEXIBILITY_KEY, EV_CALC_METHOD_KEY, IS_FINISH_IMPOSED, IS_START_IMPOSED, IS_PROGRAM, IS_REQ_APPROVAL_REQUIRED, TER_CUST_PN, OBJECTIVE, BUSINESS_OWNER, ESTIMATING_FACTOR_1, IDEA_PRJ_CATEGORY_KEY, IDEA_PRJ_TYPE_KEY, PROJECT_MGMT_OFFICE, STATUS_REPORTING_KEY, PROJECT_MGMT_OFFICE_KEY, ESTIMATING_FACTOR_2, FINANCE_GOVERNANCE, BUSINESS_OWNER_KEY, FINANCE_GOVERNANCE_KEY, TD_APRV_SCO_CH_DAYS, TD_APPR_SCP_CHNG_CST, TD_CUST_DEL_RISK_KEY, TD_CURR_BL_P4, TD_DESIGN_LATE, TD_BIN_1, TD_APP_EX_RISK_KEY, TD_RMA_QUANTITY, TD_FORECASTED_GM_DOL, TER_PART_NUMBER, TD_SCOPE_RSK_KEY, TER_HDW_BUYER, TD_DESIGN_RESPIN, TER_BU_KEY, TD_DESIGN_RESPIN_DT, TER_PART_NUMBER_KEY, TER_DESIGN_CONTROL, TD_SHIP_TOTAL, TD_MANUFACTURE_DEFEC, TER_FUND_TYPE_KEY, TD_PROD_ISS_RISK_KEY, TER_EXPECTED_COMP_DT, .
.
.
.
EXCLUDED.TD_HOURS_BUDGET, TD_HOURS_STAFF = EXCLUDED.TD_HOURS_STAFF, TER_TEST_TIME = EXCLUDED.TER_TEST_TIME, TER_PARENT_PROJECT = EXCLUDED.TER_PARENT_PROJECT, TD_SCH_CR = EXCLUDED.TD_SCH_CR, TD_ENTTY_LST_STTS_KEY = EXCLUDED.TD_ENTTY_LST_STTS_KEY, TD_FDPR_STTS_KEY = EXCLUDED.TD_FDPR_STTS_KEY, TD_CC_REVIEW_STATUS_KEY = EXCLUDED.TD_CC_REVIEW_STATUS_KEY, TD_CC_REVIEW_WHY_RE = EXCLUDED.TD_CC_REVIEW_WHY_RE, TD_HLA_PART_NUMBER_KEY = EXCLUDED.TD_HLA_PART_NUMBER_KEY, TD_FIN_CUSTOMER_KEY = EXCLUDED.TD_FIN_CUSTOMER_KEY, TD_FIN_CUSTOMER = EXCLUDED.TD_FIN_CUSTOMER, TD_GENERAL = EXCLUDED.TD_GENERAL, TD_SCHEDULE_SLIP_RAT = EXCLUDED.TD_SCHEDULE_SLIP_RAT, TD_COST_SLIP_RATE_ME = EXCLUDED.TD_COST_SLIP_RATE_ME, TD_CUSTOMER_PROCESS_KEY = EXCLUDED.TD_CUSTOMER_PROCESS_KEY, TD_ACT_COGS_2022 = EXCLUDED.TD_ACT_COGS_2022, TER_ADJ_COST_SLIP_US = EXCLUDED.TER_ADJ_COST_SLIP_US, TD_PROJ_DESC = EXCLUDED.TD_PROJ_DESC, TD_PROJ_NAME = EXCLUDED.TD_PROJ_NAME, TD_PROJ_NAME_HIST = EXCLUDED.TD_PROJ_NAME_HIST, TD_MEU_STTS_KEY = EXCLUDED.TD_MEU_STTS_KEY, TD_CC_CONFIRMED_DT = EXCLUDED.TD_CC_CONFIRMED_DT, TD_CC_CONFIRMED = EXCLUDED.TD_CC_CONFIRMED, dw_updated_date = EXCLUDED.dw_updated_date"
PL/pgSQL function inline_code_block line 9 at EXECUTE
Environment
Release : 16.0.2
Component :
Resolution
This is under Engineering investigation.
Feedback
