Workload Automation DE Server is down and cannot be restarted

Document ID : KB000094072
Last Modified Date : 02/05/2018
Show Technical Document Details
Issue:
The DE Server has shut down. When attempting to start it, it shuts down again.
Environment:
DE Server with Oracle DB
Cause:
The issue is caused by a file monitor job that has a value in the "filename" field which exceeds 1024 characters.

The job is definition is stored in the ESP_AMS_DF table.

When it is time for the job to run, the DE server tries to insert runtime information into the ESP_FILEMONTR_JOB table. The FILE_NAME column of the ESP_FILEMONTR_JOB table has a character limit of 1024. The insert fails because it is trying to insert more than 1024 characters. 

Example of the errors seen in the tracelog.txt file:

[relationaldatabase] [ERROR] DM.Appl.<appl_name>.<gen_no>: [YYYY-MM-DD_hh:mm:ss] SQLCode: 12899 SQLState: 72000 Message: ORA-12899: value too large for column "TCBD0011"."ESP_FILEMONTR_JOB"."FILENAME" (actual: 1165, maximum: 1024) 

[relationaldatabase] [ERROR] DM.Appl.<appl_name>.<gen_no>: [YYYY-MM-DD_hh:mm:ss] SQL Exception for query: INSERT INTO ESP_FILEMONTR_JOB (job_id, filename, trigger_condition) VALUES (?,?,?); the exception is: ORA-12899: value too large for column "<job_name>"."ESP_FILEMONTR_JOB"."FILENAME" (actual: 1165, maximum: 1024) 
Resolution:
There are two options to fix this. 

Option 1 - The DBA can manually increase the character limit of the FILENAME column in the ESP_FILEMONTR_JOB table. This is easier and will allow the job to continue and stop crashing the DE Server. 

Option 2 - Get the application name of the file trigger job in question.
In the ESP_AMS_DF table, find the row where the "identifier" column contains the application name.
Manually update the 'DEFINITION' column. You will need to reduce the file_name string which has 1165 characters to less than 1024. 

Note: The second option is a little more difficult to do. You have to find the 1165 filename string within the correct tag and manually change it. 

After the above, start the DE Server.