Ahh. Sybase and DB2. Oracle is a much different beast. If you are doing
the extract, transform,load then the reporting database is probably a data
wharehouse. In which case the current db isn't going to cut it(due to the
same schema). Still replication is the way to go. (If it has to be "real
time" as opposed to a few seconds lag, then sycronus replication instead of
asynchronus. Then on the other end use your business logic to do your
transform (if a view can't cut it). Just use the dbms_job functionality in
the other database to do the transformations. You could have it kick off
every 5 or 10 seconds. (or if it must be "real time" or instantly, then in
a trigger on update, insert, delete in the table schedule a dbms_job to run
immediately.)
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"servant" <mjohns1_at_uhc.com> wrote in message
news:avml6j$7k4$1_at_gabriel.uhc.com...
> Part of the problem is that I am new to the project and do not yet
> understand the amount of traffic this database will support. It is
running
> on a 2 processor RS6000 B80 under AIX which isn't the smallest machine but
> isn't the biggest either. Until I get a better understanding, I need to
> trust that the technical architect is accurately describing the need for
> reporting to hit another database. I agree that RDBMS's can handle A LOT
of
> traffic. I am new to Oracle but have seen Sybase and DB2 do some awesome
> things.
>
> As I understand ETL, basically that is what I am talking about. I am
> extracting data from one database, applying business logic to it, and
> loading a separate database. Unfortunately, the reporting requirements
> dictate that I do this in near real-time. Otherwise, I could write a
script
> that kicks off at night and the architecture becomes much cleaner.
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message
> news:LjtT9.6039$%n.1146_at_sccrnsc02...
> > Sybrand,
> > I found in SQLServer I had to create a whole other machine and replicate
> to
> > it just for some frigging reports! The 4 CPU 4 GIG W2K server with 50 -
> 100
> > concurrent (light users) users couldn't handle some basic reporting at
the
> > same time. Too much contention. So this "cheaper" RDBMS SQL Server is
> > really much more expensive than a 2 processor Sun box w/ Oracle (1
> machine,
> > 1 license, 1 backup, less hardware)
> >
> > So perhaps this is where servant is coming from. Servant, let me assure
> you
> > that Oracle is a pretty robust puppy and can certainly handle OLTP and
> some
> > reports on the same box. If you need a Datawharehouse environment the
> > schema is probably going to be much different anyway. In that case you
> are
> > going to do ETL to someother Oracle database anyway.
> >
> > Jim
> >
> >
> >
> >
> > --
> > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> > with family. Remove the negative part, keep the minus sign. You can
> figure
> > it out.
> > "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> > news:77pr1v42q0kef48ad29cacqjvsinbqp6vl_at_4ax.com...
> > > On Thu, 9 Jan 2003 13:06:43 -0600, "servant" <mjohns1_at_uhc.com> wrote:
> > >
> > > >I have been tasked with developing a reporting architecture for an
> > > >application that has been developed over the last few months. I have
> > been
> > > >told there are 2 main concerns: performance (contention) and
complexity
> > of
> > > >the data model. I am thinking of using Oracle's replication to
> maintain
> > a
> > > >(near) real-time replica. That (basically) resolves the contention
> > issue.
> > > >The complexity issue is more difficult.
> > > >
> > > >I am thinking about using 1) streams against the redo logs or 2)
> triggers
> > to
> > > >pull the data from the replica, apply business logic to it, and
> populate
> > a
> > > >"logical" data model on which reports can be generated. I would like
> to
> > use
> > > >the same abstraction layer to go from the physical to the logical
data
> > model
> > > >as the application interface uses (Java objects with EJBs). In other
> > words,
> > > >I need the code inside the streams or triggers to call Java methods
> (ie:
> > > >loadCustomerData(cust_id)) which would get the data and apply any
> > business
> > > >logic and then populate the "logical" database.
> > > >
> > > >In essence, I think I am being asked to create a data warehouse, but
I
> > > >haven't convinced myself of that yet.
> > > >
> > > >Anyway, here is my question: Does anyone have an opinion on which
> method
> > > >(streams or triggers) would tax my replica database more--from a CPU,
> > > >network, and/or contention perspective? Any opinions from other
> > > >perspectives which option is better (maintenance, etc).
> > > >
> > > >
> > >
> > > First of all I would like to question the need of a replica database
> > > for reporting purposes. As for contention, this is simply not an
> > > issue, if the application has been designed properly. Your post has a
> > > distinct smell of finding a 'hardware solution' read 'workaround' for
> > > what is in reality a software design problem.
> > > If you really think you need a hardware solution you could just as
> > > well consider RAC, to have multiple servers running on the same
> > > instance.
> > > As for your option 1) you seem to be trying to re-invent the standby
> > > database technology . One simple advice: don't. You would never get it
> > > work, and provided your organisation has still a bag of $$ floating
> > > arond: get Oracle Enterprise Edition, set up a standby database which
> > > will use the redolog to keep the standby database up-to-date and start
> > > living
> > > As for your option 2) this seems like yet another solution which is
> > > going to be designed in a bike-shed. Replication can be accomplished
> > > by setting up the Advanced Replication option, and designing
> > > 'home-grown' solutions will definitely make you bald sooner and cost
> > > more.
> > >
> > > Regards
> > >
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > To reply remove -verwijderdit from my e-mail address
> >
> >
>
>
Received on Fri Jan 10 2003 - 09:53:22 CST
