DB2 JSON Data load

Troubleshooting

Problem

Db2 JSON Data load in tables

Db2 data can be inserted in JSon tables using following config

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.json.doc/doc/t0061301.html

Symptom

While inserting data -sometimes following error

Example :

db2 create table JSonTest (test BLOB(52428800) LOGGED NOT COMPACT )

db2 "insert into JSonTest values (SYSTOOLS.JSON2BSON('{Name:"xxxx"}'))"

SQL0443N Routine "JSON2BSON" (specific name "*2445498") has returned an error
SQLSTATE with diagnostic text "JSON parsing error for: {Name:xxxx}, error
code: 4 ". SQLSTATE=22546

Cause

Due to Json formatting in clp -sometimes it fails to insert data

This should work, we have some strange logic when we run query like that from clp/command line direcly.

Environment

db2 v10.5 onwards Json insert is supported

Diagnosing The Problem

Insert to Json will fail .

Resolving The Problem

Insert as below method

$ db2 "insert into JSonTest values(systools.json2bson( '{ \"name\":\"xxxx\" } ' ))"
Or
If you put your query into SQL file, it won't have the problem.
For example
Test.sql
DROP TABLE JSonTest;
CREATE TABLE JSonTest( "ID" INTEGER NOT NULL , "DATA" BLOB(16777276) LOGGED NOT COMPACT );
insert into JSonTest values (10, systools.json2bson('{
    "policyDetails": [
    {
      "insured": [
        {
          "insID": "ABC"
        }
      ],
      "location": [
        {
          "insured": {
            "insID": "AJAYB"
          }
        }
      ]
    }
],
"version": 0
} ' ));
insert into JSonTest values(10,systools.json2bson(
'{
"name":"AVD"
}
'
));

Run db2 -tvf test.sql

It will work.

Also incase Json insert show long value for table insert from clp

SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.21.29</message>
The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=4.21.29"

Try the below method

db2 "create table test1("DATA CLOB(214748364) LOGGED NOT COMPACT)"
db2 "create table test2("DATA BLOB(214748364) LOGGED NOT COMPACT)"
db2 "load from data.del of del lobs from /home/keviny/RTCJSON/ insert into Test1"
db2 "insert into test2(DATA) SELECT systools.json2bson(DATA) from TEST1"
db2 "select systools.bson2json(DATA) from test2"

Related Information

Json data insert

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Tips