Reading JSON with SQL on IBM i
A field holding JSON can be read with SQL. Four levels, from the first JSON_VALUE query to the cases where a value quietly comes out NULL.
IBM i / AS400
Resources for IBM i
Technical articles, guides and utilities for people working on IBM i and AS/400. The tools run in the browser, with nothing to install.
Try the tool
Packed and zoned decimal
How a number is really written to disk, byte by byte, and how much room it takes.
123.4512 34 5C
The same bytes can be read as packed or as zoned: both readings appear here when they are valid. Spaces, lowercase and 0x prefixes are all accepted.
How to read these bytes
In zoned decimal every digit takes a whole byte: the high nibble is F and the low one holds the digit. In packed decimal two digits share a byte, so it takes nearly half the room. In both, the last nibble carries the sign: C positive, D negative, F unsigned. The decimal point is never stored: it lives only in the field definition. This matters when you stare at a record in hex through DSPPFM and F10, or when you need to know what a file will really weigh before you create it.
Processing happens in the browser. No registration is required and no data is transmitted.
Tools ready to use
IBM i utilities: EBCDIC conversion, date formats, packed and zoned decimal, data reading. They run in the browser, with no installation.
12607272026-07-27
123.4512 34 5C
C1 C2 C3ABC
Guides
Complete treatments of a topic, split into chapters and kept up to date. Each guide states the date of the last review and the IBM i release it was verified against.
- Programming on IBM i: sources, ILE, RPG and SQL
- Administering an IBM i: how the system works
Recipes
Short solutions to recurring problems, with ready-to-use code and the caveats to consider before running it in production.
Recipes →Software
Editor extensions, programs and command line tools useful for IBM i work. Unlike the tools on this site, which run in the browser, these are installed on your computer or on the system.
Software →IBM i releases
Every OS/400, i5/OS and IBM i release: when it came out, how long it is supported, what it brought and which Technology Refreshes followed it.
Is your release still supported? →Jobs
IBM i and AS/400 job listings, and people making themselves available. Every entry is reviewed before it appears and expires automatically.
Jobs →Latest articles
All articles →Project4i Git Mode: what changes for your source
Project4i is preparing Git Mode, with the repository on your own IBM i's IFS and promotion from a commit. How it works and what to prepare.
Integrated MFA in IBM i 7.6: benefits, risks, setup
What it actually adds to system security, which profiles cannot use it, and the setup procedure with the order to follow.
ACS 1.1.9.14: why this update cannot wait
IBM has released an ACS update closing known vulnerabilities. Why it cannot be postponed and how to check the installed version.