SYSTOOLS functions

A set of JSON-related UDFs is defined in the SYSTOOLS schema.

Note: Only UTF-8 database supports JSON UDFs.

This set of JSON SQL UDF represents a proprietary approach that was introduced by Db2® to enable SQL interaction with JSON data. With the introduction of the new SYSIBM built-in JSON SQL routines, this set of functions is no longer recommended for use with JSON data. However, these functions are still functional and are still supported within Db2.

The old JSON SQL interfaces now work with the standard BSON format. Changes made to these interfaces provide compatibility between the new and old JSON SQL interfaces. The old JSON SQL interfaces no longer produce the modified IBM BSON format that was used before Db2 version 11.1.4.4.

The one exception to this behavior change is the SYSTOOLS.JSON2BSON procedure that continues to produce the original modified IBM BSON format. The behavior of the SYSTOOLS.JSON2BSON procedure is being maintained for situations where a dependency exists on the old format outside of Db2. Otherwise, use the new SYSIBM.JSON_TO_BSON and SYSIBM.BSON_TO_JSON routines if format conversion is required.

The SYSIBM and SYSTOOLS JSON SQL functions accept both the new standard BSON format and the old modified IBM BSON format. This support applies to functions that accept the BLOB data type as input. Support for the original IBM BSON format extends the life of BSON data that is stored in Db2 versions that precede version 11.1.4.4.

Accidental removal of JSON UDFs

Since most of JSON-related UDFs are defined in the schema SYSTOOLS, it can be accidentally dropped by user. These functions can be retrieved by running:

db2updv111