SQL Anywhere Feedback - SQL remote TIMESTAMP replication

Author

SQL remote TIMESTAMP replication

Pavel Karady

2006-01-17, 9:23 am

ASA (9.X):
ASA, however is the timestamp format set, maintains the full 6-digit
fraction of second.

It would be a ***VERY FINE*** (that's three stars from both sides with caps
on) thing if the timestamp column would replicate in a ***FULL*** format,
whatever the option 'timestamp_format' would be set to, because when
replicating such a column with 'timestamp_format' set to '....nn.ss.sss'
(what I believe is the default setting), only the 'ss.sss' is replicated and
I believe ASA adds the missing three digits manually (.sss000, .sss001,
..sss002 ...).

When doing data synchronization between databases, trillions of rows show
mismatch then in timestamp columns - and it's just the remote agent who has
sent a newly added row to the remote database - with incomplete data.

This could save half, which was spent on searching for error.

Pavel

P.S. I'm sorry if this is somehow correctable, but if not and I am right,
then blizzard, lightings, fire & scream ;)

Chris Keating(iAnywhere Solutions)

2006-01-17, 9:23 am

What is your setting for SR_Timestamp_Format. The SR_Timestamp_Format is
the SQL Remote timestamp setting. BTW If you have ASE in the picture,
you should ensure that you are configured not to use the 6 digit
precision as the ASE datetime is not as precise and is only accurate to
the first three digits.
--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

********************
********************
********************
*****************
Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition =and try out the market-leading database for mobile, embedded
and small to medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

********************
********************
********************
*****************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

********************
********************
********************
*****************

Pavel Karady

2006-01-18, 3:23 am

> What is your setting for SR_Timestamp_Format. The SR_Timestamp_Format is
> the SQL Remote timestamp setting.

I did not know about this setting. I was very excited when I went to
retrieve the value of it quickly, but unfortunately, it contained six digits
in the fraction of second.

Here's a clear fragment from the remote log:
-----------------------------
I. 01/18 02:22:49. Received message from "mainusr"
(0-01024082280-01024082820-0)
I. 01/18 02:22:49. Applying message from "mainusr"
(0-01024082280-01024082820-0)
I. 01/18 02:22:49. INSERT INTO
DBA. table(column1,column
2,column3,column4,co
lumn5,column6,

column7)

VALUES ('XXX','username','2
006/01/ 18','*******','Login

Successful','usernam
e','02:18:14.125 2006/01/18')
I. 01/18 02:22:49. COMMIT
I. 01/18 02:22:50. Sending message to "mainusr" (0-0990871405-0990871405-0)
-----------------------------

The "column7" column is the timestamp. As we can see, '02:18:14.125
2006/01/18' value has been replicated. This is an insertion that has been
replicated from the consolidated to the remote db (this log belongs to the
remote db).

Consolidated database settings:
PUBLIC. 'SR_TimeStamp_Format
' = 'hh:nn:ss.Ssssss yyyy/mm/dd'
PUBLIC.'Timestamp_Format' = 'YYYY-MM-DD HH:NN:SS.SSS';

I believe both of this are defaults. ASA 9.0.2.2551

>BTW If you have ASE in the picture,

errr.. not really :)

> you should ensure that you are configured not to use the 6 digit precision
> as the ASE datetime is not as precise and is only accurate to the first
> three digits.

but it's always good to know. Thanks!

Please let me know if you need any more information. Thank you very much
Pavel

Rob Waywell

2006-01-18, 9:23 am

What happens when you change the PUBLIC.Timestamp_Format to have 6 digits of
precision?

I suspect that all of your values in the consolidated have been limited to 3
digits of precision which would mean that you don't have 6 digits available
to send to the remote.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43cdf4fb$1@foru
ms-2-dub...
>
> I did not know about this setting. I was very excited when I went to
> retrieve the value of it quickly, but unfortunately, it contained six
> digits in the fraction of second.
>
> Here's a clear fragment from the remote log:
> -----------------------------
> I. 01/18 02:22:49. Received message from "mainusr"
> (0-01024082280-01024082820-0)
> I. 01/18 02:22:49. Applying message from "mainusr"
> (0-01024082280-01024082820-0)
> I. 01/18 02:22:49. INSERT INTO
> DBA. table(column1,column
2,column3,column4,co
lumn5,column6,
>
> column7)
>
> VALUES ('XXX','username','2
006/01/ 18','*******','Login

> Successful','usernam
e','02:18:14.125 2006/01/18')
> I. 01/18 02:22:49. COMMIT
> I. 01/18 02:22:50. Sending message to "mainusr"
> (0-0990871405-0990871405-0)
> -----------------------------
>
> The "column7" column is the timestamp. As we can see, '02:18:14.125
> 2006/01/18' value has been replicated. This is an insertion that has been
> replicated from the consolidated to the remote db (this log belongs to the
> remote db).
>
> Consolidated database settings:
> PUBLIC. 'SR_TimeStamp_Format
' = 'hh:nn:ss.Ssssss yyyy/mm/dd'
> PUBLIC.'Timestamp_Format' = 'YYYY-MM-DD HH:NN:SS.SSS';
>
> I believe both of this are defaults. ASA 9.0.2.2551
>
>
> errr.. not really :)
>
>
> but it's always good to know. Thanks!
>
> Please let me know if you need any more information. Thank you very much
> Pavel
>

Sponsored Links