Dear All
Db2 version: 8.1
OS: Windows

I have 2 questions:
1) What is the optimizer which db2 uses, rule based or cost based? If any
one can clear out the difference between the two it will be a great help.

2) I have an Oracle database which I have ported to db2. Now suppose after
2 months oracle database has fresh data and I need to ensure that the data
in db2 database is in sync with this. How can I do this?

Any help will be greatly appreciated

Warm regards,

Praveen Pandey

"Praveen_db2" <er_praveen278@hotmail.com> wrote in message
news:ef2d4644438b83854fb0980f35a8f8a2@localhost.ta lkaboutdatabases.com...
> Dear All
> Db2 version: 8.1
> OS: Windows
>
> I have 2 questions:
> 1) What is the optimizer which db2 uses, rule based or cost based? If any
> one can clear out the difference between the two it will be a great help.
>
DB2 uses a cost based optimizer. That means that DB2 uses statistical data
about the tables, indexes, columns, tablespaces, etc that are captured when
runstats command is run, and information about the configuration (including
disk characteristics speed defined for the tablespace), number of CPU's CPU
speed, size of bufferpools (memory), etc, and then calculates a "relative"
cost estimate of various access path alternatives, and chooses the lowest
cost access path.

Actually, DB2 has a parameter called optimization level that determines how
long DB2 will spend trying calculate all of the various options. The value
ranges from 1-9, and 5 is the default (some levels are not currently
defined, such as 6 or 8). Optimization level 5 is usually good for most SQL
statements, but some very complex decision support queries can benefit from
optimization level of 7.

There are variations on the "rule based" method, but basically it attempts
to figure out the access path based on certain heuristics, and sometimes
explicit hints or instructions by the programmer to use a particular access
path.

I doubt that any database is 100% cost based nor 100 rule based, just like
there are no pure relational databases. But it is safe to say that DB2 is
probably relies more on cost based optimization than other database
products.

Thanx a lot Mark.
Can any one tell me about the second question. I guess this can be done by
using some third party tools.

Praveen

Thanx a lot Mark.
Can any one tell me about the second question. I guess this can be done by
using some third party tools.

Praveen

Praveen_db2 wrote:
> Thanx a lot Mark.
> Can any one tell me about the second question. I guess this can be done by
> using some third party tools.
>
> Praveen
>

This might be bold, and perhaps even heathen. But you could try creating
an external C stored procedure which interfaces with Oracle to sync the
databases.
I'm not very familiair with the Oracle DB, but if it has an export file
format the is compatible with any of the DB2s' import files you could
try that way to sync. Both ways are a bit uncommon, but I would explore
those (but I do many things others wouldn't).

-R-

You can set up DB2 replication with Oracle as a source and DB2 as a target.

Larry Edelstein

Praveen_db2 wrote:
> Thanx a lot Mark.
> Can any one tell me about the second question. I guess this can be done by
> using some third party tools.
>
> Praveen
>