DB2 Version 9.7 for Linux, UNIX, and Windows

dft_sqlmathwarn - Continue upon arithmetic exceptions configuration parameter

This parameter sets the default value that determines the handling of arithmetic errors and retrieval conversion errors as errors or warnings during SQL statement compilation.

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 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. This situation can occur if you are processing SQL statements that, on other database managers, provide results regardless of the arithmetic exceptions that occur.

Effects of changing dft_sqlmathwarn

If you change the dft_sqlmathwarn value for a database, the behavior of check constraints, triggers, and views that include arithmetic expressions might change. This might, in turn, have an impact on the data integrity of the database. You should only change the setting of dft_sqlmathwarn for a database after carefully evaluating how the new arithmetic exception handling behavior might impact check constraints, triggers, and views. Once changed, subsequent changes require the same careful evaluation.

As an example, consider the following check constraint, which includes a division arithmetic operation:

A/B > 0

When dft_sqlmathwarn is No and an INSERT with B=0 is attempted, the division by zero is processed as an arithmetic error. The insert operation fails because DB2® 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 DB2 from evaluating the constraint. The row inserted with B=0 when dft_sqlmathwarn was Yes remains in the table and can be selected. Updates to the row that cause the constraint to be evaluated will fail, while updates to the row that do not require constraint re-evaluation 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:

  ( 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 )

can be used if both A and B are nullable. 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 should first check for data that might become inconsistent by using, for example, predicates such as the following:

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

When inconsistent rows are isolated, you should take appropriate action to correct the inconsistency before changing dft_sqlmathwarn. You can also manually re-check constraints with arithmetic expressions after the change. To do this, first place the affected tables in a check pending state (with the OFF clause of the SET CONSTRAINTS statement), then request that the tables be checked (with the IMMEDIATE CHECKED clause of the SET CONSTRAINTS statement). Inconsistent data will be indicated by an arithmetic error, which prevents the constraint from being evaluated.