Archives of the TeradataForum

Message Posted: Fri, 11 Aug 2006 @ 13:16:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Friday, August 11, 2006 08:54 -->

I need some help with an alternative solution to loading some data that needs to be transformed.

I am supporting a legacy application that is sourced from another legacy application so the method of transforming the data to get it into the warehouse is probably not the most efficient.

The application that I support receives a file with Sales transactions around 11:00 p.m. with anywhere from 6 to 8 million records and then another file with additional Sales transactions around 5:00 a.m. with a volume of approximately 1/10 of the records from the first feed.

The Sales transactions are Fastloaded into a worktable; column SALE_DATE is defined as CHAR(5) and input data values are in a Julian Date format (e.g. 06221).

Once the data is Fastloaded, the data from the worktable is merged with the data in the warehouse table (approximately 1.230 terabytes in size without fallback) by doing an insert/select into an empty table. Because the application is doing an insert/select into an empty table, there is a moment of time when there are two copies of the table that is over 1.2 terabytes in size and space is really at a premium. As the data is being inserted into the empty table, the SALE_DATE column from the worktable is transformed to a Gregorian Date by concatenating 20 with the Julian Date (e.g. 20||06221) and the data is stored on the warehouse table in the format of DDMMYYYY.

I am looking for creative ways to get the data merged from the worktable into the warehouse table and I had thought I might be able to accomplish this with Multiload or TPUMP with an INMOD exit routine. However when I read the MLOAD documentation it doesn't look like I can transform the SALE_DATE column as I need to, to get the data into the warehouse table in the format I need. Based on what I read about TPUMP, TPUMP might satisfy my request but I'm not very familiar with TPUMP and don't know where to begin with writing an INMOD exit routine.

Does anyone have any alternative solutions for the problem described above whereby I could merge the data from the work table (transforming the SALE_DATE column) with the data in the warehouse table without rebuilding the tables? There are four tables in total however the table outlined above is the largest of the four.

	<Prev	Next>		<<First	<Prev	Next>	Last>>