need to copy the data of a set of tables from instance1 to instance2

Hi Prardhan,

Are you running DB2 systems that are licensed for Federation?  That will affect your options.

If so, the easiest solution might be to query the tables as if they were in different schemas in the same instance.

How do you determine what rows will need to be copied?  Do the tables have "Created Date" and/or "Modified Date" columns?  How about an identity column that can be used as a marker for existing/new data?  (Though that won't catch changed data.)

Will changed rows need to replace rows on the second system?

Hi,

Federation between DB2 LUW databases is available in all editions in DB2 v11.1

If you have Workgroup server or above you could use SQL Replication or CDC replication methods to do this.
https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_10.2.1/com.ibm.swg.im.iis.db.repl.sqlrepl.doc/topics/iiyrscncsqlreplovu.html

Regards,
    Tomas Helgi

For a OLTP environment, which approach is suitable either Federation or SQL Replication ?

I have to think from Performance point of view as well. Any inputs with respect to performance, which method is good?

i am using this version -- 10.5.0.7

I had no idea that Federation was generally available starting at release 11.1.  Thanks, Tom!!!!

10.5.0.7 is older than 11.1, so Federation isn't an option unless you've got a paid up license.

Still need to know how you're going to determine which rows to move to the second instance.

Kent

Hi,

>For a OLTP environment, which approach is suitable either Federation or SQL Replication ?
It highly depends on your overall workload and how your system works.
CDC and SQL Replication works in such way that every data processed in an UOW on System A is transferred from A to B immediately after a commit is issued. You only need to define the tables involved in each subscription (where each subscription monitors and handles a set of tables that takes part in a UOW).
On the other hand, then Federated connection is only suitable for batch-like processing (scheduled and/or event driven) of data between A and B.
You need to make sure that the workload (by looking at how your system works)  on instance B (the target) handles the chosen solution of data transfer in a correct manner.
That is. Ask yourself these questions:
Does  system B need data immediately after UOW ends on A ?
Does  system B need data on a scheduled/event driven basis ?

Regards,
    Tomas Helgi

Hi Tomas/ All

Thanks for inputs.

I am responding for the below Questions.

Does  system B need data immediately after UOW ends on A ?
Does  system B need data on a scheduled/event driven basis ?

Target System (System B) does not require the data immediately , i require daily once.
But I do not have any Tracking column ( like modified_date) to track the changes(deletes) or updates in the table.
As I do not have tracking column, thinking of opting for SQL replication instead of Federation (with PL/SQL procedures for data copy).

Hi Thomas

I have gone through the below documentation briefly.

https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_10.2.1/com.ibm.swg.im.iis.db.repl.sqlrepl.doc/topics/iiyrscncsqlreplovu.html

Can you please help me to have the direct steps with commands to simplify the things to set up the SQL replication Process.

Hi Thomas / All

Good Morning!!!

I read the below passage in the IBM documentation(
https://www.ibm.com/support/knowledgecenter/SSTRGZ_10.2.1/com.ibm.cdcdoc.sysreq.doc/concepts/supportedsourceandtargets.html
)

"Each database that will act as a source for replication will need to have an instance of the InfoSphere CDC replication engine installed, as will each target for replication.

Both the Management Console and Access Server applications must also be installed in order to configure and monitor replication between the source database and the target of replication."

I am using DB2 10.5 LUW, Do i need to install InfoSphere  to set up the data replication? I actually want to do with out GUI, though
commands.

Can you please let me know  whether i can achieve data replication with out GUI set up?  

Thanks in advance.

Hi Prardhan,

DB2 will replicate to other DB2 servers without running Infosphere.

Here's an older article on setting up DB2 replication.  It's worth a read...

  https://www.ibm.com/developerworks/data/library/techarticle/dm-0405nikolopoulou/index.html

Hi All

What is the difference between SQL replication and Q replication?

Which technique is best suitable for OLTP Data bases.

HI All

I am facing the below error while creating the registration with below ASNCLP command:

CREATE REGISTRATION (schemaname.tablename) DIFFERENTIALREFRESH STAGE cdtablename;

ASN1550E  The replication action "connection object" ended in error. The value for the input parameter "server information" is missing.

ASN1954E  ASNCLP : Command failed.

I have already altered the needed tables with below SQLS

ALTER TABLE schemaname.tablename DATA CAPTURE CHANGES;



And

I have also created the Control tables with below ASNCLP commands. Creation of Control tables is successful.

CREATE CONTROL TABLES FOR CAPTURE SERVER IN UW UOW TABSPCE16K OTHERS TABSPCE16K;

But I am not sure, why I am facing the below error while creating the Registration:

ASN1550E  The replication action "connection object" ended in error. The value for the input parameter "server information" is missing.

ASN1954E  ASNCLP : Command failed.

Hi,

Make sure you define CAPTURE SERVER and/or APPLY SERVER using the SET command shown in the example in second link

https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_10.2.1/com.ibm.swg.im.iis.db.repl.sqlrepl.doc/topics/iiyrsctbcrtluw.html?cp=SSEPGG_10.5.0

https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_10.2.1/com.ibm.swg.im.iis.db.repl.asnclp.sql.doc/topics/iiyrsclpbldscriptsql.html?cp=SSEPGG_10.5.0

Regards,
    Tomas Helgi