Hello,
We're using ASA 9.0.2.3557. We have 2300 remote databases
synchronizing to an Oracle consolidated. If a table with an
autoincrement column is purged prior to doing a full rebuild
of the database, the identity value resets to 0. Had we not
done an unload/reload, it would have continued from where it
left off. The following steps reproduce the problem.

dbinit -n d:\test.db
create table test(col1 integer default autoincrement, col2
integer);
insert into test (col2) values (1);
insert into test (col2) values (1);
insert into test (col2) values (1);
insert into test (col2) values (1);
commit;
delete from test;
commit;
dbunload -c uid=dba;pwd=sql;dbf=d:\test.db -r d:\reload.sql
d:\
del /f d:\test.db
dbinit -n d:\test.db
read d:\reload.sql
insert into test (col2) values (1);
commit;

Following these steps, you would find that the "test" table
now contains one record (1,1) instead of (5,1). However, if
instead of deleting all records, you do a "delete from test
where col1 < 4;" The new table will continue to
autoincrement at 5 as expected.

It seems during reload.sql that it somehow resets the
identity to the largest value in the table instead of
persisting the autoincrement value through the unload.

This is a problem in our environment because we synchronize
using Mobilink. We have a table with updates and inserts
synchronized up. There is a purge on the remote to remove
anything over 90 days which, in this case, removed all
records. The database had a problem requiring an
unload/reload. Since the autoincrement restarted at 1, we
now get a primary key violation when we try to synchronize
that table.

Is this expected behavior? Why isn't the identity value
stored in a table (like systable) so that it can be
retrieved after an unload/reload or truncate?

Thanks,
Andy

Andy,

I would think that this value is saved in a system table. But system
tables are not directly part of the reload process. Instead, they are
rebuilt indirectly by the creation of the "standard" tables. As rebuilding
is many times used to kind of clean up a database, I would imagine this is
intended behaviour.

You did not write why you delete all data before the reload, and for what
purpose you do the reload at all. So I ma not sure what would be the best
solution for your case.
But the way you describe would be one possible solution: Just use
something like

delete from test where col1 < (select max(col1) from test)

instead of a pure delete. You could then even delete this value after the
reload in the new database without losing the information of the next
autoincrement value.

Frank

Andy, in addition to what Frank wrote:

If you notice that your autoincrement values are improper, you can use
sa_reset_identity() to set a proper new start value.

I know that this does not answer your question but it may help to cope with
the result

HTH
Volker


"Frank Ploessel" <fpl... (AT) d_e (DOT) i_m_s_h_e_a_l_t_h.c_o_m> wrote in
newsp.t0o38fxhj0bybf (AT) bonw00164 (DOT) internal.imsglobal.com...

That is a behavior which adversely affects both AUTOINCREMENT and
GLOBAL AUTOINCREMENT columns in a synchronizing environment.

When you recreate a remote database from scratch, and reload it with
data, if the data being reloaded doesn't "push" the auto-incrementing
columns back up to their old maximum values, AND it is important to
you that the old maximums be preserved, it is up to you to do that
with sa_reset_identity. You have found one scenario where that
happens; there are others, all of them annoying.

The maximum value is stored in SYSCOLUMN.max_identity which is not
unloaded by dbunload nor restored via reload.sql. The problem isn't
that something is "resetting" the value to zero, the problem is that
nothing in the reload.sql is restoring the value. That is by design.

Let's put it another way: Your reload.sql contains a CREATE TABLE but
no INSERTs. Most folks would *expect* that the first INSERT after that
would create a primary key value of 1, not 5, in that scenario. It is
your (human) knowledge of your requirements that says otherwise. You
are not wrong, but neither is SQL Anywhere.

If you're going to do this process repeatedly, it should be possible
to construct two scripts to run before and after the process: One to
"unload" SYSCOLUMN.max_identity values that need to be preserved, and
another to call sa_reset_identity. Like dbunload, the first script
could *generate* the second script.

Breck

On 23 Oct 2007 10:41:20 -0700, Andy wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com