Creating LAMP VM – Other Databases

This is part of a series of posts on creating your own LAMP virtual machine for development purposes. Here we will cover the installation of other databases besides MySQL/MariaDB. Since I plan to use PDO, I can develop for several types of database, so may as well install any I might need. Only install what you need! Installing everything will eat up resources.

Estimated time (this post only): 1 hour, 20 minutes
* if installing everything

OraclePostgreSQLIBM DB2Sybase/SAPInformixFirebird
The most popular DBMS in the world. If you need to develop for it, you have several choices of which version to install. The official documentation from Oracle provides more detail on the differences between each. But can you install any of them you want? That is a bit of a clouded issue. It is generally agreed you can install any version of Oracle for free for development purposes, but some people interpret their license agreement differently. At any rate, there are two versions you can certainly install for free without violating the license: Express and Personal. Express is a lighter and simpler version of Oracle Database that you can use for any purpose you want. Personal Edition is essentially a limited version of the full Oracle Database, containing most features of even Enterprise, but it is limited to a single machine, so you won’t be able to connect to it remotely.

I didn’t need all the features of Enterprise or even Standard, so I chose the Express Edition. Note that Express has not yet been upgraded to version 12 as of this writing.

Install Oracle Database EE

Visit the Oracle Database Express Edition download page with your host’s web browser and download the latest Linux x64 version to the shared folder (you will need an Oracle account). As you can see, Oracle compresses their software in the Zip format, so you may need to install unzip. As well as a couple other packages we may need:

sudo yum install unzip bc

Unzip the contents and start the installer (edit file names as necessary):

cd /usr/local/src
sudo mkdir oracle
sudo unzip /media/sf_Temp/oracle-xe-11.2.0-1.0.x86_64.rpm.zip -d ./oracle/
sudo rpm -ivh oracle/Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

After installation, configure the database, set environment variables, then restart so we can test everything. It is highly recommended that you not accept the default port of 8080 for Application Express and instead go with something like 9055 (otherwise go with defaults and set your own password as desired). (You will receive an error about netstat. That’s expected because net-tools has been deprecated in the current version of CentOS. Ignore the error.):

sudo /etc/init.d/oracle-xe configure
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
echo -e ". /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh" | sudo tee /etc/profile.d/oracle_env.sh
reboot

Now make sure the variables were set correctly:

echo $PATH
echo $ORACLE_HOME
echo $ORACLE_SID

If all went well we should be able to log in as the “oracle” user and connect to the database:

sudo su - oracle
sqlplus / as sysdba
exit
logout

You should see information on the running instance of Oracle.

Starting/Stopping Oracle

To set Oracle’s automatic startup status:

sudo chkconfig oracle-xe on
sudo chkconfig oracle-xe off

To manually start/stop/restart Oracle:

sudo service oracle-xe start
sudo service oracle-xe stop
sudo service oracle-xe restart

Install PostgreSQL

The updates repository for CentOS 7 contains PostgreSQL but as of this writing it is the older 9.2 version. If this is all you want, do the following (skip if you want another version):

sudo yum install postgresql postgresql-libs postgresql-plperl postgresql-plpython postgresql-server postgresql-devel
sudo postgresql-setup initdb

If you want the latest version, do (replacing the version with whichever one you want as listed on this page):

sudo yum install http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
sudo yum install postgresql94 postgresql94-libs postgresql94-plperl postgresql94-plpython postgresql94-server postgresql94-devel
sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb

Starting/stopping PostgreSQL

To add/remove PostgreSQL from automatic startup (leave out version number if you used the standard version, or change it as required):

sudo chkconfig postgresql-9.4 on
sudo chkconfig postgresql-9.4 off

To manually start/stop/restart PostgreSQL:

sudo service postgresql-9.4 start
sudo service postgresql-9.4 stop
sudo service postgresql-9.4 restart

Install DB2

As with Oracle, the small free Express Edition was good enough for my purposes. Visit the DB2 Express-C download page with your host web browser and download the Linux 64-bit version to your shared folder.

With only a few modifications, we will be following the steps outlined in this guide here. Although that blog post refers to version 9, it is one of the best guides I’ve found for installing DB2 Express on a headless Linux box.

Add a named port for DB2 (I added spaces to make it align with the other entries in the file, but that isn’t necessary), install prerequisites, extract archive, then edit a response file:

cd /usr/local/src
sudo sed -i '$ a\db2c_db2inst1   50000/tcp               # DB2 connection service port' /etc/services
sudo yum install pam.i686
sudo tar -zxvf /media/sf_Temp/v10.5_linuxx64_expc.tar.gz
cd expc
sudo cp db2/linuxamd64/samples/db2expc.rsp my.rsp
sudo chmod +w my.rsp
sudo nano my.rsp

Edit the appropriate lines to read as follows. Particularly note the installation directory as defined by FILE. You could also just create a new file called my.rsp and type the following (with your own passwords):

PROD = EXPRESS_C
FILE = /opt/ibm/db2
LIC_AGREEMENT = ACCEPT
INTERACTIVE = YES

CONFIG_ONLY = NO
INSTALL_TYPE = CUSTOM

COMP = ACS
COMP = DB2_DATA_SOURCE_SUPPORT
COMP = DB2_SAMPLE_DATABASE
COMP = DB2_UPDATE_SERVICE
COMP = FIRST_STEPS
COMP = INSTANCE_SETUP_SUPPORT
COMP = LDAP_EXPLOITATION
COMP = APPLICATION_DEVELOPMENT_TOOLS

INSTANCE = DB2_INST
DB2_INST.NAME = db2inst1
DB2_INST.GROUP_NAME = db2inst1
DB2_INST.HOME_DIRECTORY = /home/db2inst1
DB2_INST.PASSWORD = [some password]
DB2_INST.AUTOSTART = NO
DB2_INST.START_DURING_INSTALL = YES
DB2_INST.PORT_NUMBER = 50000

DB2_INST.FENCED_USERNAME = db2fenc1
DB2_INST.FENCED_GROUP_NAME = db2fenc1
DB2_INST.FENCED_HOME_DIRECTORY = /home/db2fenc1
DB2_INST.FENCED_PASSWORD = [some password]

DAS_USERNAME = db2das1
DAS_GROUP_NAME = db2das1
DAS_HOME_DIRECTORY = /home/db2das1
DAS_PASSWORD = [some password]

Now install:

sudo ./db2setup -r my.rsp
sudo chown -R db2inst1 /opt/ibm/db2/V10.5
sudo chgrp -R db2inst1 /opt/ibm/db2/V10.5

This will take a few minutes. If you made any mistakes in the response file above just go back and correct them and re-run setup. If something went really wrong, you can completely uninstall DB2 by doing the following, then reinstall:

sudo su - db2das1
db2admin stop
logout
sudo /opt/ibm/db2/V10.5/instance/dasdrop
sudo su - db2inst1
db2stop force
db2 terminate
logout
sudo /opt/ibm/db2/V10.5/instance/db2idrop db2inst1
sudo /opt/ibm/db2/V10.5/install/db2_deinstall -a
sudo userdel -r db2inst1
sudo userdel -r db2fenc1
sudo userdel -r db2das1

Validate the installation. You can view the created log with nano for details (it will tell you the name):

sudo /opt/ibm/db2/V10.5/bin/db2val

Create, connect to, and test a DB2 database to make sure everything works (the creation may take a while so be patient):

sudo su - db2inst1
db2 create database test
db2 connect to test user db2inst1
[Enter Password]
db2
create table myTest (name varchar(10))
insert into myTest (name) values ('test')
select * from myTest
quit
db2 disconnect test
db2 drop db test
logout

Starting/Stopping DB2

To set DB2’s auto-start status, do one of the following:

sudo -u db2inst1 -i db2iauto -on db2inst1
sudo -u db2inst1 -i db2iauto -off db2inst1

To start/stop DB2 manually:

sudo -u db2inst1 -i db2start
sudo -u db2inst1 -i db2stop
Up until now we have focused on the top 10 ranked database management systems that are commonly supported by web applications. Sybase (now called SAP ASE) is still popular enough that you are likely to encounter it at some point.

Install Sybase

I went with Express Edition, but there is also a Developer Edition available if you need the full features of Sybase ASE. Both can be found on this page and there is a PDF available to view the differences between the two. Visit that site and download to the shared directory (you will need to register if you don’t have an account). Then back in the VM (change filename if necessary):

cd /usr/local/src
sudo mkdir sybase
sudo unzip /media/sf_Temp/ase160_linuxx86-64.zip -d ./sybase
sudo useradd -rmd /opt/sap sybase
cd sybase
sudo -u sybase ./setup.bin -i console

Go with defaults for most everything (just press <Enter> for most prompts) except the following exceptions. For license type, you should change to Express or Developer as desired. Create secure passwords when prompted. Lastly, there is no need to configure anything except a new Adaptive Server (select 2, 3, etc. until there is only an X next to option 1). The server startup took a long time on my test system, so just be patient.

If something goes wrong you can uninstall SAP with the following before trying the installation 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
reboot

If all went well, after the restart you should be able to start the Sybase server with the following (if your database instance is something other than “LAMP” change to match):

sudo -i -u sybase startserver -f $SYBASE/$SYBASE_ASE/install/RUN_LAMP

In my case, after the server started I didn’t get a shell prompt but everything still works and the prompt returns after the next command you type.

If you did not set an sa password during installation you should do so now (replace “LAMP” with your server name):

isql -U sa -P -S LAMP
sp_password null, new_password
go
quit

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

isql -S LAMP -U sa -P sa_password
select @@version
go
quit

Starting/Stopping Sybase

You are going to want an init script for Sybase. Trust me on this. They don’t provide one and it is a pain in the rear without one. The following is based on information I found in a couple of other blogs. Check out Caelum’s Blog for more details but don’t copy his script since he assumes a null sa password which is not recommended (to be fair, including the password in the init script as I am doing below is not ideal either but there is no other easy way to my knowledge) and I have included INIT INFO which is missing in his example. Do:

sudo touch /etc/init.d/sybase
sudo chmod +x /etc/init.d/sybase
sudo nano /etc/init.d/sybase

The file should contain the following (change server name if needed and don’t forget to include your sa password):

#!/bin/sh
#
# Startup script for Sybase ASE
# 
# description: Sybase Adaptive Server Enterprise
# is a SQL database server.
# processname: dataserver
#
### BEGIN INIT INFO
# Provides:          sybase
# Required-Start:    $local_fs $network $remote_fs
# Required-Stop:     $local_fs $network $remote_fs
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Sybase ASE
# Description:       Starts and stops Sybase ASE
### END INIT INFO

SYBASE=/opt/sap
SERVER=LAMP
SAPW=sa_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 sybase -c ". $SYBASE/SYBASE.sh; $SYBASE/$SYBASE_ASE/bin/startserver \
-f $SYBASE/$SYBASE_ASE/install/RUN_${SERVER} > /dev/null"
 ret=$? 
 if [ $ret -eq 0 ]
 then
  echo "$SYBASE_START Success."
 else
  echo "$SYBASE_START Failed!"
              exit 1
 fi
}

stop() {
 echo -n $"Stopping ${NAME} service: "
 $SU sybase -c ". $SYBASE/SYBASE.sh; isql -S $SERVER -U sa -P '${SAPW}' < \
$SYBASE/$SYBASE_ASE/upgrade/shutdown.sql > /dev/null"
 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

Now you can just stop/start/restart Sybase with:

sudo service sybase start
sudo service sybase stop
sudo service sybase restart

To set Sybase automatic startup status:

sudo chkconfig sybase on
sudo chkconfig sybase off
Another popular DBMS which you may or may not need to develop for. As with others above, there is both an Express and Developer Edition, however the Express Edition of Informix is not free, so let’s install the Developer Edition (if needed).

Install Informix

Visit the IBM Informix Developer Edition website on your host system and follow the links to download (not the “Downloads” tab, but the link halfway down the page) the latest Developer version for Linux x86_64 to your shared directory. Note: the download page can be confusing, so be careful you pick the right version. Now back in your VM do the following (changing filename as necessary):

cd /usr/local/src
sudo mkdir informix
sudo tar -xvf /media/sf_Temp/iif.12.10.FC5DE.linux-x86_64.tar -C informix
sudo useradd -rm informix
sudo informix/ids_install -DUSER_INSTALL_DIR=/opt/informix

Go with the defaults for most everything. I chose not to use the default Apache port of 8080 since this is used by other software. Instead I went with 9080 since I hadn’t used that for anything else. In my case the database didn’t initialize properly but everything installed fine. That’s okay; we are going to take care of that in a minute.

Setup the Informix user environment (replace server name as desired):

sudo su - informix
printf 'export INFORMIXDIR=/opt/informix\nexport INFORMIXSERVER=lamp\nexport ONCONFIG=onconfig' >> .profile
printf '\nexport INFORMIXTERM=terminfo\nexport PATH=$PATH:$INFORMIXDIR/bin' >> .profile
printf '\nexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$INFORMIXDIR/lib:$INFORMIXDIR/lib/esql' >> .profile
logout
sudo su - informix

Setup the onconfig configuration files as follows (again replacing server name as desired):

cp /opt/informix/etc/onconfig.std /opt/informix/etc/onconfig
sed -i 's/^ROOTPATH.*/ROOTPATH \$INFORMIXDIR\/dbspaces\/online_root/' /opt/informix/etc/onconfig
sed -i 's/^MIRRORPATH.*/MIRRORPATH \$INFORMIXDIR\/mirrors\/root_mirror/' /opt/informix/etc/onconfig
sed -i 's/^MSGPATH.*/MSGPATH \$INFORMIXDIR\/logs\/online.log/' /opt/informix/etc/onconfig
sed -i 's/^DBSERVERNAME.*/DBSERVERNAME lamp/' /opt/informix/etc/onconfig
sed -i 's/^LTAPEDEV.*/LTAPEDEV \/dev\/null/' /opt/informix/etc/onconfig
cp /opt/informix/etc/sqlhosts.std /opt/informix/etc/sqlhosts
sed -i 's/^demo_on.*/lamp         onipcshm        localhost           lamp/' /opt/informix/etc/sqlhosts

For that last line you should replace “localhost” with the hostname of the VM if you plan on accessing Informix from any other computer, including your host system. In my case, that isn’t necessary.

Now setup the Informix instance. In my case, a process was left running under the root account from the installation process, and even using kill to forcibly kill the process still would not allow me to do the initialization as the Informix user, so I finally gave up and just rebooted, which worked fine:

logout
reboot
[After reboot, log back in]
sudo su - informix
cd $INFORMIXDIR
mkdir dbspaces mirrors logs
cd dbspaces
touch online_root
chmod 660 online_root
oninit -iv
dbaccess

Now go to “Database” > “Create” and type the name for your new database. Then select “Log” > “Buffered_log” then “Exit” > “Create-new-database.” Exit dbaccess now, then do the following which will stop Informix for now:

onmode -ky
logout

Starting/Stopping Informix

Once again we are going to create an init script based on one published in Caelum’s Blog. Do:

sudo touch /etc/init.d/informix
sudo chmod +x /etc/init.d/informix
sudo nano /etc/init.d/informix

The file should contain the following (change server name if needed):

#!/bin/sh
#
# Startup script for Informix Database Server
# 
# description: Informix RDBMS
# is a SQL database server.
# processname: oninit
### BEGIN INIT INFO
# Provides:          informix
# Required-Start:    $remote_fs $syslog $named
# Required-Stop:     $remote_fs
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start Informix RDBMS
# Description:       Start Informix RDBMS
### END INIT INFO
  

export INFORMIXDIR=/opt/informix
export INFORMIXSERVER=lamp
export ONCONFIG=onconfig
  

# 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() {
 INFORMIX_START=$"Starting ${NAME} service: "
 $SU informix -c $INFORMIXDIR/bin/oninit
 ret=$? 
 if [ $ret -eq 0 ]
 then
  echo "$INFORMIX_START Success."
 else
  echo "$INFORMIX_START Failed!"
                exit 1
 fi
 echo
}
  

stop() {
 echo -n $"Stopping ${NAME} service: "
 $SU informix -c "$INFORMIXDIR/bin/onmode -ky"
 ret=$?
 if [ $ret -eq 0 ]
 then
  echo "Success."
 else
  echo "Failed!"
  exit 1
 fi
        echo
}
  

restart() {
 stop
 start
}
  

case "$1" in
  

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

Now you can just stop/start/restart Informix with:

sudo service informix start
sudo service informix stop
sudo service informix restart

To add/remove Informix from automatic startup:

sudo chkconfig informix on
sudo chkconfig informix off
This is the last database I’m going to bother with. Of course, there are many others.

Firebird

The EPEL repository maintains current versions of Firebird (you did set that up earlier right?), so just install with:

sudo yum install firebird-superclassic firebird-devel

Starting/Stopping Firebird

To start/stop/restart Firebird manually:

sudo service firebird-superclassic start
sudo service firebird-superclassic stop
sudo service firebird-superclassic restart

To set/unset Firebird to load on startup:

sudo chkconfig firebird-superclassic on
sudo chkconfig firebird-superclassic off

Leave a Reply