Troubleshooting
Problem
Db2 JSON Data load in tables
Db2 data can be inserted in JSon tables using following config
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
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"
}
'
));
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"
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"
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
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"}}]
Was this topic helpful?
Document Information
Modified date:
17 June 2019
UID
ibm10887967