Abnormal termination of stage (cast those CLOBs!)


As I started running a bunch of our delivered DataStage ETL jobs I ran into a really undescriptive warning message in several Director job logs.  Most of tem failed on the IPC stage right after the DRS stage that was extracting data out of our source database with an “Abnormal Termination of  Stage” error. After several attempts to turn on tracing and trying to find a rational reason for the error I went into my source database and realized  that there were CLOB columns in every table that was having this issue. After some testing the issue was resolved by casting the CLOB columns into the varchar2 data type:

CAST(my_column AS VARCHAR2(4000))

Now, these were jobs that were delivered with our warehouse, the designers were probably trying to make them as general as possible, after some conversations with the warehouse team I now got authorization to replace the CLOB content with empty strings to save space as the data in those columns is of low value to our BI users.

Tags: , ,

Leave a Comment