Sybase replication definitions in warm standby setup

[Former Member]

Hi,

I 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 another (third) ASE 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.

However, RS is always using it for WS and below the tests i have done:

On the primary database:

create table dbo.repdef_ws (a int identity, b int, c char(1))

go

alter table repdef_ws add    constraint lon_cst_x primary key  ( a, b )

go

sp_setreptable repdef_ws,true,owner_on

go

insert into repdef_ws values(1,'a')

insert into repdef_ws values(2,'a')

insert into repdef_ws values(1,'b')

insert into repdef_ws values(2,'c')

insert into repdef_ws values(2,'c')

On RS:

create replication definition repdef_ws_repdef

with primary at LDS.sun_prod_db

with primary table named dbo.repdef_ws

with replicate table named SCB.repdef_ws --owner on the reporting database is SCB not dbo

(

  "a" int ,

  "b" int ,

  "c" char(1)

)

primary key (a, b)

go

--I didn't use "send standby" clause

--the replication definition is created without specifying identity for columns a on purpose as the reporting database will not have identity

i have also tried the below with no luck:

alter replication definition repdef_ws send standby off

go

rs_helpexception shows that the repdef is still used for the WS:

A0159 04utf8distribute 2 ~"!,3 41 applied _st = 1024 ~!1repdef_ws_repdef.~!*rs_insert yielding after ~$"a 1 =~\"3, ~$"b 1 =~\"2, ~$"c=~""c

insert into dbo.repdef_ws (a, b, c) values (3, 2, 'c')

A0159 04utf8distribute 1 ~";Jul 28 2016 12:40:44:106AM,2 ~"!,3 1 commit transaction    

Is there anything that i am missing? also the owner specified is different from the owner on the standby database.

How can I avoid using the repdef for WS and only use it with the subscriptions for the reporting database?

Thanks,

Bilal

[former_member285566]

Can you try to create two replication definitions. The first repdef is for WS and the second repdef is for the reporting database.

The first repdef:

create replication definition repdef_ws_repdef_first

with primary at LDS.sun_prod_db

with  all tables named dbo.repdef_ws

(

  "a" identity ,

  "b" int ,

  "c" char(1)

)

primary key (a, b)

send standby all columns

go

The second repdef:

create replication definition repdef_ws_repdef_second

with primary at LDS.sun_prod_db

with primary table named dbo.repdef_ws

with replicate table named SCB.repdef_ws --owner on the reporting database is SCB not dbo

(

  "a" identity map to int ,

  "b" int ,

  "c" char(1)

)

primary key (a, b)

go

In primary database

sp_setreptable repdef_ws, true, owner_on

go

You create the subscription for the second repdef for reporting database.

This works in my test.

Regards,

Binh

[Former Member]

Hi Hunter,

Thanks for the feedback, i previously tried creating another repdef without identity and received the below, but your solution(with map to identity) seems working fine now!

create replication definition repdef_ws_repdef2

with primary at LDS.sun_prod_db

with primary table named dbo.repdef_ws

with replicate table named SCB.repdef_ws

( "a" identity ,

  "b" int ,

"c" char(1))

primary key (a, b)

send standby replication definition columns

go

Msg 15336, Level 12, State 0:

Server 'REP':

The datatype of column 'a' was not specified by other replication definitions as an IDENTITY column.

Regards,

Bilal

[Former Member]

Hi Hunter, all

I am still facing a problem with the above workaround.

The second replication definition was created successfully, but i had to map the identity column to numeric instead of int as RS generated an error while converting to int.

Now the problem is, even with the second repdef created with identity map, RS is still sending "set identity_insert on" to ASE and the connection it going down as ASE doesnt have the column defined as identity.

Any workaround that will deny RS from sending 'set insert_identity on'?

Regards,

Bilal