Synchronize statement

[sda]

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.

[forums]

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

[sda]

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

[forums]

Yes, that seems like a great approach!