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
- DB2 Connect Download
- If above fails …
- http://www-01.ibm.com/software/data/db2/linux-unix-windows/download.html → Data Server Client Packages
- http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 → 4 packages (below)
- Requires DB2 Connect license to connect to IBM i (no exceptions)
- Licenses: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.server.doc/doc/r0006748.html?cp=SSEPGG_10.5.0
- How: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.server.doc/doc/t0023608.html?cp=SSEPGG_10.5.0%2F2-4-0-0
- Use license install
db2licm -a db2consv_is.lic
…- Example my Linux:
/opt/ibm/db2/V10.5/adm/db2licm -a db2consv_is.lic
- Example my Linux:
- Use license install
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
- IBM Data Server Driver for ODBC and CLI { DB2 Connect Download }
> 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
- install Zend Server { Deb Install }
> 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