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

Moigration from Oracle 10g to Db2 8.2

P: n/a
I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.

Jun 1 '06 #1
Share this Question
Share on Google+
56 Replies


P: n/a
"Ashish Patankar" <as************@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...
I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.


IBM offers migration toolkits at the following site:
http://www-306.ibm.com/software/data/db2/migration/

DB2 is fairly close to ANSI SQL, but Oracle has introduced many proprietary
SQL extensions, so it depends on how many proprietary extensions where used
in your apps as to how difficult it will be. There are work-arounds for
almost all issues, and if you cannot find it documented in the toolkit, you
can ask here in this forum (but check Google Groups archives first).
Jun 1 '06 #2

P: n/a

"Ashish Patankar" <as************@gmail.com> a écrit dans le message de
news: 11**********************@j55g2000cwa.googlegroups. com...
I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.


The biggest pain will be strict datatype handling, date / time differences,
and error handling.
During migration constraints will also cause some difficulties.

Jun 1 '06 #3

P: n/a

"Ashish Patankar" <as************@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...
I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.


Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you may
find more or fewer things not supported or different in DB2.
Jun 3 '06 #4

P: n/a
Bob Jones wrote:
"Ashish Patankar" <as************@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...
I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.

Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you may
find more or fewer things not supported or different in DB2.

Such as?
Jun 4 '06 #5

P: n/a
Curious wrote:
Bob Jones wrote:
"Ashish Patankar" <as************@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...
I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.

Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you
may find more or fewer things not supported or different in DB2.

Such as?


The list is nearly endless. You won't find packages, either built-in or
user defined. You won't a fraction of the instrumentation. You won't
find multiversion read consistency. You won't find a shared-everything
architecture (unless on a mainframe version). You won't find 1/2 of
Oracle's table types or half of Oracle's index types. As I said, the
list is very very long.

Which doesn't mean you need those Oracle features. But if you do you
will not find them in DB2 8.2.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Jun 4 '06 #6

P: n/a
DA Morgan wrote:
Curious wrote:
Bob Jones wrote:
"Ashish Patankar" <as************@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...

I want to migrate my Oracle 10g database to Db2. I want some
documentation for the comparision between these to databases. I also
want to know which features of Oracle 10g are supported by Db2 and
which are not supported.

Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you
may find more or fewer things not supported or different in DB2.

Such as?

The list is nearly endless. You won't find packages, either built-in or
user defined. You won't a fraction of the instrumentation. You won't
find multiversion read consistency. You won't find a shared-everything
architecture (unless on a mainframe version). You won't find 1/2 of
Oracle's table types or half of Oracle's index types. As I said, the
list is very very long.

Which doesn't mean you need those Oracle features. But if you do you
will not find them in DB2 8.2.

Daniel ... you know as well as I do that these are primarily
architectural differences between Oracle and DB2. These differences have
been there for years, and the "migration from Oracle 10g to 8.2" is no
different in this respect than the migration from Oracle 8i or Oracle 9i
to DB2 8.2 (which is what the responder was trying to claim). Would be
the same thing as saying that you won't find a shared-nothing
architecture in Oracle 10g so that would make a migration from DB2 8.2
to Oracle 10g a downgrade. Or as saying that because an Airbus airplane
has electric controls and a Boeing plane has hydraulic controls, flying
on a Boeing plane is a downgrade.

And these "differences" as such aren't anything that would cause anyone
with any balanced knowledge of relational databases to say that moving
from Oracle 10g to DB2 8.2 is a "downgrade". That's utterly ridiculous.

Larry Edelstein
Jun 4 '06 #7

P: n/a
Larry wrote:
DA Morgan wrote:
Curious wrote:
Bob Jones wrote:

"Ashish Patankar" <as************@gmail.com> wrote in message
news:11**********************@j55g2000cwa.googlegr oups.com...

> I want to migrate my Oracle 10g database to Db2. I want some
> documentation for the comparision between these to databases. I also
> want to know which features of Oracle 10g are supported by Db2 and
> which are not supported.
>
Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is
a downgrade and migration. Depending on what 10g features are used,
you may find more or fewer things not supported or different in DB2.

Such as?

The list is nearly endless. You won't find packages, either built-in or
user defined. You won't a fraction of the instrumentation. You won't
find multiversion read consistency. You won't find a shared-everything
architecture (unless on a mainframe version). You won't find 1/2 of
Oracle's table types or half of Oracle's index types. As I said, the
list is very very long.

Which doesn't mean you need those Oracle features. But if you do you
will not find them in DB2 8.2.

Daniel ... you know as well as I do that these are primarily
architectural differences between Oracle and DB2.


MVCC is more than just architecture. And the 848 built-in packages in
10.2.0.2 are far more than fluff. They are tuning metrics. They are
HTTP and TCP. They are on-line rebuild capabilities. They are
partitioning by hash, range, and list. They are resumable transactions.
They are spatial mapping. They are BLOB compression. They are Advanced
Queuing and Streams Replication.

Architectural differences? I thinks not.

Which, as I said, does not mean that someone will need these
capabilities. But if they do ... it is something they should consider.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Jun 5 '06 #8

P: n/a
DA Morgan wrote:
Larry wrote:
DA Morgan wrote:
Curious wrote:

Bob Jones wrote:

> "Ashish Patankar" <as************@gmail.com> wrote in message
> news:11**********************@j55g2000cwa.googlegr oups.com...
>
>> I want to migrate my Oracle 10g database to Db2. I want some
>> documentation for the comparision between these to databases. I also
>> want to know which features of Oracle 10g are supported by Db2 and
>> which are not supported.
>>
>
>
> Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is
> a downgrade and migration. Depending on what 10g features are used,
> you may find more or fewer things not supported or different in DB2.
>
Such as?

The list is nearly endless. You won't find packages, either built-in or
user defined. You won't a fraction of the instrumentation. You won't
find multiversion read consistency. You won't find a shared-everything
architecture (unless on a mainframe version). You won't find 1/2 of
Oracle's table types or half of Oracle's index types. As I said, the
list is very very long.

Which doesn't mean you need those Oracle features. But if you do you
will not find them in DB2 8.2.


Daniel ... you know as well as I do that these are primarily
architectural differences between Oracle and DB2.

MVCC is more than just architecture. And the 848 built-in packages in
10.2.0.2 are far more than fluff. They are tuning metrics. They are
HTTP and TCP. They are on-line rebuild capabilities. They are
partitioning by hash, range, and list. They are resumable transactions.
They are spatial mapping. They are BLOB compression. They are Advanced
Queuing and Streams Replication.

Architectural differences? I thinks not.

Which, as I said, does not mean that someone will need these
capabilities. But if they do ... it is something they should consider.

As usual, Daniel ... now you are saying something else.

Your original statement was "You won't find packages, either built-in or
user defined. You won't a fraction of the instrumentation. You won't
find multiversion read consistency. You won't find a shared-everything
architecture (unless on a mainframe version). You won't find 1/2 of
Oracle's table types or half of Oracle's index types. As I said, the
list is very very long."

The "packages" statement was very non-specific ... and now that you've
managed to cleverly get more specific ... depending upon what you are
talking about, you may or may not find the same or comparable functions
in DB2. The bulk of your statement ... at least the bulk of what was
specific enough to be intelligible ... was devoted to
architectural/implementation differences (shared-everything and
multi-version read consistency), not features and you won't pull the
wool over anyone's eyes here.

We're talking migration here. We're talking that you can often
accomplish the same thing with two different databases ... using similar
features or substitute functionality that one vendor has chosen to
implement somewhat differently.

Larry Edelstein
Jun 5 '06 #9

P: n/a
DA Morgan wrote:
MVCC is more than just architecture. And the 848 built-in packages in
10.2.0.2 are far more than fluff. They are tuning metrics. They are
HTTP and TCP. They are on-line rebuild capabilities. They are
partitioning by hash, range, and list. They are resumable transactions.
They are spatial mapping. They are BLOB compression. They are Advanced
Queuing and Streams Replication.

Oracle implements implements partitioning and online rebuild (of what,
indices?) in packages? *gosh* No wonder they need so many packages.
BLOB compression... Shouldn't they start with the frequently used row
data and online and transparent please?
Or is this needed to compress lobbyfied XML types?
Queuing... Yeah that's new to IBM. I've heard that term before.. It's
not "advanced" of course. It's just this MQ thing that everyone has...
It'll have to do I s'pose
Streams replication. Here's a thought. When I was in Vienna at IM Tech a
few weeks ago learned that customers using Q-Rep on DB2 learn a new
lesson: Replication can be network bound. Now there's a thought they
weren't used to from competitive products.
In short, juts because it has a different name in DB2, doesn't mean it
doesn't exist. And just because it doesn't use fancy adjectives like
"real", "advanced" or "native" doesn't mean it doesn't work as good or
even better.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 5 '06 #10

P: n/a
Serge Rielau wrote:
Oracle implements implements partitioning and online rebuild (of what,
indices?) in packages? *gosh* No wonder they need so many packages.
Oracle does not implement paritioning or online rebuild of indices via
packages.
BLOB compression... Shouldn't they start with the frequently used row
data and online and transparent please?
Oracle supports compression of frequently repeating row data. It's
reasonably online and reasonably transparent (ALTER TABLE/PARTITION command)
Or is this needed to compress lobbyfied XML types?
No - the packaged based compression is just a straight byte compression
algorithm, typically used to compress images/vides etc stored in LOBs in
the database. I don;t think you can use it with XMLTypes.
Queuing... Yeah that's new to IBM. I've heard that term before.. It's
not "advanced" of course. It's just this MQ thing that everyone has...
It'll have to do I s'pose
MQ is a chargeable product. Oracle Advanced Queuing is not (assuming you
already have a DB license). As such it's a choice for Oracle customers
that don't want to use MQ from IBM.
Streams replication. Here's a thought. When I was in Vienna at IM Tech a
few weeks ago learned that customers using Q-Rep on DB2 learn a new
lesson: Replication can be network bound. Now there's a thought they
weren't used to from competitive products.
In short, juts because it has a different name in DB2, doesn't mean it
doesn't exist. And just because it doesn't use fancy adjectives like
"real", "advanced" or "native" doesn't mean it doesn't work as good or
even better.
There are many things that Oracle has that DB2 doesn't. There has been
thread on this in this very forum this week already, around REGEX support.

Cheers
Serge

Jun 5 '06 #11

P: n/a
Mark Townsend wrote:
Serge Rielau wrote:
Oracle implements implements partitioning and online rebuild (of what,
indices?) in packages? *gosh* No wonder they need so many packages.
Oracle does not implement paritioning or online rebuild of indices via
packages.

I know that :-) Daniel is sloppy in is allegations and claims, and he
deserves to be called upon it.
BLOB compression... Shouldn't they start with the frequently used row
data and online and transparent please?

Oracle supports compression of frequently repeating row data. It's
reasonably online and reasonably transparent (ALTER TABLE/PARTITION
command)

Hah! "Reasonably"... is that trademarked? Reasonable like Oracles' XML
support? Can we quote you on this like Mr. Drake:
http://blogs.ittoolbox.com/database/...th-oracle-8722
MQ is a chargeable product. Oracle Advanced Queuing is not (assuming you
already have a DB license). As such it's a choice for Oracle customers
that don't want to use MQ from IBM. Hmm, Oracle tends to charge for features. If they don't charge that
looks suspicious to me... Either it will not be free in the future: Make
you dependent now and charge later, or the feature simply can't compete
on merit. Which one is it?
There are many things that Oracle has that DB2 doesn't. There has been
thread on this in this very forum this week already, around REGEX support.

I don't claim that there aren't. But there are many that are claimed
don't exist in DB2 because they have a different sticker name and there
are also some that Oracle doesn't have that DB2 has (like MDC).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 5 '06 #12

P: n/a

Serge Rielau wrote:
Mark Townsend wrote:
Serge Rielau wrote:
Oracle implements implements partitioning and online rebuild (of what,
indices?) in packages? *gosh* No wonder they need so many packages.
Oracle does not implement paritioning or online rebuild of indices via
packages.

I know that :-) Daniel is sloppy in is allegations and claims, and he
deserves to be called upon it.
BLOB compression... Shouldn't they start with the frequently used row
data and online and transparent please?

Oracle supports compression of frequently repeating row data. It's
reasonably online and reasonably transparent (ALTER TABLE/PARTITION
command)

Hah! "Reasonably"... is that trademarked? Reasonable like Oracles' XML
support? Can we quote you on this like Mr. Drake:
http://blogs.ittoolbox.com/database/...th-oracle-8722
MQ is a chargeable product. Oracle Advanced Queuing is not (assuming you
already have a DB license). As such it's a choice for Oracle customers
that don't want to use MQ from IBM.

Hmm, Oracle tends to charge for features. If they don't charge that
looks suspicious to me... Either it will not be free in the future: Make
you dependent now and charge later, or the feature simply can't compete
on merit. Which one is it?
There are many things that Oracle has that DB2 doesn't. There has been
thread on this in this very forum this week already, around REGEX support.

I don't claim that there aren't. But there are many that are claimed
don't exist in DB2 because they have a different sticker name and there
are also some that Oracle doesn't have that DB2 has (like MDC).


because they have a different sticker name


Absolutely.

Also, the approach is different. Coming from Oracle myself, Oracle has
a very specific style (it tries to be "correct"), DB2 just wants to get
it done the way people want it (or according to the standard).

Personally, i prefer Oracle, it just makes sense to me. But, as you
mentioned, pricing can be a huge deterent.

B.

Jun 5 '06 #13

P: n/a
Oracle implements implements partitioning and online rebuild (of
what, indices?) in packages? *gosh* No wonder they need so many
packages.
Oracle does not implement paritioning or online rebuild of indices via
packages.

I know that :-) Daniel is sloppy in is allegations and claims, and he
deserves to be called upon it.


You reasonably called him on it. But you also inreasonably responded
with allegations of your own. Pot. Kettle. Black
Oracle supports compression of frequently repeating row data. It's
reasonably online and reasonably transparent (ALTER TABLE/PARTITION
command)

Hah! "Reasonably"... is that trademarked?


Reasonably in that it works best when the unit of data to be compressed
is reasonably large. Adding a single new row to a compressed table or
partition is not too efficient, especially if the new row introduces new
data values. Adding 1 million rows reasonably is. This tends to fit the
usage case reasonably well. Because I am a reasonable person I will give
you that the IBM algorithm looks reasonably cute.
Reasonable like Oracles' XML support? Can we quote you on this like Mr. Drake:
http://blogs.ittoolbox.com/database/...th-oracle-8722
We looked at Viper to see what all the fuss was about. Couldn't find a
reason. The Mark Drake quote was unreasonably taken out of context.
MQ is a chargeable product. Oracle Advanced Queuing is not (assuming
you already have a DB license). As such it's a choice for Oracle
customers that don't want to use MQ from IBM. Hmm, Oracle tends to charge for features. If they don't charge that
looks suspicious to me... Either it will not be free in the future: Make
you dependent now and charge later, or the feature simply can't compete
on merit. Which one is it?


Neither. Message Queuing between databases is a reasonably useful
capability, and often requested by reasonable customers. Not every one
has MQ or other third party messaging queuing systems. So we built it
into the database many years ago - even before MQ was available on Unix,
I believe. I would hazard a guess to say that Oracle's queuing is
actually more widely used than MQ. I can come up with a reasonable SWAG
of how many companies use it. How many companies use MQ ?
There are many things that Oracle has that DB2 doesn't. There has been
thread on this in this very forum this week already, around REGEX
support.

I don't claim that there aren't. But there are many that are claimed
don't exist in DB2 because they have a different sticker name and there
are also some that Oracle doesn't have that DB2 has (like MDC).


What in Daniels list does exist in DB2 under a different sticker name ?
I do have a complete list of what I think Oracle has that DB2 LUWser
doesn't, it would be fun to trade notes.
Jun 6 '06 #14

P: n/a
Mark Townsend wrote:
Oracle implements implements partitioning and online rebuild (of
what, indices?) in packages? *gosh* No wonder they need so many
packages.
Oracle does not implement paritioning or online rebuild of indices
via packages.


I know that :-) Daniel is sloppy in is allegations and claims, and he
deserves to be called upon it.

You reasonably called him on it. But you also inreasonably responded
with allegations of your own. Pot. Kettle. Black

Oracle supports compression of frequently repeating row data. It's
reasonably online and reasonably transparent (ALTER TABLE/PARTITION
command)


Hah! "Reasonably"... is that trademarked?

Reasonably in that it works best when the unit of data to be compressed
is reasonably large. Adding a single new row to a compressed table or
partition is not too efficient, especially if the new row introduces new
data values. Adding 1 million rows reasonably is. This tends to fit the
usage case reasonably well. Because I am a reasonable person I will give
you that the IBM algorithm looks reasonably cute.
Reasonable like Oracles' XML support? Can we quote you on this like
Mr. Drake:
http://blogs.ittoolbox.com/database/...th-oracle-8722
We looked at Viper to see what all the fuss was about. Couldn't find a
reason. The Mark Drake quote was unreasonably taken out of context.
MQ is a chargeable product. Oracle Advanced Queuing is not (assuming
you already have a DB license). As such it's a choice for Oracle
customers that don't want to use MQ from IBM.


Hmm, Oracle tends to charge for features. If they don't charge that
looks suspicious to me... Either it will not be free in the future:
Make you dependent now and charge later, or the feature simply can't
compete on merit. Which one is it?

Neither. Message Queuing between databases is a reasonably useful
capability, and often requested by reasonable customers. Not every one
has MQ or other third party messaging queuing systems. So we built it
into the database many years ago - even before MQ was available on Unix,
I believe. I would hazard a guess to say that Oracle's queuing is
actually more widely used than MQ. I can come up with a reasonable SWAG
of how many companies use it. How many companies use MQ ?
There are many things that Oracle has that DB2 doesn't. There has
been thread on this in this very forum this week already, around
REGEX support.


I don't claim that there aren't. But there are many that are claimed
don't exist in DB2 because they have a different sticker name and
there are also some that Oracle doesn't have that DB2 has (like MDC).

What in Daniels list does exist in DB2 under a different sticker name ?
I do have a complete list of what I think Oracle has that DB2 LUWser
doesn't, it would be fun to trade notes.

Mark,

This is NOT the point which you are completely missing. The original
responder called the move from Oracle to DB2 a downgrade ... not a
migration. That was without knowing anything about what the OP's app
looks like. That is a mischaracterization. The same could be said about
moving from certain DB2 configurations to Oracle. Daniel then tried to
interject his usual political agenda and tried to characterize what are
CLEARLY architectural/implementation differences as missing features
that would imply this move was a "downgrade". That's it ... case closed.

Larry Edelstein
Jun 6 '06 #15

P: n/a
> We're talking migration here. We're talking that you can often accomplish
the same thing with two different databases ... using similar features or
substitute functionality that one vendor has chosen to implement somewhat
differently.


Perhaps you can elaborate on how these features/functionalities are
implemented in DB2.

1. Dropping a table column.
2. Creating a bitmap index.
3. Moving an index from one tablespace to another or having indexes for a
table placed in different tablespaces.
4. Changing default tablespace for an index.
5. Flashbacks
6. Loading a table into a tablespace without affecting other objects in the
same tablespace on mainframe.
Jun 6 '06 #16

P: n/a
> This is NOT the point which you are completely missing. The original
responder called the move from Oracle to DB2 a downgrade ... not a
migration. That was without knowing anything about what the OP's app looks
like. That is a mischaracterization.
Yes, that's what I would call it. Didn't I specifically say "depending on
what 10g features are used"?
The same could be said about moving from certain DB2 configurations to
Oracle.


No, it could not. Most DBAs with experience with both brands will tell you
otherwise.
DB2 is a few years behind Oracle and mainframe is decades behind UNIX and
Windows.
Jun 6 '06 #17

P: n/a
Bob Jones wrote:
This is NOT the point which you are completely missing. The original
responder called the move from Oracle to DB2 a downgrade ... not a
migration. That was without knowing anything about what the OP's app looks
like. That is a mischaracterization.

Yes, that's what I would call it. Didn't I specifically say "depending on
what 10g features are used"?

Yes you did ... but you also said "Dude, Oracle 8i to DB2 8.2 is a
migration. Oracle 10g to DB2 8.2 is a downgrade and migration.".
Operative phrase "... IS a downgrade and migration". That's a different
claim than saying "depending on ...".
The same could be said about moving from certain DB2 configurations to
Oracle.

No, it could not. Most DBAs with experience with both brands will tell you
otherwise.


Is that why Oracle has such a significant install base in the highly
parallel shared-nothing enterprise data warehouse market?
DB2 is a few years behind Oracle and mainframe is decades behind UNIX and
Windows.

I'd like some of what you're smoking. Once again you're drawing what
sounds like an incontrovertible conclusion when it really depends.

Larry Edelstein
Jun 6 '06 #18

P: n/a
Bob Jones wrote:
We're talking migration here. We're talking that you can often accomplish
the same thing with two different databases ... using similar features or
substitute functionality that one vendor has chosen to implement somewhat
differently.


Perhaps you can elaborate on how these features/functionalities are
implemented in DB2.

1. Dropping a table column.
2. Creating a bitmap index.
3. Moving an index from one tablespace to another or having indexes for a
table placed in different tablespaces.
4. Changing default tablespace for an index.
5. Flashbacks
6. Loading a table into a tablespace without affecting other objects in the
same tablespace on mainframe.

Can't comment on point 6 (mainframe admin question). Of all the other
points the only point that is relevant for migrating an app is point 5.
Flashbacks are cute. If you want to do long term versioning stamp rows,
voila. No harm done. At least that way the app is in conrol when data is
rolled of.
But to get back to stickers. The move in DB2 is to reduce the number of
tablespaces, not to increase it. I must say that I personally don't like
this choice to the nth degree. While one can call it feature rich, one
can also call it complex. Today's users and average admins do not have
the skill it takes on average.
I fail to see the value of separating index and data table spaces in
general.

DROP COLUMN btw is in DB2 Viper. So get the best mileage out of the
deficiency while you can ;-)

DB2 customers don't ask for bitmap indexes, apparently not needed by the
masses and very likely not by the OP either.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 6 '06 #19

P: n/a
>I fail to see the value of separating index and data table spaces in general.

I thought it was so the tablespaces could be a separate physical disks,
allowing for sumultaneous reads of index and data. And that this gave a
speed increase.

I'm very interested in hearing more on this topic. I know very little
about it.

B.

Jun 6 '06 #20

P: n/a
Serge Rielau wrote:

DROP COLUMN btw is in DB2 Viper. So get the best mileage out of the
deficiency while you can ;-)

From what I can see we will still be enjoying the deficiency (albeit in
a different form) for quite awhile longer
Jun 6 '06 #21

P: n/a
Brian Tkatch wrote:
I fail to see the value of separating index and data table spaces in general.


I thought it was so the tablespaces could be a separate physical disks,
allowing for sumultaneous reads of index and data. And that this gave a
speed increase.

I'm very interested in hearing more on this topic. I know very little
about it.

B.

One of the more popular discussion points on the Oracle forum - see
http://groups.google.com/groups?lnk=...espaces+oracle
Jun 6 '06 #22

P: n/a
Brian Tkatch wrote:
I fail to see the value of separating index and data table spaces in general.


I thought it was so the tablespaces could be a separate physical disks,
allowing for sumultaneous reads of index and data. And that this gave a
speed increase.

I'm very interested in hearing more on this topic. I know very little
about it.

The trend is that you don't have much say in the placement of the data
on that SAN anyway.
You create your database with automatic storage (in fact that's the
default in DB2 Viper if I'm not mistaken).
For your tablespaces you say absolutely nothing anymore. Just name and
page size. DB2 will take care of it.
Talking of which. With large RID's tablespace limits are a thing of the
past and there is little incentive for multiple page sizes either.
For a run of the mill application using DB2 V8.2 or DB2 Viper I'd create
the data base with a 16k or even 32k page size form the get go. I would
not worry about creating any extra buffer pools (self tuning memory in
Viper) or table spaces.
Will it be as fast as an expertly tuned DB2? No, but define expert. If
it gets within 90% of maximum you're better off buying memory than
hiring that expert to create a dedicated bufferpool to pin that hot
table in a separate tablespace and eek out and extra teeny% by going DMS
RAW...

Now, the "experts" will complain and cry foul of course, but leave them
to deal with that 40TB warehouse of a credit card company Y and the 100
node DPF system that ensures you get your customized, pre-approved
credit card from Y.
There are those who need experts and those who don't. Most don't.

We have long since given up on writing assembler in the industry outside
of niche areas. Data placement IMHO falls into that category.

Cheers
Serge

PS: Keep your logs on separate disks of course. ;-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 6 '06 #23

P: n/a
Mark Townsend wrote:
Oracle implements implements partitioning and online rebuild (of
what, indices?) in packages? *gosh* No wonder they need so many
packages.

Oracle does not implement paritioning or online rebuild of indices
via packages.

I know that :-) Daniel is sloppy in is allegations and claims, and he
deserves to be called upon it.

You reasonably called him on it. But you also inreasonably responded
with allegations of your own. Pot. Kettle. Black

It's called sarcasm Mark. You know that one well, as long as you serve it.
Now be a good sport and take it.

Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 6 '06 #24

P: n/a
Mark Townsend wrote:
One of the more popular discussion points on the Oracle forum - see
http://groups.google.com/groups?lnk=...espaces+oracle

I actually liked that game:
http://www.amiga.hu/amigos/ancientoy...lemming1_1.jpg

Cheers
Serge

PS: And no, I'm not off topic.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 6 '06 #25

P: n/a

Mark Townsend wrote:
Brian Tkatch wrote:
I fail to see the value of separating index and data table spaces in general.


I thought it was so the tablespaces could be a separate physical disks,
allowing for sumultaneous reads of index and data. And that this gave a
speed increase.

I'm very interested in hearing more on this topic. I know very little
about it.

B.

One of the more popular discussion points on the Oracle forum - see
http://groups.google.com/groups?lnk=...espaces+oracle


I remember it somewhat. But, it's been a few years since i've treaded
there. Got to follow the database i'm told to work with. :)

B.

Jun 6 '06 #26

P: n/a

Serge Rielau wrote:
Brian Tkatch wrote:
I fail to see the value of separating index and data table spaces in general.
I thought it was so the tablespaces could be a separate physical disks,
allowing for sumultaneous reads of index and data. And that this gave a
speed increase.

I'm very interested in hearing more on this topic. I know very little
about it.

The trend is that you don't have much say in the placement of the data
on that SAN anyway.
You create your database with automatic storage (in fact that's the
default in DB2 Viper if I'm not mistaken).
For your tablespaces you say absolutely nothing anymore. Just name and
page size. DB2 will take care of it.
Talking of which. With large RID's tablespace limits are a thing of the
past and there is little incentive for multiple page sizes either.
For a run of the mill application using DB2 V8.2 or DB2 Viper I'd create
the data base with a 16k or even 32k page size form the get go. I would
not worry about creating any extra buffer pools (self tuning memory in
Viper) or table spaces.
Will it be as fast as an expertly tuned DB2? No, but define expert. If
it gets within 90% of maximum you're better off buying memory than
hiring that expert to create a dedicated bufferpool to pin that hot
table in a separate tablespace and eek out and extra teeny% by going DMS
RAW...

Now, the "experts" will complain and cry foul of course, but leave them
to deal with that 40TB warehouse of a credit card company Y and the 100
node DPF system that ensures you get your customized, pre-approved
credit card from Y.
There are those who need experts and those who don't. Most don't.

We have long since given up on writing assembler in the industry outside
of niche areas. Data placement IMHO falls into that category.

Cheers
Serge

PS: Keep your logs on separate disks of course. ;-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Good points, thanx Serge.
We have long since given up on writing assembler in the industry outside
of niche areas. Data placement IMHO falls into that category.


It is called "Assembly" which is "assembled" by the "Assembler".
"Assembler" code would be some form of meta-language that doesn't
exist.

One of my many pet pieves. :)

B.

Jun 6 '06 #27

P: n/a
Ian
Brian Tkatch wrote:

It is called "Assembly" which is "assembled" by the "Assembler".
"Assembler" code would be some form of meta-language that doesn't
exist.

One of my many pet pieves. :)


You mean peeves. :) Couldn't resist.

Jun 6 '06 #28

P: n/a
Ian
Serge Rielau wrote:
Data placement IMHO falls into that category.


This is true, up to a limit. Spending time to try and isolate I/O
from tables/indexes/temp/etc. is one thing. But understanding how
your data is laid out on a SAN is still very relevant.

I have seen small data warehouses absolutely CRAWL (constant 95%
I/O wait) when no one bothered to pay attention to how tablespaces,
filesystems and LUNs map to physical disks in a SAN. And this
happens a LOT.


Jun 6 '06 #29

P: n/a
Ian wrote:
Serge Rielau wrote:
Data placement IMHO falls into that category.


This is true, up to a limit. Spending time to try and isolate I/O
from tables/indexes/temp/etc. is one thing. But understanding how
your data is laid out on a SAN is still very relevant.

I have seen small data warehouses absolutely CRAWL (constant 95%
I/O wait) when no one bothered to pay attention to how tablespaces,
filesystems and LUNs map to physical disks in a SAN. And this
happens a LOT.

The rule of thumb is: strip everything across everything.
If your storage admin dedicates 2 disks to DB2 then there isn't much
help to be had. ;-)

BTW, I got, after posting my "proclamation", a tad scared and consulted
with our DMS (Data management services) folks and got the content confirmed.
I paraphrase wat I was told:
"A good(!) DBA may be able to boost performance by 10% compared to DB2's
built in algorithms, but that same DBA is likely to gain a lot more by
tuning the schema itself (create/drop indices, etc...) rather than
wasting his/her time on data placement."

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 6 '06 #30

P: n/a

"Larry" <La***@nospam.net> wrote in message news:ZI*************@fe08.lga...
Bob Jones wrote:
This is NOT the point which you are completely missing. The original
responder called the move from Oracle to DB2 a downgrade ... not a
migration. That was without knowing anything about what the OP's app
looks like. That is a mischaracterization.

Yes, that's what I would call it. Didn't I specifically say "depending on
what 10g features are used"?

Yes you did ... but you also said "Dude, Oracle 8i to DB2 8.2 is a
migration. Oracle 10g to DB2 8.2 is a downgrade and migration.". Operative
phrase "... IS a downgrade and migration". That's a different claim than
saying "depending on ...".


Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you may
find more or fewer things not supported or different in DB2.

It seems to be quite clear to me but again I am no lawyer.
The same could be said about moving from certain DB2 configurations to
Oracle.

No, it could not. Most DBAs with experience with both brands will tell
you otherwise.


Is that why Oracle has such a significant install base in the highly
parallel shared-nothing enterprise data warehouse market?


I thought that was considered by you as an architectural difference. Not any
more?
DB2 is a few years behind Oracle and mainframe is decades behind UNIX and
Windows.

I'd like some of what you're smoking. Once again you're drawing what
sounds like an incontrovertible conclusion when it really depends.


I have been smoking the flame of my management who found out the project of
migrating to DB2 was a mistake.
Jun 7 '06 #31

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4e*************@individual.net...
Bob Jones wrote:
We're talking migration here. We're talking that you can often
accomplish the same thing with two different databases ... using similar
features or substitute functionality that one vendor has chosen to
implement somewhat differently.
Perhaps you can elaborate on how these features/functionalities are
implemented in DB2.

1. Dropping a table column.
2. Creating a bitmap index.
3. Moving an index from one tablespace to another or having indexes for a
table placed in different tablespaces.
4. Changing default tablespace for an index.
5. Flashbacks
6. Loading a table into a tablespace without affecting other objects in
the same tablespace on mainframe.

Can't comment on point 6 (mainframe admin question). Of all the other
points the only point that is relevant for migrating an app is point 5.


This is not about migration. This is about downgrade.
Flashbacks are cute. If you want to do long term versioning stamp rows,
voila. No harm done. At least that way the app is in conrol when data is
rolled of.
But to get back to stickers. The move in DB2 is to reduce the number of
tablespaces, not to increase it. I must say that I personally don't like
this choice to the nth degree. While one can call it feature rich, one can
also call it complex. Today's users and average admins do not have the
skill it takes on average.
I fail to see the value of separating index and data table spaces in
general.

Tables and indexes often have different storage requirements. Separating
them makes sense. Some DBAs would do it just for readability. The ability to
do 3 and 4 is very basic in Oracle, nothing complex about it.
DROP COLUMN btw is in DB2 Viper. So get the best mileage out of the
deficiency while you can ;-)

This was available back in Oracle 8i.
DB2 customers don't ask for bitmap indexes, apparently not needed by the
masses and very likely not by the OP either.


Hmmm, I wonder if Oracle customers asked for it before it was available?
Jun 7 '06 #32

P: n/a
Bob Jones wrote:
Tables and indexes often have different storage requirements. Separating
them makes sense. Some DBAs would do it just for readability. The ability to
do 3 and 4 is very basic in Oracle, nothing complex about it.

Readability? What's readable about adding additional objects into the
mix? Also keep in mind that todays storage is not as stupid as it used
to. What was right and proper 10 years ago may be obsolete today and
harmful tomorrow.
If there is nothing complex about laying out your indexes, tablespaces
etc, etc. why is it such a popular topic in the Oracle forum as Mark
points out? Should be nothing to talk about, eh?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 7 '06 #33

P: n/a
Nice flame war...

Bob Jones wrote:
Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you may
find more or fewer things not supported or different in DB2.


Then you can also say: DB2 V5.1 to Oracle 10g is a downgrade and migration -
depending on which DB2 features you were using.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jun 7 '06 #34

P: n/a
Ian
Serge Rielau wrote:
Ian wrote:
Serge Rielau wrote:
Data placement IMHO falls into that category.
This is true, up to a limit. Spending time to try and isolate I/O
from tables/indexes/temp/etc. is one thing. But understanding how
your data is laid out on a SAN is still very relevant.

I have seen small data warehouses absolutely CRAWL (constant 95%
I/O wait) when no one bothered to pay attention to how tablespaces,
filesystems and LUNs map to physical disks in a SAN. And this
happens a LOT.

The rule of thumb is: strip everything across everything.


Meaning, 'Stripe all *DB2* data across all storage paths'. I have no
problems with this.
If your storage admin dedicates 2 disks to DB2 then there isn't much
help to be had. ;-)
That's true. But my point is that there needs to be some understanding
between the different people.

If you have 2 large SAN volumes (i.e. LUNs), but the UNIX admin
creates 30 file systems and then the DBA (somewhat understandably)
spreads every tablespaces containers across all 30 file systems, you
will be in for problems.

BTW, I got, after posting my "proclamation", a tad scared and consulted
with our DMS (Data management services) folks and got the content
confirmed.
I paraphrase wat I was told:
"A good(!) DBA may be able to boost performance by 10% compared to DB2's
built in algorithms, but that same DBA is likely to gain a lot more by
tuning the schema itself (create/drop indices, etc...) rather than
wasting his/her time on data placement."


I'm certainly not disputing this. I'm just trying to draw a distinction
between data placement within the database and container placement on
logical -> physical devices.



Jun 7 '06 #35

P: n/a

Ian wrote:
Brian Tkatch wrote:

It is called "Assembly" which is "assembled" by the "Assembler".
"Assembler" code would be some form of meta-language that doesn't
exist.

One of my many pet pieves. :)


You mean peeves. :) Couldn't resist.


Heh. I knew i shouldn't have typed that. :)

B.

Jun 7 '06 #36

P: n/a

Serge Rielau wrote:
Ian wrote:
Serge Rielau wrote:
Data placement IMHO falls into that category.
This is true, up to a limit. Spending time to try and isolate I/O
from tables/indexes/temp/etc. is one thing. But understanding how
your data is laid out on a SAN is still very relevant.

I have seen small data warehouses absolutely CRAWL (constant 95%
I/O wait) when no one bothered to pay attention to how tablespaces,
filesystems and LUNs map to physical disks in a SAN. And this
happens a LOT.

The rule of thumb is: strip everything across everything.
If your storage admin dedicates 2 disks to DB2 then there isn't much
help to be had. ;-)

BTW, I got, after posting my "proclamation", a tad scared and consulted
with our DMS (Data management services) folks and got the content confirmed.
I paraphrase wat I was told:
"A good(!) DBA may be able to boost performance by 10% compared to DB2's
built in algorithms, but that same DBA is likely to gain a lot more by
tuning the schema itself (create/drop indices, etc...) rather than
wasting his/her time on data placement."


And doing both would be better and better.

B.


Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 7 '06 #37

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4e*************@individual.net...
Bob Jones wrote:
Tables and indexes often have different storage requirements. Separating
them makes sense. Some DBAs would do it just for readability. The ability
to do 3 and 4 is very basic in Oracle, nothing complex about it.
Readability? What's readable about adding additional objects into the mix?
That's what I would like to ask. What is so readable about mixing tables and
indexes in one tablespace?
Also keep in mind that todays storage is not as stupid as it used to. What
was right and proper 10 years ago may be obsolete today and harmful
tomorrow.
I am talking about storage configurations for tablespaces, not disk storage.
If there is nothing complex about laying out your indexes, tablespaces
etc, etc. why is it such a popular topic in the Oracle forum as Mark
points out? Should be nothing to talk about, eh?


They are talking about performance issues, not simply the ability to place
indexes in different tablespaces or change default space for indexes.
If there is no point to separate tables and indexes, why even DB2 let you
specify index spaces?
Jun 8 '06 #38

P: n/a

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:e6**********@lc03.rz.uni-jena.de...
Nice flame war...

Bob Jones wrote:
Dude, Oracle 8i to DB2 8.2 is a migration. Oracle 10g to DB2 8.2 is a
downgrade and migration. Depending on what 10g features are used, you may
find more or fewer things not supported or different in DB2.


Then you can also say: DB2 V5.1 to Oracle 10g is a downgrade and
migration -
depending on which DB2 features you were using.


You mean better features or just features? I would really like to hear an
example.
Jun 8 '06 #39

P: n/a
Bob Jones wrote:
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4e*************@individual.net...
Bob Jones wrote:
Tables and indexes often have different storage requirements. Separating
them makes sense. Some DBAs would do it just for readability. The ability
to do 3 and 4 is very basic in Oracle, nothing complex about it.
Readability? What's readable about adding additional objects into the mix?


That's what I would like to ask. What is so readable about mixing tables and
indexes in one tablespace?

I for the life of it don't get ho wthe word "readable" applies to any of
that. Are we talking DDl script here or what? Some GUI display? They are talking about performance issues, not simply the ability to place indexes in different tablespaces or change default space for indexes.
If there is no point to separate tables and indexes, why even DB2 let you
specify index spaces?

Prior to DB2 Viper there is a limit to the size of a tablespace and thus
an upper limit to the size of a given table.
Having to share the space with the indexes further reduces the available
space.
Also, as I said earlier what was right 10 years ago is not that relevant
anymore.
The direction in DB2 is to reduce complexity.
Keep in mind that the dominating cost nowadays is not hardware or
software. It is labor cost. If the DBA has to spend time worrying about
where to place DB objects that's expensive.
If other vendors believe in knobs to get that last percent, that's not
bad, but it doesn't make our choice towards simplicity wrong either.
Keeps the market interesting if anything.

I recall "call for papers" to conferences with three primary topics:
Performance, Performance, Performance

I don't agree that performance is everything.
We don't drive race cars to work either. Not even on the Autobahn.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #40

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4e*************@individual.net...
Bob Jones wrote:
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4e*************@individual.net...
Bob Jones wrote:
Tables and indexes often have different storage requirements.
Separating them makes sense. Some DBAs would do it just for
readability. The ability to do 3 and 4 is very basic in Oracle, nothing
complex about it.
Readability? What's readable about adding additional objects into the
mix?


That's what I would like to ask. What is so readable about mixing tables
and indexes in one tablespace?

I for the life of it don't get ho wthe word "readable" applies to any of
that. Are we talking DDl script here or what? Some GUI display?


Both. If you look at a list of tablespaces, it is easy to tell which
contains what and how much space indexes take up. It is basically the same
reason for splitting up applications.
> They are talking about performance issues, not simply the ability to

place
indexes in different tablespaces or change default space for indexes.
If there is no point to separate tables and indexes, why even DB2 let you
specify index spaces?

Prior to DB2 Viper there is a limit to the size of a tablespace and thus
an upper limit to the size of a given table.
Having to share the space with the indexes further reduces the available
space.
Also, as I said earlier what was right 10 years ago is not that relevant
anymore.
So you think the only reason to place indexes in separate space is to make
more room for tables.
The direction in DB2 is to reduce complexity.
Keep in mind that the dominating cost nowadays is not hardware or
software. It is labor cost. If the DBA has to spend time worrying about
where to place DB objects that's expensive.
If other vendors believe in knobs to get that last percent, that's not
bad, but it doesn't make our choice towards simplicity wrong either.
Keeps the market interesting if anything.

There is nothing wrong about simplicity, just not by throwing everything
together.
I recall "call for papers" to conferences with three primary topics:
Performance, Performance, Performance

I don't agree that performance is everything.
We don't drive race cars to work either. Not even on the Autobahn.


I agree but performance was never the topic here.
Jun 8 '06 #41

P: n/a
"Bob Jones" <em***@me.not> wrote in message
news:PX*******************@newssvr13.news.prodigy. com...

So you think the only reason to place indexes in separate space is to make
more room for tables.


One reason to put indexes in a different tablespace than the table data is
to be able to assign them to a different bufferpool. This is more important
for large tables which where you would not want them in the same bufferpool
as the indexes and small tables.
Jun 8 '06 #42

P: n/a
Mark A wrote:
"Bob Jones" <em***@me.not> wrote in message
news:PX*******************@newssvr13.news.prodigy. com...
So you think the only reason to place indexes in separate space is to make
more room for tables.


One reason to put indexes in a different tablespace than the table data is
to be able to assign them to a different bufferpool. This is more important
for large tables which where you would not want them in the same bufferpool
as the indexes and small tables.

That would be a performance reason (and falls within the <10% gain I
discussed earlier).
Now, I assumed Bob was after performance here, but apparently he's not.
Space reporting was one mentioned other reason, what else?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #43

P: n/a
Serge Rielau je napisao:
Bob Jones wrote:
We're talking migration here. We're talking that you can often accomplish
the same thing with two different databases ... using similar features or
substitute functionality that one vendor has chosen to implement somewhat
differently.


Perhaps you can elaborate on how these features/functionalities are
implemented in DB2.

1. Dropping a table column.
It is a shame that DB2 is not (yet) capable to do this. It was large
disappointment for us, coming from Informix world. We need it NOW
(being in the development phase, developers demand such actions on a
weekly basis). What took you so long to implement this?
2. Creating a bitmap index.
I think we just might make use of this feature if we had it.
3. Moving an index from one tablespace to another or having indexes for a
table placed in different tablespaces.
4. Changing default tablespace for an index.
Definitely, I would like to see these features in UDB.
5. Flashbacks
6. Loading a table into a tablespace without affecting other objects in the
same tablespace on mainframe.

Can't comment on point 6 (mainframe admin question). Of all the other
points the only point that is relevant for migrating an app is point 5.
Flashbacks are cute. If you want to do long term versioning stamp rows,
voila. No harm done. At least that way the app is in conrol when data is
rolled of.
But to get back to stickers. The move in DB2 is to reduce the number of
tablespaces, not to increase it. I must say that I personally don't like
this choice to the nth degree. While one can call it feature rich, one
can also call it complex. Today's users and average admins do not have
the skill it takes on average.
I fail to see the value of separating index and data table spaces in
general.

DROP COLUMN btw is in DB2 Viper. So get the best mileage out of the
deficiency while you can ;-)

DB2 customers don't ask for bitmap indexes, apparently not needed by the
masses and very likely not by the OP either.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


IMHO, it seems that DB2 UDB development is targeted in such a way that
the product should be attractive to corporate bean counters rather than
to DBAs and developers. In long term, I believe that database product
should be marketed to IT people and tailored to their needs primarily.
Market trends will show which the right way is.

The recent Gartner and IDC reports on global market share of RDBMS
shows that Oracle holds >40% of total market share, while IBM is
<25%. (I am interested to hear which part of that percentage belongs to
Informix). If I remember well, after Informix takeover in 2001, IBM
held larger share that Oracle (according to the info I got from our
Informix/IBM representatives). Being long-term Informix and IBM
customer, I wish IBM to increase its market share, among other means,
by implementing features that IT people want and need. It is a good
thing to offer automation for administration, but we need means for
setting things up manually at occasions. Informix did offer such means,
and I suppose Oracle does also.

I speak for myself only, and not for my employer. English is not my
native language, accordingly excuse my language mistakes.

Darko Krstic
Chief DBA
Nova banka ad
B&H

Jun 8 '06 #44

P: n/a
darko wrote:
Serge Rielau je napisao:
Bob Jones wrote:
We're talking migration here. We're talking that you can often accomplish
the same thing with two different databases ... using similar features or
substitute functionality that one vendor has chosen to implement somewhat
differently.
Perhaps you can elaborate on how these features/functionalities are
implemented in DB2.

1. Dropping a table column.
It is a shame that DB2 is not (yet) capable to do this. It was large
disappointment for us, coming from Informix world. We need it NOW
(being in the development phase, developers demand such actions on a
weekly basis). What took you so long to implement this? A wise man once said products at IBM don't get delivered, they escape.
Schema evolution is one such case. July 28th is the date.
Since you are in the development phase, have you looked at the ALTER
TABLE in Control Center? It does DROP COLUMN today.
2. Creating a bitmap index. I think we just might make use of this feature if we had it.

Might?
3. Moving an index from one tablespace to another or having indexes for a
table placed in different tablespaces.
4. Changing default tablespace for an index.

Definitely, I would like to see these features in UDB.

.... because you are used to them in IDS?
BTW. In DB2 9 you can create a ranged partitioned table with one open
range and place your indexes as you wish.
IMHO, it seems that DB2 UDB development is targeted in such a way that
the product should be attractive to corporate bean counters rather than
to DBAs and developers. In long term, I believe that database product
should be marketed to IT people and tailored to their needs primarily.
Market trends will show which the right way is.

The recent Gartner and IDC reports on global market share of RDBMS
shows that Oracle holds >40% of total market share, while IBM is
<25%. (I am interested to hear which part of that percentage belongs to
Informix). If I remember well, after Informix takeover in 2001, IBM
held larger share that Oracle (according to the info I got from our
Informix/IBM representatives). You remember the Gartner numbers, not IDCs. In Gartner's counting (new
licence revenue - at least in the past) Oracle and IBM RDBMS were and
still are neck to neck. At the time of purchase Informix boosted that
number for IBM by some 3% only (Gartner actually split Informix out in
the first year). I'm sure you can find the actual number somewhere.
The IDC numbers always (at least since I monitor them) put Oracle higher
than IBM RDBMS.
Being long-term Informix and IBM
customer, I wish IBM to increase its market share, among other means,
by implementing features that IT people want and need. It is a good
thing to offer automation for administration, but we need means for
setting things up manually at occasions. Informix did offer such means,
and I suppose Oracle does also. One of the claims to fame for Informix is the number of instances of IDS
a single DBA can administer. For an application vendor or customer this
is a compelling purchase reason.
I visit a fair number of customers and business partners (including some
posting here) and that message is very clear.
Each individual customer may have their own pet-peeve, feature request,
but they all agree that the more the DBMS does itself the better it is.
I speak for myself only, and not for my employer. Who makes the purchase decisions, you or your employer?
If your employer can fire half of your staff (let's make that "redeploy
into value generating tasks") because of his choice of DBMS he will.
A DBA on the other hand has little interest of making him/herself
obsolete, learning a new product or completely new skill.
English is not my
native language, accordingly excuse my language mistakes.

Mine neither, we shall excuse each other ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #45

P: n/a
You remember the Gartner numbers, not IDCs. In Gartner's counting (new
licence revenue - at least in the past) Oracle and IBM RDBMS were and
still are neck to neck.
This years Gartner numbers do not show IBM and Oracle neck and neck at all.

IBM Oracle
2005 22% 48.6%

The IDC numbers always (at least since I monitor them) put Oracle higher
than IBM RDBMS.


As of last year the IDC numbers had Oracle and IBM as follows

IBM Oracle
2003 31.8% 40.4%
2004 30.6% 40.8%

This year IDC restated those numbers (based on advice from IBM) as follows

IBM Oracle
2003 23.5% 44.5%
2004 22.0% 45.0%
Jun 8 '06 #46

P: n/a
<snip>
IMHO, it seems that DB2 UDB development is targeted in such a way that
the product should be attractive to corporate bean counters rather than
to DBAs and developers. In long term, I believe that database product
should be marketed to IT people and tailored to their needs primarily.
Market trends will show which the right way is.
<snip> Who makes the purchase decisions, you or your employer?
If your employer can fire half of your staff (let's make that "redeploy
into value generating tasks") because of his choice of DBMS he will.
A DBA on the other hand has little interest of making him/herself
obsolete, learning a new product or completely new skill.


The way i see it is:

Larger companies, where short-tem gains make management look good, and
people are always looking to be employed, money is the main
choice-maker, and developers have to deal with it or start looking for
another job.

The smaller companies, however, must look at more long-term goals, and
in general, the developers get asked what they need. They have a strong
influence on purchase choice, basing their reccomendations on
functionality, usually including reliability but almost always ignoring
the cost to the business.

Big companies like IBM make their money from big deals with big
companies, so, they focus on cost.

B.

Jun 8 '06 #47

P: n/a
Mark Townsend wrote:
You remember the Gartner numbers, not IDCs. In Gartner's counting (new
licence revenue - at least in the past) Oracle and IBM RDBMS were and
still are neck to neck.


Mark I'm surprised at you...you of all people should know better. You
know that in this years results both Gartner and IDC are now using both
support revenue and new license revenue. And we all see from the SEC
filings that Oracle is making more money now from support revenue than
new licenses.

In an interview with the San Jose Mercury News, Gartner's Colleen
Graham is quoted as saying "In terms of new licenses, it was a
neck-and-neck race between Oracle and IBM." .

In another Gartner study from March of this year they were projecting
the future (where as the above one looks at the past, i.e. 2005)
entitled "Gartner Study on DBMS Identifies Spending and Deployment
Trends" they found that more customers are
considering IBM databases this year, almost 50% more than Oracle and
about 35% more than Microsoft. They go on to say "The overall numbers
so strongly in favour of DB2 indicate a pending increase in IBM's
market share for DBMS during the next few years."

Their words not mine.

Chris Eaton
IBM

P.S. I think you guys should start a comp.databases.flame-wars so that
people that ask questions can get concise answers and not have to deal
with the banter (although I'm sure it's fun, I don't think it helped
the person that originally posted the question)

Ok flame me know if you like.

Jun 8 '06 #48

P: n/a
Serge Rielau wrote:
darko wrote:
Serge Rielau je napisao:
Bob Jones wrote:
> We're talking migration here. We're talking that you can often accomplish
> the same thing with two different databases ... using similar features or
> substitute functionality that one vendor has chosen to implement somewhat
> differently.
Perhaps you can elaborate on how these features/functionalities are
implemented in DB2.

1. Dropping a table column.


It is a shame that DB2 is not (yet) capable to do this. It was large
disappointment for us, coming from Informix world. We need it NOW
(being in the development phase, developers demand such actions on a
weekly basis). What took you so long to implement this?

A wise man once said products at IBM don't get delivered, they escape.
Schema evolution is one such case. July 28th is the date.
Since you are in the development phase, have you looked at the ALTER
TABLE in Control Center? It does DROP COLUMN today.


Correct me if I am wrong:
It does by a sequence of activities that include unloading data from
the table, probably saving definitions of constraints, views etc.
related to that table, dropping (or renaming) it, recreating without
the column that was supposed to be dropped, restoring constraints,
views etc, reloading the data into the table. It is much easier that
way (through CC) than it would be manually, but it is painful when the
table is populated (we do have some small part of functionality in
production :). And CC, among other things being a Java app, is not the
speed (or low-resource-consumption) champion, and so far it gave us a
share of troubles. Very often my colleagues lament for lack of a tool
like ServerStudio for Informix, which for a decent price provides a
lot. Can someone suggest us an analogous tool for UDB?
2. Creating a bitmap index.

I think we just might make use of this feature if we had it.

Might?
3. Moving an index from one tablespace to another or having indexes for a
table placed in different tablespaces.
4. Changing default tablespace for an index.

Definitely, I would like to see these features in UDB.

... because you are used to them in IDS?
BTW. In DB2 9 you can create a ranged partitioned table with one open
range and place your indexes as you wish.


It seems to me that that feature is coming from Informix. Welcome!
IMHO, it seems that DB2 UDB development is targeted in such a way that
the product should be attractive to corporate bean counters rather than
to DBAs and developers. In long term, I believe that database product
should be marketed to IT people and tailored to their needs primarily.
Market trends will show which the right way is.

The recent Gartner and IDC reports on global market share of RDBMS
shows that Oracle holds >40% of total market share, while IBM is
<25%. (I am interested to hear which part of that percentage belongs to
Informix). If I remember well, after Informix takeover in 2001, IBM
held larger share that Oracle (according to the info I got from our
Informix/IBM representatives).

You remember the Gartner numbers, not IDCs. In Gartner's counting (new
licence revenue - at least in the past) Oracle and IBM RDBMS were and
still are neck to neck. At the time of purchase Informix boosted that
number for IBM by some 3% only (Gartner actually split Informix out in
the first year). I'm sure you can find the actual number somewhere.
The IDC numbers always (at least since I monitor them) put Oracle higher
than IBM RDBMS.
Being long-term Informix and IBM
customer, I wish IBM to increase its market share, among other means,
by implementing features that IT people want and need. It is a good
thing to offer automation for administration, but we need means for
setting things up manually at occasions. Informix did offer such means,
and I suppose Oracle does also.

One of the claims to fame for Informix is the number of instances of IDS
a single DBA can administer. For an application vendor or customer this
is a compelling purchase reason.
I visit a fair number of customers and business partners (including some
posting here) and that message is very clear.
Each individual customer may have their own pet-peeve, feature request,
but they all agree that the more the DBMS does itself the better it is.
I speak for myself only, and not for my employer.

Who makes the purchase decisions, you or your employer?
If your employer can fire half of your staff (let's make that "redeploy
into value generating tasks") because of his choice of DBMS he will.
A DBA on the other hand has little interest of making him/herself
obsolete, learning a new product or completely new skill.


In our particular case, purchase decision, when related to IT, is made
according to recommendation from the IT. In fact, I was involved in
making decision to pick DB2 UDB for the system under development (at
the moment when Informix's future did not seem very promising regarding
improvements; the support was never an issue). We were all very
optimistic regarding UDB's future, expecting it will unify all the best
from both DB2 and IDS. We are not pessimists now, but are still waiting
for some promises to be delivered (like onstat commands...).

In my country, largest companies are in the size range of western SMBs,
so it's quite possible that I don't have a clue regarding the way of
doing big bucks business. But I believe that we are not the only one
whose management listens to IT when making decisions that relate to IT.
With IDS, I was able alone to administer more than a dozen of servers
in a low-capacity lines WAN, using very modest hardware, not very
efficient apps and database schemas, ancient OS (SCO) and achieving
uptimes up to >1 year. We have yet to prove that we will lessen the
labor needed to maintain UDB (I hope we will).

I have no worry that our DBA and system programming group will be
reduced, quite the contrary. It seems that data volumes, as well as
types of information that business needs, are increasing faster than
hardware and (self-managed) software are following. It is good that
there is automation available. It's just that we could make our clients
(which are other departments, that are using our services and makes
revenue to the company) happier if we were able to tune some things
manually, instead of delivering slower response or asking for larger
hammer :) (more powerful server). In a way it relates to points 3 and 4
in a list (regarding indexes, tablespaces and two smoking barrels :)

It seems to me that we are now well off-topic the original theme of the
thread. I take my share of responsibility for that.
English is not my
native language, accordingly excuse my language mistakes.

Mine neither, we shall excuse each other ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


BTW, I am grateful for many answers and advices that you have shared
with this newsgroup, Serge. Your posts are important help for our trip
to DB2 UDB land.
Darko Krstic
Chief DBA
Nova banka ad
B&H

Disclaimer:
I speak for myself only, and not for my employer.
English is not my native language, accordingly excuse my language
mistakes.

Jun 8 '06 #49

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:4e*************@individual.net...
Mark A wrote:
"Bob Jones" <em***@me.not> wrote in message
news:PX*******************@newssvr13.news.prodigy. com...
So you think the only reason to place indexes in separate space is to
make more room for tables.


One reason to put indexes in a different tablespace than the table data
is to be able to assign them to a different bufferpool. This is more
important for large tables which where you would not want them in the
same bufferpool as the indexes and small tables.

That would be a performance reason (and falls within the <10% gain I
discussed earlier).
Now, I assumed Bob was after performance here, but apparently he's not.
Space reporting was one mentioned other reason, what else?


What other reasons do you need? When it comes time for maintenance, you will
thank yourself for logically separating the objects.
Jun 9 '06 #50

56 Replies

This discussion thread is closed

Replies have been disabled for this discussion.