Db2 13 for z/OS

JSON Application Development

Last updated: 2024-09-06

IBM



Notes

Before using this information and the product it supports, be sure to read the general information under

"Notices" at the end of this information.

Specic changes are indicated by a vertical bar to the left of a change. A vertical bar to the left of a gure caption

indicates that the gure has changed. Editorial changes that have no technical signicance are not noted.

©

Copyright International Business Machines Corporation 2014, 2024.

US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with

IBM Corp.

Who should read this information................................................................................................................v

Creating a column to store JSON data........................................................................................................ 3

Inserting JSON data into a table................................................................................................................. 3

Updating JSON data in a table.....................................................................................................................4

Retrieving data that is inside a JSON document.........................................................................................4

JSON_VAL scalar function........................................................................................................................... 6

JSON_LEN scalar function...........................................................................................................................9

JSON_TYPE scalar function.......................................................................................................................10

JSON_TABLE table function.......................................................................................................................11

JSON2BSON scalar function..................................................................................................................... 12

Solution planning for the Java API for JSON............................................................................................ 23

Connection management.....................................................................................................................23

Query operators....................................................................................................................................23

Security model......................................................................................................................................25

Performance features...........................................................................................................................26

Java APIs for JSON.................................................................................................................................... 28

Connect to a Db2 database..................................................................................................................28

Store JSON documents in a Db2 database..........................................................................................29

Select JSON documents from a Db2 database................................................................................... 30

Create indexes on JSON elds.............................................................................................................30

wpListener -help - ............................................................................................................................... 52

wpListener -shutdown - ...................................................................................................................... 53

wpListener -start - ...............................................................................................................................54



Programming interface information.......................................................................................................... 58

Trademarks................................................................................................................................................ 58

Terms and conditions for product documentation................................................................................... 59

iv

complete names or specic abbreviations.

Important: To nd the most up to date content for Db2 13 for z/OS, always use IBM Documentation

or download the latest PDF le from PDF format manuals for Db2 13 for z/OS (Db2 for z/OS in IBM

Documentation).

Most documentation topics for Db2 13 for z/OS assume that the highest available function level is

activated and that your applications are running with the highest available application compatibility level,

with the following exceptions:

• The following documentation sections describe the Db2 13 migration process and how to activate new

level, and the application compatibility levels of the applications. For a list of all available function levels

in Db2 13, see Db2 13 function levels (Db2 for z/OS What's New?).

Function level 100

Function level 500

Some virtual storage and optimization enhancements take effect in function level 100. Optimization

enhancements become available after full prepare of the SQL statements, depending on the statement

type:

• For static SQL statements, after bind or rebind of the package.

• For non-stabilized dynamic SQL statements, immediately, unless the statement is in the dynamic

• For stabilized dynamic SQL statements, after invalidation, free, or changed application compatibility

Who should read this information

• Db2 for z/OS application programmers with a knowledge of SQL and the JSON programming language.

and purchase a license to such utilities, and discussion of those utility functions in this publication is not

intended to otherwise imply that you have a license to them.

Db2 13 utilities can use the DFSORT program regardless of whether you purchased a license for DFSORT

on your system. For more information about DFSORT, see https://www.ibm.com/support/pages/dfsort.

Db2 utilities can use IBM

functions. Use of Db2 Sort for z/OS requires the purchase of a Db2 Sort for z/OS license. For more

information about Db2 Sort for z/OS, see Db2 Sort for z/OS documentation.

Related concepts

Db2 utilities packaging (Db2 Utilities)

IBM OMEGAMON for Db2 Performance Expert on z/OS

C, C++, and C language

CICS

vi

About this information

• Keyboard-only operation.

About this information

vii

• Optional items appear below the main path.

viii

• For some references in syntax diagrams, you must follow any rules described in the description for that

6Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Chapter 2. Working with JSON documents by using SQL

7

Statement concentrator

CREATE INDEX statement considerations

column must be dened as an inline LOB.

conditions:

– The invocation of the JSON_VAL function must be the outermost expression for key-expression.

– If the rst argument is a column, that column must be contained in a table in a partition-by-

– The third argument must end with the string ':na', to indicate that the rst argument does not

Examples

• Assume that the DATA column in table 'table1' contains a row with the following JSON document:

8

The result is 'Joe'.

You can use the JSON_VAL function to extract the name of the partner's partner (search-string of

'partner.partner') for each row and return the value as VARCHAR(40).

This example returns four rows, each containing the null value because the search string

'partner.partner' does not exist for the objects in any row.

JSON_LEN scalar function

inelem

The result of the function is an integer.

Example

Assume that table T1 contains following JSON document in a column C1 as a BSON format.

Following query returns the number of elements in ‘array’.

Chapter 2. Working with JSON documents by using SQL

9

The schema is SYSTOOLS.

injson

inelem

maxlength

2 UTF-8 string

3 Embedded document

4 Array

5 Binary data

6 Undened – deprecated

7 ObjectID

8 Boolean

9 UTC datetime

10 Null value

11 Regular expression

18 64-bit integer

10Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Example

Assume that table T1 contains following simple JSON document in a column C1 as a BSON format.

The following query returns BSON type for ‘name’ and ‘age’ in JSON document.

The JSON_TABLE user-dened function returns a table with two columns. First column is the BSON type

and second column is the string value.

The schema is SYSTOOLS.

injson

inelem

rettype

Chapter 2. Working with JSON documents by using SQL11

12

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Example

Chapter 2. Working with JSON documents by using SQL

13

Example: Creating an index on a table with JSON data

The following example creates an index on Customer.age column and Customer.name column in the

JSONCUSTOMER table.

Example: Inserting JSON data into a table

The following example inserts purchase order JSON data into the JSONPO table, and customer

information into the JSONCUSTOMER table.

14

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

The following example inserts a JSON document into a VARCHAR column. JSON2BSON is not needed for

this operation.

The following example converts BSON format to JSON format and displays the entire JSON data as text.

The following example displays customer ID JSON data in descending order.

Chapter 2. Working with JSON documents by using SQL

15

Set the connection pool size to 20:

Species the name of a trace le that your program has permission to write to.

on logging in both an application and in the properties le, the log is written to both places. In general,

logging has a performance cost.

Null (no tracing)

Values are as follows. They are specied in order of lowest detail level to highest detail level.

OFF

SEVERE

WARNING

INFO

Species that highly detailed tracing messages are logged.

Indicates that all messages are logged.

22

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Solution planning for the Java API for JSON

Information about connection management, query operators, JSON security, and performance features

will help you plan your implementation of JSON.

Tip: The Java API is no longer the recommended approach for working with JSON data in Db2 for z/OS.

For best results, use SQL and the built-in functions that are supplied with Db2 for z/OS instead. For more

a connection from the pool, perform the operation with that connection, and return it to the pool when

For connections in single-mode, the JSON API allows control of transactional behavior when you are

working with JSON documents. Therefore, it is possible to combine multiple operations in a transaction,

control auto-commit behavior, and trigger a rollback if errors occur. The transaction APIs are not

applicable for the re and forget mode. For details, see the information about connecting to a Db2

database.

Various query operators are supported, such as logical operators, comparison operators, and data

evaluation operators.

$not

$or

23

$nor

$ne (inequality comparator operator)

$in

$lte

$gt

24

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

$regex (regular expression predicate)

25

26

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Heterogeneous batching

Example 2:

27

enables re and forget for a single connection, the mode setting is ignored and the insert will be executed

single-threaded.

guarantee writes to the database. See the Java documentation for more information on WriteConcern.

Java APIs for JSON

The Java API provides methods for storing, retrieving, and manipulating JSON documents.

Tip: The Java API is no longer the recommended approach for working with JSON data in Db2 for z/OS.

For best results, use SQL and the built-in functions that are supplied with Db2 for z/OS instead. For more

information, see Chapter 2, “Working with JSON documents by using SQL,” on page 3.

•

• NoSQLClient.getDB(javax.sql.DataSource)

• NoSQLClient.getDB(String, String, String)

Species a password.

28

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Table 6. Methods on DB to control transactional behavior

Method Description

to false. If a connection pool or data source is

Note: The transaction APIs described in this table are not applicable for the re and forget mode.

Obtain a single-mode connection when you are using the transaction APIs to avoid situations where

starting a transaction forcibly changes the connection pool mode into single-mode.

Documents are not inserted if an error occurs. With these APIs, either all or none of the documents are

inserted.

JSON documents are stored in collections (or tables) in the database. These documents are represented

as a map of key value pairs where the keys are strings. The values can be primitive Java objects like

By default, insert operations wait for success conrmation from the database. If you want

commits drastically increasing speed, but also increasing transaction log space requirements on Db2

29

The following snippet from a Java program demonstrates how to select JSON documents:

To obtain specic the query output, consider using the following methods:

The following snippet from a Java program demonstrates how to create a compound (composite) index

that contains multiple elds:

for more details on index creation.

30

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

The following snippet from a Java program demonstrates how to import JSON data:

31

Sample output is as follows:

Find JSON documents according to lter criteria. You can also specify elds to be included or excluded,

determine the sort order, limit the number of documents, and page through results with an offset.

38

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

39

Returns the names of all JSON collections in a Db2 database.

None.

View all the collection names:

40

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Sample output is as follows:

Get details for the index with the specied identier.

Get details for indexes with the specied attribute.

Get details for the index with the specied name.

View a list of all indexes and type markers in the specied namespace:

41

query

Calculate the average price of books per author:

Sample output is as follows:

Lists commands that can be applied for a collection.

42

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

commit_frequency

hostname

portnr

dbname

pwd

43

hostname

portnr

pwd

Example 1: Update the specied elds in a document.

Inserts a JSON document into a collection. If the collection does not exist, it is created automatically.

44

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Sample output is as follows:

type

45

46

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

index_spec

Example 1: Remove the mypriceidx index by referring to the index name:

Renames a collection.

new_name

47

54

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

56Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

This information contains examples of data and reports used in daily business operations. To illustrate

them as completely as possible, the examples include the names of individuals, companies, brands, and

products. All of these names are ctitious and any similarity to actual people or business enterprises is

programs. The sample programs are provided "AS IS", without warranty of any kind. IBM shall not be

liable for any damages arising out of your use of the sample programs.

General-use Programming Interface and Associated Guidance Information is identied where it occurs by

the following markings:

General-use Programming Interface and Associated Guidance Information…

Product-sensitive Programming Interfaces allow the customer installation to perform tasks such as

diagnosing, modifying, monitoring, repairing, tailoring, or tuning of this IBM software product. Use of such

interfaces creates dependencies on the detailed design or implementation of the IBM software product.

Product-sensitive Programming Interfaces should be used only for these specialized purposes. Because

of their dependencies on detailed design and implementation, it is to be expected that programs written

IBM, the IBM logo, and ibm.com

are trademarks or registered marks of International Business Machines

Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks

of IBM or other companies. A current list of IBM trademarks is available on the web at "Copyright and

trademark information" at: http://www.ibm.com/legal/copytrade.shtml.

58

Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

its afliates.

Terms and conditions for product documentation

Permissions for the use of these publications are granted subject to the following terms and conditions:

your enterprise, without the express consent of IBM.

Rights: Except as expressly granted in this permission, no other permissions, licenses or rights are

granted, either express or implied, to the publications or any information, data, software or other

intellectual property contained therein.

IBM reserves the right to withdraw the permissions granted herein whenever, in its discretion, the use

of the publications is detrimental to its interest or, as determined by IBM, the above instructions are not

being properly followed.

You may not download, export or re-export this information except in full compliance with all applicable

laws and regulations, including all United States export laws and regulations.

IBM MAKES NO GUARANTEE ABOUT THE CONTENT OF THESE PUBLICATIONS. THE PUBLICATIONS

to tailor interactions with the end user, or for other purposes. In many cases no personally identiable

information is collected by the Software Offerings. Some of our Software Offerings can help enable you

to collect personally identiable information. If this Software Offering uses cookies to collect personally

identiable information, specic information about this offering’s use of cookies is set forth below.

This Software Offering does not use cookies or other technologies to collect personally identiable

information.

If the congurations deployed for this Software Offering provide you as customer the ability to collect

personally identiable information from end users via cookies and other technologies, you should seek

your own legal advice about any laws applicable to such data collection, including any requirements for

notice and consent.

59

60Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

62Db2 13 for z/OS: JSON Application Development (Last updated: 2024-09-06)

aggregate() JSON command 33

BSON2JSON

BSON2JSON function 13

commands

ndAndModify() 39

getCollectionNames() 40

getIndexes() 41

group() 42

help() 42

importFile() 43

insert() 44

remove() 46

removeIndex() 47

rename() 47

wpListener -help 52

wpListener -shutdown 53

wpListener -start 54

count() JSON command 34

disability vi

distinct() JSON command 35

drop() JSON command 36

example

BSON2JSON 13

JSON_LEN 9–11

JSON_VAL 6

JSON2BSON 12

overview 1

application development 1

batching 26

collections 18

command-line interface (CLI)

command-line options 33

Index63