DB2 & IMS

Besides the regular UPDATE statement, application-period temporal tables support time range updates where the UPDATE statement includes the FOR BUSINESS PORTION OF BUSINESS_TIME clause. A row is a candidate for update if its period begin value, period end value or both fall within the range specified in the FOR PORTION OF BUSINESS_TIME clause. In our example, the Cust_Mortgage table has an index with the BUSINESS_TIME_WITHOUT_OVERLAPS clause.

Using the FOR PORTION OF BUSINESS_TIME clause avoids period overlap problems. This clause causes rows to be changed and can result in rows being inserted when the existing time period for a row that’s being updated isn’t contained fully in the range specified in the UPDATE statement. As shown in Figure 6, the update time period contains fully the period range of the existing row. As such, the Vrate column is updated to 2.86 and the bus_start and bus_end columns remain unchanged. These rules apply to updates:

• When the BUSINESS_TIME period is contained fully in the bus_start and bus_end column values, the row is updated. The bus_start and bus_end columns remain unchanged. When the bus_end value overlaps the beginning of the specified period for a qualifying row, the row is updated. In the updated row, the bus_start value is set to the begin value of the UPDATE specified period and the bus_end value is unchanged. An additional row is inserted with the original values from the row except that the bus_end value will be the starting value of the BEGIN clause.
• When the bus_start value overlaps the end of the specified period for a qualifying row, the row is updated. In the updated row, the bus_start value is unchanged; the bus_end value is set to the end value of the UPDATE specified period. An additional row is inserted with the original values from the row, except that the bus_start value will be the ending value of the UPDATE specified period.

Continuing with our example from Figure 7, a mistake was discovered with the Vrate for acct_id 4389105 for the initial 30-day mortgage period. The account holder should have received the promotional Vrate of 2.74 for the first 30 days. The account is updated to correct this error as shown in Figure 8.

Besides the regular DELETE statement, application-period temporal tables also support time range deletes where the DELETE statement includes the FOR PORTION OF BUSINES_TIME clause. A row is a candidate for deletion if its period begin column, period end column or both fall within the range specified in the FOR PORTION OF BUSINESS_TIME clause. Delete with business time is shown in Figure 9.

As shown in Figure 9, the BUSINESS_TIME period in the DELETE FROM statement covers the entire time period, and, as a result, the row is deleted. These rules apply to deletes:

• When the BUSINESS_TIME period in the DELETE FROM statement covers the entire time period for a row, the row is deleted (see Figure 9).
• When only the bus_end value falls in the specified period, the row is deleted. A new row is inserted with the original values from the deleted row except that the bus_end value is set to the beginning value of the DELETE specified period.
• When only the bus_start value falls into the specified period, the row is deleted. A new row is inserted with the original values from the deleted row, except that the bus_start value is set to the ending value of the DELETE specified period.

6 Pages