DB2 10.5 for Linux, UNIX, and Windows

dft_sqlmathwarn - Continue upon arithmetic exceptions configuration parameter

This parameter sets the value that determines the handling of arithmetic errors, such as division by zero, and retrieval conversion errors during SQL statement execution.

Configuration type: Database
Parameter type: Configurable
Default [range]: No [No, Yes]

For static SQL statements, the value of this parameter is associated with the package at bind time. For dynamic SQL data manipulation language (DML) statements, the value of this parameter is used when the statement is prepared.

Recommendation: Use the default setting of No, unless you specifically require queries to be processed that include arithmetic exceptions. Then specify the value of Yes. The processing of queries that include arithmetic exceptions can occur if you are processing SQL statements that, on other database managers, provide results regardless of the arithmetic exceptions that occur.

Restriction: A value of Yes is not supported when column-organized tables are accessed.

Effects of changing the value of dft_sqlmathwarn

If you change the dft_sqlmathwarn value for a database, the behavior of check constraints, triggers, views, and indexes with expression-based keys that include arithmetic expressions might change. This might, in turn, have an impact on the data integrity of the database. Only change the setting of the dft_sqlmathwarn configuration parameter for a database after you have carefully evaluated how the new arithmetic exception handling behavior might impact check constraints, triggers, views, and indexes with expression-based keys. Drop and recreate all potentially impacted expression-based indexes after changing the value of the dft_sqlmathwarn configuration parameter. If you are not sure that a particular expression-based index is impacted, it is best to drop and recreate the index to avoid incorrect values in the index.

Consider the following check constraint, which includes a division arithmetic operation:

A/B > 0

When dft_sqlmathwarn is No and an INSERT statement with B=0 is attempted, the division by zero is processed as an arithmetic error. The insert operation fails because the DB2® database manager cannot check the constraint. If dft_sqlmathwarn is changed to Yes, the division by zero is processed as an arithmetic warning with a NULL result. The NULL result causes the predicate to evaluate to UNKNOWN and the insert operation succeeds. If dft_sqlmathwarn is changed back to No, an attempt to insert the same row will fail, because the division by zero error prevents the DB2 database manager from evaluating the constraint. The row that was inserted with B=0 when dft_sqlmathwarn was set to Yes remains in the table and can be selected. Updates to the row that cause the constraint to be evaluated fail, and updates to the row that do not require constraint reevaluation will succeed.

Before changing dft_sqlmathwarn from No to Yes, you should consider rewriting the constraint to explicitly handle nulls from arithmetic expressions. For example, the following code can be used if both A and B are nullable.:

  ( A/B > 0 ) AND ( CASE
                      WHEN A IS NULL THEN 1
                      WHEN B IS NULL THEN 1
                      WHEN A/B IS NULL THEN 0
                      ELSE 1
                      END
                    = 1 )

And, if A or B is not-nullable, the corresponding IS NULL WHEN-clause can be removed.

Before changing dft_sqlmathwarn from Yes to No, you must first check for data that might become inconsistent by using predicates such as the following:

   WHERE A IS NOT NULL AND B IS NOT NULL AND A/B IS NULL

If you isolate inconsistent rows, you must take the appropriate actions to correct the inconsistency before changing dft_sqlmathwarn. You can also manually recheck constraints with arithmetic expressions after the change. To manually check constraints, place the affected tables in a check pending state with the OFF clause of the SET CONSTRAINTS statement, and request that the tables be checked with the IMMEDIATE CHECKED clause of the SET CONSTRAINTS statement. Inconsistent data is indicated by an arithmetic error, which prevents the constraint from being evaluated.