SQL Anywhere Mobile - Howto: get timestamp with fractions of second

Author

Howto: get timestamp with fractions of second

Ignacio Mateos

2005-07-11, 7:24 am

ASA 9.02.3131

HI,

I have defined a timestamp value both in my remote and consolidated wich is
part of the key of my table.

I see that the precision is truncated to the second, and this is giving me
sometimes duplicate keys, making the sync process to abort.

All the timestamp options are the default, specifically
Default_timestamp_in
crement 1
Truncate_timestamp_v
alues Off
Timestamp format yyyy-mm-dd hh:mm:ss.ssssss

Yet if I do select now(*); I get a value just to the second.

Any ideas?

Regards

Nick Elson

2005-07-11, 9:24 am

Basically the getdate( ), NOW(*), CURRENT [UTC] TIMESTAMP
and default timestamps are accurate only going to be accurate to 3
decimal places and even then the system clock may be a source for
even less accuracy [depending upon platform].

The DEFAULT_TIMESTAMP_IN
CREMENT option only applies
to the case of:

- a table having a TIMESTAMP column with the DEFAULT
TIMESTAMP mechanism set for the default

- two row changes occur in such a way that they could be
recorded as the same [~millisecond accurate] 'value'

In the case where both conditions apply, then a timestamp increment of
1 (or your setting for default_timestamp_in
crement) microsecond(s) is
added to the latter one to help differentiate them.

This mechanism [which is really a 'row versioning' mechanism and not
simply a date+time mechanism] causes 'timestamp default timestamp'
column values to be uniquely be defined [to fulfill their intended mission]
for all changes. This mechanism does not apply to just any other timestamp
value/column values and the option has no other purpose.

Note, if it is not obvious yet, the 'default timestamp' mechanism is
different from either the [default] 'current timestamp' and the [default]
'current utc timestamp' system constants or the getdate() and now(*)
system functions.

This does not preclude you from coming up with some sort of external
clocking mechanism on your own and to store your own microseconds,
but it does mean that the stored sub-millisecond part is either artificial
(sequenced but artificial), zero or user supplied.

A further caveat for ML:
================
In the case of Mobilink, the consolidated database may not even be an
ASA server and the RDBMS technology use there may impose even
stringent restrictions on the accuracy of datetime columns and values;
so be careful with your design choices here; they may not be portable.

References:
=======
ASA SQL Reference, SQL Language Elements, Special values:
TIMESTAMP special value
and
CURRENT TIMESTAMP special value

"Ignacio Mateos" <imateos@telefonica.net> wrote in message
news:42d25caa$1@foru
ms-2-dub...
> ASA 9.02.3131
>
> HI,
>
> I have defined a timestamp value both in my remote and consolidated wich
> is part of the key of my table.
>
> I see that the precision is truncated to the second, and this is giving me
> sometimes duplicate keys, making the sync process to abort.
>
> All the timestamp options are the default, specifically
> Default_timestamp_in
crement 1
> Truncate_timestamp_v
alues Off
> Timestamp format yyyy-mm-dd hh:mm:ss.ssssss
>
> Yet if I do select now(*); I get a value just to the second.
>
> Any ideas?
>
> Regards
>

David Fishburn

2005-07-11, 9:24 am

"Ignacio Mateos" <imateos@telefonica.net> wrote in
news:42d25caa$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:

IM> ASA 9.02.3131

Thank you.
What is the MobiLink server connected to?
And if it is Oracle (which I suspect) what version of Oracle (please be
very specific).
Also which ODBC drivers (again version) is MobiLink using to connect to
it?

IM> I have defined a timestamp value both in my remote and consolidated
IM> wich is part of the key of my table.

Using timestamps are part of a primary key is generally a bad idea.
Is there a reason you are taking this approach?
It is to make sure no 2 remotes create the same key value?

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Ignacio Mateos

2005-07-11, 11:24 am

Thank you both for your answers.

I am using ASA 9-0-2 both as consolidated and remote.

The reason I put the date_time in a key field is exactly that: to arrive at
a unique key amongst all the remotes. Otherwise, I could have used just an
autoincrement Integer as my key, but what if two remotes create the same?

The problem I am having now is not of accuracy, it seems to me that the PDAS
are just giving me the second, not any fraction. Also if I do a select
now(*); I only see seconds. I wonder if I could force now(*) to show me its
fraction so I can determine what really is going on, but it seems to me that
I get nothing but seconds, not fractions.

Thank you

"David Fishburn" <fishburn_spam@off.ianywhere.com> escribió en el mensaje
news:Xns96906D6CE322
4fishburnsybasecom@1
27.0.0.1...
> "Ignacio Mateos" <imateos@telefonica.net> wrote in
> news:42d25caa$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:
>
> IM> ASA 9.02.3131
>
> Thank you.
> What is the MobiLink server connected to?
> And if it is Oracle (which I suspect) what version of Oracle (please be
> very specific).
> Also which ODBC drivers (again version) is MobiLink using to connect to
> it?
>
>
> IM> I have defined a timestamp value both in my remote and consolidated
> IM> wich is part of the key of my table.
>
> Using timestamps are part of a primary key is generally a bad idea.
> Is there a reason you are taking this approach?
> It is to make sure no 2 remotes create the same key value?
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> [url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

Reg Domaratzki $iAnywhere Solutions$

2005-07-11, 11:24 am

If you are using ASA as consolidated and remote, you should be using DEFAULT
GLOBAL AUTOINCREMENT to define your unique primary keys in all databases.
With a BIGINT data type (or choose a numeric(x,0) to suit your needs), by
default you can define 4 billion remotes databases that can each have 4
billion primary key values.

Note that using the default range values for BIGINT with DEFAULT GLOBAL
AUTOINCREMENT would mean that you could deploy a remote database to 2/3rd of
the current population of the entire planet, and every person that received
the remote database could insert a record into the table every second they
were alive (assuming 100 year life) without exhausting their individual
primary key range. Please contact us right now if this is your plan so we
can use you as a reference customer. :)

I'm in complete agreement with Dave that timestamps in a primary key are a
bad idea.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Ignacio Mateos" <imateos@telefonica.net> wrote in message
news:42d28d86@forums
-2-dub...
> Thank you both for your answers.
>
> I am using ASA 9-0-2 both as consolidated and remote.
>
> The reason I put the date_time in a key field is exactly that: to arrive
at
> a unique key amongst all the remotes. Otherwise, I could have used just an
> autoincrement Integer as my key, but what if two remotes create the same?
>
> The problem I am having now is not of accuracy, it seems to me that the
PDAS
> are just giving me the second, not any fraction. Also if I do a select
> now(*); I only see seconds. I wonder if I could force now(*) to show me
its
> fraction so I can determine what really is going on, but it seems to me
that
> I get nothing but seconds, not fractions.
>
> Thank you
>
> "David Fishburn" <fishburn_spam@off.ianywhere.com> escribió en el mensaje
> news:Xns96906D6CE322
4fishburnsybasecom@1
27.0.0.1...
>
>

Greg Fenton

2005-07-11, 11:24 am

Reg Domaratzki (iAnywhere Solutions) wrote:
> If you are using ASA as consolidated and remote, you should be using DEFAULT
> GLOBAL AUTOINCREMENT to define your unique primary keys in all databases.

Just for the record, GLOBAL AUTOINCREMENT can also be used with non-ASA
consolidateds as well. Though the consolidated wouldn't have a GLOBAL
AUTOINCREMENT default as the remotes would, you essentially pre-assign
range of values for the consolidated and use its natural
AUTOINCREMENT/SEQUENCE/IDENTITY/whatever to assign unique values within
that range.

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

Breck Carter [TeamSybase]

2005-07-11, 1:24 pm

To express Greg's comment in another way, Oracle and other
consolidated databases *effectively* default to GLOBAL_DATABASE_ID =
'0' or partition number zero for these columns.

If every SQL Anywhere remote database uses a
GLOBAL_DATABASE_ID/partition number *other than* zero, life is good.

SQL Anywhere doesn't just work the way *it* should, it makes Oracle,
SQL Server, DB2, etc., work the way *they* should :)

Breck

On 11 Jul 2005 09:52:50 -0700, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>Reg Domaratzki (iAnywhere Solutions) wrote:
>
>Just for the record, GLOBAL AUTOINCREMENT can also be used with non-ASA
>consolidateds as well. Though the consolidated wouldn't have a GLOBAL
>AUTOINCREMENT default as the remotes would, you essentially pre-assign
>range of values for the consolidated and use its natural
>AUTOINCREMENT/SEQUENCE/IDENTITY/whatever to assign unique values within
>that range.
>
>greg.fenton

--
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-07-11, 1:24 pm

Are you using ASA or UltraLite on the remotes?

On 11 Jul 2005 08:17:28 -0700, "Ignacio Mateos"
<imateos@telefonica.net> wrote:

>Thank you both for your answers.
>
>I am using ASA 9-0-2 both as consolidated and remote.
>
>The reason I put the date_time in a key field is exactly that: to arrive at
>a unique key amongst all the remotes. Otherwise, I could have used just an
>autoincrement Integer as my key, but what if two remotes create the same?
>
>The problem I am having now is not of accuracy, it seems to me that the PDAS
>are just giving me the second, not any fraction. Also if I do a select
>now(*); I only see seconds. I wonder if I could force now(*) to show me its
>fraction so I can determine what really is going on, but it seems to me that
>I get nothing but seconds, not fractions.
>
>Thank you
>
>"David Fishburn" <fishburn_spam@off.ianywhere.com> escribió en el mensaje
> news:Xns96906D6CE322
4fishburnsybasecom@1
27.0.0.1...
>

--
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

Ignacio Mateos

2005-07-12, 7:27 am

I am using ASA 9.02

Well, thanks everybody who answered, I am going to change my key, although I
am not sure I will use the global autoincrement.

I am planning on having a two key field (easier to read than the partitions,
I think): Pda id, and some Integer autoincrement.

Regards

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> escribió en el
mensaje news:gte5d1h6dvt1n77
li1sn1qmce75fvmb72h@
4ax.com...
> Are you using ASA or UltraLite on the remotes?
>
> On 11 Jul 2005 08:17:28 -0700, "Ignacio Mateos"
> <imateos@telefonica.net> wrote:
>
>
> --
> 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

Greg Fenton

2005-07-12, 9:24 am

Breck Carter [TeamSybase] wrote:
> To express Greg's comment in another way, Oracle and other
> consolidated databases *effectively* default to GLOBAL_DATABASE_ID =
> '0' or partition number zero for these columns.

Well, you can "assign" the consolidated more than just "0". You can
start numbering your remotes from a number higher than "1" (say,
"10000") so that the consolidated has LOTS of room.

Or the other approach some customers have taken is to use a SEQUENCE (or
whatever) to start at the top of the partition space and work their way
down, while the remotes start at the bottom and work their way
up...never the two shall meet (cryogenics, here I come!)

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-07-12, 9:24 am

Ignacio Mateos wrote:
>
> I am planning on having a two key field (easier to read than the partitions,
> I think): Pda id, and some Integer autoincrement.
>

Though two-part primary keys is a valid strategy (they are called
"composite keys" in the ML documentation), recognize that the drawback
is that you now have more complicated SQL statements (e.g. joins) and a
an impact on performance.

The "hard part" of global autoincrement is assigning each database its
GLOBAL_DATABASE_ID value...but you will have this same issue with the
"pda id" strategy as well.

Our team recommends GLOBAL AUTOINCREMENT above all other strategies. We
only look to others when there are circumstances in a particular
application that do not allow the use of GA.

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/

Breck Carter [TeamSybase]

2005-07-12, 9:24 am

On 12 Jul 2005 06:53:50 -0700, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>Or the other approach some customers have taken is to use a SEQUENCE (or
>whatever) to start at the top of the partition space and work their way
>down, while the remotes start at the bottom and work their way
>up...never the two shall meet (cryogenics, here I come!)

....and they will revive you when they find a cure for Oracle? <grad>

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

Ignacio Mateos

2005-07-12, 11:23 am

OK, that makes sense.

One question now, Do I need a key in the consolidated after all?

(My table is just a log of incidences of the remote that get all uploaded
together for analysis at the consolidated. They never get downloaded).

I have tried to eliminate the PK in the consolidated and it has worked!
(while keeping my two part key in the remote).

I was under the assumption that I needed a Primary Key in the consolidated
table but I am looking to the documentation and can't find it.

If I drop the key, I make sure that the sync process always works, I might
get some duplicated records, but that should be fine, because the analysis I
do is DISTINCT

SO, Can I just drop the primary key of the consolidated?

Thanks and regards

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> escribió en el mensaje
news:42d3d1b5$1@foru
ms-2-dub...
> Ignacio Mateos wrote:
>
> Though two-part primary keys is a valid strategy (they are called
> "composite keys" in the ML documentation), recognize that the drawback is
> that you now have more complicated SQL statements (e.g. joins) and a an
> impact on performance.
>
> The "hard part" of global autoincrement is assigning each database its
> GLOBAL_DATABASE_ID value...but you will have this same issue with the "pda
> id" strategy as well.
>
> Our team recommends GLOBAL AUTOINCREMENT above all other strategies. We
> only look to others when there are circumstances in a particular
> application that do not allow the use of GA.
>
> 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-07-12, 8:24 pm

Ignacio Mateos wrote:
>
> SO, Can I just drop the primary key of the consolidated?
>

What do you mean by "drop the primary key" ?

Are the rows all upload-only (i.e. you will *never* download rows to the
remotes)?

Do you generate new rows at the consolidated ever (via applications
running at the consolidated)?

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

Graham Hurst

2005-07-13, 1:24 pm

MobiLink relies on primary keys to match up rows on the remotes with
rows on the consolidated. In general:

- All synced tables must have primary keys.

- Primary keys must never be updated.

- New rows must have primary keys that are unique across all the
synchronized databases.

In your case, where the table is upload only, and rows are only inserted
(not updated or deleted) by the remotes, then you should not need to
define a primary key on the consolidated table.

If you are uploading updates or deletes for that table, then MobiLink
will need the equivalent of the primary key. It won't have to be defined
as a primary key on the consolidated (MobiLink doesn't check that).
However note that multiple rows may be updated or deleted (if the
effective primary key is not unique) and performance may be slower (if
it is not indexed).

Cheers,

Graham

Ignacio Mateos wrote:
> By drop the primary key I mean just not having a PK in the consolidated
> table.
>
> Yes, the rows are upload only, never download.
>
> No the consolidated will never generate a row, it only gets them from the
> remotes.
>
> Thanks so much!

Sponsored Links