In this article, I’m going to show you another database option we now have on our IBM i.

All these choices!  Little did we think we would have any alternative for a database, we expected only to have the excellent DB2 for i.

So, what is it?

SQLite is a serverless database, self-contained and is open source.

It is also referred to as an embedded database which means the database engine runs as a part of your application.

There is no central server, so no configuration jobs to do there.  The main purpose of SQLite is that it can be embedded in programs.  So if you need to store data, more on a tempory basis, maybe this database is a better option for your needs.

In my next article, we will be building a quick Node.js application that will need to store a very small amount of information that the application can easily and very swiftly access.

How do we install it?

Like all the open-source offerings made available by IBM, installation is easy, by using either the Access for Client Solutions (ACS) or using Yum from a SSH shell session.

Once again, I will use my preferred method, I will use Yum to install this package.

yum install sqlite3 sqlite3-devel

Using SQLite

To start SQLite, just type SQLite3 and the name of a database you want to work with, into a SSH shell session.

A new database is created if the file does not previously exist.

The screenshot below, shows creating a new table called employee, in the HRDATA schema, and inserting a couple of records.

1. Start SQLite and create HRDATA schema
2. Create the employee table
3. Insert records into the employee table
4. List all employee records.

We are now in the database command line interface.

Here we can run any standard SQL statement and each SQL statement must end in a semi-colon character.

DOT Commands

While you are in the SQLITE3 command line, there are numerous commands you can input to show you information about your database.  These commands all start with the full stop character.

I’ve listed the most common ones, I use in the table below.

Command	Description
.tables	List all tables in the database
.databases	List all the databases
.log	Switch logging on or off
.quit	Exit SQLite command processing (You can also use Ctrl-C)
.help	Has been used once or twice!

The screen shot below shows entering a couple of the dot commands, with the output they produce.

1. Show me all the databases
2. Show me all the tables in this database
3. Show the actual file that is storing everything in the database.  The complete database is held in a single file (hrdata).   Makes life very easy for saving and restoring.

Comparison

In a previous PowerWire article, I wrote an article on MariaDB, the light version of MySQL. 

So how do these two databases compare with each other?

Comparison	SQLite	MariaDB (MySQL)	Comments
Column types	Only supports: Blob, Integer, Null, Text & Real datatypes	Full set of datatypes as you would expect	As you can tell, MySQL is a lot more flexible when it comes to data types.
Storage and Portability	Stores the database in a single file, making it easily portable, while very small in size. Remember, no configurations are required	With a bigger footprint,  MariaDB is easily scalable and can handle a bigger database with less effort	SQLite is suitable for smaller databases, while optimisation is easier with MariaDB
Security	SQLite does not have an inbuilt authentication mechanism. The database files can be accessed by anyone.	MariaDB comes with integral security features, which includes authentication.	If you have any security concerns, and need to restrict access, then MariaDB is the only route to take between the two.

SQLite is ideal for

• Developing small standalone apps
• Smaller projects which do not require much scalability
• When you have a requirement to read and write directly from the disk
• Basic development and testing

Both SQLite and Maria have slight architectural and characteristic differences. At the end of the day, only you can decide what works best for your project. 

Now you can decide which is the best of these open-source databases to use within your application, or do you stick with DB2 for i?  It is great having these choices available to us.

Conclusion

SQLite is a great addition to open source on IBM i, that gives us an easier option, both in coding and configuration/administration than other heavyweight databases.

My next article will show how easy SQLite is to use from a programmer’s perspective.

While we are talking open-source, join me at the next i-UG UK user group meeting in March, where I will be adding my two bobs-worth on open source on IBM i.

If you have any questions, either on this article, or anything else open source, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion 2021