Retek ETL for Oracle 8i won't work with 10g

March 16, 2009 6:00 AM

Anyone out there familiar with Retek ETL?

At work we run a fairly old Retek data warehouse on AIX 4.3.3, Oracle 8i. A large part of the nightly load is handled by the ETL program that comes as part of Retek's package.

In an effort to enter the 21st century, we've begun a project to migrate our data warehouse to Oracle 10g running on AIX 5.1 (and, maybe sometime in the future, change to Oracle on Linux).

What we've noticed is that when we try to use the Retek ETL to load data onto the 10g database, it fails with a series of error messages, and we can't figure out why. We haven't moved the ETL binary - it's still operating under AIX 4.3.3. All we did was modify the pointer to the database (in Retek's config.env, in case you're interested).

It seems like the ETL must do some backdoor dealings with the Oracle 8i database that don't work with Oracle 10g. The version number of the ETL (obtained by typing rfx -h) is 10.2.1 build 888.

We can't really upgrade to the latest version of Retek because a) it's too expensive, and b) we've customized our data warehouse pretty thoroughly.

Can anyone give me some insight into what might be going on with Retek's ETL?

posted by Ritchie to computers & internet (4 comments total)

A typical example of the error messages received is

DATABASE ERROR: Error Fetching SQL Statement
Error Fetching SQL Statement
ORA-24374: define not done before fetch or execute and fetch

posted by Ritchie at 6:02 AM on March 16

It might just not be compatible with Oracle 10g. The release notes for Retek ETL version 11 state that's it's only certified for Oracle 9, so I'd guess 10 isn't certified for Oracle 10g either. There probably is no simple solution to your problem.
posted by dhoe at 7:09 AM on March 16

Seconding dhoe, and have you tried calling Retek?
posted by rhizome at 9:58 AM on March 16

Okay, it's true. Version 10 of the ETL is not compatible with Oracle 10g. However we were able to download version 13 of the ETL from Oracle (who own Retek), and install it on the AIX 5.3 box (it won't run on AIX 4.3). After some fiddling, we got it to connect to the database and perform some basic operations.

So, we're back in business, albeit with some modifications to our original project plan.
posted by Ritchie at 6:54 PM on March 18