PK81406: SQLCODE545 issued incorrectly when insert into table with check constraints

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE -545 is issued incorrectly when an insert is made to a
  table with check constraints. An example of the failing case
  is like the following:
  
  CREATE TABLE  TESTTB  (
         DEC_4PT2  DECIMAL(8,2)      NOT NULL
                        ) IN TESTTS ;
  
  ALTER TABLE TESTTB  ADD CONSTRAINT DEC_4PT2 CHECK (
  DEC_4PT2 BETWEEN -9.99 AND +9.99 )        ;
  
  Then
  INSERT INTO DSEH.EE4_CONSTRAINT_TEST3 (DEC_4PT2) VALUES (+9.98);
  failed with  SQLCODE = -545.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 R810 and R910 users who use tables   *
  *                 with check constraints.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -545 received when inserting    *
  *                      into a table with check constraints.    *
  *                      The constraint contains decimal         *
  *                      constants with signs.                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  SQLCODE -545 is issued incorrectly when an insert is made to
  a table with check constraints.
  When a check constraint contains decimal constants with + or -
  signs, DB2 processes the constraint incorrectly which results
  in a loss of the last digit.
  
  For example, +2.25 would be interpreted as +2.2 by DB2.
  This could cause -545 to be issued incorrectly when inserting
  qualified data to a table with such check constraint.  On the
  other hand, DB2 could also allow unqualified data to be
  inserted.
  
  The check constraint must be dropped and recreated after
  application of this PTF.
  The constraints associated with a table can be found by querying
  catalog table, SYSIBM.SYSCHECKS.
  
  Here are the steps to drop and recreate the check constraint.
  1. Find all the check constraints associated with table TESTTB.
  Inspect column, SYSCHECKS.CHECKCONDITION, to find affected
  check constraints.
    SELECT * FROM SYSIBM.SYSCHECKS WHERE TBNAME='TESTTB'
  2. Issue an ALTER TABLE statement to drop the check constraint.
    ALTER TABLE TESTTB DROP CONSTRAINT CHK_TESTTB
  3. Add the check constraint back.
    ALTER TABLE TESTTB ADD CONSTRAINT CHK_TESTTB CHECK(C2>+100.9)
  The table will be placed in a CHECK PENDING state after the
  check constraint is recreated.  It is recommended to run
  CHECK DATA utility to validate the existing data in the table.
  Please see the DB2 Utility Guide and Reference for more
  information about the CHECK DATA utility.
  

Problem conclusion

• DB2 is modified to process check constraints with a decimal
  constant correctly.
  
  Additional keywords: SQLDECIMAL SQLCODE545
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK46339 UK46340

Modules/Macros

• DSNXODTR
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK46339

     UP09/05/22 P F905

• R910 PSY UK46340

     UP09/05/22 P F905

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.