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

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.

"jdokos" <Jeffery.Dokos@nscorp.com> wrote in message
news:1133817184.553599.14500@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.

Mark A wrote:
> "jdokos" <Jeffery.Dokos@nscorp.com> wrote in message
> news:1133817184.553599.14500@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

Check out http://www.tpc.org/tpch/results/tpch_perf_results.asp

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.

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

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,

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

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