From YiPs Wiki (i powered)

PHP: DB 2 C Onnection

(click to open)

Quick Page Table of Contents

Scanning…

PHP DB2 Connections

[goto main page]

[goto document links]

[goto XMLSERVICE links]

ibm_db2 1.9.7 for Lunix/Windows/Unix (LUW notice only)

Remote LUW connection to IBM i using DB2 Connect 10.5+ will support system naming (*LIBL works). Thanks IBM LUW guys, we asked, you listened, we now have it. I personally use LUW DB2 Connect 10.5 on my Linux machines to IBM i, on/off these days, very nice to have a laptop environment to play with scripts that can also call IBM i stored procedures with the *LIBL set correctly. You will need IBM i V7R1 or higher to enable the new LUW DB2 Connect *LIBL feature, see this link LUW DB2 Connect *LIBL

DB2 Connect V10.1 and IBM i 7.1 have been enhanced to 
provide naming mode control (aka, *LIBL).

SQL_ATTR_DBC_SYS_NAMING is used on the sqlsetconnectattr() API

    SQL_TRUE - switch the connection to SYSTEM naming
    SQL_FALSE - switch the connection to SQL naming

ibm_db2 1.9.7 for IBM i

There have been some modernization default changes in ibm_db2 1.9.7. Some, older database site may experience issues (CPF4328), you can return to old behaviours with settings in the ibm_db2.ini file (see older IBM i tables — below).

IBM i ibm_db2.ini defaults changed: 
1) Default setting ibm_db2.ini is tables with journal enabled (DB2 system default). 
   if you experience no journal table errors (CPF4328),
   please modify ibm_db2.ini to ibm_db2.i5_allow_commit=0 (*NONE).
2) Default setting PASE CCSID is database UTF-8/1208 (Unicode).
   if you experience CCSID issues, re-enable older PASE CCSID conversions, 
   please modify ibm_db2.ini ibm_db2.i5_override_ccsid=0, .
3) Default setting no longer allows blank userid/password.  
   To re-enable older blank userid/password insecure behaviour,
   please modify ibm_db2.ini to ibm_db2.i5_blank_userid=1.

Try these settings for older IBM i tables without journal and/or older security behavior (';' - comment):
$ cat /usr/local/zendsvr6/etc/conf.d/ibm_db2.ini
extension=ibm_db2.so
ibm_db2.i5_allow_commit=0
ibm_db2.i5_override_ccsid=0 
ibm_db2.i5_blank_userid=1

; ===========
; details
; ===========

;ibm_db2.i5_allow_commit=[0..4]
;0 - DB2_I5_TXN_NO_COMMIT - Commitment control is not used (set no journal, if see CPF4328)
;1 - DB2_I5_TXN_READ_UNCOMMITTED - Dirty reads. (default most machines)
;2 - DB2_I5_TXN_READ_COMMITTED - Dirty reads are not possible.
;3 - DB2_I5_TXN_REPEATABLE_READ - Dirty reads and nonrepeatable reads are not possible. 
;4 - DB2_I5_TXN_SERIALIZABLE - Dirty reads, non-repeatable reads, and phantoms are not possible

;ibm_db2.i5_override_ccsid=[0, ascii ccsid]
;1208 - UTF-8 ccsid (default)
;!0 - other pase ccsid
; 0 - original PASE 'job guess' ccsid

;ibm_db2.i5_blank_userid=[0,1]
;0 - normal no blank user/pwd db2 connect (default)
;1 - allow blank user/pwd db2 connect

;ibm_db2.i5_dbcs_alloc=[0,1]
;0 - normal allocations (default)
;1 - expanded allocations for conversion

;ibm_db2.i5_all_pconnect=[0,1]
;0 - normal db2_connect (default)
;1 - force db2_connect to db2_pconnect

;ibm_db2.i5_ignore_userid=[0,1]
;0 - normal user/pwd using QSQSRVR job (default)
;1 - force ignore user/pwd, when possible, no QSQSRVR job 

;ibm_db2.i5_job_sort=[0,1]
;0 - normal sort order (default)
;1 - profile sort order

;ibm_db2.i5_log_verbose=[0,1]
;0 - normal php.log message (default)
;1 - expanded php.log messages

;ibm_db2.i5_max_pconnect=[0,n]
;0 - normal nomax db2 persistent connection (default)
;n - use count recycle db2 persistent connection

;ibm_db2.i5_check_pconnect=[0,1]
;0 - normal no-check db2 persistent connection (default)
;1 - get conn attribute test
;2 - get conn meta test
;3 - create stmt test
;4 - exec/fetch test

;ibm_db2.i5_sys_naming=[0,1]
;0 - sql naming, schema.table (default)
;1 - system naming, *libl/file

;ibm_db2.i5_guard_profile=[0;1]
;0 - normal no monitor use db2 connection (default)
;1 - before use, assure connect profile correct


;ibm_db2.i5_servermode_subsystem=[null,'name']
;   > crtlib adc                                     
;   > CRTSBSD SBSD(ADC/ADC) POOLS((1 *BASE)) TEXT('tony subsystem descr')
;   > CRTJOBD JOBD(ADC/ADC) TEXT('tony job descr')   
;   > CRTCLS CLS(ADC/ADC) TEXT('tony class')              
;   > ADDPJE SBSD(ADC/ADC) PGM(QSYS/QSQSRVR) MAXJOBS(*NOMAX)
;   > strsbs adc/adc
;null - normal default susbsystem QSQSRVR jobs (default)
;'name' - named subsystem QSQSRVR jobs prestart

Connections summary

In general the user profile/jobd sets the LIBL/iASP for any connection from a PGM/SRVPGM/CMD point of view. However, DB2 supports both SQL naming (schema.table) and system naming (libl/table), where script must set “system naming” before LIBL will have any effect on DB2 unqualified objects look up. Some 2-tier drivers and PHP interfaces do not allow setting system naming as of this date, so LIBL becomes a called user program only attribute that is not DB2 stored procedure enabled (ILE PGM ok LIBL, DB2 no LIBL).

Connection IBM i Job Comments
ODBC
LUW 2-tier 
- IBM i Client Access odbc driver interface
- PHP odbc interface 
JOB: QZDA
USER: User profile 
      -> JOBD
         -> Initial library list
         -> Initial ASP group
CCSID: dspsysval qccsid 65535 (target machine issue)
- CHGUSRPRF USRPRF(myprofile) CCSID(37)
- use valid CCSID DB2 PHP/XMLSERVICE
  (2-tier DB2 may set valid CCSID without you)
IBM i start:
STRHOSTSVR SERVER(*DATABASE)
- port 8471 (database)

ODBC config - Linux:
/etc/odbc.ini
[lp0264d]
Description	= iSeries os400
Driver		= iSeries Access ODBC Driver
System		= lp0264d.rchland.ibm.com
/etc/odbcinst.ini
[iSeries Access ODBC Driver]
Description	= iSeries Access for Linux ODBC Driver
Driver		= /usr/lib/libcwbodbc.so
Driver64	= 
Setup		= /usr/lib/libcwbodbc.so
Setup64		= 
UsageCount	= 
CPTimeout	= 
CPReuse		= 
Threading	= 2
DRDA
IBM i 2-tier 
- WRKRDBDIRE 
- remote IBM i
- PASE CLI driver interface (libdb400.a) 
LUW 2-tier 
- DB2 Connect driver interface
- PHP ibm_db2+pdo_ibm 
JOB: QRW
USER: User profile 
      -> JOBD
         -> Initial library list
         -> Initial ASP group
CCSID: dspsysval qccsid 65535 (target machine issue)
- CHGUSRPRF USRPRF(myprofile) CCSID(37)
- use valid CCSID DB2 PHP/XMLSERVICE
IBM i start:
STRTCPSVR SERVER(*DDM)
- port 446 (DDM/DRDA)

IBM i config:
WRKRDBDIRE
 Option  Entry               Location                  Text                       
         ENOSPC              LOOPBACK                  Entry added by system
         LP0164D             lp0164d.rch.stglabs.ib >  LP0164D
         LP0264D             *LOCAL
         MYASP2              LOOPBACK                  Entry added by system


DB2 Connect config  - Linux (depends on install selection):
/opt/ibm/db2/cfg/db2cli.ini
/home/db2inst1/sqllib/cfg/db2cli.ini
/home/db2inst2/sqllib/cfg/db2cli.ini
[LP0264D]
Database=LP0264D
Protocol=tcpip
Hostname=lp0264d.rch.stglabs.ibm.com
Servicename=446
SchemaList=”*USRLIBL”

CLI
IBM i 1-tier 
- Native CLI interface (RPG, C, etc.) 
- PASE CLI driver interface (libdb400.a)
- PASE PHP ibm_db2+pdo_ibm+odbc 
JOB: QSQ
USER: User profile 
      -> JOBD
         -> Initial library list
         -> Initial ASP group
CCSID: dspsysval qccsid 65535 (target machine issue)
- CHGUSRPRF USRPRF(myprofile) CCSID(37)
- use valid CCSID DB2 PHP/XMLSERVICE
IBM i start:
Job control
- ENDPJ SBS(QSYSWRK) PGM(QSQSRVR) OPTION(*IMMED)
- STRPJ SBS(QSYSWRK) PGM(QSQSRVR)
pre-started QSQ jobs
- shared memory based "connections"
- CLI server mode used typical
- PASE CLI driver /usr/lib/libdb400.a

PASE/ILE config:
None.

Zend Server issue:
Disable following bad driver version
included by accident in ZS build 
then restart Zend Server ... 
call qp2term
> mv /usr/local/zendsvr/lib/libdb400.a /usr/local/zendsvr/lib/libdb400.a-nozend
Swap user profile
- also known as adopt authority (wrong name)
- any connection above
NO:
- We don't swap the lib list or the asps.
YES: 
- Just authority related information,
  user, groups, audit info, 
  group authority, 
  group authority type, 
  mandatory menu indicator, 
  mandatory initial program indicator.
LIBL profile (swap or not)
- IBM i 1-tier/2-tier
  system naming allowed, 
  therefore LIBL DB2 side allowed,
  ILE PGM side normal usage
- LUW 2-tier DB2 connect 
  no support system naming (today),
  therefore LIBL DB2 side is ignored,
  ILE PGM side normal usage
- ibm_db2, odbc support
  system naming where possible
  therefore LIBL DB2 side possible,
  ILE PGM side normal usage
- pdo_ibm not support setting 
  system naming (today)
XMLSERVICE 
- works any connection
  ODBC, DRDA, CLI, etc.
- ADDs a private connection
  known as IPC or internalKey
- call PGM, SRVPGM, CMD,
  System APIs, PASE utilities,
  DB2, etc., using XML
- built in DB2 access by XML
  with FULL DB2 system naming + capabilities,
  including calling other stored procedures
  and prepare/execute with in/out parameters 
  from any connection above 1/2-tier
  (see xmlservice link)
Use any full, persistent connection:
- $ipc="";$ctl="";
- 'stateless'=>true,'InternalKey'=>"" 
  (PHP toolkit default)
  run inside the QZDA, QRW, QSQ job.
JOB: QZDA, QRW, QSQ or ...
USER: User profile 
      -> JOBD
         -> Initial library list
         -> Initial ASP group

private connection only (IPC or internalKey ): 
- $ipc="/tmp/fred";$ctl="*sbmjob"; 
- 'stateless'=>false,'InternalKey'=>$ipc 
  (PHP toolkit "internalKey" default)
  additional child job created by 
  user controllable settings on *sbmjob or
  sbmjob is open to any user customization
- $ipc="/tmp/fred";$ctl=""; 
  (xmlservice default)
  additional child job created by 
  spawn inherits client attributes possible
JOB: XTOOLKIT
USER (*sbmjob):
- prior to xmlservice prior 1.7.3 *JOBD default used,
  may not match your sbmjob needs
- xmlservice 1.7.3+ *CURRENT default used,
  matching most sbmjob needs
- however, user can override all
  sbmjob attributes with explicit
  toolkit/xmlservice request
  // build complete SBMJOB command full customization
  // xmlservice sees ... <sbmjob>SBMJOB ... </sbmjob> 
  $sbmjobCommand = "SBMJOB CMD("; 
  $sbmjobCommand .= "CALL PGM($toolkitLib/XMLSERVICE)"; 
  $sbmjobCommand .= " PARM('$ipc'))"; 
  $sbmjobCommand .= " JOB(FREDKITJOB)";
  $sbmjobCommand .= " JOBD(ZENDSVR/ZSVR_JOBD)";
  $sbmjobCommand .= " INLLIBL(*CURRENT)";
  $conn->setToolkitServiceParams(array(
    'stateless'     => false,
    'InternalKey'   => $ipc,
    'sbmjobCommand' => $sbmjobCommand));
Use any full, persistent connection:
- you must set LIBL each use


private connection only (IPC or internalKey ): 
- any change made is job scope
  LIBL, iASP, profile swap,
  much easier to understand 
  and use more like 5250 jobs


TIP:
// share my ibm_db2 connection 
// either persistent or full open/close
if ($i5persistentconnect) 
  $conn = db2_pconnect($database,$user,$password);
else 
  $conn = db2_connect($database,$user,$password);
// route location to same job on IBM i, 
// known as IPC or internalKey (also known as state full)
// if IPC missing you just use temp job with 
// no recall of xmlservice when script ends (stateless) 
$internalKey = '/tmp/packers';
// toolkit calling same job as regular old ibm_db2
try { $ToolkitServiceObj = ToolkitService::getInstance($conn); }  
catch (Exception $e) { die($e->getMessage()); }
$ToolkitServiceObj->setToolkitServiceParams(
array('InternalKey'=>$internalKey, 'plug'=>"iPLUG32K")
);
// reuse connection call from ibm_db2 
// call xmlservice with XML only
// (same code stuff inside Toolkit)
$stmt = db2_prepare($conn, 
        "call $libxmlservice.iPLUG65K(?,?,?,?)");
$ipc = $internalKey;  // same job as Toolkit above
$ctl = '*sbmjob';
$clobIn = getxml();
$clobOut = "";
$ret=db2_bind_param($stmt, 1, "ipc", DB2_PARAM_IN);
$ret=db2_bind_param($stmt, 2, "ctl", DB2_PARAM_IN);
$ret=db2_bind_param($stmt, 3, "clobIn", DB2_PARAM_IN);
$ret=db2_bind_param($stmt, 4, "clobOut", DB2_PARAM_OUT);
$ret=db2_execute($stmt);
// reuse the same DB2 connection for a query
$result = db2_exec($conn,"select * from animals");

Commit/Isolation level control

Full commit/isolation level control is available with ibm_db2 interface running on IBM i only.

ibm_db2.ini ibm_db2.i5_allow_commit=0/1 :

There has been discussion to change ibm_db2.ini i5_allow_commit=0/1 to allow 0–4 to match isolation levels. PHP on-line documentation for ibm_db2 is in error, PHP.INI file ibm_db2.i5_allow_commit=0 overrides db2_connect manual setting (not as documentation). This is a bug that will be fixed in future release of ibm_db2. For now, ibm_db2.i5_allow_commit=1 will allow manual control any setting.

auto-commit array('autocommit' => DB2_AUTOCOMMIT_ON/OFF) :

isolation levels array('i5_commit'=>DB2_I5_TXN_NO_COMMIT)
Each step up from DB2_I5_TXN_NO_COMMIT to DB2_I5_TXN_SERIALIZABLE adds more locks to database tables/records/fields, therefore may stall or become slower web site. Therefore, balance your desire for ‘latest updated data’ with practical web site behaviour/performance. Also, often a mistaken idea that an ‘update php script/job’ running higher isolation levels (DB2_I5_TXN_READ_COMMITTED, DB2_I5_TXN_REPEATABLE_READ, DB2_I5_TXN_SERIALIZABLE), will force a wait all other PHP scripts running default/lower isolation levels (DB2_I5_TXN_NO_COMMIT or DB2_I5_TXN_READ_UNCOMMITTED), this is not true. If you wish all PHP scripts to wait for commits without dirty reads (not recommended), all PHP scripts must specify higher isolation levels (DB2_I5_TXN_NO_COMMIT to DB2_I5_TXN_SERIALIZABLE). For example, if you have a PHP write job running DB2_I5_TXN_SERIALIZABLE, then following read isolation rules apply to other PHP jobs.

PHP/any readers job(s)           PHP writer job (updater)
============================     =============================
DB2_I5_TXN_NO_COMMIT-------------------| (default, i5_allow_commit=0)
DB2_I5_TXN_READ_UNCOMMITTED------------| (default, i5_allow_commit=1)
                                       | These jobs read dirty data, no fix, you are out of luck
                                       | $conn = db2_connect($d,$u,$p,array('i5_commit'=>DB2_I5_TXN_NO_COMMIT)); ......... i will NOT wait (default, i5_allow_commit=0)
                                       | $conn = db2_connect($d,$u,$p,array('i5_commit'=>DB2_I5_TXN_READ_UNCOMMITTED)); .. i will NOT wait (default, i5_allow_commit=1)
                                       |
DB2_I5_TXN_READ_COMMITTED----|         | 
DB2_I5_TXN_REPEATABLE_READ---|         |
DB2_I5_TXN_SERIALIZABLE------.-->DB2_I5_TXN_SERIALIZABLE (i think i am holding file lock, make all wait, but not true)
                             |   DB2_AUTOCOMMIT_OFF      (correctly using autocommit=off, but again, no difference, will not make all wait)
                             |
                             These jobs will wait, but you need to change every PHP script (EVERY script)
			     $conn = db2_connect($d,$u,$p,array('i5_commit'=>DB2_I5_TXN_READ_COMMITTED)); ... i will wait
			     $conn = db2_connect($d,$u,$p,array('i5_commit'=>DB2_I5_TXN_REPEATABLE_READ)); .. i will wait
			     $conn = db2_connect($d,$u,$p,array('i5_commit'=>DB2_I5_TXN_SERIALIZABLE)); ..... i will wait

IBM i DB2 has been default NO_COMMIT or READ_UNCOMMITTED, well, forever, therefore dirty reads of uncommitted data is normal. However, some people do not believe this statement, believe a bug in ibm_db2. Well, not true, here is an experiment that will let you see for yourself.

Test is simple, web or command line, start zzfilelock.php holding open transaction for 30 seconds, while zzfilelock.php is running, try zzfileread.php at various levels of isolation, you will find:

zzfilecreate.php ($user will be schema):
<?php
require_once('connection.inc');
$conn = db2_connect($database, $user, $password);
//Create $user schema (library)
$create = "CREATE SCHEMA $user";
$res = db2_exec($conn, $create);
//Create the animal table
$create = 'CREATE TABLE ANIMALS (ID INTEGER, BREED VARCHAR(32), NAME CHAR(16), WEIGHT DECIMAL(7,2))';
$res = db2_exec($conn, $create);
//Populate the animal table
$animals = array(
      array(0, 'cat',        'Pook',         3.2),
      array(1, 'dog',        'Peaches',      12.3),
      array(2, 'horse',      'Smarty',       350.0),
      array(3, 'gold fish',  'Bubbles',      0.1),
      array(4, 'budgerigar', 'Gizmo',        0.2),
      array(5, 'goat',       'Rickety Ride', 9.7),
      array(6, 'llama',      'Sweater',      150)
    );
$insert = 'INSERT INTO ANIMALS (ID, BREED, NAME, WEIGHT) VALUES (?, ?, ?, ?)';
$sth = db2_prepare($conn, $insert);
foreach($animals as $row){
   $res = db2_execute($sth, $row);
}
?>

zzfilelock.php:
<?php
require_once('connection.inc');
$conn = db2_connect($database, $user, $password, array('i5_commit'=>DB2_I5_TXN_SERIALIZABLE)); 
db2_autocommit( $conn, DB2_AUTOCOMMIT_OFF ); // ... i need to hold transaction open a long time
$sql = 'UPDATE animals SET id = 9';
$res = db2_exec($conn, $sql);
sleep(30); // ... sleep here with transaction open
db2_rollback($conn);
db2_close($conn);
?>

zzfileread.php:
<?php
require_once('connection.inc');
echo `ps -ef | grep zzfile`;
// DB2_I5_TXN_NO_COMMIT ... i will not wait, i will see the id=9 of uncommitted data
// DB2_I5_TXN_READ_UNCOMMITTED ... i will not wait, i will see the id=9 of uncommitted data
// DB2_I5_TXN_READ_COMMITTED ... i will wait until zzfilelock.php completes (perhaps bad idea web)
// DB2_I5_TXN_REPEATABLE_READ ... i will wait until zzfilelock.php completes (perhaps bad idea web)
// DB2_I5_TXN_SERIALIZABLE ... i will wait until zzfilelock.php completes (perhaps bad idea web)
$conn = db2_connect($database, $user, $password, array('i5_commit'=>DB2_I5_TXN_READ_UNCOMMITTED));
// db2_autocommit( $conn, DB2_AUTOCOMMIT_OFF );
$stmt = db2_exec( $conn, "select * from animals" );
$onerow = db2_fetch_array( $stmt );
var_dump( $onerow );
db2_close($conn);
?>

php.ini/ibm_db2.ini summary

The attributes most affecting DB2 Connections for IBM i:

full open/close w/o ‘p’ (db2_connect())
  • QSQSRVR jobs cleaned end of script (return to pre-start pool)
  • 10X slower then persistent/pooled connections
  • mistakenly referred to as stateless, in fact both persistent/full are stateless
  • $conn = db2_connect($database,$user,$password);
    $conn = odbc_connect($database,$user,$password);
    $database = "ibm:".$database;
    $opt = array(PDO::ATTR_AUTOCOMMIT=>true);                              
    $conn = new PDO($database, strtoupper($user), strtoupper($pass), $opt);
    
persistent/pooled connection w/’p’ (db2_pconnect())
  • QSQSRVR jobs are held open forever (returned to pre-start on php-cgi job end)
  • 10X faster then full open/close connections
  • mistakenly referred to as private, but both persistent/full are stateless
  • $conn = db2_pconnect($database,$user,$password);
    $conn = odbc_pconnect($database,$user,$password);
    $database = "ibm:".$database;
    $opt = array(PDO::ATTR_PERSISTENT=>true, PDO::ATTR_AUTOCOMMIT=>true);  
    $conn = new PDO($database, strtoupper($user), strtoupper($pass), $opt);
    
sql naming ‘.’ (select * from db2.anmials)
  • Setting the LIBL in this job has NO AFFECT on unqualified SQL operations
  • (default Zend Server)
  • $opt = array("i5_naming"=>DB2_I5_NAMING_OFF, 'i5_lib'=>"MYLIB");
    $conn = db2_connect($db,$user,$pwd,$opt);
    $stmt = db2_exec($conn,"select * from db2.anmials");
    
system naming ‘/’ (select * from db2/anmials)
  • Setting the LIBL in this job WILL AFFECT on unqualified SQL operations
  • ONLY works running on IBM i (not supported 2-tier Linux/Windows)
  • $opt = array("i5_naming"=>DB2_I5_NAMING_ON, 'i5_libl'=>"MYLIB YOURLIB ANYLIB");
    $conn = db2_connect($db,$user,$pwd,$opt);
    $stmt = db2_exec($conn,"select * from db2/anmials");
    
ibm_db2.i5_allow_commit=0 (DB2_I5_TXN_NO_COMMIT)
  • /usr/local/zendsvr/etc/conf.d/ibm_db2.ini
  • for use with CRTLIB containers (w/o journaling)
  • isolation modes ONLY available running on IBM i (not supported 2-tier Linux/Windows)
  • (default Zend Server)
  • $opt = array('i5_commit'=>DB2_I5_TXN_NO_COMMIT);
    db2_(p)connect($db,$user,$pwd,$opt);
    
ibm_db2.i5_allow_commit=1 (DB2_I5_TXN_READ_UNCOMMITTED)
  • /usr/local/zendsvr/etc/conf.d/ibm_db2.ini
  • for use with create schema containers (w/journaling)
  • isolation modes ONLY available running on IBM i (not supported 2-tier Linux/Windows)
  • // auto-commit (recommended for persistent connections)
    $options = array('autocommit' => DB2_AUTOCOMMIT_ON, 'i5_commit'=>DB2_I5_TXN_READ_UNCOMMITTED);
    $conn = db2_pconnect($database, $user, $password, $options);
    
    // manual commit (best used only full open/close connections)
    $options = array('autocommit' => DB2_AUTOCOMMIT_OFF, 'i5_commit'=>DB2_I5_TXN_SERIALIZABLE);
    $conn = db2_connect($database, $user, $password, $options);
    // commit transaction
    db2_commit($conn);
    // or roll-back transaction (default on close)
    db2_rollback($conn);
    

Full, persistent/pooled, private connections

There are 2 types of connections for most interfaces (below). The figures below show PHP+DB2 running on IBM i (Zend Server), however when DB2 attaching 2-tier Linux/Windows the picture is consistent with QSQSRVR jobs (except your IBM i job name may be QW, etc.).

Before we get lost in performance details, while PHP script is running, SQL statements using a DB2 connection are using the same QSQSRVR job. A common ‘human’ problem is mistaken thinking that browser clicks through Apache/FastCGI will return to same PHP/connection job last used, this is not true, and, is reason for db2_connect vs. db2_pconnect performance differences.

QSQSRVR jobs many (or not)??

When running on IBM i, php-cgi+db2 is normally running in “server mode” (SQLSetEnvAttr(SQL_ATTR_SERVER_MODE)) allowing a single php-cgi worker job ability to use/attach multiple pre-started QSQSRVR jobs, thereby enabling multiple transaction boundaries for different user profiles (multiple users handled by same php-cgi job). This is the reason you see many QSQSRVR jobs on your machine if you choose to support many active profiles on your web site(s). Depending on your point of view, many QSQSRVR jobs can be viewed as bad or good, but this is intended nature of IBM i QSQSRVR jobs.

The number of pre-started QSQ jobs waiting in IBM i and how many more can be created/spawned is controlled by your pre-start settings.

To start and stop QSQ pool use following:

  • ENDPJ SBS(QSYSWRK) PGM(QSQSRVR) OPTION(*IMMED)
    STRPJ SBS(QSYSWRK) PGM(QSQSRVR)
    

People using IBM i Java may notice fewer QSQSRVR jobs are needed support a given web site, this is due to the fact that Java “server” programs tend to be threaded, where each thread handles a client user profile + transaction boundary via “pooled” QSQSRVR connection reuse. Difference Java vs. PHP is ONE threaded Java job can more efficiently “pool” QSQSRVR connections, where multiple “single threaded” php-cgi jobs doing exact same “pooling” cannot share QSQSRVR jobs across processes. However, many people are comforted by the idea that multiple PHP jobs are less likely to kill the whole site if a tragic event occurs in only one job (Java maybe not so much).

With ibm_db2 a special ibm_db2.ini mode was introduced ibm_db2.i5_ignore_userid=1 , this special ibm_db2 mode allows you run DB2 in php-cgi job and remove additional QSQSRVR job by ignoring user/password db2_(p)connect(db,user,password) becomes db2_(p)connect(null,null,null) . Although a few production sites use this technique, the penalty is administration granting default php-cgi job profile QTMHHTTP access to your database files (no free lunch). Also you need to run a “pure” ibm_db2 site, because using any of the other DB2 protocols (odbc, pdo_ibm), will likely push the php-cgi job into using server mode, and once entered into server mode a php-cgi job can NEVER return to ignoreuserid mode.

Retrieving the Current User Profile for a Prestart Job

This topic is confusing to many people trying to retrieve user of a given pre-started DB2 job (QZDA, QSQ, etc.). The following documentation explains IBM i behaviour of pre-started jobs from ODBC point of view, but the same rules apply to DRDA connections including PASE clients (ibm_db2, odbc, pdo_ibm, etc.).

Apache + FastCGI, not QTMHHTP1 (like RPG CGI)???

The original ported code design of FastCGI did not allow for switch user profile operations like ServerUserID and/or UserID directive, therefore RPG CGI developers familiar with IBM i Apache auto-magic switch to QTMHHTP1 on activation may be a bit disappointed. Today, all default php-cgi jobs are QTMHHTTP. No matter your profile preference, default QTMHTTP has one redeeming value in php-cgi world, PHP can be embedded into HTML (unlike RPG), therefore people often “like” these hybrid xxx.phtml files owned/accessed by a single profile just like normal xxx.html files (QTMHHTTP).

As for running xxx.phtml files in your php-cgi jobs just append AddType/AddHandler to your Apache ZENDSRV instance (in case you did not already know). BTW - you probably do not want to run all your xxx.html files through php-cgi, because it will drag on web site performance … and … you may want to use FRCA xxx.html caching for super fast performance of plain html files (see FastCGI IBM i).

httpd.conf: 

AddType application/x-httpd-php .php
AddHandler fastcgi-script .php
AddType application/x-httpd-php .phtml
AddHandler fastcgi-script .phtml

Apache/FastCGI - Junk in my ibm_db2 queries (ibm_db2)

Common reasons for junk characters in your ibm_db2 queries.

Apache/FastCGI - DBCS bad character conversion sql (Chinese, etc)

SQL naming no/LIBL or System naming w/LIBL (ibm_db2)

Full naming mode both SQL and system naming is available with ibm_db2 interface running on IBM i only (not Linux/Windows … yet). Over time 2-tier ibm_db2 may change, but if you are having difficulties with a specific application “LIBL” scenario, please read this link.

Common error mixing ‘i5_lib’ and ‘i5_libl’ on IBM i

Issue with connect (user error):

1) This is invalid, aka, a user error. You must NEVER (ever) use 'i5_lib' with DB2_I5_NAMING_ON.
$conn = db2_(p)connect(x,x,x,array('i5_naming'=>DB2_I5_NAMING_ON, 'i5_libl'=>'IEFFECT', 'i5_lib'=>'IEFFECT'));

2) This is valid for SQL naming.
$conn = db2_(p)connect(x,x,x,array('i5_naming'=>DB2_I5_NAMING_OFF, 'i5_lib'=>'IEFFECT'));

3) This is valid for system naming.
$conn = db2_(p)connect(x,x,x,array('i5_naming'=>DB2_I5_NAMING_ON, 'i5_libl'=>'IEFFECT', 'i5_curlib'=>'IEFFECT'));

Manual:
 i5_lib
    A character value that indicates the default library that will be used for resolving unqualified file references. 
    This is not valid if the connection is using system naming mode.

 i5_libl
    A character value that indicates the library list that will be used for resolving unqualified file references. 
    Specify the library list elements separated by blanks 'i5_libl'=>"MYLIB YOURLIB ANYLIB". 

 i5_curlib (missing)
    A character value default library resolving unqualified file references. 
    Specify the library  'i5_curlib'=>"MYLIB". 

db2_pclose - db2_(p)connect failures

You should avoid using db2_pclose as it will not mix well with db2_connect. A db2_pclose fix will be needed in a future ibm_db2 release. The known sequence that leads to trouble (possibly others):

step 1) s1.php
$conn = db2_pconnect();
$r = db2_exec($conn,$sql); /* $r works */
db2_pclose($conn);

step 2) s2.php
$conn = db2_connect();
$r = db2_exec($conn,$sql); /* $r works */
/* db2_close($conn); -- missing s1.php will fail next request step 3) */
/* db2_close($conn); -- present both s1.php and s2.php work continuous */

step 3) s1.php
$conn = db2_pconnect();
$r = db2_exec($conn,$sql); /* $r fails -- if db2_close($conn); missing in s2.php step 2) */
db2_pclose($conn);

Note: Unfortunately random nature of Apache/FastCGI php-cgi child worker select/poll may make difficult to reproduce sequence on a busy system.

db2_connect(‘*LOCAL-*DEBUG’,$user,$pwd) - halting message qsecofr

At times we want to stop connection process to see what is going on.

1. For the database name, use ‘*LOCAL-*DEBUG’ :

$existingDb = db2_connect('*LOCAL-*DEBUG',$user,$pwd);  

2. This will cause the PHP job to wait with a message on QSYSOPR after db2_connect runs. Look at the joblog of that waiting PHP job (jobname ZENDSVR). In the joblog, look for the job name/ number of the associated QSQSRVR job.

3. Find that QSQ job and inspect its joblog for errors.

BTW — if your machine can not perform this task, you probably have down level DB2 PTFS.

DB2 PTFs for *LOCAL-*DEBUG ...
change: Sorry misprint not available V6 ... v6r1 SI45121 - DSPPTF LICPGM(5761SS1) SELECT(SI45121) 
v7r1 SI45862 - DSPPTF LICPGM(5770SS1) SELECT(SI45862)
Fix base release beyond V7R1

Author(s)

Tony “Ranger” Cairns - IBM i PHP / PASE

Retrieved from http://youngiprofessionals.com/wiki/index.php/PHP/DB2COnnection
Page last modified on January 10, 2017, at 11:16 AM EST