IT22392: SQL0420 AFTER UPGRADE TO V11.1

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

• Codegen precomputes predicates in scan lolepops if we deem them
  invariant for the duration of the scan. This is causing problems
  with CASE statements and we may end up performing the THEN
  before the WHEN.
  
          CASE
          WHEN TRANSLATE (vc_in, '', '1234567890') THEN CAST
  (vc_in AS INTEGER)
          ELSE NULL
  
  Above is causing a SQL0420N casting error when the value of
  vc_in was 'T001', we would expect the WHEN to avoid doing the
  cast and avoiding the error.
  
  
  Here is reproduction testcase:
  
  CREATE OR REPLACE FUNCTION METADATA.SAFE_INT (vc_in VARCHAR
  (500))
  RETURNS INTEGER
  NO EXTERNAL ACTION
  RETURN
          CASE
          WHEN TRANSLATE (vc_in, '', '1234567890') = '' THEN CAST
  (vc_in AS INTEGER)
          ELSE NULL
  END
  @
  
  CREATE OR REPLACE FUNCTION METADATA.TEST_FUNC1( vc_in
  VARCHAR(4))
  RETURNS VARCHAR(20)
     BEGIN ATOMIC
     DECLARE v_list_status_msg VARCHAR(20) DEFAULT 'START';
  
      IF METADATA.SAFE_INT(vc_in) IS NULL THEN
          SET v_list_status_msg = 'Test Worked';
      END IF;
  
      RETURN v_list_status_msg;
  END
  @
  
  drop TABLE t1@
  
  CREATE TABLE t1
  (
      c1                   VARCHAR(4) NOT NULL
  )@
  
  INSERT INTO t1 VALUES('T001')@
  
  SELECT METADATA.TEST_FUNC1(c1) FROM t1@
  

Local fix

• db2set DB2_TCG_DEFAULT_OPTIONS="set precompute_expr off"
  It needs an instance restart to take effect and any static SQL
  needs to
  be rebound to benefit from the workaround.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to v11.1.3.3 or above                                 *
  ****************************************************************
  

Problem conclusion

• First fixed in v11.1.3.3
  

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:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP