Is there a need to call sa_reset_identity() when re-extracting remotes?

[VolkerBarth]

...under those certain circumstances, I should say...

I just run into a strange replication issue:

We use SQL Remote 8.0.3 for a salesforce application and generate PKs with GLOBAL AUTOINCREMENT.

When extracting remotes, we use a customized reload.sql file. AFAIK, the max values for global autoincrement columns in syscolumn.max_identity are initially 0 and get updated by the according LOAD TABLE statements.

When re-extracting remotes, we use a custom tool that allows to re-use the GLOBAL DATABASE ID, which is what we usually want. Generally, that works fine.

There's only a small problem with re-organisation of sales persons:

Say, we have a sales person S1 whose data is part of remote1 using DbId 1. Consequently, the data entries for S1 are entered with PKs in the DbId 1 range.

If now S1 moves to a different sales group (and uses remote2 furtheron), and we then re-extract remote1, the data of S1 won't get extracted for remote1. As stated above, the max_identity values will be taken from the extracted data.

Now, imagine that the consolidated contains a table in which the max PK (say 1000) for the DbId 1 range was entered by S1. Then that PK value won't be extracted to remote1 and won't be used as max_identity in remote1. As a consequence, remote1 might now generate PK values for that table starting with, say, 800. Those PKs are fine in remote1 but will lead to PK conflicts when inserted in the consolidated.

Question:

How can I avoid this unwanted situation while still re-using GLOBAL DATABASE IDs when re-extracting remotes?

[VolkerBarth · Accepted solution]

FWIW, just some sample code I have found useful:

I have added a stored produre and a function to our consolidated (somewhat different to these simplyfied samples) that can be used to list the maximum values for a particular GlobalDBId and to create an appropriate script with sa_reset_identity calls.

Such a script can then easily be integrated in the reload phase, possibly by including a

READ <strFileName>;

command in the customized reload.sql file.

It runs both on ASA 8.0.3.5574 and SA 12.0.0.2566. Note: The system catalog is used with the ASA 8 base system tables - AFAIK they are preserved as compatibility views in SA10 and above.

Assumptions:

• All global autoincrement columns are defined by an user defined type (here called "DT_PK_Global_Autoincrement"), sharing one partition size.
• PK columns use an INT type (could be extended to BIGINT easily).
• All such columns are parts of the according subscriptions. Otherwise (e.g. for consolidated-only tables), one would have to filter out those tables.

Feel free to correct/comment:)

---

-- Proc lists the according column with the maximum value - based on the consolidated's current contents
-- Uses a temp table to list all columns and uses dynamic SQL to query the maximum value for the particular range.

create procedure dbo.STP_CalcMaxIdentityValuesPerRemote(in nDbId int)
result (TableName varchar(128), OwnerName varchar(128), ColumnName varchar(128), MaxIdentity int)
begin
   declare strGATypeName varchar(128);
   declare strGADefaultPrefix  varchar(128);
   declare strPartitionSizeDef varchar(128);
   declare nPartitionSize int;
   declare nMinValue int;
   declare nMaxValue int;
   declare nCurMaxValue int;
   declare strStmt varchar(1000);

declare local temporary table LT_MaxIdentity (
      TableName               varchar(128)   not null,
      OwnerName               varchar(128)   not null,
      ColumnName              varchar(128)   not null,
      MaxIdentityCalculated   int            not null,

PRIMARY KEY(TableName, OwnerName, ColumnName)
   );

-- To add: Make sure the nDbId parameter is valid for the newly built remote
   -- (i.e. tie to your user-defined remote management tables)

set strGATypeName = 'DT_PK_Global_Autoincrement';
   set strGADefaultPrefix = 'global autoincrement(';

set strPartitionSizeDef = 
      (select substr("default", length(strGADefaultPrefix) + 1)
         from sys.sysusertype
         where type_name = strGATypeName);

set strPartitionSizeDef = trim(replace(replace(strPartitionSizeDef, '(', ''), ')', ''));

set nPartitionSize = cast(strPartitionSizeDef as int);

set nMinValue = nPartitionSize * nDbId + 1;
   set nMaxValue = nPartitionSize * (nDbId + 1);
   message 'STP_CalcMaxIdentityValuesPerDbId: Expects values for DbId ' || nDbId || ' in the range [' || nMinValue || ';' || nMaxValue || '].';

-- To add: Filter out those tables/columns that are not part of the current subscriptions
   -- (i.e. tie to your user-defined remote management tables, possibly by the joining remote user name
   --  against sysarticle/syspublication/syssubscription...)
   for forCol as crsCol cursor for
         select table_name, user_name as owner_name, column_name
         from sysuserperm SUP key join systable ST key join syscolumn SC key join sysusertype SUT
         where ST.table_type = 'BASE' and SUT.type_name = strGATypeName
         order by 2, 1, 3
      for read only
   do
      set strStmt = 'set nCurMaxValue = (select max(' || column_name || ') from ' || owner_name || '.' || table_name
            || ' where ' || column_name || ' between ' || nMinValue || ' and ' || nMaxValue || ');';
      execute immediate strStmt;

-- if not even the nMinValue was used, set to the previous value
      -- -> then the first value used will be the nMinValue itself
      if isnull(nCurMaxValue, 0) < nMinValue then
         set nCurMaxValue = nMinValue - 1;
      end if;

insert LT_MaxIdentity
         values (table_name, owner_name, column_name, nCurMaxValue);
   end for;

select * from LT_MaxIdentity
   order by 2, 1, 3;
end;

-- Call the above STP for the given GlobaDBId to write out the fitting sa_reset_identity calls to the file specified by strFileName
-- Returns 1 if successful, otherwise 0
create function FKTN_ExportMaxIdentityValuesPerRemote(nDbId int, strFileName varchar(255))
returns int
not deterministic
begin
   declare nReturn int;
   declare strContents long varchar;

declare local temporary table LT_Contents (
      pk_Line  int not null default autoincrement,
      Line     long varchar default '',
      PRIMARY KEY(pk_Line)
   );

set nReturn = 0;

begin
      insert LT_Contents (Line) values ('');
      insert LT_Contents (Line) values ('-- Script to explicitly reset the max_identity values for a new remote.');
      insert LT_Contents (Line) values ('');
      set strContents =
            '-- Automatically generated on ' || dateformat(current timestamp, 'yyyy-mm-dd')
         || ' at ' || dateformat(current timestamp, 'hh:nn:ss')
         || ' with database version ' || @@version || '.';
      insert LT_Contents (Line) values (strContents);
      insert LT_Contents (Line) values ('');

begin
         for forList as crsList cursor for
            call dbo.STP_CalcMaxIdentityValuesPerRemote(nDbId)
         do
            set strContents =
                  '   call dbo.sa_reset_identity(''' || TableName || ''', ''' || OwnerName
               || ''', ' || MaxIdentity || ');';
            insert LT_Contents (Line) values (strContents);
         end for;
      end;

insert LT_Contents (Line) values ('');
      insert LT_Contents (Line) values ('   commit;');
      insert LT_Contents (Line) values ('');
      insert LT_Contents (Line) values ('-- End');
      insert LT_Contents (Line) values ('');

-- dynamic SQL because of variable file name (unnecessary for ASA 9.0.1 and above).
      -- "Escapes off" is necessary to handle the comma in the sa_reset_identity call as such.
      execute immediate 'unload select Line from LT_Contents order by pk_Line
         to ''' || strFileName || ''' quotes off escapes off format ascii';

set nReturn = 1;

exception
         when others then
            message errormsg(sqlstate) type warning to client;
         set nReturn = 0;
   end;

return nReturn;
end;

---

-- Call example
select FKTN_ExportMaxIdentityValuesPerRemote
    (21, 'C:\\\\MyDb\\\\Reload\\\\ResetIdentityScript.sql');

Generates a file like:

-- Script to explicitly reset the max_identity values for a new remote.

-- Automatically generated on 2010-10-06 at 14:28:51 with database version 12.0.0.2566.

call dbo.sa_reset_identity('MyTable1', 'dbo', 4200606);
   call dbo.sa_reset_identity('MyTable2', 'dbo', 4200138);
...
   call dbo.sa_reset_identity('MyTablen', 'dbo', 4200092);

commit;

-- End

[VolkerBarth · Accepted solution]

Just to add: The sample could need some more comments, I guess, but for the moment, I'd like to publish it AS IS...

[VolkerBarth · Accepted solution]

Another addition: I used a local temp table to gather the string ouput (instead of concatenating lines and use xp_writefile()) simply because the unload select statement seems to be the only one to handle newline platform-independent, cf. that question: http://sqlanywhere-forum.sap.com/questions/1127.

[VolkerBarth · Accepted solution]

After studying several newsgroup threads (and finding it sooooo hard to search for several keywords), the following seems to be a solution:

(cf. the thread "Inconsistent autoincrement behavior" in the general newsgroup, particularly Breck's answer from 24 Oct 2007)

As part of the customized re-extraction, one could check for the maximum PK value in the particular GLOBAL DATABASE ID range of all consolidated tables that get extracted.

Then one could include sa_reset_identity() calls in the reload.sql script (after the LOAD TABLE parts) and set the max_identity columns accordingly.

That way it can be guaranteed that the remote won't use PK values that it has been generated before, even if the relevant rows don't "belong" to that remote anymore.

[Former Member · Accepted solution]

speaking of soooo hard to search the newsgroups.... is that port coming anytime soon?

[VolkerBarth · Accepted solution]

@Jen: I don't know, I'm just an ordinary SQLA user. You might want to post that as a separate question here...