DB 2 Connect

(click to open)

Quick Page Table of Contents

Scanning…

DB2 Connect to IBM i

A few notes on DB2 Connect 2-tier to IBM i that may be helpful.

Download

HEY Windows people, don’t make a 64bit mistake …

DO NOT TRY use DB2 Connect 64 bit drivers with Zend Server PHP 32bit (many hours lost by some Windows 64bit folks i know). Zend Server is 32 bit distribution (as of this date), so use 32 bit DB2 Connect drivers (not 64 bit). Don’t be fooled into attempting to mix 32/64 bit DB2 Connect drivers on your Windows 64 bit machine it will not work with Zend Server for Windows (unless Zend builds a 64bit version some day).

Linux instructions (similar for Windows):

Q: What are the packages for DB2 Connect?
A: 4 packages ...
1) IBM Data Server Driver Package (ds driver) <-- use this one (my favorite)
   (small package)- best practices client for deployment (drivers only)
2) IBM Data Server Runtime Client (runtime client)
   (large package)- superset Data Server Driver (+ utilities)
3) IBM Data Server Client (client)
   (enormous)- drivers and libraries for programming (+ GUI, all) 
4) IBM Data Server Driver for ODBC and CLI (cli driver)
   (tiny)- solely for ISVs to embed application
Note 1: In terms of DB2 Connect - all of these client packages 
        CAN BE enabled for z/OS and IBM i servers by applying 
        a DB Connect license key. 
Note 2: IBM’s Call Level Interface (CLI) is a superset of the ODBC API. 
Download: http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217

Q: How do i catalog a connection IBM i? 
A: use the db2 command line (IBM Data Server Runtime Client ++)
>su db2inst1
>db2
catalog tcpip node myIBMi remote myIBMi.lab.com server 446
catalog db myIBMi at node myIBMi authentication dcs
catalog dcs db myIBMi as myIBMi
terminate
Note: "myIBMi" - can be found using WRKRDBDIRE (local RDB name / database name)
-- or packages without db2 utility --
> sudo gedit /opt/ibm/cli/cfg/db2cli.ini
[LP0164D]
Database=myIBMi
Protocol=tcpip
Hostname=myIBMi.lab.com
Servicename=446

Q: What steps do I take to resolve conf issues?
A: common problem:
If db2 catalog fails, likely you need to install missing license.
> db2licm -a db2/license/license_filename
where license_filename represents (see Passport Advantage):
  db2conee.lic - DB2 Connect Enterprise Edition
  db2conpe.lic - DB2 Connect Personal Edition
  db2conue.lic - DB2 Connect Unlimited Edition
  db2conas.lic - DB2 Connect Application Server Edition
-- or packages without db2licm utility --
> sudo -s
> unzip DB2_CUEi_QS_Activation_97.zip  (wherever you get .lic file)
> ls db2/consv_is/db2/license/
db2consv_is.lic  db2jcc_license_cisuz.jar  sam31.lic  sam32.lic  UNIX  Windows
> cp db2/consv_is/db2/license/* /opt/ibm/cli/license/. (wherever installed)

Q: how do i test my db2 connect connection?
A: use db2 test your connection.
> db2 connect to myIBMi user myUid using myPassword

Q: Why does my DB2 Connect hang port 8471 (database)?
A: Because you need to use port 446 (DDM/DRDA).

Q: SQL0805N  Package "NULLID.SYSSH000" was not found
A: need to bind packages for application to your IBM i
$ db2
db2 => connect to lp0364d user db2 using xxxxx 
db2 => bind "/opt/ibm/db2/V10.5/bnd/@db2cli.lst" blocking all grant public
db2 => terminate
Note: On IBM i CRTLIB NULLID (if not exist, before bind)

Linux Install Zend Server + Db2 Connect to IBM i Experience

> cd Downloads
> gunzip ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar.gz
> tar -xf ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar
> sudo mv odbc_cli/clidriver /opt/ibm/cli

configure
> sudo gedit /opt/ibm/cli/cfg/db2cli.ini
[LP0164D]
Database=LP0164D
Protocol=tcpip
Hostname=lp0164d.rchland.ibm.com
Servicename=446

> sudo -s
# gedit /etc/profile
DB2_CLI_DRIVER_HOME=/opt/ibm/cli
DB2CLIINIPATH=/opt/ibm/cli/lib/cfg
PATH=$PATH:/usr/local/zend/bin:/opt/ibm/cli/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/zend/lib:/opt/ibm/cli/lib
# source /etc/profile  (after restart/ipl linux source not needed)

run (not need export if set in /etc/profile)
> export PATH=$PATH:/opt/ibm/cli/bin
> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/ibm/cli/lib
> export DB2_CLI_DRIVER_HOME=/opt/ibm/cli
> export DB2CLIINIPATH=/opt/ibm/cli/lib/cfg
> php testdb2.php


> sudo -s
# gedit /etc/apt/sources.list &
deb http://repos.zend.com/zend-server/deb server non-free
# wget http://repos.zend.com/zend.key -O- |apt-key add -
# aptitude update
# aptitude install zend-server-php-5.2
# aptitude install php-5.2-ibmdb2-zend-server
# aptitude install php-5.2-pdo-ibm-zend-server

===
web - fix db2 load extension error
===
zend server console
https://localhost:10082/ZendServer (secure) 
http://localhost:10081/ZendServer

Server Setup -> Extensions
Err ibm_db2 The system could not load this extension
Err pdo_ibm The system could not load this extension
FIX: see chrpath below (Linux PHP DB2 load issues)
> sudo -s
# apt-get install chrpath
cd /usr/local/zend/lib/php_extensions
sudo chrpath -r /opt/ibm/cli/lib/ ibm_db2.so
sudo chrpath -r /opt/ibm/cli/lib/ pdo_ibm.so
Note: assumes DB2 Connect installed as above
      (mv odbc_cli/clidriver /opt/ibm/cli)

Restart PHP (button bottom-right of Server Setup -> Extensions)
- ibm_db2 and pdo_ibm should be green for go (green on)

============
command line
(if not below)
============
> export PATH=$PATH:/usr/local/zend/bin
> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/zend/lib
> php -v
-- see php log errors --
> sudo tail /usr/local/zend/var/log/php.log

================
-- did not do --
================
configuration (all users)
> sudo -s
# gedit /etc/profile
PATH=$PATH:<install_path>/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<install_path>/lib
# source /etc/profile

-- or --

configuration (single user)
> cd
> gedit .bashrc &
PATH=$PATH:<install_path>/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<install_path>/lib
> source ~/.bashrc


================
-- did not do --
================
# aptitude install php-5.2-extra-extensions-zend-server
# aptitude install php-5.2-loader-zend-server
# aptitude install control-panel-zend-server
# aptitude install php-5.2-source-zend-server
--or --
# aptitude install zend-server-php-5.3
# same 5.3 packages above

Linux PHP DB2 load issues

You probably will not need this section, but here if you find you can’t get PHP to load the drivers. 

Q: What is Linux binary load search order?
1) RPATH compiled into binary (ibm_db2.so/pdo_ibm.so)
2) LD_LIBRARY_PATH user manual setting
3) Linux system path administrator settings
RPATH inside the binary overrides LD_LIBRARY_PATH settings 
which makes things like running a precompiled binary 
out of a user's home directory or some other non-default RPATH 
location difficult or impossible (including PHP distributions 
attempting to use DB2 Connect to IBM i).

Q: Any desperate measures force PHP to load DB2 Connect drivers?
A: Try chrpath (or patchelf).
Example Zend Server (32 bit Ubuntu):

1) Query your ibm_db2/pdo_ibm binary 
   to see where RPATH wants to find DB2 Connect driver:
cd /usr/local/zendsvr/lib/php_extensions
-- or --
cd /usr/local/zend/lib/php_extensions
sudo readelf -d  ibm_db2.so | grep -i rpath
 0x0000000f (RPATH)                      Library rpath: [/usr/lib/]
sudo readelf -d  pdo_ibm.so | grep -i rpath
 0x0000000f (RPATH)                      Library rpath: [/usr/local/zend/lib/]
Note: If there is no libdb2.so in the RPATH,
      then LD_LIBRARY_PATH should work fine

2) Find your DB2 Connect driver (likely /opt/ibm/db2 of course): 
ls /opt/ibm/db2/V9.1/lib32/libdb2.so
/opt/ibm/db2/V9.1/lib32/libdb2.so
-- or --
ls /opt/ibm/cli/lib/libdb2.so
Note: Your version DB2 Connect /opt/ibm/db2/Vx.x/libxx, 

3) Change RPATH location in ibm_db2/pdo_ibm binaries:
cd /usr/local/zendsvr/lib/php_extensions
-- or --
cd /usr/local/zend/lib/php_extensions
sudo chrpath -r /opt/ibm/db2/V9.1/lib32/ ibm_db2.so
-- or --
sudo chrpath -r /opt/ibm/cli/lib/ ibm_db2.so
Note: On some Linux distributions you will need to 
      use newer utility patchelf (not my Ubuntu) ...
Happy hacking Linux wizards!

Author(s)

Tony “Ranger” Cairns - IBM i PHP / PASE