SQLite on z/OS

Introduction

SQLite is an application embeddable SQL database. It is not intended as a networked highly scalable enterprise solution like Db2 for z/OS, but rather as an application local data store. Note that Python – including Open Enterprise SDK Python for z/OS – includes SQLite, so the database that we create can be queried and analysed by Python tooling.

It’s worth noting that the implementation of the database is written to a single file, so copying and archiving is fairly straight forward, but handling large data volumes is probably best done with Db2 or IMS!

The SQLite documentation includes a page covering use cases – when it’s good and when you should be using something else:

https://sqlite.org/whentouse.html

As an open source solution, it is available as source code, but does not have pre-built binaries for z/OS available on its website.

How to Build SQLite

To start off with, you’ll need the source code. There are a large number of options available on the Download page (https://sqlite.org/download.html) but the one that you want is the “amalgamation” copy. This contains all of the SQLite C code files merged into a single item. Features and options can be configured with symbols defined at compile time. For 3.44.0, which we’re working with at the time of writing, we downloaded the zip:

https://sqlite.org/2023/sqlite-autoconf-3440000.tar.gz

This contains the C source code and headers for sqlite, headers for folks that want to write extensions and the shell wrapping which we’ll use to show that it’s all working:

Having not used the USS based xlc compile command before, this was a bit of a voyage of discovery, but the following worked for us:

xlc -F./xlc.cfg
-D_XOPEN_SOURCE_EXTENDED=1
-D_XOPEN_SOURCE=600
-D_OPEN_THREADS=1
-DSQLITE_BYTEORDER=4321
-DHAVE_NANOSLEEP=0
shell.c sqlite3.c
-lm
-q64
-Wc,DLL

This performs a compile and link / bind to produce the executable, which it annoyingly names “a.out”. We couldn’t persuade it to produce any other more sensible name, so just ended up renaming it to “sqlite3”.

The shell CLI is documented here:

https://sqlite.org/cli.html

Here’s a simple example that we ran:

GILLJ:/u/gillj/sqlite3: >./sqlite3 example.db
SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite> create table eg1 (ts timestamp, type text, count int);
sqlite> insert into eg1 values(current_timestamp, 'Eggs', 48);
sqlite> select * from eg1;
2023-11-16 21:19:04|Eggs|48
sqlite> .exit 0
GILLJ:/u/gillj/sqlite3: >

A Little Gotcha

As we found that Python (3.11) on z/OS (the Open Enterprise SDK version) includes SQLite, we thought it might be nice to include an example here as well:

import sqlite3

conn = sqlite3.connect('example.db')
crs = conn.cursor()
crs.execute('SELECT * FROM eg1')
print(crs.fetchone())
conn.close()

However, when we came to run this, it looks like there might be a version compatibility issue, as Python didn’t think that our example.db file was an SQLite3 database. Further investigation shows:

GILLJ:/u/gillj/sqlite3: >python
Python 3.11.5 (heads/pyz_dev-3.11.ziip:307931de97, Oct 19 2023, 09:40:03) [Clang 14.0.0 ] on zos
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.32.3'
>>>

Or put another way, Python’s SQLite version is 3.32.3 (2020-06-18), and our build level is 3.44.0 (2023-11-01). SQLite should be backwards compatible, so this is probably an “opportunity” for us to dig a bit further into the compiler options.

Conclusions

Whilst I won’t say it’s entirely straight-forward working out how to get SQLite built, it is totally worth the trouble if you have a use case that fits. In our case, capturing some SMF data from buffers to support a little online performance reporting, this is fantastic.

Whilst we would absolutely normally use Db2 for z/OS for this, we can’t be sure that the customer will have this installed and if they did, that they will want to manage the data and objects in a way that works well with our use case. SQLite lets us do this with a private use Open Source software component to capture and hold data for later analysis without additional dependencies.

We have some more chasing to do to get to a point where our build is compatible with the IBM Open Enterprise SDK for Python folks, but, maybe we can all compare notes on building this Open Source tool?

I’ve shown mine…