Technical Blog Post
Abstract
Getting Error code: -551 (CDJSN1156E failed to execute function 'Enable') when enabling JSON environment
Body
We are seeing multiple reports of this issue so trying to document the behavior here.
Here is the repro on Db2 11.1 Mod 4 Fix Pack 4
db2inst1@machine007:~/sqllib/json/bin> ./db2nosql.sh -p 50001 -db primet01
JSON Command Shell Setup and Launcher. This batch script assumes your JRE is 1.5 and higher. 1.6 will mask your password. Type db2nosql.sh -help to see options IBM DB2 NoSQL JSON API 1.1.0.0 build 1.4.246 Licensed Materials - Property of IBM (c) Copyright IBM Corp. 2013 All Rights Reserved. nosql>Type your JSON query and hit <ENTER> nosql>Type help() or help for usage information. All commands are case sensitive. CDJSN1155E Verification failed for DB2 JSON enablement. Reason "2", SQLCode "-204" nosql>enable(true) nosql>Executing SQL... CREATE OR REPLACE FUNCTION SYSTOOLS.JSON_TABLE(INJSON BLOB(16M), INELEM VARCHAR(2048), RETTYPE VARCHAR(100)) RETURNS TABLE(TYPE INTEGER, VALUE VARCHAR(2048 LANGUAGE C PARAMETER STYLE SQL PARAMETER CCSID UNICODE NO SQL NOT FENCED DETERMINISTIC NO EXTERNAL ACTION DISALLOW PARALLEL SCRATCHPAD 2048 EXTERNAL NAME 'db2json!jsonTable' Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=db2inst1;REPLACE FUNCTION;SYSTOOLS.JSON_TABLE, DRIVER=3.72.52 CDJSN1156E Failed to execute function 'enable'
Issue still exists even if we try to resolve by running 'disable(true)' and 'enable(true)' as suggested in technote reference #: 0787533
/support/pages/node/787533
db2inst1@machine007:~/sqllib/json/bin> ./db2nosql.sh -p 50001 -db primet01
JSON Command Shell Setup and Launcher. This batch script assumes your JRE is 1.5 and higher. 1.6 will mask your password. Type db2nosql.sh -help to see options IBM DB2 NoSQL JSON API 1.1.0.0 build 1.4.246 Licensed Materials - Property of IBM (c) Copyright IBM Corp. 2013 All Rights Reserved. nosql>Type your JSON query and hit <ENTER> nosql>Type help() or help for usage information. All commands are case sensitive. CDJSN1155E Verification failed for DB2 JSON enablement. Reason "2", SQLCode "-204" nosql>disable(true) CDJSN1216W The DB2 database is not enabled for JSON, no action performed. nosql>enable(true) nosql>Executing SQL... CREATE OR REPLACE FUNCTION SYSTOOLS.JSON_TABLE(INJSON BLOB(16M), INELEM VARCHAR(2048), RETTYPE VARCHAR(100)) RETURNS TABLE(TYPE INTEGER, VALUE VARCHAR(2048 LANGUAGE C PARAMETER STYLE SQL PARAMETER CCSID UNICODE NO SQL NOT FENCED DETERMINISTIC NO EXTERNAL ACTION DISALLOW PARALLEL SCRATCHPAD 2048 EXTERNAL NAME 'db2json!jsonTable' Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=db2inst1;REPLACE FUNCTION;SYSTOOLS.JSON_TABLE, DRIVER=3.72.52 CDJSN1156E Failed to execute function 'enable'
There are some behavior changes since Db2 v11.1.2.2 and we have to use "-setup enable" instead of "enable (true)" that was valid prior to v11.1.2.2.
So for Db2 v11.1.2.2 onwards, start the NoSQL command line interface and create the tables and functions for the database to enable Db2 JSON capability by running the script appropriate for your machine with the "-setup enable" option. But again, if you still get the -551 error, then you can fix it by executing the script again to disable and then re-enable JSON capability. For example:
./db2nosql.sh -setup disable
./db2nosql.sh -setup enable
So depending on your db2 version, way to enable JSON capability will differ.
PS: As of now, this option is not documented in IBM Knowledge Center.
Thanks,
Shashank Kharche
IBM DB2 LUW Lab
UID
ibm13285831