0

I am trying to do replication from Sybase ASE 15.5 to Sybase ASE 15.5 for a list of given tables via replication definitions and subscriptions in Replication Server Version 15.7.1. I found there is a table which one of the columns is defined as IDENTITY. After creating the replication definition and subscription for that table, I am getting the below error messages in replication server:

Message from server: Message: 584, State 1, Severity 16 -- 'Explicit value specified for identity field in table 'customers' when 'SET IDENTITY_INSERT' is OFF'.

I have tried to run 'SET IDENTITY_INSERT customers ON' on both of my primary and replicate database, but no luck.

Appreciate if you could help. Thanks!

|improve this question
0

Assuming your primary/replicate tables were create like such:

create table mytable
(id     int           identity
,name   varchar(100)
,dob    date
... snip ...

And assuming you want to replicate identity values from the primary table to the replicate table, you need to tell SRS that the id column is an identity column, and the way you do this is by designating the id column as an identity in the repdef, like such:

create replication definition mytable_repdef
with primary at ...
with all tables named mytable
(id     identity             <<<=== use 'identity' instead of the actual datatype
,name   varchar(100)
,dob    datetime
... snip ...

By designating the id column as an identity column in the repdef, SRS should now enclose all INSERTs (sent to the replicate database) in a set identity_insert on/off wrapper, and UPDATEs should be wrapped in a set identity_update on/off wrapper.

|improve this answer
  • Thanks @markp. It works for ASE to ASE. Currently, I'm setting the same tables with same structure (with IDENTITY column) to Oracle (replicating from ASE to Oracle). I hit the SRS error 'ociStmt::Execute ORA-00922: missing or invalid option'. Do you have any idea on this? – Tittus Mar 5 '18 at 10:48
  • I haven't tried replicating the ASE identity attribute to an Oracle sequence ... not even sure SRS can handle that; when the DSI goes down you can dump the problematic txn (sysadmin dump_queue) to see what command SRS is passing to Oracle, which I'm assuming is going to be an invalid command in Oracle ... – markp Mar 5 '18 at 13:14
  • Thanks @markp. I have tried to dump_queue to check what command is running, but they are those common insert, update, delete statements, which should not cause the ORA-00922. I also see there are some command to insert value into INDENTITY value from ASE <ex. set identity_insert customer on insert into customer (id, customer_name) values (1, 'Jerry')set identity_insert customer off > Not sure if this command goes wrong in Oracle? Since the dump_queue from replication is only showing what transactions are executing in ASE, how am I suppose to check which command is invalid in Oracle? – Tittus Mar 6 '18 at 2:04
  • The key is the part about set identity_insert customer on insert ... as that's part of the command ASE is sending to Oracle, and since set identity_insert is not a valid command in Oracle then that is likely the cause of the error the DSI is receiving. If you're trying to replicate the same ASE table to both ASE and Oracle then you'll need to create 2x repdefs ... one for ASE w/ column defined as identity, one for Oracle w/ column defined as int... and it's important you create the ASE/identity repdef before you create the Oracle/numeric repdef; then you'll need 2 diff subs – markp Mar 6 '18 at 2:10
  • Did you mean the structure to handle this IDENTITY column replication should be ASE1 --> ASE 2 (with column defined as identity), then from ASE1 --> Oracle (with column defined as int)? But how can I resolve my DSI error? I could not even drop the subscription/repdef of the particular table. – Tittus Mar 6 '18 at 2:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.