Best Practices for Number of tables(DB2 I-series Source)
Hello Team,
What is the best practice on maximum number of tables loading at a time to target? I am using 5(default value). Does increasing it causes connectivity issue?
On Full Load Tuning the Maximum number of tables default value is 5 which is recommended value. I am going to explain what will happen if we increase\decrease this value and based on your environment resources, you need to fine-tune the value.
If you increase the max connections to 10 then Replicate can initiate up to 10 database connections and run select * from Table on the source. If 5 connections utilized 10% of database resources then 10 connections can utilize 20%. For mission-critical databases, an always a good option to use 5 or <5 connections while doing FL and If you do FL on non-pick hours(maintenance window) then you can have up to 20 connections.
On Full Load Tuning the Maximum number of tables default value is 5 which is recommended value. I am going to explain what will happen if we increase\decrease this value and based on your environment resources, you need to fine-tune the value.
If you increase the max connections to 10 then Replicate can initiate up to 10 database connections and run select * from Table on the source. If 5 connections utilized 10% of database resources then 10 connections can utilize 20%. For mission-critical databases, an always a good option to use 5 or <5 connections while doing FL and If you do FL on non-pick hours(maintenance window) then you can have up to 20 connections.
It's all about choices. Notable the choice which is more important for the intended usage? Load time or immediate resource consumption. In the end the same amount of work needs to be done right? There maybe a requirement to finish the work in a particular time window regardless of the peak cost. There maybe a requirement to have minimal impact on source or target, no matter how long it takes. There may be a limit on Replicate server resources (CPU's).
5 is a relatively 'safe' choice, but on the low end for hundreds or thousands of largish tables.
On the other hand we often see 2 or 3 tables taking much longer than the rest. In that case having more than 5 will only create more competition (concurrency) for those extra large tables and it might ever be better to give those 2 or 3 table a higher load priority to get them started right away and only have 1 or 2 more stream to load all the other table one after another while the large ones do their thing. In such situation, if you gave it 10 streams, you might see a 'mad' rush for the first 10% of the load time, 70% of the time a few large tables and 20 or 30% of the time is waiting for the slowest table to finish up with nothing else going on.
As always 'it depends'. It depends on the actual needs and feeds which may take a trial run or two to figure out.
Btw... don't feel bad about aborting a trial run. Once it 'settles in' evaluate where it is going and decide to let it go or abort and try with other parameters. Never be tempted to think 'it'll be done in a minute' without justification. It's amazing to to watch perfectly smart technicians waiting for something to magically be done against their own better judgement :-). Hope springs enternal!
