Generic/portable LISTEN/NOTIFY, DBMS_ALERT, sp_add

Generic/portable LISTEN/NOTIFY, DBMS_ALERT, sp_add_alert etc...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eoin - 15 Jun 2004 12:49 GMT

Hello,

I'm trying to come up with a design for a system that has to integrate
with a number of different customer databases on different platforms.
Currently our customers are using SQL Server and Oracle databases
only, but there should be no restriction on the actual DB being used
by the client. I plan to use Java and JDBC to address the portability
and connectivity issues, but I have hit a problem trying to implement
the exact functionality I'm looking for in a database independent
manner. I'm quite new to database programming/design, so don't know
what is possible, what is not, if I'm on the right track or not etc...

What I want to achieve is to have a Java application running a thread
that is 'listening' for changes in the database tables (and processing
any change). I have spent quite a bit of time searching the newsgroups
and web to find out how to do this, and have found that Oracle provide
this functionality via a DBMS_ALERT package, Postgres provides a
"LISTEN/NOTIFY" package for this, SQL Server provides a "sp_add_alert"
package (although I'm not entirely sure this is intended for the same
purpose as DBMS_ALERT or LISTEN/NOTIFY). Ideally, I'd like something
that is generic in case a new database is introduced. Is there a way
of doing this? I want to avoid sleep/poll approaches as a) the DB
change should be detected in real-time (not polled), and b) the DB
tables can potentially get very big and needless polling could
introduce performance problems.

I've looked at triggers, stored procedures, Java stored procedures
etc.. but none of these things seem to be fully portable, a Java
stored procedure would be nice due to the portability of the language
but SQL Server doesn't support Java stored procedures (I think, there
may be a version that supports Microsoft Java, which isn't portable
anyway, but it supports C# and other Microsoft promoted languages).
I'm slightly worried about the performance of Java anyway in a stored
procedure, there can be many changes to the table per second. Triggers
are used with the Alert packages mentioned above, so if trigger syntax
is not portable, that's going to cause a slight problem anyway.

Can anybody point me in the right direction? How should I be trying to
achieve this? How do external applications usually get notified of
changes in DBs? What realities should I accept (i.e., xyz can't be
done, no point in trying it etc..)? Are there any capabilities in DBs
that might help, for example, can the DB open a socket and pump the
change down the socket, or send a signal to a process ID to notify it
of a change etc...? Should I be exec'ing a client script process from
a trigger to notify the external app (this could be a performance
issue again)? Should I be creating another, small table, where I add
the rowID of the changed row and have the external app poll that small
table? I'm not sure in which direction I am supposed to be looking!!

Thanks in advance,
Eoin.

Reply to this Message

Ed prochak - 21 Jun 2004 18:33 GMT

> Hello,
>
[quoted text clipped - 3 lines]
> only, but there should be no restriction on the actual DB being used
> by the client.

It's called middleware.

>... I plan to use Java and JDBC to address the portability
> and connectivity issues, but I have hit a problem trying to implement
> the exact functionality I'm looking for in a database independent
> manner. I'm quite new to database programming/design, so don't know
> what is possible, what is not, if I'm on the right track or not etc...

Is this a project team of ONE? This is a big endeavor.

> What I want to achieve is to have a Java application running a thread
> that is 'listening' for changes in the database tables (and processing
[quoted text clipped - 5 lines]
> purpose as DBMS_ALERT or LISTEN/NOTIFY). Ideally, I'd like something
> that is generic in case a new database is introduced.

Your software is what is going to be generic to the Front end
application.

>... Is there a way
> of doing this? I want to avoid sleep/poll approaches as a) the DB
> change should be detected in real-time (not polled),

Define what YOU mean by real-time.
(I've done lots of hard real-time programming and the way most folks
use the term, they obviously do not know what real-time means in
software engineering context.)

>... and b) the DB
> tables can potentially get very big and needless polling could
> introduce performance problems.

That all depends on you DB features, application datamodel and
implimentation, and exactly what you are polling for. Polling is not
bad in all contexts.

> I've looked at triggers, stored procedures, Java stored procedures
> etc.. but none of these things seem to be fully portable, a Java
> stored procedure would be nice due to the portability of the language
> but SQL Server doesn't support Java stored procedures (I think, there
> may be a version that supports Microsoft Java, which isn't portable
> anyway, but it supports C# and other Microsoft promoted languages).

Most other DBMS if they support triggers and stored procedures are
likely to be in proprietary languages, NOT java. So you better plan on
having custome interfaces to each DBMS, sometimes even between
versions of of the DB.

> I'm slightly worried about the performance of Java anyway in a stored
> procedure, there can be many changes to the table per second. Triggers
> are used with the Alert packages mentioned above, so if trigger syntax
> is not portable, that's going to cause a slight problem anyway.

You cannot worry about performance if you don't define your criteria.
Are you talking about: millisecond, second, minute, hourly, or daily
updates to the front end? Is this a hard real-time control application
(eg. plant process control)? Or is it a dynamic user interface (stock
market tracking)?

What are the consequences of missed updates? (in the first example,
the plant may blow up, while in the second example missing one trading
pricepoint in an hour might be invisible.)

> Can anybody point me in the right direction? How should I be trying to
> achieve this? How do external applications usually get notified of
[quoted text clipped - 11 lines]
> Thanks in advance,
> Eoin.

You need to refine exactly what you are trying to achieve. You are
worrying about the finegrain details (overhead of sockets, versus
signals, versus forking new processes) when you should be concerned
about the functionality you need. Make a features list. Be SPECIFIC.
The term "real-time" is meaningless by itself. Describe what are the
timing deadlines instead, eg "the data is wrong/useless/dangerous if
it arrives at the client application XXX seconds after it is enterred
in the Database."

There are products out there that have much of the functionality you
propose to build from scratch. Check them out. You'll save yourself
and your boss a good deal of pain.

HTH,
ed

Reply to this Message

Eoin - 25 Jun 2004 16:06 GMT

ed.prochak@magicinterface.com (Ed prochak) wrote in message

Hi Ed, thanks for the response.

> Is this a project team of ONE? This is a big endeavor.

Don't I know it!

> Your software is what is going to be generic to the Front end
> application.

Exactly. There's a bit of extra functionality around it too, but
that's the main gist of it.

> Define what YOU mean by real-time.
> (I've done lots of hard real-time programming and the way most folks
> use the term, they obviously do not know what real-time means in
> software engineering context.)

Sorry, to clarify, the databases I'm listening to are bursty in
nature, it could be quiet for hours, and then hundreds of inserts per
second can potentially occur ('event storms'). I'd like to process
these absolutely ASAP as they happen (if possible), as the processing
often fixes the problem that is causing the inserts.

> That all depends on you DB features, application datamodel and
> implimentation, and exactly what you are polling for. Polling is not
[quoted text clipped - 4 lines]
> having custome interfaces to each DBMS, sometimes even between
> versions of of the DB.

That's ok, I'm coding in Java so I can define a base DB interface
class, extend and customise pretty easily and quickly per DB, and the
rest of the code can deal with the base interface class
(polymorphism), abstracting the code from the actual DB class being
used. If I have to add a new DB vendor or version, I extend the base
class and customise it, the rest of the code stays the same.
Instantiation of the class will happen in a Factory so the main code
doesn't create objects directly. It should be sufficient.

> You cannot worry about performance if you don't define your criteria.
> Are you talking about: millisecond, second, minute, hourly, or daily
> updates to the front end? Is this a hard real-time control application
> (eg. plant process control)? Or is it a dynamic user interface (stock
> market tracking)?

Millisecond updates. The back-end system monitors IT resources,
raising 'alerts' if something goes wrong, and the processing of these
alerts in the front end can happen automatically, fixing the problem.

> What are the consequences of missed updates? (in the first example,
> the plant may blow up, while in the second example missing one trading
> pricepoint in an hour might be invisible.)

Not reacting immediately to the 'alert' can mean many more alerts
being inserted (reporting the same problem), which can lead to
'escalations' of a problem before the actual problem has been detected
in the first place. If I poll, the poll time/delay could cause this to
happen, unless the time/delay was very small, but because the database
could be quiet for hours/days, this would mean a lot of needless
polling during quiet periods.

> You need to refine exactly what you are trying to achieve. You are
> worrying about the finegrain details (overhead of sockets, versus
[quoted text clipped - 4 lines]
> it arrives at the client application XXX seconds after it is enterred
> in the Database."

Because it's something that happens in terms of milliseconds, I'm
concerned about performance of starting up hundreds of java virtual
machines per second etc..

> There are products out there that have much of the functionality you
> propose to build from scratch. Check them out. You'll save yourself
> and your boss a good deal of pain.

Any pointers or recommendations? Would they have functionality to wait
for a trigger/alert, or just the insert/update/delete SQL
functionality?

If I go with the customisation per db approach, is it possible to
implement the 'have a thread, blocking, listening for activity on a
table' logic as is provided by Oracle's DBMS_ALERT
(DBMS_ALERT.WAIT...) package? Can this be done with SQL Server? Is
this sort of functionality available in SQLServer?

Thanks again Ed,
Eoin.

Reply to this Message