IT27659: SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON("") RUNS IN A LOOP AND NEVER RETURN

Db2 Version 11.1 Mod 4 Fix Pack 5 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 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• The query below runs in a loop and never completes.
  
  SELECT VALUE
  FROM TABLE(
  SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON(''),
  'items',
  's:200'
  )
  );
  
  Repro steps below:
  
  create database jsondb automatic storage yes using codeset utf-8
  territory us pagesize 32 K;
  connect to jsondb;
  CREATE BUFFERPOOL NOSQLSYSTOOLSBP ALL DBPARTITIONNUMS SIZE
  AUTOMATIC
  PAGESIZE 32K;
  CREATE TEMPORARY TABLESPACE NOSQLSYSTOOLSTEMP PAGESIZE 32K
  MANAGED BY
  AUTOMATIC STORAGE BUFFERPOOL NOSQLSYSTOOLSBP;
  update db cfg for jsondb using dft_table_org row;
  
  -- Must use minimum INLINE LENGTH as per docs for 10K blob, 6
  -- fails with SQL0604
  -- CREATE TABLE T1 (C1 BLOB(10K) INLINE LENGTH 6 );
  CREATE TABLE T1 (C1 BLOB(10K) INLINE LENGTH 116 );
  
  INSERT INTO T1 VALUES (BLOB('')) ;
  SELECT VALUE FROM T1, TABLE(SYSTOOLS.JSON_TABLE(C1,
  'PO.items.item.productName', 's:20')) X;
  
  
  /* This query just returns empty rows forever */
  
  SELECT
  JSON_VAL(
  SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))),
  'aspectRangeId', 'i')
  as RANGE_ID,
  JSON_VAL(
  SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))), 'aspectName',
  's:50')
  as ASPECT_NAME,
  JSON_VAL(
  SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))),
  'aspectLabel', 's:50')
  as ASPECT_LABEL,
  JSON_VAL(
  SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))), 'fromValue',
  'i')
  as FROM_VAL,
  JSON_VAL(
  SYSTOOLS.JSON2BSON(CAST(VALUE as VARCHAR(2000))), 'toValue',
  'i')
  as TO_VAL
  FROM TABLE(
  SYSTOOLS.JSON_TABLE(SYSTOOLS.JSON2BSON(''),
  'items',
  's:200'
  )
  );
  
  
  RANGE_ID ASPECT_NAME ASPECT_LABEL FROM_VAL TO_VAL
  ----------- ------------ ------------- ----------- -----------
  - - - - -
  - - - - -
  (returns empty rows forever)
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 5
  

Temporary fix

• See LOCAL 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