Oracle to Teradata migration

muralisankarr · Post by **muralisankarr** » Tue Oct 13, 2009 1:36 am

We have nearly 600 datastage jobs in which the target table & target lookup tables are Oracle EE stages.

Now we need to fork lift the Oracle database to teradata.

Is there any other way we have other than opening each & individual job and changing the oracle stage to teradata stage?

Before some time when we need to change a parameter in 40 jobs we have exported the dsx and made changes in the dsx code and re-imported it. After compilation it worked. Will it be be possible in this case?

can any one please guide us with your ideas.

Thanks
MSR

chulett · Post by **chulett** » Tue Oct 13, 2009 5:30 am

It won't be that simple, I'm afraid. You could test that process out by exporting one job as a control, then manually changing the stage to Teradata and exporting it again, comparing the two exports. You'd need to quantify the exact differences between the two and see if you could come up with some way to automate that change. And I don't mean via an editor search/replace but perhaps a Server job to effect the change?

"Fork lift", eh? If you mean what I think you mean - you create an "exact" image of the Oracle database design in Teradata - then I wouldn't want to be in your shoes. I worked somewhere where another team attempted to do this and it was a complete and utter failure. Sure, to management it sounds like the quickest way to get you there, but you really need someone proficient in Teradata to work on redesigning it properly for that database to take alleged advantage of all the reasons you all went with it and then migrating your data to it. Dropping an Oracle design unchanged onto Teradata is a recipe for disaster, IMHO. Good luck with that.

ps. That place? They "went back" to Oracle.

muralisankarr · Post by **muralisankarr** » Wed Oct 14, 2009 11:38 am

chulett wrote:It won't be that simple, I'm afraid. You could test that process out by exporting one job as a control, then manually changing the stage to Teradata and exporting it again, comparing the two exports. You'd need to quantify the exact differences between the two and see if you could come up with some way to automate that change. And I don't mean via an editor search/replace but perhaps a Server job to effect the change?

Thanks chulett. We are on the way. But it is really tough to map the Oracle attributes to Teradata attributes for the tables. Some tables are target and some are look up and some are intermediate temp tables.The code logic should map all input, output & look up stages if the table involved is present in Data warehouse

For the forklift approach I can't really help myself. The architects are at my commanding position and I left with no option as they think their EDM will do wonders after this lift

vmcburney · Post by **vmcburney** » Sun Oct 18, 2009 7:57 pm

There may be a very big shortcut. Version 8.1 comes with a new set of Connector stages for Teradata and Oracle and it comes with a Connector converter - an application/wizard that turns Enterprise Stages into Connector stages. There may be a way to use it to turn Oracle Enterprise stages into Teradata Connector stages.

I would hate to see an Oracle data model dumped straight into Teradata - there are some significant differences as to how Teradata handles primary keys. You could end up with a slow and expensive Teradata configuration. I think unless you go through a data model validation and redesign exercise using a Teradata guru you are headed for a complete disaster.

Primary index determines the distribution of table rows on the disks controlled by AMPs. In Teradata RDBMS, a primary index is required for row distribution and storage. When a new row is inserted, its hash code is derived by applying a hashing algorithm to the value in the column(s) of the primary code (as show in the following figure). Rows having the same primary index value are stored on the same AMP.

- http://teradata.uark.edu/research/wang/indexes.html

You will need to consider how to configure each Teradata target stage as to which load option is the most efficient as Teradata has several ways to load data.

ray.wurlod · Post by **ray.wurlod** » Sun Oct 18, 2009 9:32 pm

Gosh, that's how UniVerse table storage (and Unidata table storage) is handled too! Both of these, however, were invented in the 1980s. They were also Y2K compliant from day one.

chulett · Post by **chulett** » Sun Oct 18, 2009 10:21 pm

vmcburney wrote:I think unless you go through a data model validation and redesign exercise using a Teradata guru you are headed for a complete disaster.

I know so, having witnessed one such disaster first-hand.

hamzaqk · Post by **hamzaqk** » Tue Dec 08, 2009 12:23 am

you can write a shell script to do this.. we have done it is such migration problem

chulett · Post by **chulett** » Tue Dec 08, 2009 6:40 am

After all this discussion, which "this" is the this you wrote a shell script for?

kamalshil · Post by **kamalshil** » Sun Dec 13, 2009 11:43 pm

Hey, As per my understanding it isnt a good approach to go for any shortcuts for Oracle to teradata migration .Reason is as follows: In Oracle we have DateTime fields but in Teradata there is split to date and time .I.e. Say in Oracle we have a field Current_Date which is Datetime,we have to split that to Current_Date (Date) and Current_Date_Time(TimeStamp).So we have to go for job level modifications,as there would be so many such fileds in every job.

hamzaqk · Post by **hamzaqk** » Wed Dec 16, 2009 11:03 pm

"this" would the jobs that have to be converted from oracle to teradata for which a shell script can be written to change the target from oracle to a TD stage. This for "this" requires some good detailed homework though.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 16, 2009 11:36 pm

Teradata Hybrid Information System ?

DSXchange

Oracle to Teradata migration

Oracle to Teradata migration

Re: Oracle to Teradata migration