IBM Support

IT23671: SYSTOOLS.JSON_UPDATE CAN CRASH OR RETURN "BSON VALUE IS TOO LONG" WHEN UPDATING A NULL FIELD

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When using the SYSTOOLS.JSON_UPDATE UDF in DB2, it can return
    "BSON value is too long" when you try to update a NULL Field.
    For example, if you have a table defined with a single insert as
    follows:
    
    CREATE TABLE MYSCHEMA.T1 (
    "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START
    WITH 1 INCREMENT BY 1 ),
    "TEXT_1" VARCHAR(255 OCTETS),
    "DATE_1" TIMESTAMP,
    "FIELD_LAST_UPDATE" BLOB(16M)
    )
    
    INSERT INTO MYSCHEMA.T1(TEXT_1,DATE_1) VALUES ('case2', CURRENT
    TIMESTAMP)
    
    ... this statement can fail:
    
    UPDATE MYSCHEMA.T1 SET FIELD_LAST_UPDATE =
    SYSTOOLS.JSON_UPDATE(field_last_update,
    '{$set:{"text_1":{"$date": "2018-01-01T00:00:00"}}}')
    WHERE TEXT_1='case2'
    
    
    This last command will fail with:
    
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL0443N  Routine "SYSTOOLS.JSON_UPDATE" (specific name
    "SQL171211192352357")
    has returned an error SQLSTATE with diagnostic text "BSON value
    is too long".
    SQLSTATE=22001
    
    
    Although rare, this can also crash the DB2 instance, producing a
    stack like this:
    
    0x00007FAAF420C850 address: 0x00007FAAF420C850 ; dladdress:
    0x00007FAAF41FD000 ; offset in lib: 0x000000000000F850 ;
    (/lib64/libpthread.so.0)
    0x00007FAACCFE259D memcpy + 0x03dd
    (/lib64/libc.so.6)
    0x00007FAA20D9459D jsonUpdate2 + 0x0af9
    (/home/db2inst1/sqllib/function/db2json)
    0x00007FAAE675C1CD sqloInvokeFnArgs + 0x0164
    (/home/db2inst1/sqllib/lib64/libdb2e.so.1)
    0x00007FAAE697CA38 sqloInvokeUDF + 0x04d4
    (/home/db2inst1/sqllib/lib64/libdb2e.so.1)
    0x00007FAAE83E5074
    _Z19sqlriInvokerTrustedP10sqlri_ufobP21sqlriRoutineErrorIntfb +
    0x0d60
    

Local fix

  • Change the statement to insert the JSON value directly without
    performing an Update:
    
    INSERT INTO MYSCHEMA.T1(TEXT_1,DATE_1,FIELD_LAST_UPDATE)
    VALUES ('case2', CURRENT TIMESTAMP,
    SYSTOOLS.JSON2BSON('{"text_1" : {"$date":
    "2017-11-01T16:27:00"}, "date_1" : {"$date":
    "2017-11-01T16:27:00"}}'))
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 3 Fix Pack 3 or higher               *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 3 Fix Pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT23671

  • 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

    2018-01-09

  • Closed date

    2018-03-19

  • Last modified date

    2018-03-19

  • 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":"BU048","label":"IBM Software"},"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:
19 March 2018