I need help regarding 2 way replication with DB2. If some one is familiar about this, please let me know. We have lot of identity columns which needs to be a part of replication. If you could help me figure out how two databases can be in synch with identity values. And we issue some unique keys. Based on time and date, those keys need to be in synch too. We should not by any chance give the same key in 2 databases.
I must say, 2 way replication ain't easy thing! I think there are only these options:
1) Pseudo-2-way replication: Table A is updated in database D01 and there are table A' replicated read-only copy in database D02. And for table B (in database D02) there are RO-copy B' in database D01.
2) True-2-way replication: the key must include the database id:
- KEY(DATE, TIME, IDENTITY_ID, DATABASE_ID)
This way you may have same date+time and even identity, but then database will be different.
If you are replicating Identity columns using the Update-Anywhere (Replica) scenario, you need to do the following things.
1) the Identity Columns MUST be "Generated by Default" instead of "Generated Always". This is the only way to keep the actual Identity value that was originally created when the row was created. Replication, as a whole, does not work with any column that is "Generate Always", even non-Identity Columns.
2) Use the "ALTER TABLE ALTER COLUMN identcol RESTART WITH xxx command to put each server generating values in different ranges. e.g. Server A start at 0, Server B start at 100,000,000, Server C start at 200,000,000, etc.) The starting values would be determined on how you expect the number of rows generated to be inserted.
Identity with 2 way replication - DB2 Reply to Thread
Thanks for the replies. As of now all the identities are "Generated always" , if i replace them with "generated by default", I read in one manual that it doesnot give guarantee that the values generated would be unique. But generated always will always give unique values.
In generated always, database will take care and guarantees the uniqueness of the identity column, in default application needs to make sure that it generates the values unique.
You cannot have "Generated Always" columns in Update-Anywhere Replication. Server A inserts a row That generates value 121. You cannot insert that value int Server B because it wants to generate it own value, the 121 would be lost (actually the insert fails and no data is replicated).
I have not used sequences yet, it may work OK, as long as the value for the sequence can be supplied and not generated always, otherwise you are stuck like above.
I am planning to use Generated by default. No values passed to it from app, so that it behaves like generated always. It will have primary key on the identity column. And continue with ranges to help replication.