LAMP 2019Linux

Other Databases – Building a LAMP Server (2019)

This is the seventh article in a series on building the ultimate LAMP Server. This covers installing databases besides MySQL/Mariadb. This is entirely optional and not a requirement of a LAMP stack, but you will want to do this before installing PHP if you plan to develop for some of these engines.

Estimated time (this post only): 0 minutes to 2 hours
* varies depending on how much you install


Notes On PDO

Warning: there may be conflicts compiling PDO drivers if you install all these databases. This is particularly true with MS-SQL and SAP ASE. A typical LAMP server runs MySQL/MariaDB and (optionally) PostgreSQL. I am including instructions for other databases mainly for educational purposes and won’t be focusing on them much in this tutorial.

If you intend to develop PHP applications for DBEs other than MySQL/MariaDB, you will probably want to use PDO, which is an abstraction layer allowing access to multiple database engines. There are PDO wrappers such as Aura, though not all of them support all PDO drivers. You may also consider using an ORM (object relational mapper) such as Doctrine or RedBeanPHP. Or my personal favorite framework, Symphony. Deploying these can be difficult on some shared hosts, so for this tutorial I will stick to vanilla PDO and vendor-specific extensions.


PDO-Supported Engines

Let’s take a quick look at the main RDBMS engines supported by PDO. I am not covering engines not supported by PDO for this guide (such as MongoDB). These are listed by order of popularity. Note that other engines may be accessible by PDO via standard ODBC connectors.

Oracle Database: The most popular database engine in the world overall.

Microsoft SQL Server: Now available on Linux servers as well as Windows. While not widely used on LAMP servers at this time, it may be worthwhile to develop for it.

PostgreSQL: Another popular engine, and possibly the second most popular (next to MySQL/MariaDB) on Linux platforms.

Db2: Formerly DB2, this is one of the oldest database engines still being supported. Almost never used for web applications, but you could develop for it if you choose.

SQLite: This is an embedded RDBMS, which means no server-side installation is required. Although not officially ranked in the top 10, it may actually be the most widely-used database engine in the world since it is the default for many applications (digital gas pumps, multimedia software, web browsers, and so on).

Teradata: Not normally an engine you will develop web applications for as it is primarily used by large datacenters. However, since it supports standard ODBC connectors, PDO can connect to it. I will not be covering Teradata in this guide.

SAP ASE: Formerly known as Sybase, Sybase DB, or Sybase SQL Server. Also rarely used for web applications, but I will cover it anyway.

Informix: Another IBM product, though its development has been outsourced to a third-part company. I am not going to cover it anymore as of this year.

Vertica: Developed by HP and losing popularity, you can connect to this via the ODBC driver. I’m not going to cover this in this guide.

Firebird: In my opinion (and others), this is an underrated open-source RDBMS. It is the successor to Borland’s Interbase and routinely surpasses PostgreSQL and MySQL in virtually every standard test regarding both performance and code quality. Definitely worth a look.

4D and Cubrid: These engines are officially supported by PDO but they are so obscure I’m not even going to bother covering them here.


Oracle Database

Special thanks to Gerald Venzi’s blog post for his simple instructions on this.

I suggest reading the license on the Oracle website carefully. The OSS site no longer maintains current versions of Oracle Database. I am not a lawyer, but from what I understand, at this time you can install any version of Oracle Database for development purposes free-of-charge.

Important: While I was easily able to build older versions of Oracle Database on every distribution listed here, I spent several days trying to install version 18 on Debian-based systems without success. Nor could I find any article online where someone was able to do so (and the official documentation lists only RPM-based distributions). So the following instructions apply only to CentOS, Fedora, and openSUSE.

There is no easy way to download Oracle Database via wget or curl. It is also difficult to download using a text browser. It is simpler to download it from the Oracle website using a regular desktop browser and transfer the file to your home directory via SFTP. For our purposes we will be installing the Express Edition. Here is a screenshot of how to transfer the file on a Windows system using Bitvise’s built-in SFTP client:

Now, via your SSH shell do the following (change filenames and URLs as necessary):

CentOSFedoraopenSUSE

If you have trouble with future versions of CentOS, see my instructions for Fedora. But as of this writing, the following worked for the latest version.

cd ~
curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm \
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo yum install oracle-database*18c*

As of this writing, Fedora 29 has some difficulties installing Oracle Database XE 18c. The following contains workarounds for this. In the future, hopefully the repos will be updated and you will be able to use the same method as outlined for CentOS.

cd ~
curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm \
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo dnf install libnsl \
http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libcap1-1.10-7.el7.x86_64.rpm
sudo dnf install oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo rpm -i --nodigest oracle-database-xe-18c-1.0-1.x86_64.rpm

The last command will take a while to complete with little feedback, so be patient.

On Fedora I encountered an error configuring Oracle Database which said "No valid IP Address returned for the host…" The solution was to add a non-loopback valid IP address to the hosts file. For example, if your full host name is "fedora-lamp.mydomain" and you have a fixed IP address of 192.168.56.102, you would fix the problem by doing (obviously replace the IP address and host name as appropriate):

echo '192.168.56.102   fedora-lamp.mydomain' | \
sudo tee -a /etc/hosts

As with Fedora, I found I had to use workarounds for the current version of openSUSE. Hopefully this gets fixed soon, but the following works as of this writing (ignore any errors regarding GPG keys):

cd ~
curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm \
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo zypper install libnsl2
sudo rpm -i --nodeps oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo zypper install oracle-database-xe-18c-1.0-1.x86_64.rpm

Do the following to configure the default database:

export ORACLE_SID=XE
export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE
export PATH=$ORACLE_HOME/bin:$PATH
sudo /etc/init.d/oracle-xe-18c configure

Now run a quick test (the default username is "system" and the password is whatever you set during configuration).

sqlplus
create table test (test varchar(30));
insert into test (test) values ('It works!');
select * from test;
drop table test;
quit;

To start, stop, or restart Oracle Database XE use one of the following commands:

sudo systemctl start oracle-xe-18c
sudo systemctl stop oracle-xe-18c
sudo systemctl restart oracle-xe-18c

To set it to start/stop automatically upon boot, use one of the following commands:

sudo systemctl enable oracle-xe-18c
sudo systemctl disable oracle-xe-18c

Now remove the RPMs to free up space:

cd ~
rm oracle*.rpm

MS-SQL Server

This will cover the installation of Microsoft SQL Server 2017 on supported platforms covered in this tutorial. 2019 is expected to be released later this year and the preview version is currently available, but the installation process differs slightly. You can find more details in the official documentation, so I will just simplify Microsoft’s detailed instructions here (but I recommend reading their documentation as it is very thorough without being too confusing).

CentOSFedoraUbuntuDebianopenSUSEArch Linux

If the following doesn’t work, see the instructions for Fedora.

sudo curl -o /etc/yum.repos.d/mssql-server.repo \
https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
sudo yum install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

Only the Evaluation, Developer, and Express editions are freely licensed at this time. Choose Express if you aren’t sure which you want.

You should now install mssql-tools so you have a command-line tool capable of connecting to MS-SQL:

sudo curl -o /etc/yum.repos.d/msprod.repo \
https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum install mssql-tools unixODBC-devel
export PATH="$PATH:/opt/mssql-tools/bin"
echo 'export PATH=$PATH:/opt/mssql-tools/bin' | \
sudo tee /etc/profile.d/mssql.sh

The current version of Fedora does not support older versions of OpenSSL, which MS-SQL 2017 requires. Although some package managers allow you to ignore this dependency dnf does not. The following workaround worked for me, but is not officially recommended. So do it at your own risk. In my tests, this method worked, but could potentially break the package manager if you tried to install a package later that also relied on OpenSSL. In other words, I strongly recommend waiting until the next version of MS-SQL comes out before installing on the current version of Fedora.

First, visit https://packages.microsoft.com/rhel/7/mssql-server-2017/ to find the most recent version. The following installs the latest as of this writing.

cd ~
wget \
https://packages.microsoft.com/rhel/7/mssql-server-2017/mssql-server-14.0.3048.4-1.x86_64.rpm
sudo rpm -ivh mssql-server*.rpm --nodeps
rm mssql-server*.rpm
sudo /opt/mssql/bin/mssql-conf setup

Only the Evaluation, Developer, and Express editions are freely licensed at this time. Choose Express if you aren’t sure which you want.

Install mssql-tools so you have a command-line tool capable of connecting to MS-SQL:

sudo curl -o /etc/yum.repos.d/msprod.repo \
https://packages.microsoft.com/config/rhel/7/prod.repo
sudo dnf install mssql-tools unixODBC-devel
export PATH="$PATH:/opt/mssql-tools/bin"
echo 'export PATH=$PATH:/opt/mssql-tools/bin' | \
sudo tee /etc/profile.d/mssql.sh

Although the current version of Ubuntu is not officially supported by Microsoft, I was able to successfully install MS-SQL by slightly modifying their installation instructions. If this doesn’t work, check the instructions for Debian.

wget -qO- https://packages.microsoft.com/keys/microsoft.asc \
--no-check-certificate | sudo apt-key add -
sudo add-apt-repository \
"$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list --no-check-certificate)"
sudo apt update
sudo reboot
sudo apt install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

Only the Evaluation, Developer, and Express editions are freely licensed at this time. Choose Express if you aren’t sure which you want.

Install mssql-tools so you have a command-line tool capable of connecting to MS-SQL:

curl https://packages.microsoft.com/keys/microsoft.asc \
| sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list \
| sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt update
sudo apt install mssql-tools unixodbc-dev
export PATH="$PATH:/opt/mssql-tools/bin"
echo 'export PATH=$PATH:/opt/mssql-tools/bin' | \
sudo tee /etc/profile.d/mssql.sh

Very important: Debian is not officially supported by Microsoft as a platform for MS-SQL. I was able to successfully install it and get it to run, however I ran into connectivity issues (see this post). The error message would seem to indicate a firewall problem, but disabling or even uninstalling the firewall did not fix the issue. I verified via every tool imaginable that the port was open, so this must be a bug. If I find a fix I will publish it. Short version: the following should successfully install MS-SQL but I can’t guarantee which (if any) clients will be able to connect to it, even locally. Hopefully this will get fixed at some point. I am leaving the instructions here just for educational purposes in case anyone wants to try to get to the bottom of this issue.

The current version of Debian does not support older versions of OpenSSL, which MS-SQL 2017 requires. Although some package managers allow you to ignore this dependency apt does not. The following workaround worked for me, but is not officially recommended. So do it at your own risk. I am assuming you are running Debian 9 (stretch), so we will add the repos for Debian 8 (jessie) in order to install the required version of OpenSSL libraries (this shouldn’t overwrite any of your existing files since we aren’t installing the older binaries).

sudo cp /etc/apt/sources.list /etc/apt/sources.list.d/jessie.list
sudo sed -i 's/stretch/jessie/g' /etc/apt/sources.list.d/jessie.list
wget -qO- https://packages.microsoft.com/keys/microsoft.asc \
--no-check-certificate | sudo apt-key add -
sudo add-apt-repository \
"$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list --no-check-certificate)"
sudo apt update
sudo reboot
sudo apt install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

Only the Evaluation, Developer, and Express editions are freely licensed at this time. Choose Express if you aren’t sure which you want.

Install mssql-tools so you have a command-line tool capable of connecting to MS-SQL:

curl https://packages.microsoft.com/keys/microsoft.asc \
| sudo apt-key add -
curl https://packages.microsoft.com/config/debian/9/prod.list \
| sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools
export PATH="$PATH:/opt/mssql-tools/bin"
echo 'export PATH=$PATH:/opt/mssql-tools/bin' | \
sudo tee /etc/profile.d/mssql.sh
sudo apt install unixodbc-dev

On the current LTS release of openSUSE, I ran into an issue starting MS-SQL due to a missing library (libldap-2.4.so.2). A simple link to the existing library fixed the issue for me:

sudo ln -s libldap_r-2.4.so.2 \
/usr/lib64/libldap-2.4.so.2

If you aren’t sure if you need the above fix, run the following to check that that exact file exists:

sudo ls -lah /usr/lib64/libldap*

If prompted that you have a newer version of OpenSSL, choose the option to continue (break) MS-SQL.

sudo zypper addrepo -fc \
https://packages.microsoft.com/config/sles/12/mssql-server-2017.repo
sudo zypper --gpg-auto-import-keys refresh
sudo zypper install mssql-server
sudo /opt/mssql/bin/mssql-conf setup

Only the Evaluation, Developer, and Express editions are freely licensed at this time. Choose Express if you aren’t sure which you want.

Install mssql-tools so you have a command-line tool capable of connecting to MS-SQL:

sudo zypper addrepo -fc https://packages.microsoft.com/config/sles/12/prod.repo
sudo zypper --gpg-auto-import-keys refresh
sudo zypper install mssql-tools unixODBC-devel
export PATH="$PATH:/opt/mssql-tools/bin"
echo 'export PATH=$PATH:/opt/mssql-tools/bin' | \
sudo tee /etc/profile.d/mssql.sh

As of this writing, the latest version of MS-SQL is available via the AUR, which makes installation under Arch Linux possible (though it isn’t officially supported). Just do the following.

sudo pacman -S numactl python2-configparser sssd openssl-1.0
cd ~
git clone https://aur.archlinux.org/mssql-server.git
cd mssql-server
makepkg -Acs
sudo pacman -U *.pkg.tar.xz
sudo mv *.pkg.tar.xz /usr/local/src/
cd ~
rm -rf mssql-server
sudo /opt/mssql/bin/mssql-conf setup

Only the Evaluation, Developer, and Express editions are freely licensed at this time. Choose Express if you aren’t sure which you want.

Install mssql-tools so you have a command-line tool capable of connecting to MS-SQL (also available via the AUR):

sudo pacman -S curl krb5 unixodbc patchelf
cd ~
git clone https://aur.archlinux.org/msodbcsql.git
cd msodbcsql
makepkg -Acs
sudo pacman -U *.pkg.tar.xz
sudo mv *.pkg.tar.xz /usr/local/src/
cd ~
rm -rf msodbcsql
git clone https://aur.archlinux.org/mssql-tools.git
cd mssql-tools
makepkg -Acs
sudo pacman -U *.pkg.tar.xz
sudo mv *.pkg.tar.xz /usr/local/src/
cd ~
rm -rf mssql-tools
export PATH="$PATH:/opt/mssql-tools/bin"
echo 'export PATH=$PATH:/opt/mssql-tools/bin' | \
sudo tee /etc/profile.d/mssql.sh

Check that the service is running:

sudo systemctl status mssql-server

If it is not, this is probably due to some of the workarounds I mentioned above. To fix this, try the following (worked for every distribution I tested):

sudo systemctl restart mssql-server
sudo systemctl stop mssql-server
sudo /opt/mssql/bin/mssql-conf setup

Once you’ve run through the setup again, re-check the status of the service. It should be fine now. This fix also works if the SA account gets locked out somehow.

Now connect to the database and run a simple test (use the password you created earlier):

sqlcmd -S localhost -U SA
CREATE DATABASE TestDB
SELECT Name from sys.Databases
GO
USE TestDB
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
GO
SELECT * FROM Inventory WHERE quantity > 152;
GO
QUIT
sqlcmd -S localhost -U SA
DROP DATABASE TestDB
GO
QUIT

You will know the test succeeded if you were able to connect, create the database, create the table, create the two entries, return only the second entry, and then drop (delete) the database without any errors.

To start/stop/restart the service, do:

sudo systemctl start mssql-server
sudo systemctl stop mssql-server
sudo systemctl restart mssql-server

To enable/disable the service starting automatically:

sudo systemctl enable mssql-server
sudo systemctl disable mssql-server

PostgreSQL

CentOSFedoraUbuntuDebianopenSUSEArch Linux

The official CentOS repos currently provide PostgreSQL 9.2. Let’s install the latest stable version instead.

sudo yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
sudo yum install postgresql11 postgresql11-server postgresql11-devel
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

To start/stop/restart PostgreSQL, do the following:

sudo systemctl start postgresql-11
sudo systemctl stop postgresql-11
sudo systemctl restart postgresql-11

To enable/disable PostgreSQL automatically starting:

sudo systemctl enable postgresql-11
sudo systemctl disable postgresql-11

The official Fedora repos currently provide PostgreSQL 10.7. Let’s install the latest stable version instead.

sudo dnf install https://download.postgresql.org/pub/repos/yum/11/fedora/fedora-29-x86_64/pgdg-fedora11-11-2.noarch.rpm
sudo dnf install postgresql11 postgresql11-server postgresql11-devel
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

To start/stop/restart PostgreSQL, do the following:

sudo systemctl start postgresql-11
sudo systemctl stop postgresql-11
sudo systemctl restart postgresql-11

To enable/disable PostgreSQL automatically starting:

sudo systemctl enable postgresql-11
sudo systemctl disable postgresql-11

The official Ubuntu repos currently provide PostgreSQL 10. Let’s install the latest stable version instead.

echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" \
| sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \
--no-check-certificate | sudo apt-key add -
sudo apt update
sudo reboot
sudo apt install postgresql-11 pgadmin4 postgresql-server-dev-11

To start/stop/restart PostgreSQL, do the following:

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

To enable/disable PostgreSQL automatically starting:

sudo systemctl enable postgresql
sudo systemctl disable postgresql

The official Debian repos currently provide PostgreSQL 10. Let’s install the latest stable version instead.

echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" \
| sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \
--no-check-certificate | sudo apt-key add -
sudo apt update
sudo apt install postgresql-11 pgadmin4 postgresql-server-dev-11

To start/stop/restart PostgreSQL, do the following:

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

To enable/disable PostgreSQL automatically starting:

sudo systemctl enable postgresql
sudo systemctl disable postgresql

I spent way more time than I care to admit trying to install PostgreSQL 11 on Tumbleweed. The official repos only support SLES and not openSUSE (despite what it says on their website. To install version 11 on openSUSE, you need to downgrade several packages and can easily break your system. I’m leaving the notes on what I uncovered so far as commented HTML below (if you really want to try it I assume you know how to view the source code). So I will not be covering PostgreSQL 11 + openSUSE Tumbleweed at this time. Instead I recommend just sticking to the official repo version which is very recent by doing the following:

sudo zypper install postgresql postgresql-server

To start/stop/restart PostgreSQL, do the following:

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

To enable/disable PostgreSQL automatically starting:

sudo systemctl enable postgresql
sudo systemctl disable postgresql

Arch Linux maintains very recent versions of PostgreSQL, so best to just install it via pacman:

sudo pacman -S postgresql
sudo -iu postgres
initdb -D /var/lib/postgres/data
exit
sudo touch /var/lib/postgres/.psql_history
sudo chown postgres:postgres /var/lib/postgres/.psql_history

To start/stop/restart PostgreSQL, do the following:

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

To enable/disable PostgreSQL automatically starting:

sudo systemctl enable postgresql
sudo systemctl disable postgresql

Now run a simple test to make sure everything works (replace xxx with your own username and yyy with your password):

sudo su - postgres
psql
CREATE SCHEMA test;
CREATE USER xxx PASSWORD 'yyy';
GRANT ALL ON SCHEMA test TO xxx;
GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;
\q
exit
psql -d postgres
CREATE TABLE test.test (coltest varchar(20));
insert into test.test (coltest) values ('It works!');
SELECT * from test.test;
DROP TABLE test.test;
\q
sudo su - postgres
psql
DROP SCHEMA test;
\q
exit

Db2

Given its complexity and the amount of resources Db2 12 requires, you will probably not see it used much for web servers any longer, so feel free to skip this. In fact, other than the installation process, I will not be covering it much in this year’s tutorial.

Previously I had recommended the Express C edition, but it appears to no longer be available. A few days before I created this post, IBM created a blog post, which as I read it basically says that students and developers can install any version of Db2 provided it is not being used in a production environment. As always, I recommend checking with the company or consulting a lawyer, but that’s my best understanding. Here is the post, which also contains a link to the download page. Choose the standard Db2 version rather than the Developer Edition if you are sure you are using it legally. (At this time, the link for the Developer Edition points to a Docker image, so if installing that version, find the link for the Developer-C Edition instead, which I am not covering here, though the installation process is similar.) Download the Linux x64 tarball by whichever means you wish and then transfer it to your home directory on your test server just as we did with Oracle above.

Important: these instructions assume this is a clean installation and not an upgrade.

Now do the following:

CentOSFedoraUbuntuDebianopenSUSEArch Linux
sudo yum install pam.i686

Extract the archive and perform the initial installation:

tar -zxvf v11.1_linuxx64_server_t.tar.gz
rm v11*.tar.gz
cd server_t
sudo ./db2_install

Fedora is not a supported platform for Db2, so you will need to bypass the prerequisite checks. The following worked for me, but I can’t guarantee anything.

sudo dnf install pam.i686

Extract the archive and perform the initial installation:

tar -zxvf v11.1_linuxx64_server_t.tar.gz
rm v11*.tar.gz
cd server_t
sudo ./db2_install -f sysreq
sudo apt install libpam0g:i386 lib32stdc++6

Extract the archive and perform the initial installation:

tar -zxvf v11.1_linuxx64_server_t.tar.gz
rm v11*.tar.gz
cd server_t
sudo ./db2_install

Debian is not a supported platform for Db2, so you will need to bypass the prerequisite checks. The following worked for me, but I can’t guarantee anything.

sudo apt install libpam0g:i386 lib32stdc++6

Extract the archive and perform the initial installation:

tar -zxvf v11.1_linuxx64_server_t.tar.gz
rm v11*.tar.gz
cd server_t
sudo ./db2_install -f sysreq

openSUSE is not a supported platform for Db2, so you will need to bypass the prerequisite checks. The following worked for me, but I can’t guarantee anything.

sudo zypper install pam-32bit libstdc++6-32bit

Extract the archive and perform the initial installation:

tar -zxvf v11.1_linuxx64_server_t.tar.gz
rm v11*.tar.gz
cd server_t
sudo ./db2_install -f sysreq

Arch Linux is not a supported platform for Db2, so you will need to bypass the prerequisite checks. The following worked for me, but I can’t guarantee anything.

sudo pacman -S lib32-pam lib32-gcc-libs

Extract the archive and perform the initial installation:

tar -zxvf v11.1_linuxx64_server_t.tar.gz
rm v11*.tar.gz
cd server_t
sudo ./db2_install -f sysreq

If you get an error about TSAMP, you can safely ignore it according to IBM. See this article.

Install the server. Everything else is optional. I will assume you are installing to the default path.

Before continuing with the setup, you need to create a response file. You have two options. You can customize your own response file by doing the following:

cp db2/linuxamd64/samples/db2server.rsp ./
nano db2server.rsp

And then go through hundreds and hundreds of lines, reading all the notes as you go. Your second option is to do the following which will give you a basic installation:

nano db2server.rsp

Then paste the following in this new file, replacing "yyyyyy" with whatever password(s) you desire. You can also change user and instance names if you wish, but for this tutorial I will assume you went with defaults.

PROD                      = DB2_SERVER_EDITION
FILE                      = /opt/ibm/db2/V11.1
LIC_AGREEMENT             = ACCEPT
INSTALL_TYPE              = TYPICAL
INSTANCE                  = DB2_INST
DB2_INST.NAME             = db2inst1
DB2_INST.GROUP_NAME       = db2iadm1
DB2_INST.HOME_DIRECTORY   =
DB2_INST.PASSWORD         = yyyyyy
DB2_INST.AUTOSTART        = NO
DB2_INST.START_DURING_INSTALL = YES
DB2_INST.SVCENAME        = db2c_db2inst1
DB2_INST.PORT_NUMBER     = 50000
DB2_INST.FCM_PORT_NUMBER = 60000
DB2_INST.DB2CF_PORT_NUMBER = 56001
DB2_INST.DB2CF_MGMT_PORT_NUMBER = 56000
DB2_INST.FENCED_USERNAME  = db2sdfe1
DB2_INST.FENCED_GROUP_NAME = db2fsdm1
DB2_INST.FENCED_PASSWORD = yyyyyy
CentOS/UbuntuFedora/Debian/openSUSE/Arch Linux
sudo ./db2setup -r db2server.rsp
sudo ./db2setup -r db2server.rsp -f sysreq

Now create a test database and table to make sure everything works (the database creation process may take a while so be patient):

sudo su - db2inst1
db2
START DATABASE MANAGER
CREATE DATABASE test
ACTIVATE DATABASE test
CONNECT TO test USER db2inst1
CREATE TABLESPACE test
CREATE TABLE test(id int, message varchar(50), datetest date, hiddentest varchar(50) implicitly hidden) in test
INSERT INTO test(id, message, datetest, hiddentest) values(1, 'It worked!', current timestamp, 'This is hidden')
SELECT * FROM test
SELECT id, message, datetest, hiddentest FROM test
DROP TABLE test
DROP TABLESPACE test
DISCONNECT test
STOP DATABASE MANAGER
START DATABASE MANAGER
DROP DATABASE test
QUIT
exit

Remove the installation directory:

cd ~
rm -rf server_t

To start Db2:

sudo su - db2inst1
~/sqllib/db2profile
db2start
exit

To stop Db2:

sudo su - db2inst1
~/sqllib/db2profile
db2
START DATABASE MANAGER
LIST APPLICATIONS
FORCE APPLICATION ALL
QUIT
db2stop
exit

To start/stop/restart the fault monitor daemon under CentOS/Fedora/openSUSE/Arch Linux do the following:

sudo systemctl start db2fmcd
sudo systemctl stop db2fmcd
sudo systemctl restart db2fmcd

To enable/disable the fault monitor daemon starting automatically under CentOS/Fedora/openSUSE/Arch Linux:

sudo systemctl enable db2fmcd
sudo systemctl disable db2fmcd

To start/stop the fault monitor daemon on Ubuntu/Debian:

sudo /opt/ibm/db2/V11.1/bin/db2fm -i db2inst1 -a on
sudo /opt/ibm/db2/V11.1/bin/db2fm -i db2inst1 -f on
sudo /opt/ibm/db2/V11.1/bin/db2fm -i db2inst1 -a off
sudo /opt/ibm/db2/V11.1/bin/db2fm -i db2inst1 -f off

SAP ASE

The Enterprise Edition is available for free for nonproduction use per the official website. Check that site for more information on deciding which edition to install. I will cover installing the Express Edition since it has a smaller footprint, is free, and is suitable for most development purposes.

First, download and transfer the archive from the above website just as shown for Oracle above. Then do the following (change filenames as appropriate).

Setup the Sybase user:

sudo useradd -rmd /opt/sap sybase
sudo groupadd sybase
sudo usermod -a -G sybase sybase
sudo passwd sybase

Ignore any errors in the above commands; I customized them for different distributions (mainly openSUSE). Now extract the source files and set permissions for them:

sudo mkdir /usr/local/src/sybase
cd ~
sudo tar -xvf ASE_Suite.linuxamd64.tgz --directory=/usr/local/src/sybase
sudo chown -R sybase:sybase /usr/local/src/sybase
rm ASE*.tgz
cd /usr/local/src/sybase

Install (this will take a while, so be patient):

sudo -u sybase ./setup.bin

Go with the defaults for most options, but choose the Express Edition, select the option to "Configure new SAP ASE" and configure the server under the sybase username. And be sure the SAP ASE name is something you will remember (I used "LAMP" for this tutorial). I would also disable Cockpit monitoring since this is a development machine.

If something goes wrong you can uninstall SAP with the following before trying the last command again:

pgrep -u sybase | sudo xargs kill -9
sudo userdel -r sybase

Do a few things to secure the installation:

sudo rm -rf /opt/sap/OCS-16_0/sample
sudo rm -rf /opt/sap/DataAccess/ODBC/samples
sudo rm -rf /opt/sap/jConnect-16_0/sample2
sudo rm -rf /opt/sap/jConnect-16_0/classes/sample2
sudo rm -rf /opt/sap/ASE-16_0/sample
sudo rm -rf /opt/sap/WS-16_0/samples

Now run the following commands:

echo -e '/opt/sap/OCS-16_0/lib3p64\n/opt/sap/ASE-16_0/symlib' | sudo tee /etc/ld.so.conf.d/sybase.conf
sudo ldconfig -v
echo -e ". /opt/sap/SYBASE.sh" | sudo tee /etc/profile.d/sybase.sh
sudo reboot

SAP ASE does not come with any init scripts. Since most distributions are moving towards systemd, we should create a custom script. The following is based on this post, but includes fixes for certain distributions.

sudo nano /etc/systemd/system/sybase.service

In this file paste the following (change paths if you didn’t go with defaults). Important: I commented out the PIDFile line as it caused problems under several distributions, but you may need to uncomment it in some cases (I had to do so only with openSUSE). While the highly modified init script for Arch Linux would technically work for all distributions, my workaround there isn’t perfect. For example: I noticed in a few cases that systemd would report the service as running even if something had gone wrong. But it was the only way I could get it to work under Arch Linux.

Most DistributionsArch Linux
[Unit]
Description=SYBASE ASE
After=network.target

[Service]
ExecStart=/opt/sap/sybase_start start
Type=forking
#PIDFile=/opt/sap/sybase.pid
ExecStop=/opt/sap/sybase_start stop
[Unit]
Description=SYBASE ASE
After=network.target

[Service]
ExecStart=/opt/sap/sybase_start start
Type=forking
#PIDFile=/opt/sap/sybase.pid
ExecStop=/opt/sap/sybase_start stop
Type=oneshot
RemainAfterExit=yes

Restart=no

Now create the start script:

sudo touch /opt/sap/sybase_start
sudo chmod +x /opt/sap/sybase_start
sudo nano /opt/sap/sybase_start

Paste the following, changing paths and read the notes to customize to your installation (be sure to set your password):

#!/bin/sh
#
# Startup script for Sybase ASE
#
# description: Sybase Adaptive Server Enterprise
# is a SQL database server.
# processname: dataserver

#change the path to where sybase has been installed,
#how your server is named, the username,
#and the sa password
SYBASE=/opt/sap
SERVER=LAMP
USER=sybase
PASSWORD=replace_this_with_your_password

# Source environment variables.
. $SYBASE/SYBASE.sh

# Find the name of the script
NAME=`basename $0`

# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]
then
  SU=runuser
else
  SU=su
fi

start() {
  SYBASE_START=$"Starting ${NAME} service: "
  $SU $USER -c ". $SYBASE/SYBASE.sh; $SYBASE/$SYBASE_ASE/bin/startserver -f $SYBASE/$SYBASE_ASE/install/RUN_${SERVER} > /dev/null"
  ret=$?
  if [ $ret -eq 0 ]
  then
    showserver | grep dataserver | awk '{ print $4 }' > /opt/sap/sybase.pid
    echo "$SYBASE_START Success."
  else
    echo "$SYBASE_START Failed!"
    exit 1
  fi
}

stop() {
  echo -n $"Stopping ${NAME} service: "

#password needs to be given in clear text:
  #$SU $USER -c ". $SYBASE/SYBASE.sh; isql -S $SERVER -U sa -P '$PASSWORD' < #$SYBASE/$SYBASE_ASE/upgrade/shutdown.sql > /dev/null"

#kill the process
  pkill dataserver
  ret=$?
  if [ $ret -eq 0 ]
  then
    echo "Success."
  else
    echo "Failed!"
    exit 1
  fi
}

restart() {
  stop
  start
}

case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  restart)
    restart
    ;;
  *)
    echo $"Usage: $0 {start|stop|restart}"
    exit 1
esac
exit 0

Finally, reload the services:

sudo systemctl daemon-reload

You should now be able to start/stop/restart/check status of the Sybase server with the following (if your database instance is something other than "LAMP" change to match):

sudo systemctl start sybase
sudo systemctl stop sybase
sudo systemctl restart sybase
sudo systemctl status sybase

Restart the service and check the status to ensure everything works.

If you did not set an sa password during installation you should do so now (replace "LAMP" with your server name and "new_password" with your desired password):

sudo su - sybase
isql -U sa -P -S LAMP
sp_password null, new_password
go
quit
exit

To test that SAP ASE is running, run the following which should return the version number (replacing the server name as needed):

sudo su - sybase
isql -U sa -S LAMP
select @@version
go

Create a test database and table to make sure everything works:

create database test
go
create table test..test (text char(32)not null)
go
insert into test..test (text) values ('It Works!')
go
select * from test..test
go
drop database test
go
select name from master..sysdatabases
go
quit
exit

The above should create a database and table with a single column and return the value as shown, then at the end show that the test database has been removed.

To enable/disable SAP ASE starting automatically:

sudo systemctl enable sybase
sudo systemctl disable sybase

Firebird

Important: as of this writing, the most recent version of Ubuntu has a lot of issues installing Firebird from source in my testing. At least under VirtualBox, I had a lot of segfaults, regardless of which version of GCC I used. You can still install it via apt with sudo apt install firebird-server, but you would then need to change your paths for the commands below since it would not be installed under /opt. In short, for this tutorial I am not supporting a Firebird installation under Ubuntu. If someone finds a solution to this issue please let me know. I used valgrind, went through core dumps with backtrace, talked to Ubuntu experts on multiple forums, but couldn’t find a solution as of this date.

For this tutorial we will be installing the new version 3.0.4. Although some distributions like Fedora provide the latest version in their official repos we will still be installing from source for several reasons (lack of init scripts, inability to choose installation directory, etc.).

Check the official website to find the latest version. Change that in the second and last lines below if necessary:

cd ~
wget --no-check-certificate https://github.com/FirebirdSQL/firebird/releases/download/R3_0_4/Firebird-3.0.4.33054-0.tar.bz2
tar -xvjf Firebird-*.tar.bz2
cd Firebird-3.0.4.33054-0

Configure and install Firebird:

./configure --prefix=/opt/firebird
make
sudo -E env "PATH=$PATH" make install
sudo cp /opt/firebird/misc/firebird-superserver.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl restart firebird-superserver
cd ~
rm Firebird-*.tar.bz2
sudo mv Firebird-* /usr/local/src/

To start/stop/restart Firebird:

sudo systemctl start firebird-superserver
sudo systemctl stop firebird-superserver
sudo systemctl restart firebird-superserver

To enable/disable Firebird starting automatically:

sudo systemctl enable firebird-superserver
sudo systemctl disable firebird-superserver

Now let’s create a test database to make sure everything works:

sudo mkdir /opt/firebird/databases
sudo /opt/firebird/bin/isql
CREATE DATABASE '/opt/firebird/databases/test.fdb' page_size 8192
user 'SYSDBA' password 'replace_this_with_your_sysdba_password';
quit;
sudo chown -R firebird:firebird /opt/firebird/databases
echo 'test = /opt/firebird/databases/test.fdb' | sudo tee -a /opt/firebird/databases.conf
sudo /opt/firebird/bin/isql
CONNECT test;
CREATE TABLE test (
text char(64));
INSERT INTO test (text) VALUES ('It works!');
SELECT * FROM test;
commit;
DROP TABLE test;
DROP DATABASE;
quit;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.