When choosing a value for INCREMENTS, consider the rate at which each table consumes its available identity values. Account for the needs of the table which consumes numbers at the highest rate, as well as any backlog in MIMIX processing and the activity causing you to run the command. If you have available numbers to use, add a safety factor of at least 100 percent. For example, if the rate of the fastest file is 1,000 numbers per hour and MIMIX is 15 minutes behind (0.25 hours), the value you specify for INCREMENTS needs to result in at least 250 numbers (1000 x 0.25) being skipped. Adding 100% to 250, results in an increment of 500.
For example, data group ORDERS contains tables A and B. Each row added to table A increases the identity value by 1 and each row added to table B increases the identify value by 1,000. Rows are inserted into table A at a rate of approximately 600 rows per hour. Rows are inserted into table B at a rate of approximately 20 rows per hour. Prior to a switch, on the production system the latest value for table A was 75 and the latest value for table B was 30,000. Consider the following scenarios:
-
Scenario 1. You performed a planned switch for test purposes. Because replication of all transactions completed before the switch and no users have been allowed on the backup system, the backup system has the same values as the production. Before starting replication in the reverse direction you run the SETIDCOLA command with an INCREMENTS value of 1. The next rows added to table A and B will have values of 76 and 31,000, respectively.
-
Scenario 2. You performed an unplanned switch. From previous experience, you know that the latency of changes being transferred to the backup system is approximately 15 minutes. Rows are inserted into Table A at the highest rate. In 15 minutes, approximately 150 rows will have been inserted into Table A (600 rows/hour * 0.25 hours). This suggests an INCREMENTS value of 150. However, since all measurements are approximations or based on historical data, this amount should be adjusted by a factor of at least 100% to 300 to ensure that duplicate identity column values are not generated on the backup system. The next rows added to table A and B will have values of 75+(300*1) = 375 and 30,000 + (300*1000)= 330,000 respectively.