PI61484: SQLCODE443 WITH JSON2BSON ON DB2 V11 NFM

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• In data sharing group, sqlcode -443 occurs while performing a
  SQL invoking JSON2BSON UDF.
  select SYSTOOLS.JSON2BSON(RES_JSON) from dolads3.json_test_table
  order by ssn;
  
  ----------- SQLCODE Messages -----------
  DSNT408I SQLCODE = -443, ERROR:  ROUTINE JSON2BSON (SPECIFIC
  NAME JSON2BSON) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC
  TEXT JSON parsing
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users who work on JSON   *
  *                 documents using DB2 provided UDF's (user-    *
  *                 defined functions).                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR addresses multiple problems   *
  *                      that can occur while processing JSON    *
  *                      documents when using the                *
  *                      SYSTOOLS.JSON2BSON function.            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR addresses the following four problems that can occur
  when using the SYSTOOLS.JSON2BSON function.
  
  (1) An incorrect result can occur when the SYSTOOLS.JSON2BSON
      function is invoked multiple times in an SQL statement if
      one of the invocations of the function is passed an empty
      string.
  
      Example
      -------
  
      CREATE TABLE T1 (C1 VARCHAR(1000));
  
      INSERT INTO T1 VALUES
                       ('{"Customer":{"ID":1, "Name":"Mike"}}');
  
      INSERT INTO T1 VALUES ('');
  
      SELECT JSON_VAL(SYSTOOLS.JSON2BSON(C1),
                      'Customer.Name','S:10')
      FROM T1;
  
  
  (2) An SQLCODE -443 can occur when the SYSTOOLS.JSON2BSON
      function is invoked multiple times in an SQL statement;
      each invocation of the function receives a different
      length (a longer document followed by a shorter document) of
      JSON document.
  
      Example
      -------
  
      CREATE TABLE T1(C1 VARCHAR(1000) );
  
      INSERT INTO T1 VALUES ('{"a":"xyz/a"}');
  
      INSERT INTO T1 VALUES ('{"a":"x"}');
  
      SELECT SYSTOOLS.JSON2BSON(C1) FROM T1;
  
  
  (3) An error is expected when SYSTOOLS.JSON2BSON is passed
      a valid JSON document followed by additional characters.
      However, only the valid portion of a JSON document is
      processed and no error is issued.
  
      Example
      -------
  
      SELECT SYSTOOLS.JSON2BSON('{"Name":"Mike"} abc')
      FROM SYSIBM.SYSDUMMY1;
  
  
  (4) The backslash character (\) is incorrectly removed from a
      JSON document by the SYSTOOLS.JSON2BSON function because it
      is (incorrectly) processed as an escape character.
  
      Example
      -------
  
      SELECT SYSTOOLS.BSON2JSON(
                         SYSTOOLS.JSON2BSON('"a:"Bob\"s book"}'))
      FROM SYSIBM.SYSDUMMY1;
  
  
  Please note.
  This APAR delivers 10 DLLs for JSON DB2 provided user-defined
  functions.  They are originally shipped via the Accessory Suite
  and are currently located in the SDSNLOAD library.  The reason
  for delivering all 10 DLLs is because the source file that gets
  updated is used to build all 10 UDFs.  So, there could be other
  error symptoms that are not mentioned.
  

Problem conclusion

• DB2 provided UDF's (user-defined functions) for JSON support
  have been updated to address the four problems listed.
  
  
  Additional Keywords:  SQLINCORR INCORROUT SQLINCORROUT
                        DB2INCORR/K SQLCODE443 JSON SQLUDF
  
  220Y
  DSN5JSBI
  DSN5JSBJ
  DSN5JSBT
  DSN5JSGP
  DSN5JSJB
  DSN5JSLN
  DSN5JSTA
  DSN5JSTB
  DSN5JSTP
  DSN5JSUP
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI38645

Modules/Macros

• DSN5JSBI DSN5JSBJ DSN5JSBT DSN5JSGP DSN5JSJB
  DSN5JSLN DSN5JSTA DSN5JSTB DSN5JSTP DSN5JSUP
  

Fix information

• Fixed component name

  JSON

• Fixed component ID

  JSON00001

Applicable component levels

• R220 PSY UI38645

     UP16/06/28 P F606 Ž

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.