By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,117 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,117 IT Pros & Developers. It's quick & easy.

Migrating from Teradata to DB2 EEE.

P: n/a
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.
Thanks in advance for any and all information.

Jeff

Dec 5 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Should be relatively easy since they are built on the same
architecture. Obviously the technical details are different, but the
database design would usually be similar or even the same.

As in Teradata, the main thing to keep in mind is to minimize cross
partition joins by choosing the proper partitioning key.

Dec 5 '05 #2

P: n/a
"jdokos" <Je***********@nscorp.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.
Thanks in advance for any and all information.

Jeff

One more thing. I hope you are using DB2 ESE with DPF instead of DB2 EEE,
since version 7 is no longer supported by IBM.
Dec 6 '05 #3

P: n/a
Mark A wrote:
"jdokos" <Je***********@nscorp.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.
Thanks in advance for any and all information.

Jeff

One more thing. I hope you are using DB2 ESE with DPF instead of DB2 EEE,
since version 7 is no longer supported by IBM.


I heard that IBM came out with a new BCU for ICE on Linux but I
couldn't find any reference for that on the website other than the
BCU for AIX info. [http://www-306.ibm.com/software/data/db2bi/bcu/]
Does anyone has experience with that ? Any idea how that performs on a
~3TB data warehouse and beyond ?

-J

Dec 6 '05 #4

P: n/a
Ian
jdokos wrote:
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.


I don't know of any published white papers, but from my experience the
most significant conversion issues you are likely to run into will be
with your ETL processes (if you use BTEQ scripts) and/or complex,
hand-written SQL statements.

The two large conversions I have worked on both used BTEQ extensively
to handle the data load processes and the DB2 CLP is not as flexible.
If you are using a 3rd party tool for the Teradata ETL processing (or
are planning to use an ETL tool with DB2 then this may not be as big
of an impact).

Differences in SQL between DB2/Teradata should be expected, but there
are some things that DB2 does not support that make the translation more
complex. For example, in Teradata if you provide an alias for an
expression (i.e., T1.C1+T3.C3+T4.C8 AS MYSUM), you can refer to this
alias (i.e. MYSUM) within the same SQL statement (e.g., as part of
another expression or in a GROUP BY). DB2 requires that you to provide
the full expression.
Dec 6 '05 #6

P: n/a
Ian wrote:
Differences in SQL between DB2/Teradata should be expected, but there
are some things that DB2 does not support that make the translation more
complex. For example, in Teradata if you provide an alias for an
expression (i.e., T1.C1+T3.C3+T4.C8 AS MYSUM), you can refer to this
alias (i.e. MYSUM) within the same SQL statement (e.g., as part of
another expression or in a GROUP BY). DB2 requires that you to provide
the full expression.

Let me clarify this paragraph in order to not confuse the causal reader.

This does not work:
CREATE TABLE T(c1 INT, c2 INT);
SELECT c1 + c2 as x FROM T WHERE x = 5;
SELECT c1 + c2 as x FROM T GROUP BY x;
SELECT c1 + c2 as x FROM T ORDER BY x;

FYI, this is in accordance with the SQL standard. Personally I could be
convinced that the ORDER BY case is semantically sane (because order by
is a _post_ processing of the resultset) and perhaps could/should be
supported in some future.
The others make me shudder in an "only over my dead body" kinda way ;-)

This however is allowed:
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S WHERE x = 5;
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S GROUP BY x;
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S ORDER BY x;

So a column alias _cannot_ be reused within the defining _fullselect_.
A column alias _can_ be used within the same _SQL_Statement_.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 7 '05 #7

P: n/a
Ian
Serge Rielau wrote:
Ian wrote:
Differences in SQL between DB2/Teradata should be expected, but there
are some things that DB2 does not support that make the translation more
complex. For example, in Teradata if you provide an alias for an
expression (i.e., T1.C1+T3.C3+T4.C8 AS MYSUM), you can refer to this
alias (i.e. MYSUM) within the same SQL statement (e.g., as part of
another expression or in a GROUP BY). DB2 requires that you to provide
the full expression.

Let me clarify this paragraph in order to not confuse the causal reader.

This does not work:
CREATE TABLE T(c1 INT, c2 INT);
SELECT c1 + c2 as x FROM T WHERE x = 5;
SELECT c1 + c2 as x FROM T GROUP BY x;
SELECT c1 + c2 as x FROM T ORDER BY x;

FYI, this is in accordance with the SQL standard. Personally I could be
convinced that the ORDER BY case is semantically sane (because order by
is a _post_ processing of the resultset) and perhaps could/should be
supported in some future.
The others make me shudder in an "only over my dead body" kinda way ;-)

This however is allowed:
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S WHERE x = 5;
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S GROUP BY x;
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S ORDER BY x;

So a column alias _cannot_ be reused within the defining _fullselect_.
A column alias _can_ be used within the same _SQL_Statement_.

Serge,

Thanks for the clarification. Re-reading my paragraph above I
agree that it certainly needed some clarification :-)

Although I generally am happy with DB2 for sticking with standards,
in this particular case I wish that the SQL standard would be modified
to allow for column aliases to be re-used within the fullselect
(I'm _sure_ it's too much to ask IBM to add their own SQL extension).

Creating 10 nested-subselects to resolve the various dependencies is
not fun! (You could make a good argument that the database design
needs to be reviewed/fixed to avoid this kind of Franken-SQL, but in
the real world, that's not always feasible).
Thanks,


Dec 7 '05 #8

P: n/a
Ian wrote:
Serge Rielau wrote:
Ian wrote:
Differences in SQL between DB2/Teradata should be expected, but there
are some things that DB2 does not support that make the translation more
complex. For example, in Teradata if you provide an alias for an
expression (i.e., T1.C1+T3.C3+T4.C8 AS MYSUM), you can refer to this
alias (i.e. MYSUM) within the same SQL statement (e.g., as part of
another expression or in a GROUP BY). DB2 requires that you to provide
the full expression.


Let me clarify this paragraph in order to not confuse the causal reader.

This does not work:
CREATE TABLE T(c1 INT, c2 INT);
SELECT c1 + c2 as x FROM T WHERE x = 5;
SELECT c1 + c2 as x FROM T GROUP BY x;
SELECT c1 + c2 as x FROM T ORDER BY x;

FYI, this is in accordance with the SQL standard. Personally I could
be convinced that the ORDER BY case is semantically sane (because
order by is a _post_ processing of the resultset) and perhaps
could/should be supported in some future.
The others make me shudder in an "only over my dead body" kinda way ;-)

This however is allowed:
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S WHERE x = 5;
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S GROUP BY x;
SELECT x FROM (SELECT c1 + c2 as x FROM T) AS S ORDER BY x;

So a column alias _cannot_ be reused within the defining _fullselect_.
A column alias _can_ be used within the same _SQL_Statement_.


Serge,

Thanks for the clarification. Re-reading my paragraph above I
agree that it certainly needed some clarification :-)

Although I generally am happy with DB2 for sticking with standards,
in this particular case I wish that the SQL standard would be modified
to allow for column aliases to be re-used within the fullselect
(I'm _sure_ it's too much to ask IBM to add their own SQL extension).

Well, competitors are on record for complaining that the SQL Standard
reads too much like DB2's SQL Reference Manual - allhough that might
have been a reference to the SQL standard's extraordinary readability ;-)

This, however, is not about a simple extension.
Let me use the WHERE clause as an example.
The order of execution of a select is:
FROM <table> WHERE <predicate> SELECT <projection>
What this means becomes clear when you run this experiment:
SELECT (c1 / c2) AS X FROM T WHERE c2 <> 0
Woudl you expect a divsion by zero error here?
So: How could the WHERE clause possibly refer to anything in the select
list which doesn't even exist yet?
The same holds true for the GROUP BY clause.
I guess the standrad would much rather entertain adding a column alias
to the GROUP BY expressions and using them in the select list than the
other way around:
SELECT x, SUM(c1) FROM T GROUP BY (c2 + c3) AS X

This is what really happens: group and return the grouping expression.
Remember that the grouping expression doesn't even have to be in the
select list!

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 7 '05 #9

P: n/a

jdokos wrote:
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.
Thanks in advance for any and all information.

Jeff


Jeff-

There are lot's of companies that have migrated from Teradata to DB2-
(JC Penney, E*Trade, Mutual of Omaha... the list goes on.) There are
case studies available that I am sure your local IBM BI Rep can provide
- I also know that IBM has a Migration Services team that specializes
in migrating Teradata and other DW appliance or legacy BI platforms to
IBM's BI SW (DB2 DWE ). You should think about going to one of the IBM
BI events- Their BI customer event in San Francisco this year featured
several customers that were former Teradata and would give you the
opportunity to meet up with a few hundred of their larger BI and
Datawarehouse customers to hear about the differences first hand.

Good Luck.
Brian

Dec 7 '05 #10

P: n/a
Thanks for all of your responses. We just went to the Torolab and
attended some presentations on BCU and DWE. Which gave us some good
ideas.

BrianMc wrote:
jdokos wrote:
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.
Thanks in advance for any and all information.

Jeff


Jeff-

There are lot's of companies that have migrated from Teradata to DB2-
(JC Penney, E*Trade, Mutual of Omaha... the list goes on.) There are
case studies available that I am sure your local IBM BI Rep can provide
- I also know that IBM has a Migration Services team that specializes
in migrating Teradata and other DW appliance or legacy BI platforms to
IBM's BI SW (DB2 DWE ). You should think about going to one of the IBM
BI events- Their BI customer event in San Francisco this year featured
several customers that were former Teradata and would give you the
opportunity to meet up with a few hundred of their larger BI and
Datawarehouse customers to hear about the differences first hand.

Good Luck.
Brian


Dec 8 '05 #11

P: n/a
Hi all

we are planning to migrate DB2UDB EE to teradata. Any one knows about
migration process and what are the steps that we need to take care.

thanks in advance
jdokos wrote:
Thanks for all of your responses. We just went to the Torolab and
attended some presentations on BCU and DWE. Which gave us some good
ideas.

BrianMc wrote:
jdokos wrote:
Does anyone know of any good sources (white papers, etc.) regarding
migrating from Teradata to DB2 UDB EEE?

We are in the very beginning stages of investigating this as an option
for some of the smaller projects that are going to Teradata presently.
Thanks in advance for any and all information.

Jeff


Jeff-

There are lot's of companies that have migrated from Teradata to DB2-
(JC Penney, E*Trade, Mutual of Omaha... the list goes on.) There are
case studies available that I am sure your local IBM BI Rep can provide
- I also know that IBM has a Migration Services team that specializes
in migrating Teradata and other DW appliance or legacy BI platforms to
IBM's BI SW (DB2 DWE ). You should think about going to one of the IBM
BI events- Their BI customer event in San Francisco this year featured
several customers that were former Teradata and would give you the
opportunity to meet up with a few hundred of their larger BI and
Datawarehouse customers to hear about the differences first hand.

Good Luck.
Brian


Dec 28 '05 #12

P: n/a
Infatdguy wrote:
Hi all

we are planning to migrate DB2UDB EE to teradata. Any one knows about
migration process and what are the steps that we need to take care.

As in DB2 V7 EE? Going from a serial to an MPP system requires some
thought whether it's DB2 with DPF, Teradata or XPS
(or Oracle RAC if you're serious about scale out)

Since you are posting to a DB2 newsgroup I allow myself the question:
Why do you migrate to Teradata? Such migrations are very far and few
between and while Teradata has some nice technology it is also very
limiting (limited solution stack).
Did you evaluate DB2 (V8) with DPF?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Dec 28 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.