DB 2 Storage Engine Document

(click to open)

Quick Page Table of Contents


DB2 Storage Engine


Big picture

Client (your PHP mysql or mysqli script):
    | PHP script (ZendDBi client):                 |  Zend Server for IBM i (binary download Zend site)
    | - pecl mysql or mysqli (PHP api syntax)      |  - /www/zendsvr/htdocs/mysql(i).php (CREATE TABLE examples)
    | - binary: libmysqlclient.a (client driver)   |  - /usr/local/zendsvr/lib/libmysqlclient.a 
                    port 3306 (default port)
Server (ZendDBi):   |
    | ZendDBi (ZendDBi server)                     |   ZendDBi for IBM i (binary download Zend site)
    | - binary: mysqld (database server)           |   - /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysqldata
    | - runtime storage engine choice (or my.cnf)  |     (or green screen go zendsvr/zsmenu -> mysql option)
    |   .->heap    -> in memory only (very fast)   |   - CREATE TABLE t1 (i INT) ENGINE = MEMORY; (source mysql/storage/heap MySql) 
    |   .->myisam  -> /usr/local/mysqldata         |   - CREATE TABLE t2 (i INT) ENGINE = MYISAM; (source mysql/storage/myisam MySql) 
    |   .->innodb  -> /usr/local/mysqldata         |   - CREATE TABLE t3 (i INT) ENGINE = InnoDB; (source mysql/storage/innodb MySql) 
    |   ... blackhole, federated, etc. ...         |                                              (source mysql/storage/'other' MySql) 
*-->|   .->ibmdb2i -> DB2 lib/file.mbr             |   - CREATE TABLE t4 (i INT) ENGINE = IBMDB2I;(source mysql/storage/ibmdb2i Yips) 
                    PASE calls DB2 from engine=ibmdb2i
IBM PTFs:           |
        | DB2 special PTFs for MySql (IBM OS) |        IBM PTFs to enable ZendDBi ibmdb2i
        | -> storage exchange data            |        - same DB2 files seen RPG, etc.
        |    between ibmdb2i (mysql script)   |        - same lib/file.mbr (wrklibpdm lib)
        |    and lib/file.mbr (IBM i DB2)     |        - just another DB2 use on IBM i
        |    - ascii / ebcdic                 |
        |    - type conversions               |
        |    - etc.                           |
*- MySql source distributions will not include up to 
   date versions of ibmdb2i, all new versions are 
   published/provided here at Yips site intended to
   replace mysql/storage/ibmdb2i for PASE compiles (ZendDBi)
 - Zend provides download ZendDBi binaries (see Zend web site),
   therefore IBM i customers do not have to compile 
   anything, just download the binary distribution (Yahoo) 
 - IBM does not provide PTFs for ibmdb2i (only source)
 - IBM provides IBM i Operating system PTFs for DB2 
   (additional IBM i kernel enhancements DB2 for MySql)
 - if you don't want to deal with changing 
   existing PHP scripts syntax to engine=ibmdb2i,
   set the default storage engine in my.cnf ...
   call qp2term
   > chmod 0775 /usr/local/mysql/bin/my.cnf                                 (Zend shipped wrong authorisation -- ignored) 
   > cp /usr/local/mysql/bin/my.cnf /usr/local/mysql/bin/my.cnf-orig        (make a copy -- just in case)
   > echo 'default-storage-engine=IBMDB2I' >> /usr/local/mysql/bin/my.cnf   (be careful, >> append, > is replace)
   > cat /usr/local/mysql/bin/my.cnf                                        (cat will let you see your changes)
   You will need to start/stop ZendDBi server (go zendsvr/zsmenu), 
   but before start/stop you most likely want to
   move/copy/delete old database info out of way new engine,
   before recreate all your PHP script tables again. 
   -- http://myibmi(:10088)/phpMyAdmin/                                     (easy tool move/copy shipped Zend Server)


Incompatible UPPER case change 5.1.50

Version 5.1.50 of DB2 Storage Engine implemented a new default UPPER case behavior change. ZendDBi installed into case insensitive directory (/usr/local) will now force UPPER case instead of “lower” case to avoid double quotes incompatible with legacy databases and applications. UPPER case will be the default for DB2 Storage Engine from now forward.

-- DB2 Storage Engine previous lower case (incompatible IBM i legacy) --
   "mylib" "mytable" "myfield"

-- DB2 Storage Engine 5.1.50+ UPPER case (compatible IBM  i legacy) --

History and the possible IFS directories …

  • /usr/local/mysql/mysqldata (ZendDBi default today)
    • Most IBM i DB2 installations prefer non case sensitive UPPER CASE folding so that legacy/RPG programs using MySql datasets do not have to specify “myMixedName”, but instead can use either mymixedname or MYMIXEDNAME (same goes for PHP ibm_db2).
  • /QOpenSys/usr/local/mysqlmysqldata (exception prior configuration)
    • Few minority IBM i DB2 installations follow a pure ANSI database scheme where case sensitivity has been factored into legacy/RPG clients and “myMixedName” is expected behavior.


If you wish to restore the old behaviour ZendDBi (“mylib” “mytable” “myfield”, etc.), move your ZendDBi install directory to a case sensitive IFS directory(/QOpenSys).

call qp2term
> mkdir -p /QOpenSys/usr/local
> mv /usr/local/mysqldata /QOpenSys/usr/local/.

However, move MAY BE incompatible with previously created DB2 objects using default ZendDBi, therefore you will likely have to recreate all tables, etc., after the move to /QOpenSys.

Also you will need to start mysql by hand instead of using GO ZENDSVR/ZSMENU.

bin/mysqld_safe --datadir=/QOpenSys/usr/local/mysqldata

Zend Server (ZendDBI) ships my.cnf issue (not working)

WARNING: Zend Server (ZendDBI) ships my.cnf “world writeable” (*PUBLIC RWX), which will render my.cnf not usable by mysql start (not read at all), you need to change my.cnf “world access” for it to work (see below).

/usr/local/mysql/bin/my.cnf installed by ZendDBI used via zendsvr/zsmenu ...
call qp2term
... as shipped by Zend ....
> ls -l /usr/local/mysql/bin/my.cnf
-rwxrwxrwx    1 qsecofr  0               256 Oct 23 2007  /usr/local/mysql/bin/my.cnf
... change to this ...
> chmod 0775 /usr/local/mysql/bin/my.cnf
> ls -l /usr/local/mysql/bin/my.cnf
-rwxrwxr-x    1 qsecofr  0               256 Oct 23 2007  /usr/local/mysql/bin/my.cnf
my.cnf to set DB2 as default storage engine, add following ...
-- and/or --
You can even specify the table to be used when the table is created:
For tables that already exist, the storage engine can be changed through the ALTER statement:
Note: /etc/my.cnf installed by ZendDBI, but NOT used zendsvr/zsmenu 
> ls -l /etc/my.cnf
-rw-rw----    1 mysql    0               101 Jan 20 2012  /etc/my.cnf

Compiling DB2 Storage Engine

Zend provides a MySql distribution and PTF fixes for DB2 Storage Engine (/usr/local/mysql), so it is probably easiest to simply install Zend Server for IBM i community edition.

However, for those that wish to compile from MySQL original version.

  1. Download mysql-5.1.50.tag.gz from MySQL web page.
  2. Unpackage the file. I assume the dir is $MYSQLHOME
  3. Copy myConfigure and myCommonEnvironmentSetup to $MYSQLHOME/. { myConfigure myCommonEnvrionmentSetup }
  4. Create include400 dir under $MYSQLHOME/. Copy files as400_protos.h as400_types.h os400msg.h qlgusr.h qmyse.h to $MYSQLHOME/include400. { include400.tar.gz }
  5. Create lib400 dir under $MYSQLHOME/. Copy files as400_libc.exp libdb400.exp libpthread.a to $MYSQLHOME/lib400. { lib400.tar.gz }
  6. Execute myConfigure
  7. Change dir to $MYSQLHOME/include.
  8. Execute make. my_config.h was generated. Comment #define HAVE_BZERO 1 in my_config.h.
  9. Change dir to $MYSQLHOME/storage/ibmdb2i. Unpackage ibmdb2i_5.1.50.tar.gz and copy all source files and patches to current dir. This is to make you have the latest files for ibm db2i storage engine. (see main page for download)
  10. Execute make.
  11. ibmdb2i storage engine so files will be generated under $MYSQLHOME/storage/ibmdb2i/.libs.

Existing IBM i DB2 Tables

Discovery of existing tables is not supported by IBM, and will not work if your existing table contains NUMERIC (zoned decimal) fields.

  • Link for DECIMAL vs. NUMERIC in MySql http://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html
    • “ In MySQL, NUMERIC is implemented as DECIMAL, .. “
      • PHP MySql scripts uses NUMERIC (zoned decimal), database physically stored as DECIMAL (packed decimal)
        • YES … even with DB2 storage engine
        • therefore, MySql DB2 storage engine can only recognize packed decimal in existing table, not zoned decimal

Practically speaking, IBM provided no mechanism for MySQL to find existing DB2 tables, but you didn’t come to this site for the word ‘no’. So here is the simplest way to “discovery” if your tables do not have zoned decimal …

1) create a dummy schema in MySQL, create a table with the same name (and letter case) and same column definition as the DB2 table

create table dummy.MyExistingTable(int Flag, ....);

2) create a schema in MySQL with the same name and letter case as the DB2 schema (phpmyadmin or etc.)

create schema MyRealLibrary;

3) in the MySQL data directory in the IFS, copy the .frm file corresponding to the table from the dummy schema’s directory to the real schema’s directory

call qp2term
> cd /usr/local/mysqldata
> cp dummy/MyExistingTable.frm MyRealLibrary/MyExistingTable.frm

Note: We are assuming the profile MySQL server is running under has the requisite authority to the DB2 schema/table (security is IBM i administrators responsibility).

From a technical perspective, “discovery” should work, but with several restrictions:

  1. Column definitions in DB2 must be definable from within MySQL. In other words, there must exist a mapping from a MySQL data type to the DB2 data type
  2. Indexes and constraints created in DB2 will not be visible to MySQL
  3. Sequences, identity columns, and other concepts not present in MySQL may not work as expected


Common MySql files, commands, etc. on IBM i.

directories (Zend install)
  /QOpenSys/usr/local/mysql/mysql  -- PASE programs
  /QOpenSys/usr/local/mysql/data   -- MySql data
configuration file

start examples
  bin/mysql --host=localhost --user=root --pass=mypwd
  bin/mysqld_safe -default_storage_engine=IBMDB2I

root password set
  bin/mysqladmin -u root password 'new-password'
  bin/mysqladmin -u root -h myi5.domain.com password `new-password'

install db2 engine
  bin/mysql -u root
  install plugin ibmdb2i soname "ha_ibmdb2i.so";

create DB2 table 
  create table mydb1.mytable1 (...) engine = ibmdb2i;

DB2 headers from ILE

headers needed for compile copied form ILE
bash-4.2$ cat zzcpyincludes.sh 
ls /QIBM/include/sql* > ./list2
for i in $(< ./list2)
  system -v "CPY OBJ('$i') TODIR('/usr/include/') TOCCSID(*STDASCII) DTAFMT(*TEXT) REPLACE(*YES)"                                      
ls /QIBM/include/*lgu* > ./list2
for i in $(< ./list2)
  system -v "CPY OBJ('$i') TODIR('/usr/include/') TOCCSID(*STDASCII) DTAFMT(*TEXT) REPLACE(*YES)"                                      
ls /QIBM/include/*my* > ./list2
for i in $(< ./list2)
  system -v "CPY OBJ('$i') TODIR('/usr/include/') TOCCSID(*STDASCII) DTAFMT(*TEXT) REPLACE(*YES)"