Hi ,
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.

Thanks for ur help
venkat

Hi there!
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.

Cheers, Bill

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.

HTH
Andy

Hi,
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.

thanks again for ur help
venkat

Hi,
If i want to avoid identity, can i go ahead and use sequences and that can ensure uniqueness , the same value would not be repeated again. and simulate identity. Does this have any problems?

thanks
venkat

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.

Andy

'Generated by default' does not gurantee uniqueness because you can add a value to that column from an appln ...

assume,cola is generated by default ...
insert into tab1(colb) values(2) ;
will insert 1 into cola and 2 into colb

insert into tab1(cola,colb) values(2,2) ;
will insert 2 into cola and 2 into colb

insert into tab1(colb) values(2) ;

will try to insert 2 into cola and hence cola gets duplicate values ...

As you can see, sequences will not eliminate this problem ...

The other problem with sequences is that the application has to be recoded to use the sequence ...

Sequences generally help if the same key value has to be inserted into multiple tables , say you have to insert a transactionid into a number of tables .....

HTH
Sathyaram

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.

Thanks again.
venkat