DB2 FOR LUW / DB2FORLUW / B10 - DB2

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

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

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"}}'))

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
 

First fixed in Db2 11.1 Mod 3 Fix Pack 3

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

09.01.2018
19.03.2018
19.03.2018