Replicating identity columns from ASE to IQ

[Former Member]

Dear Team,

This is a continuation of the below archived discussion as no solution was provided:
https://archive.sap.com/discussions/message/16863364

In brief, we have a Warm Standby setup from ASE to ASE configured with the following versions, all on solaris sparc:

ASE primary: ASE 15.7 SP122

RS: 15.7.1 SP301

ASE standby: ASE 15.7 SP122

I am trying to create a replication definition for a table to be replicated to an IQ server that will be used for reporting purposes. I don't want this replication definition to be used in the WS setup, i just need it for the reporting database. The difference is that the table in ASE primary and standby have an identity column, while in IQ it doesn't have.

For that, we have created 2 replication definitions as follow, and we have mapped the identity columns to numeric in the second one:
The first repdef:

create replication definition ATT_EMP_TRANSACTIONS_repdef

with primary at LDS.sun_prod_db

with all tables named dbo.ATT_EMP_TRANSACTIONS

( "TRANS_SER" identity

, "EMP_SER" numeric, "TRANS_DATETIME" datetime , "TRANS_DATE" datetime , "CARD_ID" numeric, "REDAR_NO" numeric, "TRANS_TYPE" numeric, "TRANS_ENTRY_TYPE" numeric, "IMPORT_NO" numeric, "ADD_DT" datetime , "ADD_US" char(8), "UPD_US" char(8), "UPD_DT" datetime , "CNF_US" char(8), "CNF_DT" datetime , "DEL_US" char(8), "DEL_DT" datetime , "DEL_FLG" char(2) ) primary key (TRANS_SER) send standby all columns go

The second repdef:

create replication definition ATT_EMP_TRANSACTIONS_repdef_iq

with primary at LDS.sun_prod_db

with primary table named dbo.ATT_EMP_TRANSACTIONS

with replicate table named SCB.ATT_EMP_TRANSACTIONS (

"TRANS_SER" identity map to numeric,

"EMP_SER" numeric, "TRANS_DATETIME" datetime , "TRANS_DATE" datetime , "CARD_ID" numeric, "REDAR_NO" numeric, "TRANS_TYPE" numeric, "TRANS_ENTRY_TYPE" numeric, "IMPORT_NO" numeric, "ADD_DT" datetime , "ADD_US" char(8), "UPD_US" char(8), "UPD_DT" datetime , "CNF_US" char(8), "CNF_DT" datetime , "DEL_US" char(8), "DEL_DT" datetime , "DEL_FLG" char(2) ) primary key (TRANS_SER) go

The subscription to the IQ server is pointing to the second replication definition.

The problem is that, under certain scenarios executed on the primary server that we are not able to identity, RS is still sending "set temporary option identity_insert" to the IQ server even though the replication definition has a mapping of the identity. and this is resulting in the connection going down.:

E. 2016/10/18 20:16:58. ERROR #1028 DSI EXEC(211(1) SAPIQ.sapiq) - neric/dsi/dsiqmint.c(4878)

Message from server: Message: -1000292, State 0, Severity 14 -- 'SQL Anywhere Error -1000292: Invalid use of set option 'identity insert'. Table ATT_EMP_TRANSACTIONS does not have an Identity/Autoincrement Column.

-- (dblib\db_Identity.cxx 166) '.

i have rebuilt the setup many times, and made sure to create the repdefs in the above specified order with no luck.

Is it possible to modify the related function string so that RS doesnt use option 'identity insert' at all when replicating to IQ?

Appreciate your asssitance.

Regards,

Bilal

[former_member89972]

Just for the heck try following :

In the ASE to ASE repdef declare identity column as

"ser_no" numeric map to identity (seems illogical, but try it please)

And in the ASE to IQ repdef

"ser_no" numeric

I had faced similar issue while replicating to Oracle

Spurious "set identity_insert table_name on" were noticed.

And I tried above workaround.

Avinash

[former_member89972]

Hi Bilal

Did you get a chance to try my suggested workaround ?

Avinash

[Former Member]

Hi Marc,

Thanks for the reply. Basically the issue shouldn't have anything to do with IQ but mainly why RS itself is sending "set option 'identity insert'" in the first place.

There is no possibility of old subscriptions, i have dropped the environment 2 days ago, installed the latest RS version 15.7.1 SP304, and completely dropped the RSSD database before that. there are no old subscriptions or repdefs that could lead to that issue.

The rs_helpexception shows the correct repdef and the dbid of the active database..

I am attaching:

-admin who

- repdef created for the WS system

-repdef created for the IQ system

-subscription to IQ

-rs_helpexception output

-REP server error log

admin-who.txtreplog.txtrepdef-for-iq.txtrepdef-for-ws.txtrs-helpexception.txtsubscription-to-iq.txt

Regards,

Bilal

[Former Member]

Hi Mark,

I didn't get your first statement.

1- Yes identity is being replicated to WS/standby successfully

2- Yes inserts are continuously working fine but we have no idea why at certain time it stops working, there is an EOD process that runs similarly every day, and after our last IQ resync, it worked fine for 8 days(with 8 EODs run) and then suddenly the DSI went down, as RS sent "set temporary option identity_insert" to the IQ server where the table doesn't have identity.