This covers the installation of IBM DB2 Connect Personal Edition on Gentoo Linux and integration with PHP and Apache.
IBM DB2 allows us to connect to our AS/400 JDE system and run SQL queries. Which we do so we can make pretty HTML reports for sales & management :)
NOTE: I recently saw this http://en.gentoo-wiki.com/wiki/Db2
NOTE : This is what I'm using it with, may not be an absolute requirement..
I recently instaleld this on CentOS 6.2, I needed the following packages installed as well.
I also had some problems where apache wasn't allowed to access the db2 library files due to SELinux restrictions. So for now, I just disabled SELinux.
http://www.zimbio.com/Linux/articles/5GcsxCWN6MD/How+Disable+SELinux+CentOS+6+2
Notes http://codehole.blogspot.com/2010/02/setting-up-db2-for-php-on-centos.html
http://werk.feub.net/2010/11/ingredients-php-db2-and-unixodbc/
Extract DB2_CONPE_V913_LNXX86.tar to any temp folder, then cd into that folder. I used /opt/db2conpe
./db2_install Default directory for installation of products - /opt/ibm/db2/V9.1 *********************************************************** Do you want to choose a different directory to install [yes/no] ? no Specify one or more of the following keywords, separated by spaces, to install DB2 products. CLIENT Enter "help" to redisplay product names. Enter "quit" to exit. *********************************************************** CLIENT DB2 installation is being initialized. Total number of tasks to be performed: 32 Total estimated time for all tasks to be performed: 581 Task #1 start Description: Checking license agreement acceptance Estimated time 1 second(s) Task #1 end Task #2 start Description: Product Messages - English Estimated time 10 second(s) Task #2 end Task #3 start Description: Base client support Estimated time 69 second(s) Task #3 end Task #4 start Description: IBM Software Development Kit (SDK) for Java(TM) Estimated time 104 second(s) Task #4 end Task #5 start Description: Java Help (HTML) - English Estimated time 7 second(s) Task #5 end Task #6 start Description: Java support Estimated time 6 second(s) Task #6 end Task #7 start Description: SQL procedures Estimated time 3 second(s) Task #7 end Task #8 start Description: ICU Utilities Estimated time 39 second(s) Task #8 end Task #9 start Description: Java Common files Estimated time 20 second(s) Task #9 end Task #10 start Description: Control Center Help (HTML) - English Estimated time 13 second(s) Task #10 end Task #11 start Description: Configuration Assistant Estimated time 4 second(s) Task #11 end Task #12 start Description: Spatial Extender client Estimated time 6 second(s) Task #12 end Task #13 start Description: Base application development tools Estimated time 11 second(s) Task #13 end Task #14 start Description: Control Center Estimated time 38 second(s) Task #14 end Task #15 start Description: Replication tools Estimated time 13 second(s) Task #15 end Task #16 start Description: DB2 LDAP support Estimated time 3 second(s) Task #16 end Task #17 start Description: DB2 Instance Setup wizard Estimated time 4 second(s) Task #17 end Task #18 start Description: XML Extender Estimated time 11 second(s) Task #18 end Task #19 start Description: First Steps Estimated time 3 second(s) Task #19 end Task #20 start Description: Product Signature for DB2 Client Estimated time 5 second(s) Task #20 end Task #21 start Description: DB2 Web Tools Estimated time 12 second(s) Task #21 end Task #22 start Description: XML Extender samples Estimated time 3 second(s) Task #22 end Task #23 start Description: ADT sample programs Estimated time 18 second(s) Task #23 end Task #24 start Description: Spatial Extender samples Estimated time 3 second(s) Task #24 end Task #25 start Description: Setting DB2 library path Estimated time 120 second(s) Task #25 end Task #26 start Description: Executing control tasks Estimated time 20 second(s) Task #26 end Task #27 start Description: Updating global registry Estimated time 20 second(s) Task #27 end Task #28 start Description: Updating the db2ls link Estimated time 1 second(s) Task #28 end Task #29 start Description: Registering DB2 licenses Estimated time 5 second(s) Task #29 end Task #30 start Description: Setting default global profile registry variables Estimated time 1 second(s) Task #30 end Task #31 start Description: Initializing instance list Estimated time 5 second(s) Task #31 end Task #32 start Description: Updating global profile registry Estimated time 3 second(s) Task #32 end The execution completed successfully. For more information see the DB2 installation log at "/tmp/db2_install.log.26132".
This should have installed IBM DB2 Connect PE to /opt/ibm
useradd db2inst1 mkdir /home/db2inst1
cd /opt/ibm/db2/V9.1/instance/ ./db2icrt -a server -u db2inst1 db2inst1 DBI1768W Unable to add /home/db2inst1/sqllib/db2profile to the .profile file or /home/db2inst1/sqllib/db2cshrc to the .login file of user db2inst1. Explanation: DB2 has failed to modify the .profile file or the .login file of this user. These files may not exist, or you may not have write permission to them. Without the modification you need to set your environment manually each time when you log in as this user to use DB2. User Response: Add the following line to the .profile file or the .login file: . /home/db2inst1/sqllib/db2profile to the .profile file, or source /home/db2inst1/sqllib/db2cshrc to the .login file See the Quick Beginning Guide for more details on how to set the DB2 environment automatically. DBI1070I Program db2icrt completed successfully.
This will have populated the /home/db2inst1 folder with a bunch of files.
I am not 100% on this section - I mean.. It works and all for me in my environment but finding documentation on it was shetchy so there may be more details that I am missing for a proper setup. I am also not a IBM guy so my description of some things may be a bit off. I believe you can do this step via some gnome/kde GUI - and there's more documentation supporting that method. I, however, am doing this from the command-line. What we are doing here is creating a link from the local (linux) system to the iSeries AS/400 system.
A part that confused me is the database name. Apparently on the AS/400 there is a database name and then schema's within that database name. To me, I thought the “schema” was the database name :). You get the database name from the as/400 command WRKRDBDIRE - another anoynance is once you connect to this database you must specificy the schema within your sql statements. So, if you want all users from the table user in the schema webportal you might ask “select * from webportal.users”
su - db2inst1 cd ~ . sqllib/db2profile db2 catalog tcpip node <node> remote <as400-host> server 446 remote_instance <as400-dbname> ostype os400 catalog dcs database <as400-dbname> catalog database <as400-dbname> at node <node> AUTHENTICATION DCS
You will need to modify that slightly. The items encased with < and > symbols need to be changed for your system. Here's a break down.
* <node> = Any string representation you wish to use as the “node” name for this database. I used “as400” as far as I can tell this is only used internally by DB2 Connect * <as400-host> = The host or IP address of your as400 system. * <as400-dbname> = the Database name on your as400 that you want to attach to. You can find this with the as/400 command WRKRDBDIRE, looking for the entry with a Remote Location value of “*LOCAL”.
Also, you can make aliases if you wish. I kept the same naming to keep things simple. But say you have some confusing DB name on the AS/400 (like me) but want to name it as400db on the linux system for whatever reason.
catalog tcpip node <node> remote <as400-host> server 446 remote_instance <as400-dbname> ostype os400 catalog dcs database as400db as <as400-dbname> catalog database as400db at node <node> AUTHENTICATION DCS
- Note, to remove those entries for whatever reasons
uncatalog node uncatalog dcs database uncatalog database
Now you can test it by connecting to it
connect to <as400-dbname or alias> user username using password select count(*) from SCHEMA.TABLE
Please change SCHEMA to a valid SCHEMA on the AS400 and TABLE to a valid table within that SCHEMA.
Now if you get data here, we should be all set.
Not covering how to install Apache or configure it to use PHP. Maybe someday but not today. There's 9 million examples already on the net.
I'm locking into PHP pre 5.3 due to some application compatibility problems.
I added a mask to package.mask
>=dev-lang/php-5.3.0
For my use statements
dev-lang/php cli pcre xml zlib gd exif simplexml db2 pdo-external ftp mysql sessions sockets calendar odbc apache2 sqlite
I'm sure many of that isn't needed for most but you will need to add db2 to your use flags.
Then..
. /home/db2inst1/sqllib/db2profile emerge php
emerge -va pear
emerge -va pear pecl install ibm_db2 downloading ibm_db2-1.9.1.tgz ... Starting to download ibm_db2-1.9.1.tgz (150,760 bytes) .................................done: 150,760 bytes 5 source files, building running: phpize Configuring for: PHP Api Version: 20041225 Zend Module Api No: 20060613 Zend Extension Api No: 220060519 DB2 Installation Directory? : /opt/ibm/db2/V9.1 building in /tmp/pear-build-root2qYLVb/ibm_db2-1.9.1 running: /tmp/ibm_db2/configure --with-IBM_DB2=/opt/ibm/db2/V9.1 checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for a sed that does not truncate output... /bin/sed checking for cc... cc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether cc accepts -g... yes checking for cc option to accept ISO C89... none needed checking how to run the C preprocessor... cc -E checking for icc... no checking for suncc... no checking whether cc understands -c and -o together... yes checking for system library directory... lib checking if compiler supports -R... no checking if compiler supports -Wl,-rpath,... yes checking build system type... i686-pc-linux-gnu checking host system type... i686-pc-linux-gnu checking target system type... i686-pc-linux-gnu checking for PHP prefix... /usr/lib/php5.2 checking for PHP includes... -I/usr/lib/php5.2/include/php -I/usr/lib/php5.2/include/php/main -I/usr/lib/php5.2/include/php/TSRM -I/usr/lib/php5.2/include/php/Zend -I/usr/lib/php5.2/include/php/ext -I/usr/lib/php5.2/include/php/ext/date/lib checking for PHP extension directory... /usr/lib/php5.2/lib/extensions/no-debug-non-zts-20060613 checking for PHP installed headers prefix... /usr/lib/php5.2/include/php checking if debug is enabled... no checking if zts is enabled... no checking for re2c... no configure: WARNING: You will need re2c 0.13.4 or later if you want to regenerate PHP parsers. checking for gawk... gawk checking for IBM_DB2 support... yes, shared checking Looking for DB2 CLI libraries... checking in /opt/ibm/db2/V9.1... checking in /opt/ibm/db2/V9.1/lib64... checking in /opt/ibm/db2/V9.1/lib32... found checking for DB2 CLI include files in default path... checking in /opt/ibm/db2/V9.1... found in /opt/ibm/db2/V9.1 checking for ld used by cc... /usr/i686-pc-linux-gnu/bin/ld checking if the linker (/usr/i686-pc-linux-gnu/bin/ld) is GNU ld... yes checking for /usr/i686-pc-linux-gnu/bin/ld option to reload object files... -r checking for BSD-compatible nm... /usr/bin/nm -B checking whether ln -s works... yes checking how to recognize dependent libraries... pass_all checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking dlfcn.h usability... yes checking dlfcn.h presence... yes checking for dlfcn.h... yes checking the maximum length of command line arguments... 1572864 checking command to parse /usr/bin/nm -B output from cc object... ok checking for objdir... .libs checking for ar... ar checking for ranlib... ranlib checking for strip... strip checking if cc supports -fno-rtti -fno-exceptions... no checking for cc option to produce PIC... -fPIC checking if cc PIC flag -fPIC works... yes checking if cc static flag -static works... yes checking if cc supports -c -o file.o... yes checking whether the cc linker (/usr/i686-pc-linux-gnu/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... no checking dynamic linker characteristics... GNU/Linux ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... yes checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... no creating libtool appending configuration tag "CXX" to libtool configure: creating ./config.status config.status: creating config.h running: make /bin/sh /tmp/pear-build-root2qYLVb/ibm_db2-1.9.1/libtool --mode=compile cc -I. -I/tmp/ibm_db2 -DPHP_ATOM_INC -I/tmp/pear-build-root2qYLVb/ibm_db2-1.9.1/include -I/tmp/pear-build-root2qYLVb/ibm_db2-1.9.1/main -I/tmp/ibm_db2 -I/usr/lib/php5.2/include/php -I/usr/lib/php5.2/include/php/main -I/usr/lib/php5.2/include/php/TSRM -I/usr/lib/php5.2/include/php/Zend -I/usr/lib/php5.2/include/php/ext -I/usr/lib/php5.2/include/php/ext/date/lib -I/opt/ibm/db2/V9.1/include -DHAVE_CONFIG_H -g -O2 -c /tmp/ibm_db2/ibm_db2.c -o ibm_db2.lo /tmp/pear-build-root2qYLVb/ibm_db2-1.9.1/libtool: line 480: CDPATH: command not found /tmp/pear-build-root2qYLVb/ibm_db2-1.9.1/libtool: line 1304: func_opt_split: command not found libtool: Version mismatch error. This is libtool 2.2.10, but the libtool: definition of this LT_INIT comes from an older release. libtool: You should recreate aclocal.m4 with macros from libtool 2.2.10 libtool: and run autoconf again. make: *** [ibm_db2.lo] Error 63 ERROR: `make' failed
As you can see this didn't work. Maybe someday they'll fix it. IF it works for you then they must have fixed it.
So, here's my work-around.
cd /usr/src wget http://pecl.php.net/get/ibm_db2 mv ibm_db2 ibm_db2-1.9.1.tgz ## (wget saved the file as just ibm_db2) gzip -d ibm_db2-1.9.1.tgz tar xvvf ibm_db2-1.9.1.tar cd ibm_db2-1.9.1 phpize rm aclocal.m4 autoreconf ./configure --with-IBM_DB2=/home/db2inst1/sqllib/ make mkdir /usr/lib/php5.2/lib/extensions/ mkdir /usr/lib/php5.2/lib/extensions/no-debug-non-zts-20060613 # above two folders didn't exist for me but it was defined as the extension_dir in /etc/php/apache2-php5.2/php.ini cp modules/ibm_db2.so /usr/lib/php5.2/lib/extensions/no-debug-non-zts-20060613/
You need the following two lines added to your php.ini file.
[PHP] extension=ibm_db2.so [ibm_db2] ibm_db2.instance_name=db2inst1
You can use a phpinfo() command and make sure the heading ibm_db2 exists. If so you should have ibm_db2 module installed and working. You may need to restart apache if you just installed it.
<? phpinfo(); ?>
Here's a quick sample test to see if php is really working with db2 or not.
<?php $database = 'dbname'; $user = 'username'; $password = 'password'; $link = db2_connect($database,$user,$password); if (!$link) { print db2_conn_errormsg(); } else { print "It works!"; db2_close($link); } ?>
build test failed. Please check the config.log for details. You need to source your DB2 environment before running PHP configure: # . $IBM_DB2/db2profile
As it mentions, you need to source the db2profile first.
. /home/db2inst1/db2profile