Skip to Content
avatar image
Former Member

Replicating identity columns from ASE to IQ

Dear Team,

This is a continuation of the below archived discussion as no solution was provided:

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.



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Nov 30, 2016 at 05:34 PM

    *arg* system ate my first response, so not going to spend a lot of time on this one ...

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

    2 - what do you mean by 'under certain scenarios', ie, do inserts replicate successfully into IQ on some occasions?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 04, 2016 at 06:31 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • I don't work with IQ so my tests are 100% ASE (ASE -> WS -> ASE; WS[logical conn] -> MSA -> ASE).

      With very simple testing (normal insert; forced insert of identity column; forced update of identity column) my identity values get replicated properly as identity (WS) and numeric (MSA). So, basic, functional testing shows no problems; obviously I'm not able to emulate your EOD processing (either logic or volume wise).

      For your particular case I'd want to:

      - log the problematic txn to the exceptions log, then review the exception; in particular I'd want to verify what repdef the rs_insert function is referencing; I'd also want to verify the @origin value refers to your WS/active db's dbid (rs_databases.dbid); objective is to verify the source of the data and the repdef SRS thinks should be used; if the repdef and source db are correct then I'd suggest you've found a bug; if the repdef and/or source is incorrect then I'd want to perform a thorough review of your SRS environment with emphasis on any components (articles, pubs, repdefs, subs, function strings) that relate to the table in question to see if there's an explanation for why the wrong repdef and/or source db is coming into play

      - I'd also want to take the offending row and try to track it back through the EOD processing; objective being to see if there's some sort of special processing being called for that particular row, something that could give a hint as to why the problem is occurring (and ideally something that could be used in a reproduction)


      For an intermittent issue I'm guessing either a) an extra/unwanted configuration (eg, old subscription w/ a where clause that only fires occasionally) or b) a bug with SRS (which would require opening a case with tech support).

  • avatar image
    Former Member
    Dec 07, 2016 at 01:10 PM

    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




    Add comment
    10|10000 characters needed characters exceeded

    • Looking at the exception I noticed the following:

      insert #1 : 34 columns, ser_no = 11000000002064931, log_dt = 20161207 09:58:47:233
      insert #2 : 44 columns, ser_no = 11000000002184464, log_dt = 20161207 09:58:47:280
      insert #3 : 44 columns, ser_no = 11000000002184465, log_dt = 20161207 09:58:47:306
      update #1 : 33 columns updated (sans ser_no), ser_no = 11000000002064931, log_dt = 20161207 09:58:47:233


      1 - How did the system generate such a large gap between the ser_no values for inserts #1 and #2, while #2 and #3 are sequential (as expected)?

      2 - Why does insert #1 only show 34 columns, while inserts #2/#3 show 44 columns? [NOTE: The repdef lists 44 columns.]

      3 - What did the original transaction actually look like in the PDB? [Wondering if the *real* txn was 'insert #2 + insert #3 + update #1', but for some reason SRS turned the update #1 into 'insert #1 + update #1'? Wondering if this particular txn is an exception to normal processing?]

      4 - Are you by any chance running RTL or HVAR?

      5 - update #1 updates 33 columns ... the same set of columns (sans ser_no) as listed for insert #1; 6 of the columns make up the primary key (and 'where' clause) and were 'set' to their same value, so I'm guessing you've got replicate_minimal_columns disabled/off [Wondering what would happen if you enabled replicate_minimal_columns for the IQ connection?]

      6 - Did these same 3 rows get to the WS/standby database with the same values? Any chance you were able to capture (eg, from monSysStatement; save interval on the SRS queue + sysadmin dump_queue) the same exact transaction going into the WS/standby database? [Wondering if the same 4x commands were issued against the WS/standby db?]


      There's something wrong with insert #1 (only 34 columns in the insert, large gap in identity values) ... wondering if insert #1 is some sort of ghost transaction introduced in support of update #1 ... but don't know if this is 'normal' for replication into IQ (eg, could RTL/HVAR be introducing this?). I'm thinking whatever the issue is with insert #1, it is somehow tied to the incorrect issuance of the 'set temporary option identity_insert' commands.

      Does the PDB process perform a lot of transactions like this exception (ie, insert #1, insert #2, insert #3, update row associated with insert #1)? Or perhaps a transaction that looks like : insert #2, insert #3, update row associated with older insert? Do you have an environment where you could test a few different transaction constructs to see if you can reproduce the issue?

      Have you opened a case with tech support?

  • Nov 16, 2017 at 09:19 PM

    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.


    Add comment
    10|10000 characters needed characters exceeded