SQL Anywhere database - How to capture all SQL commands to create a Different Log

Author

How to capture all SQL commands to create a Different Log

Hugo Teran

2005-08-17, 9:27 am

I need to capture all SQL commands fired to the Database,
maybe using a trigger or some kind of event. I want to store
all the commands in a special table for synchronization
purpose.

I don=92t want to extract that info from the log file
generated by the Database.

Thanks.

Stephen Rice

2005-08-17, 9:27 am

Hugo Teran wrote:
> I need to capture all SQL commands fired to the Database,
> maybe using a trigger or some kind of event. I want to store
> all the commands in a special table for synchronization
> purpose.
>
> I don’t want to extract that info from the log file
> generated by the Database.
>
> Thanks.

There is no such specific capability. You would have to write a trigger
for each operation type, on each table, to record the information you
need into a transaction table which you can then use to generate SQL for
you target database. Remember to write all of the code to deal with
errors, partial failures, missing transactions, etc.

OR

you could just use one of the 3 supported data movement technologies
which already manage the whole thing for you.

Is there a particular reason you need to experience the pain of
developing your own data movement capability? (I'm not kidding - it is
painful. I've done it myself in a previous life and cannot imagine how
you justify doing it yourself)

/steve - yes I know I'm biased but that doesn't make me wrong
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions

--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com

Donna Osburn

2005-08-17, 8:23 pm

> I need to capture all SQL commands fired to the Database,
> maybe using a trigger or some kind of event. I want to
> store all the commands in a special table for
> synchronization purpose.
>
> I don=92t want to extract that info from the log file
> generated by the Database.
>

please check into the -z, -zl, -zo, -zr and -zs. by adding
these to your startup dsn - it makes very nice logs of all
sql commands to the database. Extremely helpful for
debugging. don't forget to set a limit for your log size so
as not to run out of room!
Donna

> Thanks.

Hugo Teran

2005-08-17, 8:23 pm

The reason why I want to do this by my self is because; I
have experience anything but problems with Mobilink and Sql
Remote. Now I prefer to do this all by my self.

I realy love Sybase database systems...
I hate Sybase replication systems

Thanks

> Hugo Teran wrote:
>
> There is no such specific capability. You would have to
> write a trigger for each operation type, on each table,
> to record the information you need into a transaction
> table which you can then use to generate SQL for you
> target database. Remember to write all of the code to
> deal with errors, partial failures, missing transactions,
> etc.
>
> OR
>
> you could just use one of the 3 supported data movement
> technologies which already manage the whole thing for
> you.
>
> Is there a particular reason you need to experience the
> pain of developing your own data movement capability?
> (I'm not kidding - it is painful. I've done it myself in
> a previous life and cannot imagine how you justify doing
> it yourself)
>
> /steve - yes I know I'm biased but that doesn't make me
> wrong --
> Stephen Rice
> Technical Services Manager
> iAnywhere Solutions
>
> --- Please Post ---
> Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
> "Report a bug" are all available on www.ianywhere.com

Hugo Teran

2005-08-17, 8:23 pm

Thanks, I will try that.
[color=darkred]
>
> please check into the -z, -zl, -zo, -zr and -zs. by
> adding these to your startup dsn - it makes very nice logs
> of all sql commands to the database. Extremely helpful
> for debugging. don't forget to set a limit for your log
> size so as not to run out of room!
> Donna

Donna Osburn

2005-08-17, 8:23 pm

The Mobilink System can be frustrating. The books online
has good documentaion. Speaking from one who has "been
there, done that, got the t-shirt" work out your mobilink
kinks. it will be much less time consuming than trying to
write your on replication.

I have found that the key to mobilink is good database
design. On both the consolidated and the remote. The
scripts will truly almost write themselves if you take
considerable care to design your database correctly.

Donna[color=darkred]

> The reason why I want to do this by my self is because; I
> have experience anything but problems with Mobilink and
> Sql Remote. Now I prefer to do this all by my self.
>
> I realy love Sybase database systems...
> I hate Sybase replication systems
>
> Thanks
>
>

Greg Fenton

2005-08-17, 8:23 pm

Donna Osburn wrote:
>
> I have found that the key to mobilink is good database
> design. On both the consolidated and the remote. The
> scripts will truly almost write themselves if you take
> considerable care to design your database correctly.
>

By "take considerable care", for the most-part means following good
relational database design:

- primary keys on all tables used SOLELY to uniquely identify rows
- do NOT modify primary keys in your applications (see above)
- unique key generation strategy (somewhat different in a distributed
database from simple ol' AUTOINCREMENT...but not much different)
- avoid cyclical relationships (supportable under synchronization, but
can be problematic)

There are a few other things to consider, but the above are the major
points. Other things to think about :

- recording DELETEs in your consolidated such that they can be
synchronized to all needed remotes
- identify rows that can be modified at multiple databases (i.e. conflicts)
- backup and recovery strategy(ies)

The time that people often run into frustrations is when they need to
support a legacy schema that does not match the above criteria. There
are techniques we have developed to work around common legacy problems.

The MobiLink newsgroup is a great place to start discussing these issues
and iAnywhere Professional Services is available for knowledge-transfer
and/or application design and development work.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Greg Fenton

2005-08-17, 8:23 pm

Donna Osburn wrote:
>
> please check into the -z, -zl, -zo, -zr and -zs.

This can also be controlled in a running database by using the
sa_server_option() procedure to set the various Request* options.

In the SQLAnywhere 9.x docs, see:

ASA SQL Reference
System Procedures and Functions
System and catalog stored procedures
- sa_server_option system procedure

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

krisztian pinter

2005-08-18, 3:23 am

On 17 Aug 2005 15:27:29 -0700, <Hugo Teran> wrote:

> The reason why I want to do this by my self is because; I
> have experience anything but problems with Mobilink and Sql
> Remote. Now I prefer to do this all by my self.
>
> I realy love Sybase database systems...
> I hate Sybase replication systems

You think you can write a better replication than currently
provided by Sybase? I really would like to see the basic
concept behind your system then. No kidding, i'm curious.

For I use SQL Remote for some years now, and sure, we
experienced a lot of problems, but it always turned out
that the problem was because lack of knowledge on our part,
or there is simply no better solution possible, because the
problem itself is that complicated.

Breck Carter [TeamSybase]

2005-08-18, 9:40 am

On 17 Aug 2005 02:33:25 -0700, Hugo Teran wrote:

>I need to capture all SQL commands fired to the Database,
>maybe using a trigger or some kind of event. I want to store
>all the commands in a special table for synchronization
>purpose.
>
>I don’t want to extract that info from the log file
>generated by the Database.
>
>Thanks.

Sounds like you want to build Oracle Lite replication... slow to
execute, hard to set up, really hard to customize. Good luck!

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Breck Carter [TeamSybase]

2005-08-18, 9:40 am

Please ignore my smart-aleck comments about implementing Oracle Lite.
They were uncalled for (I stand by Oracle Lite being slow, but I
apologize for the implication that you're gonna fail)

Before you embark on your ambitious project, please take a few moments
to explain what you do not like about MobiLink. Not SQL Remote,
though, since its flaws are well known... it is MobiLink that is up
for debate, and you are not alone if you feel "MobiLink Is Too
Complex".

I am not an iAnywhere employee, but I *do know* that they want to know
what people really think. And it is very difficult to get people to
talk about what they don't like. You are a valuable resource, in this
respect. If you don't want to get into an open argument in this
newsgroup, just send me an email.

Breck

On 17 Aug 2005 02:33:25 -0700, Hugo Teran wrote:

>I need to capture all SQL commands fired to the Database,
>maybe using a trigger or some kind of event. I want to store
>all the commands in a special table for synchronization
>purpose.
>
>I don’t want to extract that info from the log file
>generated by the Database.
>
>Thanks.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Hugo Teran

2005-08-18, 8:23 pm

Hi,
First, I did not want to offend anyone with my opinion.
Second, I am a spanish speaker from Argentina, so I=92ll try
to do my best with my english, I hope to be able to be
understandable .

I am using SQL Anywhere Studio 8, in a environment very
difficult, connecting farms information located very inside
the country using Satellite links very slow, and with long
delays (Very old tech), 16 kb =96 32 kb bandwidth and 2
seconds of delay between any single operation.

So this is completely different to connect to devices
through a direct cable, where bandwidth is not an issue.

One of the problems with Mobilink where that every time the
synchronization process started it kept transferring a lot
of info through this very slow link, may be 1 mega or more.
It took 15 minutes to 20 minutes for every synchronization.
I needed to replicate this info every 10 minutes.

Other problem was that the Mobilink agent was simple closed,
simple not running anymore, when I checked 2 hours later.

Another was that it was to difficult to deal with errors, I
was never able to write scripts to handle primary key
problems, or problems related to changes in the structure of
tables because updates made in our applications that were
applied not exactly at the same time in every location.

What I needed was to clone the same database in the 3
different locations, with exactly the same tables and
everything, and what ever happens in a location be
transmitted to the other locations,

For that, I pretended to use a software very simple with a
very simple configuration because what I wanted to do was
very simple.

I mean, why it has to be so complicated=85

What I finally did=85

I create a Powerbuilder application. A synchronization
client and a synchronization server. (part using PB 6.5 and
part using PB 8).

At this time this application has been in production for 6
months and deals every 10 minutes with 12 databases of 5
different companies and it WORKS. I have been doing a lot a
debugging but it works.

The only thing I don=92t like, is that I have to use
dbtran.exe to extract the SQL commands from log files.

That=92s why I placed the question in the newsgroup because
I wanted to find a better way to get SQL commands.

I hope this may help someone.

Hugo Teran
Synagro SRL.

Pd: I have been using Sybase products for 10 years and I
deeply love them.

Hugo Teran

2005-08-18, 8:23 pm

Hi,
First, I did not want to offend anyone with my opinion.
Second, I am a spanish speaker from Argentina, so I=92ll try
to do my best with my english, I hope to be able to be
understandable .

I am using SQL Anywhere Studio 8, in a environment very
difficult, connecting farms information located very inside
the country using Satellite links very slow, and with long
delays (Very old tech), 16 kb =96 32 kb bandwidth and 2
seconds of delay between any single operation.

So this is completely different to connect to devices
through a direct cable, where bandwidth is not an issue.

One of the problems with Mobilink were that every time the
synchronization process started it kept transferring a lot
of info through this very slow link, may be 1 mega or more.
It took 15 minutes to 20 minutes for every synchronization.
I needed to replicate this info every 10 minutes.

Other problem was that the Mobilink agent was simple closed,
simple not running anymore, when I checked 2 hours later.

Another was that it was to difficult to deal with errors, I
was never able to write scripts to handle primary key
problems, or problems related to changes in the structure of
tables because updates made in our applications that were
applied not exactly at the same time in every location.

What I needed was to clone the same database in the 3
different locations, with exactly the same tables and
everything, and what ever happens in a location be
transmitted to the other locations,

For that, I pretended to use a software very simple with a
very simple configuration because what I wanted to do was
very simple.

I mean, why it has to be so complicated=85

What I finally did=85

I create a Powerbuilder application. A synchronization
client and a synchronization server. (part using PB 6.5 and
part using PB 8).

At this time this application has been in production for 6
months and deals every 10 minutes with 12 databases of 5
different companies and it WORKS. I have been doing a lot a
debugging but it works.

The only thing I don=92t like, is that I have to use
dbtran.exe to extract the SQL commands from log files.

That=92s why I placed the question in the newsgroup because
I wanted to find a better way to get SQL commands.

I hope this may help someone.

Hugo.

Pb: I have been using Sybase products for 10 years and I
deeply love them.

Greg Fenton

2005-08-19, 3:23 am

Comments inline.

Hugo Teran wrote:
> Hi,
> First, I did not want to offend anyone with my opinion.

No offense taken. We in the newsgroup just like to get feedback from
people who say that our tools are hard to use.

Also, as a few people have pointed out, writing synchronization software
is easy to start, and very hard to get right. Recognize that MobiLink
has been developed over more than 7 years and is in use in thousands of
solutions.

> the country using Satellite links very slow, and with long
> delays (Very old tech), 16 kb – 32 kb bandwidth and 2
> seconds of delay between any single operation.

Very slow indeed. But ML is being used in all sorts of remote and
wireless configurations including some customers using satellite
communications.

> One of the problems with Mobilink were that every time the
> synchronization process started it kept transferring a lot
> of info through this very slow link, may be 1 mega or more.

MobiLink only transfers the data that you tell it to. The overhead is
kept to a minimum. If you feel that too much data is being transfered,
you need to look at your publication definition (which identified data
for uploading) and your synchronization scripts (which select the data
for downloading).

> Other problem was that the Mobilink agent was simple closed,
> simple not running anymore, when I checked 2 hours later.

We'd need more details as to why this is occuring. It certainly
shouldn't if configured correctly.

> Another was that it was to difficult to deal with errors, I
> was never able to write scripts to handle primary key
> problems, or problems related to changes in the structure of
> tables because updates made in our applications that were
> applied not exactly at the same time in every location.

There are tools (features) within ML specifically to handle these
issues. There are unique data generation strategies such as GLOBAL
AUTOINCREMENT and UNIQUEIDENTIFIER (amongst others). There are ways to
properly send out schema changes as well, including the ScriptVersion
property and the client-side hook event sp_hook_dbmlsync_sch
ema_upgrade.

The above are fundamental principles of distributed database design. I
hope you address these prior to getting too deep into the development of
your own synchronization system, otherwise you'll be facing the same
complex issues that you ran into with working against the basic design
of MobiLink.

> What I needed was to clone the same database in the 3
> different locations, with exactly the same tables and
> everything, and what ever happens in a location be
> transmitted to the other locations,

The design and features of MobiLink specifically eliminate the need for
this type of coordination between remote sites. We have customers
running with 3 or 4 completely different versions of their application
running at different sites all over the world, upgrading sites when the
site is ready, not when someone at "head office" tells them they have to.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links