SQL Anywhere database - global autoincrement

Author

global autoincrement

Brent Williams

2005-05-25, 11:23 am

build version 9.0.2.3044

I have an event that gets fired to increment the global database id of
the consolidated. It seems to work but is skipping a bunch of values.
In one case the column is defined as 'global autoincrement(1000)'
but
only 50 values of the 1000 get used per database id. The event we are
using is:

CREATE EVENT "IncrementGlobalDBID" TYPE "GlobalAutoincrement"
WHERE event_condition('Rem
ainingValues') < 20
AT CONSOLIDATED
HANDLER
BEGIN
declare @dbID unsigned bigint;
declare @newID varchar(100);
DECLARE @cmd VARCHAR(500);
SELECT db_property('GlobalD
BId') + 1 into @dbID;
SELECT CAST(@dbID AS varchar) into @newID;
SET @cmd = 'set option public.global_database_id = ''' + @newID + '''';
EXECUTE immediate @cmd;
END;

Greg Fenton

2005-05-25, 1:23 pm

Brent Williams wrote:
>
> CREATE EVENT "IncrementGlobalDBID" TYPE "GlobalAutoincrement"
> WHERE event_condition('Rem
ainingValues') < 20
> AT CONSOLIDATED
> HANDLER
> BEGIN
> declare @dbID unsigned bigint;
> declare @newID varchar(100);
> DECLARE @cmd VARCHAR(500);
> SELECT db_property('GlobalD
BId') + 1 into @dbID;
> SELECT CAST(@dbID AS varchar) into @newID;
> SET @cmd = 'set option public.global_database_id = ''' + @newID + '''';
> EXECUTE immediate @cmd;
> END;

You can simplify this code somewhat:

ALTER EVENT "IncrementGlobalDBID" TYPE "GlobalAutoincrement"
WHERE event_condition('Rem
ainingValues') < 20
HANDLER
BEGIN
DECLARE @dbID unsigned bigint;
DECLARE @cmd VARCHAR(500);
SELECT db_property('GlobalD
BId') + 1 into @dbID;
SET @cmd = 'set option public.global_database_id = ' +
CAST(@dbID AS VARCHAR);
EXECUTE IMMEDIATE @cmd;
MESSAGE 'Setting new dbID with [' || @cmd || ']';
END;

Since you are using MobiLink and not SQLRemote, you can also forget the
AT clause.

Note that events fire on their own separate connections, unlike
triggers. So if you have multiple simulatneous threads doing inserts
that cause the event to fire, it may fire many times each in their own
connection, in their own transaction. So you should likely add code to
your event such that any simultaneous executions of the event block on
some common resource (e.g. update a particular row).

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

2005-05-27, 8:23 pm

I haven't figured out how to write a TYPE
"GlobalAutoincrement" event myself, so I have some
questions:

(1) This event is intended to run at all the *remote*
databases, correct?

(2) It would only be included on a consolidated database if
it used ASA, correct?

(3) Wouldn't the technique of simply adding 1 to this
database's GlobalDBId be rather dangerous? Shouldn't it try
to pick a value that is not currently being used by some
other database?

(4) Shouldn't the MESSAGE statement include TO CONSOLE,
since events have no "clients" to send messages to.

Breck

> Brent Williams wrote:
>
>
> You can simplify this code somewhat:
>
> ALTER EVENT "IncrementGlobalDBID" TYPE
> "GlobalAutoincrement" WHERE
> event_condition('Rem
ainingValues') < 20 HANDLER
> BEGIN
> DECLARE @dbID unsigned bigint;
> DECLARE @cmd VARCHAR(500);
> SELECT db_property('GlobalD
BId') + 1 into @dbID;
> SET @cmd = 'set option public.global_database_id = '
> +
> CAST(@dbID AS VARCHAR);
> EXECUTE IMMEDIATE @cmd;
> MESSAGE 'Setting new dbID with [' || @cmd || ']';
> END;
>
> Since you are using MobiLink and not SQLRemote, you can
> also forget the AT clause.
>
> Note that events fire on their own separate connections,
> unlike triggers. So if you have multiple simulatneous
> threads doing inserts that cause the event to fire, it
> may fire many times each in their own connection, in
> their own transaction. So you should likely add code to
> your event such that any simultaneous executions of the
> event block on some common resource (e.g. update a
> particular row).
>
> 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-05-30, 9:23 am

Breck Carter wrote:
>
> (1) This event is intended to run at all the *remote*
> databases, correct?
>

I believe in this case that Brent is running this at the consolidated
(see grandparent post to yours).

> (2) It would only be included on a consolidated database if
> it used ASA, correct?

Yes. They happen to be using ASA as the consolidated [I have "out of
band knowledge" of this application ;-) ]

> (3) Wouldn't the technique of simply adding 1 to this
> database's GlobalDBId be rather dangerous? Shouldn't it try
> to pick a value that is not currently being used by some
> other database?

Not if the only place this is being done is in the consolidated. I do,
however, concern your concern.

I believe, in this case, that the consolidated is generating a Global DB
ID starting at 1 and increasing whereas remotes are starting at MAX and
working their way backwards [again, "out of band knowledge"].

Brent, is this right?

> (4) Shouldn't the MESSAGE statement include TO CONSOLE,
> since events have no "clients" to send messages to.
>

TO CONSOLE is the default.

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-05-30, 11:23 am

On 30 May 2005 06:24:43 -0700, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:
>
>TO CONSOLE is the default.

Doh! :)

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

Sponsored Links