Download a software tool to manage the Zend Server SQLite databases

This article is now available in our new knowledge base: Download a software tool to manage the Zend Server SQLite databases

Issue

You would like to explore the Zend Server databases, or perform an edit when asked by Zend Support.

Environment

Any version of Zend Server for IBM i running on any supported version of IBM i.

Resolution

Here are two free SQLite editors that can be used with the Zend Server databases.  The first runs on your Windows PC, and can be used if you have a network drive set up to access your IBM i.  The second can be used from a web browser.

Note:  It is possible to make changes to your database tables that
will make them unusable, forcing a recreate of the database or even
possibly a clean install of Zend Server.  Please use caution and
common sense when using these tools.  Also, please make a copy of any
.db file for backup before making any changes to it.  It is best to
make the changes while the Zend Server jobs are not running.

DB Browser for SQLite

DB Browser for SQLite (formerly SQLite Database Browser) is a simple SQLite manager that is easy to use and free to download.  The windows download can be found here:

https://github.com/sqlitebrowser/sqlitebrowser/releases

To install this in Windows, just download and run the .exe file.  To open a database, navigate to the /usr/local/zendsvr6/var/db directory and choose one of the files with a .db extension.  Before making any changes to any of these files, make a back up copy.

phpLiteAdmin

phpLiteAdmin is a PHP script that provides a SQLite management GUI in a web browser.  You can find the download here:

https://code.google.com/p/phpliteadmin/wiki/DownloadLinks

There are some installation instructions here:

https://code.google.com/p/phpliteadmin/wiki/Installation

To use phpLiteAdmin with the Zend Server databases, probably the simplest thing to do is to download the software and unzip it on your PC.  The entire software consists of a single phpLiteAdmin.php file.  There is an optional configuration file, or you can make your configuration changes right in the phpLiteAdmin.php file.

To configure, find these two lines of code:

//password to gain access
$password = 'admin';

//directory relative to this file to search for databases (if false, 
manually list databases in the $databases variable)
$directory = '.';

Change the password to anything you like.  Change the directory to the Zend Server databases directory:

$directory = '/usr/local/zendsvr6/var/db';

Save your changes, then upload the phpLiteAdmin.php file (and the configuration file if you used it) to your web site.  You can then run the phpLiteAdmin.php script on your website to work with the Zend Server databases.  Remember to back up any database files before making any changes.

Note: This utility offers very basic password protection.  It is a 
good idea to only have this script on your website when you are 
actually using it.  You can remove it when you don't need it, and 
quickly post it back up to the site the next time you need to use it.