Database replication definition and primary keys

[Former Member]

I have noticed that database replication definitions created using the following method:

create database replication definition db_repdef
    with primary at <server>.<db>

do not respect primary keys defined in the primary tables.

The where part has all columns of a specific table, which is not good in terms of performance.

I there any way in order for the database replication defininition to achieve this?

If not, I suppose the solution is to have separate table replication definitions, and exclude those from the db rep def?

[Former Member]

Primary: Adaptive Server Enterprise/12.5.4 (also RSSD DS)

Secondary: Adaptive Server Enterprise/12.5.4

Repserver:  15.2

So, If I upgrade to Repserver 15.7, will I get all the above goodies?

No table-level repdefs exist.

Database repdef (msa):

-- Create Database Replication Definition db1_repdef

create database replication definition db1_repdef

    with primary at PDB.db1

    replicate DDL

    not replicate tables in (t1, t2, t3)

    replicate functions

    replicate transactions

    not replicate system procedures in (*.sp_dropuser,*.sp_recompile)

Thanks!

Message was edited by: Mihalis Miliarakis

[Former Member]

I just found this, which I was trying to find all day!

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32511.1571100/doc/html/je...

So I suppose this is the answer to my problem?

[former_member182259]

Not as long as you are using ASE 12.5.x.   What Mark was referring to is only available with ASE 15.7 ESD #1 and higher along with RS 15.7.1 and higher.

However, I am not sure I understand part of your question.    First of all, without repdefs, RS knows nothing about which primary keys or indexes exist.   That is why we have LONG LONG LONG advocated the use of repdefs even with database repdefs (they are not mutually exclusive!).   In ASE 15.7, the RepAgent thread now adds primary key and quoted column info to the LTL stream so that later versions of RS that support that LTL version can find out that information without needing a repdef.    Since you are ASE 12.5.x.......you need to use repdefs.

Now, then, in your original post, you then said:

"....If not, I suppose the solution is to have separate table replication definitions, and exclude those from the db rep def?..."

This is strange.   Nowhere that I am aware of has it ever been taught that the two are mutually exclusive - as MOST places do both table repdefs AND database repdefs - but then a single database subscription (vs. table subscriptions).   The table repdefs are there primarily to identify:

a) primary key columns

b) quoted column names

c) minor datatype translations

d) column name differences

e) table name/owner differences

f) referential integrity rules when using ASO option (e.g. HVAR)

g) column exclusions

....most of the time, the chief reason is (a) and most definitely for performance at the replicate database.   A WHERE clause that has a ton of columns takes longer for ASE (or any DBMS) to process - especially if there are large varchar fields (note that text/image columns were never used in the where clause when RS constructed one without pkey info).

There are a ton of utilities out there (including PowerDesigner), shell scripts from RS admin classes, stored procs many have developed, etc. that can help you generate the necessary repdefs enmass very quickly (e.g. in a few minutes) with minimal typing on your part.   If you need further help with this, send a private email.

As far as upgrading - since all versions of both ASE and RS you are using are extremely out of date, it would be advisable to upgrade all of them.  RS 15.6 was certified with ASE 12.5.4 as was RS 15.7 despite the EOL date.....RS 15.7.1 was not certified with ASE 12.5.4 as it was considered to old to even attempt to certify....but it should work fine.   If/when you upgrade, consider moving to the eRSSD vs. using ASE - especially having the RSSD in the primary database can lead to huge RS performance issues - having the RSSD in the standby isn't any better (because guess what happens when you fail over...yep...it is now the primary).