IT18170: WRONG RESULT IS POSSIBLE IF GENERATED ALWAYS EXPRESSION REFERENCES A BUILT-IN FUNCTION WITH MORE THEN ONE STRING INPUT

DB2 Version 11.1 Mod1 Fix Pack1 iFix001 for Linux, UNIX, and Windows
DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 4 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Insert or update may cause wrong result when the target table
  has a GENERATED ALWAYS expression that references a built-in
  function with more than one string input.
  
  example:
  
  create table t1 (
    i int,
    c1 varchar (18),
    c2 varchar (18) GENERATED ALWAYS AS
       (sysibm.ltrim( c1, X'E6BCA2' ) ));
  
  
  create or replace variable v varchar(18) for bit data;
  
  set v = X'E6BCA2E6BCA1';
  
  insert into t1 values (1, v, default );
  insert into t1 values (2, X'E6BCA2E6BCA1', default );
  
  select hex(c1), hex(c2 ) from t1 order by i;
  
  1                                    2
  ------------------------------------
  ------------------------------------
  E6BCA2E6BCA1                         A1
  E6BCA2E6BCA1                         E6BCA1
  
    2 record(s) selected.
  

Local fix

• Avoid using function with more than one string argument  in the
  definition  OF GENERATED ALWAYS
  column
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 11.1 Mod1 Fix Pack 1 iFix001                  *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 11.1 Mod1 Fix Pack 1 iFix001
  

Temporary fix

Comments

APAR Information

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

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

  IT18200 IT18201 IT18202

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP