Ibm Db 2

(click to open)

Quick Page Table of Contents

Scanning…

PHP ibm_db2

A few tips on ibm_db2 running on IBM i …

PHP DB2 ibm_db2 simple example with sign-on

The following code is a simple, but complete IBM i session sign-on manager using ibm_db2 for a web application with button actions on table db2.animal.

Code: {Download myapp}

Notes:

  • unzip and ftp/copy to /www/zendsvr/htdocs/myapp (binary please needs to stay ascii files)
  • standard session.php user profile validation/connection via db2 connection (db2_connect)
  • also the usual index.php used for URL landing with no parameters (http://my400/myapp)
  • and look at bye.php to see browser being forced to a route location (Location)
  • after sign-on the demo creates schema db2 for use (if you wish to delete)
  • files in demo myapp (click to see source):
  • You may need to change session.php for your DB2 installation
-- perhaps --
$_SESSION['db'] = '*LOCAL';
-- or --
$_SESSION['db'] = '';

You can check with ... 
WRKRDBDIRE
 Option  Entry               Location                  Text                          
         ENOSPC              LOOPBACK                  Entry added by system
         LP0164D             *LOCAL                    php examples
         RCHAS2M3            rchas2m3.rchland.ibm.c >  RCHAS2M3
Also interesting to note...
I could have easily specified RCHAS2M3 for a remote IBM i connection.

PHP DB2 ibm_db2 commit?

In order to enable ibm_db2 commit, you may have to change the setting shipped by default by Zend Server on i.

Allow commit
   /usr/local/zendsvr/etc/conf.d/ibm_db2.ini
   extension=ibm_db2.so
   ; change allow commit
   ; 0 = default (crtlib); 1=allow commit
   ibm_db2.i5_allow_commit=1

PHP DB2 ibm_db2 performance using profiles (with QSQSRVR jobs)?

If you plan to use user profiles with PHP ibm_db2, primary effective way to gain performance with ibm_db2 is to switch to using persistent connections (up to 40% web performance improvement).

1) Use db2_pconnect() - persistent connections
   change script 
     db2_connect(x,x,x)
     -- change to --
     db2_pconnect(x,x,x)
   Remember with persistent connections ...
     db2_commit()
   ... you must commit transactions to avoid undesired rollbacks.
   Note:
     Use this technique to free cursors, result sets, statement, etc.
     $stmt = db2_prepare(); 
     $stmt = db2_execute(); 
     -- or --
     $stmt = db2_exec();
     -- or --
     $stmt = db2_next_result($ostmt); // multiple sets IBM i bug you may need PTFs
                                      // or $stmt[$i++] = db2_result_set
     :
     $mybool = db2_commit($conn); // if needed
     $stmt = ''; // done fetching ... whatever ... close resource
                 // forcing completed $stmt resource out of "scope"
     Even when doing SELECTs (reads), to sudo close cursors, 
     thereby allowing commands for operator functions 
     like CLRPFM to work.
     (QSQSRVR jobs remain active during sudo close,
      but operator actions like CLRPFM will 
      drop SHRRD locks)

If you want to try this out without altering your PHP ibm_db2 scripts, you can use the following setting in the php.ini file (ibm_db2.ini).

2) Override PHP script and force db2_pconnect()
   /usr/local/zendsvr/etc/conf.d/ibm_db2.ini
   extension=ibm_db2.so
   ; change all connections to persistent connections (performance improvement)
   ; 0 = default ; 1 = db2_connect(x,x,x)->db2_pconnect(x,x,x)
   ibm_db2.i5_all_pconnect=1

However, you will find using persistent connections causes a each php-cgi job to add a single QSQSRVR job for each user profile used by the php-cgi job. You can calculate the total number of jobs in the pool using the following:

Number IBM i jobs formula: 
  (nbr php-cgi jobs) * (nbr of user profiles active) = (nbr QSQSRVR jobs)

Example:
   php-cgi--->QSQSRVR(profile fred)
          --->QSQSRVR(profile sally)
          --->QSQSRVR(profile john)
   php-cgi--->QSQSRVR(profile fred)
          --->QSQSRVR(profile sally)
          --->QSQSRVR(profile john)
   php-cgi--->QSQSRVR(profile fred)
          --->QSQSRVR(profile sally)
          --->QSQSRVR(profile john)
   (3 php-cgi jobs) * (3 active profiles) = (9 QSQSRVR jobs)

As you can see above, it is a good idea to limit the number of profiles used
in your site so the number of QSQSRVR jobs does not get "unmanageable".

Performance improvement using no profiles (no QSQSRVR jobs)

If you find that you cannot tolerate the extra QSQSRVR jobs created by persistent connections (above), or you simply wish to rely on full open/close DB2 behavior, then it is best practice to change your entire site to use no profiles at all with the following php.ini setting (ibm_db2.ini). This force all your PHP scripts using ibm_db2 to ignore all user/passwords on connect and run DB2 directly in the php-cgi job (no QSQSRVR jobs).

3) If you MUST use full open/close connections (not recommended), 
   a) forget about user profiles and call DB2 in-line php-cgi jobs
   /usr/local/zendsvr/etc/conf.d/ibm_db2.ini
   extension=ibm_db2.so
   ; change all connection to in-process php-cgi (entire site must co-operate)
   ; 0 = default ; 1 = db2_(p)connect(x,x,x)->db2_(p)connect(null,null,null)
   ibm_db2.i5_ignore_userid=1

   b) Do not put full open/close in a loop
   NO: 
       for() { db2_connect(); ... do stuff ...; db2_close(); }
   YES:
       conn=db2_connect();
       for () {
         ... do stuff ...
       }
       db2_close(); 

Get up to date with IBM PTFs (broken record)

Example:
PTF#s DB2 (or CLOBs fail, etc,):
SI39831/SI39917 - V7R1 
SI39829 - V6R1 
SI39610  V5R4
Note: There are many other DB2 fixes, 
      best to simply get the latest cumulative 

Author(s)

Tony “Ranger” Cairns - IBM i PHP / PASE