Making queries with PHP 5.3 and DB2 over ODBC on top of CentOS 6.7

This is  a follow-up to my previous posting about setting up DB2 and ODBC on top of CentOS 6.7.

The pre-requirement for the setup is to have the database running on the same system. If this is not the case, download and install the DB2 ODBC drivers from IBM. The ODBC drivers of this setup have been included with the DB2 installation, so this setup uses them locally.

Please see my previous posting for contents of odbcinst.ini and odbc.ini.

 1) Install the http server, php and ODBC php package:

yum install httpd php php-odbc

2) Start up the server

service httpd start

3) Create the test application ie. /var/www/html/db2.php:

<?php
# Probably not mandatory
putenv("DB2INSTANCE=db2inst1");

$conn = odbc_connect('SAMPLE','db2inst1','[password]');

# This could be used if there is no odbc.ini
#$conn = odbc_connect('DRIVER={DB2};DATABASE=SAMPLE','db2inst1','[password]');

if($conn === false){
    die('failed to connect');
}

$qry = "SELECT * FROM EMPLOYEE";
$res = odbc_exec($conn,$qry) or die("Query error: $qry\n");

while( $row = odbc_fetch_object($res) ) {
       print_r($row);
}

odbc_close($conn);
?>


4) Access the page with a browser ie.  http://[host]/db2.php

Installing DB2 10.5 Express-C edition with ODBC on CentOS 6.7

One day I needed a DB2 for proof-of-concept testing. IBM has a "free" Express-C-edition available with restricted license. 

Altough there is a wide range of native drivers for DB2 available, but I discovered that most of them required whole lot of more work than generic ODBC (in case of PHP for example). For the sake of simplicity using ODBC was suggested in some other postings I found, so that was the approach I decided to take as well. Generally said, this is not production-grade setup, please don't use this as a one.

Here's what steps should be taken to get it up and running:

1) Download "DB2 Express-C for Linux 64-bit" from IBM, it's located at the time of writing here: http://www-01.ibm.com/software/data/db2/express-c/download.html

2) On the test system:
tar zxvf v10.5_linuxx64_expc.tar.gz

3) Install the database (without X11 installer). I found out that the X11 installer ("db2setup") does not always work as expected, so skip that one and go directly to manual installation. Execute using root privileges:

./expc/db2prereqcheck

This will report the issues with the prerequirements which I did have apparently plenty. Fortunately for testing you just need fulfill the requirements for  "Checking prerequisites for DB2 installation. Version "10.5.0.5". Operating system "Linux"" and not for the pureScale option, so just check the first part of the tools output.

These 32-bit packages were missing from the installation I had to install:
yum install libstdc++.i686 pam.i686

And then (horrible) put selinux on permissive mode (this a test system):
edit /etc/selinux/config, mode=permissive

hint: Remember to reboot to get the selinux config active.

4) You should be ready to install the database itself regardless of complaint about the db2_install-tool is deprecated:

[root@localhost ~]# ./expc/db2_install 

Answer "yes" for the question about the path under /opt/ibm/db2. The installation should run successfully.

5) Add the users and configure the instance and populate with sample data:

[root@localhost ~]# useradd db2inst1
[root@localhost ~]# passwd db2inst1 (use the password of your choice)
[root@localhost ~]# /opt/ibm/db2/V10.5/instance/db2icrt -p 50000 -u db2inst1 db2inst1

Then start up the server and populate the sample data:

[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2start
SQL1063N  DB2START processing was successful.
[db2inst1@localhost ~]$ db2sampl
  Creating database "SAMPLE"...
  Connecting to database "SAMPLE"...
  Creating tables and data in schema "DB2INST1"...
  Creating tables with XML columns and XML data in schema "DB2INST1"...

  'db2sampl' processing complete.
[db2inst1@localhost ~]$ 

6) Test you're able to see some sample data:

[db2inst1@localhost ~]$ db2

...

db2 => select * from EMPLOYEE

...

EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE   JOB      EDLEVEL SEX BIRTHDATE  SALARY      BONUS       COMM       

------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------

000010 CHRISTINE    I       HAAS            A00      3978    01/01/1995 PRES          18 F   08/24/1963   152750.00     1000.00     4220.00

000020 MICHAEL      L       THOMPSON        B01      3476    10/10/2003 MANAGER       18 M   02/02/1978    94250.00      800.00     3300.00

...

7) Success - It's working. Now you should have DB2 running on port 50000/tcp. You can have a try with a SQL tool and DB2 driver (java drivers have been included with the package, db2jcc_license_cu.jar and db2jcc4.jar in /opt/ibm/db2/V10.5/java). 

JDBC URL: jdbc:db2://[host or ip]:50000/SAMPLE 
Username: db2inst1
Password: [whatever you used in step 5]

Hint: Make sure there is no firewall blocking the port.

8) Now to the ODBC setup:

[root@localhost ~]# yum install unixODBC

Add to /etc/odbcinst.ini the following lines:
[DB2]
Description     = DB2 Driver
Driver64        = /opt/ibm/db2/V10.5/lib64/libdb2.so
FileUsage       = 1

And for testing purposes create a file /etc/odbc.ini with contents:

[SAMPLE]

Driver=DB2

9) Test the setup using ODBC isql tool:

[root@localhost ~]# isql -v SAMPLE db2inst1 [your password here]

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> SELECT * FROM EMPLOYEE

+-------+-------------+--------+----------------+---------+--------+-----------+---------+--------+----+-----------+------------+------------+------------+

| EMPNO | FIRSTNME    | MIDINIT| LASTNAME       | WORKDEPT| PHONENO| HIREDATE  | JOB     | EDLEVEL| SEX| BIRTHDATE | SALARY     | BONUS      | COMM       |

+-------+-------------+--------+----------------+---------+--------+-----------+---------+--------+----+-----------+------------+------------+------------+

| 000010| CHRISTINE   | I      | HAAS           | A00     | 3978   | 1995-01-01| PRES    | 18     | F  | 1963-08-24| 152750.00  | 1000.00    | 4220.00    |

...

10) It's working. Now proceed to use the ODBC with PHP or whatever you like.

ODBC DSN using host/port without odbc.ini would be:

'DRIVER={DB2};DATABASE=SAMPLE;SYSTEM=localhost;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP'

ODBC DSN without odbc.ini (the database on the same system) would be:

'DRIVER={DB2};DATABASE=SAMPLE'

ODBC DSN using the database alias defined in odbc.ini would be as follows:

'SAMPLE'

Happy hacking with DB2 and ODBC!