Sybase Database - Replication Agent Question...

Author

Replication Agent Question...

badbrownie@gmail.com

2006-03-10, 8:23 pm

Please excuse my multiple posts. I assure you that I'm reading around
plenty and not simply posting any questions that cross my mind.

My ultimate goal is to have a custom app be aware of changes that
happen in any specified Sybase DB. Therefore I can't add custom code
to the db (triggers et al.) as the solution must work with our
customer's db.

I'm trying to find some way to use replication server to give me the
update and insert information that acts on the db and I see 2 tools
that look like they might be used for the job. RepConnector and
ReplicationAgent. However, as I understand these tools they both have
limitations:

RepConnector uses JMS and the messages that the source db would send
over JMS must be coded, so it won't work as a general solution.

ReplicationAgent looks like it does more of what I want (I can specify
the tables I want watched and it will transmit updates to those
tables). However, it is designed only to replicate from non-sybase
db's (Oracle, Informix, DB2) to a Sybase DB.

Is there any way I can get what I want from ReplicationServer. That is
to have my Sybase Db communicate and data changes (inserts/updates) to
my custom application?

alan

Mark A. Parsons

2006-03-10, 8:23 pm

I think you're going to have to provide a bit more detail on exactly what
you're looking to do, eg, does every insert/update/delete on every table in
every database generate an action in your application?

Repserver could probably be configured to pass data changes directly to an
application but ... repserver installation/setup/maintenance is a
non-trivial matter ... repserver is a whole 'nother product that requires
$$ for licensing and possibly additional hardware ... and the connection
from repserver to your application will most likely have to be custom built
by you.

Other alternatives may be to use the ASE Replicator (think poor man's
version of repserver). ASE Replicator comes (free) with ASE and performs
some very, very basic replication capabilities.

Another option may be to setup Auditing within your dataserver. Auditing
dumps audit records into a table in the sybsecurity database; from here
your application could periodically pole said table for the desired info.

It may also be possible to use one of the above, in conjunction with RTDS,
to accomplish what you want.

Other options (and/or variations on the above) may provide what you're
looking for ... but it's really going to depend on having a more detailed
explanation of what you're looking to do (and would probably be mucho
helpful to have some examples).

badbrownie@gmail.com wrote:

> Please excuse my multiple posts. I assure you that I'm reading around
> plenty and not simply posting any questions that cross my mind.
>
> My ultimate goal is to have a custom app be aware of changes that
> happen in any specified Sybase DB. Therefore I can't add custom code
> to the db (triggers et al.) as the solution must work with our
> customer's db.
>
> I'm trying to find some way to use replication server to give me the
> update and insert information that acts on the db and I see 2 tools
> that look like they might be used for the job. RepConnector and
> ReplicationAgent. However, as I understand these tools they both have
> limitations:
>
> RepConnector uses JMS and the messages that the source db would send
> over JMS must be coded, so it won't work as a general solution.
>
> ReplicationAgent looks like it does more of what I want (I can specify
> the tables I want watched and it will transmit updates to those
> tables). However, it is designed only to replicate from non-sybase
> db's (Oracle, Informix, DB2) to a Sybase DB.
>
> Is there any way I can get what I want from ReplicationServer. That is
> to have my Sybase Db communicate and data changes (inserts/updates) to
> my custom application?
>
> alan
>

badbrownie@gmail.com

2006-03-10, 8:23 pm

My goal is to be able to cache JDBC resultSets so an application would
only hit the db if the result of the query had changed. This system
would handle external updates to the data, so my app would need to be
alerted when any data changed in the db.

For example, I may have cached 'select * from employee' and another
application might have updated the employee table so my app must know
that its cached entry is now stale and therefore go to the db the next
time the query is executed.

In an ideal world I'd be alerted about what rows were updated and what
their values have changed from and to. The more information I can
receive about the data updates the better a cache invalidation policy I
can create. A minimum would be to understand what tables had been
touched by an insert/delete/update, so I could invalidate queries that
depended on those tables.

Let me know if my example does not adequately clarify my question and
thanks for your thought and input.

alan

Mark A. Parsons

2006-03-10, 8:23 pm

Sounds like you're talking about local caching of data within some sort of
middleware component.

[I have no idea, technically, how most middleware software keep the local
cache up-to-date.)

You mention that you cannot (realistically) add triggers on the various
tables ... but I'll assume you would have not problems with making database
and/or dataserver level changes (as would be required to use ASE
Replicator, Repserver, or to enable auditing).

I'll also assume that these cached tables typically do not change very often.

----------------

An auditing solution would let you know about a table being changed, but
you probably couldn't rely on a timely update to your middleware.

Auditing dumps audited events into the sybsecurity.sysaudits_0x table. The
only way to get data out of this table is to query it or dump the contents
into another table.

You cannot put a trigger on sysaudits_0x ... but you could put a trigger on
the 'other' table into which you dump the contents of sysaudits_0x.

Because you wouldn't be expecting a very large volume of table changes you
would be forced into periodically polling sysaudits_0x to see if has any
'new' info for your middleware.

.... probably not a viable solution ...

----------------

Repserver could be used to subscribe to specific tables (eg, your cached
lookup/reference tables). The results of any data changes
(insert/update/delete) could be replicated to where ever you like.

Destination options could include a table in another database. A trigger
on this table could generate your RTDS call, or possibly an OS call via the
XP server. (XP server comes free with the dataserver and you're only
limited by your creativity; could easily pass tokens to an OS shell script,
or program; which in turn could get the info to your middleware).

Another destination option would be to write your own OpenServer based
application. The idea would be to have the RepServer send the replicated
transaction to this openserver application; which in turn forwards the
necessary data to your middleware.

The nice thing about RepServer is that not only could you notify the
middleware of a change in your table, but you could also tell it the change
(ie, full contents of the 'insert', which columns were modified in an
update, the PK of a deleted record).

RepServer is a little expensive in terms of $$ (software, hardware,
training, someone to manage it). RepServer may also be an overkill for the
low volumes of data you're considering replicating. (Obviously if you
already have RepServer in house then most of the cost has already been
incurred.)

-----------------

ASE Replicator could be utilized the same way as RepServer, and may
actually be a better route if you're expecting to replicate (relatively)
small amounts of data (ie, lookup/reference tables are not changing very much).

The only destination option you'd have for Replicator is to dump a
transaction into another table (eg, in your own database). From here you'd
use the same type of trigger as mentioned above for RepServer.

As with RepServer, Replicator would allow you to pass along not only the
fact that a change has been made in the table, but also *what* change has
been made (full contents of an insert, the values of columns modified in an
update, the PK of a deleted record).

ASE Replicator is 'free' with ASE and it's a good bit easier to setup and
maintain. The drawback is that ASE Replicator is really not very efficient
if you're looking to replicate large volumes of data (probably not an issue
with the scenario you've outlined).

------------------

'course, at this point we're probably re-inventing the wheel as I'm sure
plenty of folks using middleware have had to contend with this same issue,
ie, what do other folks do in this situation?

(And, yeah, there are probably some middleware products from Sybase that
could help cut down on the development time ... but I's just a dumbo DBA
and woodn't noe nuttin' about other Sybase products ... )

badbrownie@gmail.com wrote:

> My goal is to be able to cache JDBC resultSets so an application would
> only hit the db if the result of the query had changed. This system
> would handle external updates to the data, so my app would need to be
> alerted when any data changed in the db.
>
> For example, I may have cached 'select * from employee' and another
> application might have updated the employee table so my app must know
> that its cached entry is now stale and therefore go to the db the next
> time the query is executed.
>
> In an ideal world I'd be alerted about what rows were updated and what
> their values have changed from and to. The more information I can
> receive about the data updates the better a cache invalidation policy I
> can create. A minimum would be to understand what tables had been
> touched by an insert/delete/update, so I could invalidate queries that
> depended on those tables.
>
> Let me know if my example does not adequately clarify my question and
> thanks for your thought and input.
>
> alan
>

badbrownie@gmail.com

2006-03-13, 1:23 pm

Thanks for the detailed response Mark. That's much help. It seems like
ASE Replicator is the way to go for the moment, utilizing RTDS via JMS
to get the information to my application. Can you tell me if this
solution would transfer easily into using RepServer if the need scaled
beyond what ASE Replicator could adequately handle? I imagine so as
with either solution I'm dropping the transaction information into a
table that has a trigger that is using RTDS to send the information to
my app.

However, to save the expense and maintenance effort of a JMS system,
I'm quite attracted to the method you mentioned of having the trigger
make an OS call that could communicate with my app. Can this be
acheived equally with the ASE Replicator solution? I imagine so as it
appears XP server is part of the ASE product.

Thanks again for the time you've spent explaining this subject to me.

alan

Mark A. Parsons

2006-03-13, 8:23 pm

re: transfer easily from Replicator to Repserver ...

Yes, you should be able to transition somewhat easily. I say 'somewhat'
because although Replicator and Repserver have similar concepts they use
totally different configurations/commands. But you would just be replacing
the replication portion ... the target table (and the follow on code) would
remain the same.

re: Replicator + trigger + XP calls ...

Yeah, should be doable. You'll just need to play with XP to see if it will
provide all the functionality you want (eg, there are security issues when
making OS calls via XP, you typically cannot pass info back from the OS
call to the ASE process which made the XP call, etc.).

badbrownie@gmail.com wrote:

> Thanks for the detailed response Mark. That's much help. It seems like
> ASE Replicator is the way to go for the moment, utilizing RTDS via JMS
> to get the information to my application. Can you tell me if this
> solution would transfer easily into using RepServer if the need scaled
> beyond what ASE Replicator could adequately handle? I imagine so as
> with either solution I'm dropping the transaction information into a
> table that has a trigger that is using RTDS to send the information to
> my app.
>
> However, to save the expense and maintenance effort of a JMS system,
> I'm quite attracted to the method you mentioned of having the trigger
> make an OS call that could communicate with my app. Can this be
> acheived equally with the ASE Replicator solution? I imagine so as it
> appears XP server is part of the ASE product.
>
> Thanks again for the time you've spent explaining this subject to me.
>
> alan
>

Michael Peppler

2006-03-13, 8:23 pm

On Mon, 13 Mar 2006 11:16:57 -0800, badbrownie wrote:

> Thanks for the detailed response Mark. That's much help. It seems like
> ASE Replicator is the way to go for the moment, utilizing RTDS via JMS to
> get the information to my application. Can you tell me if this solution
> would transfer easily into using RepServer if the need scaled beyond what
> ASE Replicator could adequately handle? I imagine so as with either
> solution I'm dropping the transaction information into a table that has a
> trigger that is using RTDS to send the information to my app.
>
> However, to save the expense and maintenance effort of a JMS system, I'm
> quite attracted to the method you mentioned of having the trigger make an
> OS call that could communicate with my app. Can this be acheived equally
> with the ASE Replicator solution? I imagine so as it appears XP server is
> part of the ASE product.

You might also consider using the sp_sendmsg functionality, which allows
you to send short text messages from a stored proc or trigger to a UDP
port. It's then up to you to write the appropriate handler on the other
side to handle the messages and take the needed action.

The next step beyond that is to write a complete Open Server app where
your client calls the open server to perform the actions in the database
and the open server acts as a caching gateway. Complicated, and requires
additional Open Server license from Sybase, but very powerful.

Michael
--
Michael Peppler [TeamSybase] mpeppler@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

badbrownie@gmail.com

2006-03-13, 8:23 pm

Do you know where I might see example code of how this configuration is
acheived?

I assume it's not possible to replicate to a table in the same db
(which would probably be the simplest solution).

Can I set up a second database in the same ASE or is my reading of the
term 'database' as used by Sybase mistaken?

Basically, I'm looking to make this as simple, maintainable and
reproducible as possible.

Thanks again,

alan

badbrownie@gmail.com

2006-03-13, 8:23 pm

It sounds like writing to a UDP port would result in a non guaranteed
message which probably wouldn't meet my needs. Thanks for the thought
though.

Mark A. Parsons

2006-03-13, 8:23 pm

I would probably want to set up a new database ... in turn this database
could reside in *any* dataserver you wish.

I say this for a few reasons:

1 - you're not in a position to go making stored prod/trigger changes so
I'm assuming you may run into some grief if you dump your own table(s) and
procs/triggers into someone else's database (YMMV)

2 - if you have multiple databases from which you'll be pulling data then
having a single point of concentration (failure?) for your data may be
easier to manage; also makes it easier to add other databases to your
environment, ie, just have them point to your 'special' database (as
opposed to installing your components into yet another 'user' database)

3 - due to the security issues with the XP server you may find that you
need to setup your XP server calls within a dataserver where you have more
control over who has access to the dataserver as well as who can make XP
server calls

badbrownie@gmail.com wrote:

> Do you know where I might see example code of how this configuration is
> acheived?
>
> I assume it's not possible to replicate to a table in the same db
> (which would probably be the simplest solution).
>
> Can I set up a second database in the same ASE or is my reading of the
> term 'database' as used by Sybase mistaken?
>
> Basically, I'm looking to make this as simple, maintainable and
> reproducible as possible.
>
> Thanks again,
>
> alan
>

badbrownie@gmail.com

2006-03-13, 8:23 pm

Ok. I'm convinced. The source db will replicate to my own database
that will be in the same dataserver as the source db.

BTW - If I use XP Server then will the OS notifications be synchronous
with any data changes in the source db? Synchronous communication is a
requirement for this system. It seems like it will be synchronous but
if it isn't I need to rethink my approach.

alan

Mark A. Parsons

2006-03-13, 8:23 pm

I'd suggest spending some time up front running some tests with XP server
calls to see if it'll do what you want.

Replication, whether Replicator or Repserver, is asynchronous. Once you
make the change to the base table there is, technically, no way to tell how
long it will take for that change to find it's way (via replication) to the
target table.

If you want truly synchronous you'll have to put a trigger on the source
table. This trigger would then have to make the XP call out to the OS.

An XP server call (from within the dataserver) will wait for the OS call to
complete. So at this point the question of 'synchronous' will depend on
the synchronicity of your OS program/script/session/whatever.

The obvious (?) issue with a fully synchronous setup is that if any one
component is not available then you disrupt activity in the source
database/table. ("Duh, Mark!" ?)

badbrownie@gmail.com wrote:

> Ok. I'm convinced. The source db will replicate to my own database
> that will be in the same dataserver as the source db.
>
> BTW - If I use XP Server then will the OS notifications be synchronous
> with any data changes in the source db? Synchronous communication is a
> requirement for this system. It seems like it will be synchronous but
> if it isn't I need to rethink my approach.
>
> alan
>

Sponsored Links