Load DWH job fails intermittently after running for a long time

Products

• Clarity PPM On Premise

Issue/Introduction

The job fails silently without any visible errors in the logs, just by reaching some timeout value. Also, adjusting ETL Job Timeout setting does not seem to have any effect. 

Environment

Release : 15.9.x, 16.x

Component :

Cause

The cause for this issue seems to be with the Oracle RAC Scan url being used for DWH connectivity. Intermittently it was unable to relay the connection to the actual DB host and hence the job is timing out.

Resolution

Possible solutions :- 

1. Either have the DBA validate it and have it fixed.
2. Use the direct oracle hostname in the connection string of DWH.
3. Try the below URL format.
   
   jdbc:clarity:oracle://oracscan:1521;ServiceName=serviceTNS;BatchPerformanceWorkaround=true;InsensitiveResultSetBufferSize=0;ServerType=dedicated;supportLinks=true;AlternateServers=(oracscan:1521);FailoverMode=Select;ConnectionRetryCount=20;ConnectionRetryDelay=15;LoadBalancing=true"
   
   where you use the orascan name in the alternate servers parameter as well.