Skip to Content
author's profile photo Former Member
Former Member

Database replication definition and primary keys

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Oct 16, 2013 at 03:45 PM

    Are you replicating ASE to ASE and if so, what is your ASE version(s)? (select @@version)

    What is your RS version? (admin version)

    Have you defined any table-level repdefs?

    Generally speaking, if you're running ASE 15.7+, RS 15.7+, and the PDB/repagent is using LTL version 740+ then the system may be able to auto-magically determine a table's primary key (ie, no need for you to create a table-level repdef). Keep in mind that there are a few other requirements, eg, PDB table must have a unique index, the existence of a table-level repdef may disable this feature depending on the repdef options and whether the RDB subscribes to the repdef.

    Soooo, to address your issue we'll need more details (eg, ASE/RS versions, existence of repdef, MSA vs warm standby vs table-level vs a mix-of-these-setups, etc).

    Add a comment
    10|10000 characters needed characters exceeded

    • Now then, remember, the question was relating to ASE 12.5. ASE 12.5 didn't *bother* forwarding pkey information (neither did ASE's 15.0 and 15.5 btw) to RS - so RS didn't have a CLUE about what columns were primary keys or not. Consequently, yes, prior to ASE 15.7 (with RS 15.7 - which is required to understand the new LTL syntax with pkey info) you needed to have repdefs to avoid "over exuberant" where clauses - sometimes ones that caused database inconsistencies (e.g. when a float/real datatype column was used).

      Did I say it wasn't tedius??? No. Yep - it was a real pain. Now, in some cases, all you needed was a "minimal repdef" in which only the pkey columns were listed in the column list and the phrase 'send standby all columns' (instead of 'send standby replication definition columns') was used. As a result, this simplified things for some people as they only had to modify the repdef if someone did something entertaining such as changing the primary key. It happens - but not often.

      Now then, the point was to use table repdefs + single database repdef + single database subscription to avoid the even nastier situation of maintaining table repdefs + individual table subscriptions......Sooooo....yeah.....even without ASE 15.7, you could reduce the workload by about 50% by using database repdefs/subscriptions vs. table subscriptions.....

      I argued for years for a simpler solution - e.g. add a command such as "check schema" which would query ASE for any changes for a given connection....but......instead they decided to do a "non-manual" method for 15.7......

      Now, what if to a non-standby - e.g. something with a different schema - sorry - no getting away from a repdef. Ditto if using a function string (yes, we actually do check the syntax of the function string variables on creation time compared to the columns in the repdef)....

      ...but also arguably, if you were using PD for this from the beginning, you could compare models to see the changes and see which repdefs you needed to change quite quickly....before things go into prod.

      Otherwise, what RS needs ( my mind) is a "check schema" command which validates existing repdef columns against table columns and notes any differences. Has been asked for as well..... Unfortunately, I am but one voice in the forest...and I also equally understand the problem engineering has with limited resources and a long long long list of enhancement requests. Tools I mentioned all make the initial creation of the repdefs grotesquely simple....maintaining them can be a bit tedious (although PD can even automate that).

      ...but you can kinda do this fairly simply on your own....soooooo....while I agree with the lack of RS tools being a real downer and rather frustrating (and I have been one of the biggest internal complainers about this), it doesn't take much to whack together some of your own to avoid the every Friday release panic..... I reaalllllyyyyyy hate repetitive tasks, myself.

      BTW - 20GB of changes producing 300GB of RS space is only partly due to where clauses....actually, RS (especially with binary packing of IBQ) can explode things easily to 4x the size in the ASE log (if that is where you measured from)... Partly because all that metadata..... Sometimes is also is the low rate of changes - we have seen cases where 1 command every second uses 128MB of disk space in RS because the timers trip on block writes before the next command arrives...resulting in only 1 command per block. Configurable, btw. Just sayin' I would be hesitant about blaming all that space usage on lack of pkeys/repdefs.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.