Synchronize statement

Questions
1

The synchronize statement works indeed very well with insert, removing the technical identity column rowid$ from the insert logic.

But unless if I’m wrong I’m having difficulties with synchronizing from an @inmemorystorage table into an @sql container with an insert or update clause.

Invantive Query Tool reports the error:

itgenclr039:
One or more errors occurred.
Impossible to update identity column ‘rowid$’.

I have also described my subject here.

I am asking myself may be there is something wrong in Query Tool logic on a:

synchronize 
to          account_invoice@inmemorystorage
from        account_invoice@sage_sql2019_vega_replication
with        insert or update
identified
by          id

in regards with the rowid$, Query Tool trying to update identity column whereas it should not.

Capture d’écran 2021-03-31 à 23.28.45
Capture d’écran 2021-03-31 à 23.28.451158×1360 67 KB

2

The question is not completely clear yet on how to reproduce the problem, but maybe you are referring to the need to exclude the column rowid$ with identity-generated values from the update-statement fired by the Invantive synchronize-statement.

This can be done by adding except to the update-clause as in for instance:

synchronize target@targetdc
from        source@inmemorystorage
with        insert
            or
            update all except created_by, created_at, date_created, session_created, uid
            ignore changes to modified_by, modified_at, date_modified, session_modified, created_by, created_at, date_created, session_created, uid
identified
by          column1
,           column2

In this sample, the update does not consider a row changed when only changes are found in the listed columns:

  • modified_by
  • modified_at
  • date_modified
  • session_modified
  • created_by
  • created_at
  • date_created
  • session_created
  • uid

It also only tries to update the row for columns not in the list of:

  • created_by
  • created_at
  • date_created
  • session_created
  • uid

Effectively this can be used to provide both create and modification audit-information on initial insert, and only modification audit-information on subsequent updates.

The uid column is some kind of unique value, so might resemble your need with rowid$.

3

Sorry for not being clear at that time of this ticket.

In fact the issue is solved by using the except rowid$ in the statement.

At that time it was the first time I was using this kind of workflow:

  • using Invantive to execute and store some values in a @inmemorystorage table, thus a rowid$ column is automatically added by Invantive
  • then use the synchronize with insert statement to update a @sql table; itself also containing a rowid$ column created by Invantive SQL.

So when

  • Source table has not been created by Invantive SQL, synchronize does not require to add the except rowid$,
  • Source table has been created by Invantive SQL, synchronize requires to add the except rowid$.
4

Yes, that seems like a great approach!