IBM Support

IT22392: SQL0420 AFTER UPGRADE TO V11.1

Subscribe

You can track all active APARs for this component.

 

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 number

    IT22392

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-09-13

  • Closed date

    2017-10-12

  • Last modified date

    2017-10-12

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
12 October 2017