JSON Storage Options in DB2

Frank Fella

JSON Storage Options in DB2

Hello,

We are thinking about storing JSON  documents in columns of a DB2 table. Research has shown me that I have the option of storing it in a VARCHAR, CLOB to keep it in text format or BLOB to store it in BSON format using the scalar functions BSON2JSON and JSON2BSON when retrieving and storing the JSON document.

Each JSON document will be well under the 16M limit I understand is imposed on JSON/BSON objects.

Does anyone have advice/guidelines where one might choose one option over the other?  Any performance issues I should be aware of?

Thanks!

Frank J. Fella, Developer, Progressive Insurance

[login to unmask email]

440-395-8597

Daniel Luksetich

JSON Storage Options in DB2
(in response to Frank Fella)


Well Frank you say 16MB so I think that rules out VARCHAR. I've used the
JSON functions and they seem to work really well. So, I think it's a
simple question. Will all your documents be properly formatted JSON
documents, and will you need to perform JSON functionality within the
database access layer in taking advantage of the DB2 functions to speed
application development? If the answer is yes, then store them in BSON.
If the only reason for the database is to store and retrieve then go
CLOB. You can always apply the functions to the CLOB data if you need to
use SQL to extract data from JSON documents.

Cheers,

Dan

On 09.06.2017 12:34, Frank Fella wrote:

> Hello,
>
> We are thinking about storing JSON documents in columns of a DB2 table. Research has shown me that I have the option of storing it in a VARCHAR, CLOB to keep it in text format or BLOB to store it in BSON format using the scalar functions BSON2JSON and JSON2BSON when retrieving and storing the JSON document.
>
> Each JSON document will be well under the 16M limit I understand is imposed on JSON/BSON objects.
>
> Does anyone have advice/guidelines where one might choose one option over the other? Any performance issues I should be aware of?
>
> Thanks!
>
> Frank J. Fella, Developer, Progressive Insurance
>
> [login to unmask email]
>
> 440-395-8597
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/?post=182800&anc=p182800#p182800
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2

Frank Fella

RE: JSON Storage Options in DB2
(in response to Daniel Luksetich)

Thanks, Dan!  As usual your advice is spot-on and greatly appreciated!

Frank J. Fella, Developer, Progressive Insurance

[login to unmask email]

440-395-8597