-1

I have JSON data stored in CLOB in DB2 database. I want to query this JSON data and retrieve some data .

I tried with JSON_VAL and get this error

DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=JSON_VAL.

can anyone please let me know if I can use JSON_VAL if not right one with an example helps.

Paul Vernon
  • 3,818
  • 1
  • 10
  • 23
Raj anand
  • 1
  • 1
  • 1
  • 1
    Which Db2 version and platform? – data_henrik Aug 23 '19 at 05:33
  • 1
    Which programming language are you using to retrieve the CLOB? Edit your question to *show your code*, and explain how the application inserted the json into the database. Learn how to properly ask a question , giving all relevant facts in the question. – mao Aug 23 '19 at 07:18

1 Answers1

1

One way to work out if your version of Db2 supports a given function, is to find the function in the latest version of the Knowledge Center for your Db2 platform. Then switch the version to the one you are on. If the page no longer exists, then you're version does not support that function.

If using Db2 for z, you can see the JSON_VAL is in Db2 for z/OS 12, and Db2 for z/OS 11, but not Db2 for z/OS 10

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/json/src/tpc/db2z_bif_jsonval.html

Or, if you are using Db2 for iSeries, you can see that JSON_VALUE is in IBM i 7.4 and also 7.3, 7.2 but not IBM i 7.1

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzscajsonvalue.htm

For Db2 for LUW, you would look here

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0070417.html

Paul Vernon
  • 3,818
  • 1
  • 10
  • 23
  • Thank you for response . I'm using DB2 V11 . I will go thru the links and follow the instructions that you posted and post a reply based on it. Again Thanks so much . – Raj anand Aug 23 '19 at 17:48
  • OK. BTW "DB2 V11" is somewhat ambigious. It could be "DB2 11 for z/os", or maybe "Db2 11.1" (for LUW). I guess you mean the former, and will tag your question as so. – Paul Vernon Aug 23 '19 at 18:02
  • I'm using Db2 Luv 11. I went thru the link for LUW that you posted and followed it still no luck same error. A sample query helps to make sure I m executing the right syntax, – Raj anand Aug 23 '19 at 21:17
  • If you are still stuck, try posting your full SQL statement, and Db2 version. Maybe in a new question. – Paul Vernon Aug 25 '19 at 21:38
  • Thanks to all who responded and tried helping with this issue. Again My Db2 version in Luv 11, and the column CLOB type in which JSON Data stored. All that I want to know is if JSON_VALUE is supported to extract JSON data from CLOB data type. Need experts advise on this appreciate your response. – Raj anand Sep 01 '19 at 19:09
  • `JSON_VALUE` will "return an SQL scalar value from JSON text". If you pass a CLOB (i.e. a "character string data type") then "it is treated as JSON data.". https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0070417.html – Paul Vernon Sep 04 '19 at 14:00
  • If you want to return (a sub-set of) JSON data from a JSON or CLOB data type, use `JSON_QUERY` https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0070413.html – Paul Vernon Sep 04 '19 at 14:02