Database Setup

How do I convert my existing database for use with FBReplicator?

First off, take a backup of your production database and restore it where we can get to it. You can make all the modifications on this backup first. When you’re happy, we can do a database compare and run the modification into the production version later.

To convert your existing database you’ll first need to have integer PKs on all of the tables you wish to replicate. If you have Int32 data types, you can change them to Int64 if you wish, but Int32 is still valid and will work successfully with FBReplicator. Even SmallInt will work just fine.

If you have non integer PKs on these tables or composite PKs, you’ll need to change them. Maybe you can make them unique constraints only instead of PKs. We’ll have to talk later about the impact of unique constraints on replication. You then need to populate the new “PK”. You’ll need to create a generator to populate this new field, something like

  • Create the field and declare it NOT NULL,
ALTER TABLE CUSTOMER
ADD MYNEWREPLKEY BIGINT
NOT NULL;
  • Set it’s current value to 0 for the moment.
UPDATE MYTABLE SET MYNEWREPLKEY=0;

(IBExpert and other tools will automatically add this DML statement for you but you’ll need to keep it for later)

  • Now create a generator and set it to 0
CREATE GENERATOR GEN_MYNEWGEN;
SET GENERATOR GEN_MYNEWGEN TO 0;
  • Now update the table as follows:
UPDATE MYTABLE SET MYNEWREPLKEY=(SELECT GEN_ID(GEN_MYNEWGEN, 1) FROM RDB$DATABASE);

(keep this DML statement for later since a metadata compare will not include this statement).

  • Commit your changes and now add a unique constraint
ALTER TABLE MYTABLE ADD CONSTRAINT UNQ_REPLFIELD UNIQUE (MYNEWREPLKEY)

Now we can decide how many replication stations you wish to create. Let’s assume here that 10,000 stations would fill your needs by a long shot.

So now you need to change all your triggers on tables you wish to replicate so their before insert trigger call their associated generator with a 10000 gap instead of the likely gap of 1 set currently. e.g. new.MYNEWREPLKEY = gen_id( GEN_TABLEGEN, 10000 );

Of course your client application also needs to have it’s code changed so that where ever it makes direct calls to obtain a generator value, it now calls for it using the 10000 gap instead of the one gap. Both IBX and IBO components allow for this. Some applications do not make this call at all. If you add REPLKEY fields to each table,

Now it’s time to realign your PKs or REPLKEY fields. To lift the value of the existing field which most likely started at 0, we need to add some 0s to the end of the current values.

  • On each table you’ll need to do this:
UPDATE TABLENAME SET MYPK=CAST(CAST(MYPK AS VARCHAR(5))||'0000' as numeric(18,0));

Here, the 5 stands for the number of significant digits in your already populated PK field. So, for example, if the maximum value of your PK in this table is 956,876, then you would need to use 6 instead of 5.
If you did this:

UPDATE TABLENAME SET MYPK=CAST(CAST(MYPK AS VARCHAR(5))||'0001' as numeric(18,0));

You would be setting this copy of the database up as station 1, leaving it as 0000 would be making it the master target. I’m sure you can decide how you want them all numbered.

You also have to make sure that your referential integrity is maintained. If you have set your cascading updating correctly, the foreign keys in all tables which are dependent on this PK will be updated. If not you will need to do it manually. You could also turn your constraints off for this job too. Either way you need to do:

UPDATE MYTABLE SET OTFK=CAST(CAST(OTFK AS VARCHAR(5))||'0000' as numeric(18,0));

or ‘0001’ is you chose to make station 1.

Now all your tables have integer PKs and all PKs and foreign keys have been scaled up correctly.

The last step if to set generator value for this station. If it’s station 1 and you have scaled up to 9,567,860,001 as your maximum PK for a table, then set the generator for this table to that figure

SET GENERATOR GEN_MYNEWGEN TO 9567860001;

You need to do this for each table.

You now have station one. To create station two anytime you want or station 3 sometime down the track, just backup and restore a copy of the database and reset all the generator values to 1000n where n is the new station number.

SET GENERATOR GEN_MYNEWGEN TO 10003;

will create station 3. The next new record in this table will have a PK of 20003 and so on.