No future for DB2

[rkusenet]

This article is very bleak about future of DB2. How credible is the
author. http://www.eweek.com/article2/0,1895,1839681,00.asp

[DA Morgan]

rkusenet wrote:

I am beginning to wonder whether being a full time DBA is a
dying profession left for old fogies like me.

I think from one standpoint it is. That standpoint being the
traditional DBA job: It is on its last legs. The DBAs and SysAdmins
of the last decade or two are the equivalent of the county sheriff
during the days of the Wild West. A bunch of self-selected cowboys
with a skill set viewed as valuable but untrained, uncertified, and
being driven into obsolecense by changes in the law and changes in
the demands placed upon them by the community at large.

Just as a point-in-time came when it was not enough to be tough and
be good with a gun ... we are rapidly approaching the time when it
will no longer be enough to be as we have been.

Just as a group of physicians formed the American Board of Medical
Specialities (www.abms.org) and just as attorney's formed the American
Bar Association: With equivalent organizations for other professions
such as accountant, engineer, and pharmacist we too will need to
define and certify members of our profession. All joking aside ...
how many people do you know in IT today that can't, given a single
first-normal form table, convert it to 2NF, 3NF, etc.

More on this later and hopefully something about the American College
of Database Professionals: An organization in its formative stages and
modeled after other professional organizations such as www.facs.org.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)

[DA Morgan]

Buck Nuggets wrote:

db2 has less moving parts to worry about

Oracle has one ... RMAN ... how many less does DB2 have?

--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)

[DA Morgan]

Mark A wrote:

"rkusenet" <rk******@hotma il.com> wrote in message
news:3l******** *****@individua l.net...

The project I am working on has been developed by UK folks and we are
customizing it for this Canadian customer. I too got a feeling that those
guys are experts only in SQL Server.
Their concept of a RDBMS seems to be weak. For e.g. in SQL Server
one can create a Foreign Key without creating an index. In fact index
creation on the FKY columns is a separate process. Same in Oracle
and DB2. What the UK folks never realized is that almost all the time
a FKY column is joined with PKY column for Query (otherwise why
would it be a separate table). So creating index on the FKY column
should be automatic when creating the FKY constraint.
When I joined the project, one of the first fire I had to fight in the
testing phase was locking. SQL server use to do table scan when it
can't find matching index and that pretty soon escalated into locking
and deadlock problem.
ISOLATION LEVEL - well most of them don't even know what it is,
let alone it is.

I am beginning to wonder whether being a full time DBA is a
dying profession left for old fogies like me.

The subject of foreign keys and indexes has been discussed in the Oracle
newsgroup recently.

Not all foreign keys need (or should have indexes). Some foreign keys are
merely connected to code tables that are used to make sure a valid value is
used, and they are never joined. The example used in the Oracle thread is
division_code on sales transaction table that relates to a division_code
table with only 3 rows (divisions).

Having an index on the foreign key for division_code would slow down inserts
on the sales transaction table, and would never be used for queries
(cardinality of 3 is too low for a RDBMS to use this index for queries),
except for the extremely unlikely event of someone trying to change or
delete a row in the division_code table.

So creating an index on a foreign key should not be automatic.

A syntax such as the following would solve the problem:

ALTER TABLE tab1
ADD CONSTRAINT fk_tab1_col1
FOREIGN KEY (col1)
REFERENCES tab2(col2)
USING INDEX;

Giving the power to decide whether to index, or not, to the
database professional. It needn't be an all, or nothing, syntax.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)

[DA Morgan]

Mark A wrote:

The result is that many schemas have way too many indexes that will never be
used, and of course invariably there are at least a few indexes missing.

While I have seen my fair share of under and over indexed tables I am
a wondering why this concern about slowing up an insert. Rarely is the
problem with an application's performance related to speed of inserts.
Rather it is the speed to retrieval, SELECT, that is the issue and
the focus on getting the data IN should not take precedence over getting
it back out.

One can only insert a record one time. Likely the record will be queried
many many times thereafter.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)

[Mark A]

"DA Morgan" <da******@psoug .org> wrote in message
news:1122836023 .709609@yasure. ..

Oracle has one ... RMAN ... how many less does DB2 have?

Daniel A. Morgan

I think he is talking about the Oracle control files, which are optionally
backed up. RMAN is a big improvement for backups compared to what was
previously required in Oracle, but amazingly not everyone uses it.

DB2 just has one command, the backup command. All of the control files
needed to restore a database are included. If doing an online backup, you
can also include the log files that were active during the online backup in
the backup image.

[DA Morgan]

Mark A wrote:

"DA Morgan" <da******@psoug .org> wrote in message
news:1122836023 .709609@yasure. ..

Oracle has one ... RMAN ... how many less does DB2 have?

Daniel A. Morgan

I think he is talking about the Oracle control files, which are optionally
backed up. RMAN is a big improvement for backups compared to what was
previously required in Oracle, but amazingly not everyone uses it.

DB2 just has one command, the backup command. All of the control files
needed to restore a database are included. If doing an online backup, you
can also include the log files that were active during the online backup in
the backup image.

I think he is talking about dinosaurs that still use command line
scripts to back up Oracle and thus have to concern themselves with
control files, log files, data files, temp files, etc. But lets
acknowledge that these DBAs are dinosaurs using techniques no longer
reflecting best practice in the product.

Sole exception from my comment ... those poor souls precluded from
upgrading to 9i or above by management short-sightedness.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.wash ington.edu
(replace x with u to respond)

[Mark A]

"DA Morgan" <da******@psoug .org> wrote in message
news:1122836433 .950130@yasure. ..

While I have seen my fair share of under and over indexed tables I am
a wondering why this concern about slowing up an insert. Rarely is the
problem with an application's performance related to speed of inserts.
Rather it is the speed to retrieval, SELECT, that is the issue and
the focus on getting the data IN should not take precedence over getting
it back out.

One can only insert a record one time. Likely the record will be queried
many many times thereafter.
--
Daniel A. Morgan

When a row is inserted into a table (for example a sales_transacti on table)
then the database must add the table row and add a row to each index.
Typically, it takes more time to add the index row than the data row in a
b-tree index because it must be stored in exact order in the index, and if
the index block is full, a block split occurs, and the non-leaf blocks need
to be updated.

With a low cardinality column like division_code (I assumed there were only
3 valid divisions), an single column index on division_code would not be
used by a query (unless the entire table happened to be in physical sequence
by division_code, or the sales_transacti on table had an extremely large row
length). Typically, there are many of these foreign key relationships to
parent tables, so we are not talking about just one additional index.

If we were talking about the department_code in the employee table, it would
not be much of a problem because I don't know of any companies adding so
many employees to their employee table to make a difference. But for a
sales_transacti on table, where rows are inserted at a high volume, it
certainly could make a difference, especially with multiple unnecessary
indexes..

But my philosophy is, regardless of the size of the table, that if an index
will not be used (assuming that no one is going to do delete cascade or
update on the parent division_code table), then whey have it?

Unfortunately, most "DBA's" don't understand the nature of the application
well enough, and they don't understand enough about how optimizers work, to
make these decisions on a case by case basis. Many DBA's are looking for a
single rule they can follow in every circumstance. IMO, these people are not
real DBA's, and should consider becoming a UNIX/Linux Administrator.

[Mark A]

"DA Morgan" <da******@psoug .org> wrote in message
news:1122837601 .611024@yasure. ..

I think he is talking about dinosaurs that still use command line
scripts to back up Oracle and thus have to concern themselves with
control files, log files, data files, temp files, etc. But lets
acknowledge that these DBAs are dinosaurs using techniques no longer
reflecting best practice in the product.

Sole exception from my comment ... those poor souls precluded from
upgrading to 9i or above by management short-sightedness.
--
Daniel A. Morgan

I have seen 9i and 10g shops that don't use RMAN.

[Captain Pedantic]

"DA Morgan" <da******@psoug .org> wrote in message
news:1122835935 .944779@yasure. ..

Just as a group of physicians formed the American Board of Medical
Specialities (www.abms.org) and just as attorney's formed the American
Bar Association: ... More on this later and hopefully something about the
American College
of Database Professionals: ... orrganizations such as www.facs.org.

Remind me what that "ww" stands for in www. again ...?

[HansF]

On Sun, 31 Jul 2005 13:24:14 -0600, Mark A interested us by writing:

But my philosophy is, regardless of the size of the table, that if an index
will not be used (assuming that no one is going to do delete cascade or
update on the parent division_code table), then whey have it?

This is one statement with which I can unconditionally agree.

It follows with my personal belief that the need of every index should be
periodically re-evaluated, and - if it's existance can not (or can no
longer) be justified, the index should be eliminated.

Thankfully, Oracle provides the ability to monitor index usage.

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard _at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***