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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s