DB 2 Connection
Quick Page Table of Contents
Scanning…
PHP DB2 Connections
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 :
- i5_allow_commit=0 (DB2_I5_TXN_NO_COMMIT) — works with CRTLIB no journal, dirty reads of uncommitted data other processes. CRTLIB traditional library containers often do not have journaling enabled, therefore isolation access can ONLY be DB2_I5_TXN_NO_COMMIT or DB2 access fails.
- i5_allow_commit=1 (DB2_I5_TXN_READ_UNCOMMITTED) - CREATE SCHEMA modern SQL library containers are journal enabled by default, therefore all isolation levels can be used for DB2 access.
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) :
- DB2_AUTOCOMMIT_ON - transactions are committed as they occur (default)
$options = array('autocommit' => DB2_AUTOCOMMIT_ON); $conn = db2_connect($database, $user, $password, $options); - DB2_AUTOCOMMIT_OFF - transactions wait for script action db2_commit() to occur or will rollback if no commit specified (*). if you forget to db2_commit, you may find that ALL your updates will rollback when you close the connection. Wherein, persistent connection rollback can mean A TON OF TRANSACTIONS will rollback when you end DB2 server mode job subsystem or otherwise kill QSQSRVR jobs. My advice (2 cents), do not use DB2_AUTOCOMMIT_OFF with persistent connections.
$options = array('autocommit' => DB2_AUTOCOMMIT_OFF); $conn = db2_connect($database, $user, $password, $options); : db2_commit($conn); // -- or -- db2_rollback($conn);
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.
- (not wait) DB2_I5_TXN_NO_COMMIT - Commitment control is not used, dirty reads, etc.. i will not wait on ‘lock’, not a bug (i5_allow_commit=0)
- (not wait) DB2_I5_TXN_READ_UNCOMMITTED - Dirty reads, nonrepeatable reads, and phantoms are possible. i will not wait on ‘lock’, not a bug (i5_allow_commit=1)
- (wait) DB2_I5_TXN_READ_COMMITTED - Dirty reads are not possible. Nonrepeatable reads, and phantoms are possible. i will wait, but not the default (change all your php files)
- (wait) DB2_I5_TXN_REPEATABLE_READ - Dirty reads and nonrepeatable reads are not possible. Phantoms are possible. i will wait, but not the default (change all your php files)
- (wait) DB2_I5_TXN_SERIALIZABLE - Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible i will wait, but not the default (change all your php files)
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:
- array(‘i5_commit’=>DB2_I5_TXN_NO_COMMIT) … i will not wait, i will see the id=9 of uncommitted data (default, i5_allow_commit=0)
- array(‘i5_commit’=>DB2_I5_TXN_READ_UNCOMMITTED) … i will not wait, i will see the id=9 of uncommitted data (default, i5_allow_commit=1)
- array(‘i5_commit’=>DB2_I5_TXN_READ_COMMITTED) … i will wait until zzfilelock.php completes (perhaps bad idea web)
- array(‘i5_commit’=>DB2_I5_TXN_REPEATABLE_READ) … i will wait until zzfilelock.php completes (perhaps bad idea web)
- array(‘i5_commit’=>DB2_I5_TXN_SERIALIZABLE) … i will wait until zzfilelock.php completes (perhaps bad idea web)
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 vs. persistent/pooled connection
- php.ini/ibm_db2.ini: ibm_db2.i5_allow_commit=0 (Zend Server default) vs. ibm_db2.i5_allow_commit=1
- sql naming (Zend Server default) vs. system naming
full open/close w/o ‘p’ (db2_connect())
| persistent/pooled connection w/’p’ (db2_pconnect())
|
sql naming ‘.’ (select * from db2.anmials)
| system naming ‘/’ (select * from db2/anmials)
|
ibm_db2.i5_allow_commit=0 (DB2_I5_TXN_NO_COMMIT)
| ibm_db2.i5_allow_commit=1 (DB2_I5_TXN_READ_UNCOMMITTED)
|
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.
- 1) full open/close connection — slow connection, very safe script programming one use
(1) (2) (3) Apache FastCGI DB2 (server mode) ------- --------------- --------------------- browser/client -->thread--socket->php-cgi--->QSQSRVR(profile fred) <--shut down after script end --->QSQSRVR(profile sally) <--shut down after script end --->QSQSRVR(profile john) <--shut down after script end ->php-cgi--->QSQSRVR(profile fred) <--shut down after script end --->QSQSRVR(profile sally) <--shut down after script end --->QSQSRVR(profile john) <--shut down after script end ->fastcgi.conf controls number of php-cgi workers/children (SetEnv="PHP_FCGI_CHILDREN=10") $conn = db2_connect($database,$user,$password); // full open/close connection (ibm_db2) $conn = odbc_connect($database,$user,$password); // full open/close connection (odbc) $database = "ibm:".$database; $opt = array(PDO::ATTR_AUTOCOMMIT=>true); $conn = new PDO($database, strtoupper($user), strtoupper($pass), $opt);// full open/close connection (pdo_ibm)
- 2) persistent/pooled connection — fast connection, but requires thoughtful script programming to share connection pool
(1) (2) (3) Apache FastCGI DB2 (server mode) ------- --------------- --------------------- browser/client -->thread--socket->php-cgi--->QSQSRVR(profile fred) <--remains active available pool (10X faster) --->QSQSRVR(profile sally) <--remains active available pool (10X faster) --->QSQSRVR(profile john) <--remains active available pool (10X faster) ->php-cgi--->QSQSRVR(profile fred) <--remains active available pool (10X faster) --->QSQSRVR(profile sally) <--remains active available pool (10X faster) --->QSQSRVR(profile john) <--remains active available pool (10X faster) ->fastcgi.conf controls number of php-cgi workers/children (SetEnv="PHP_FCGI_CHILDREN=10") QSQ formula: QSQ jobs = (PHP_FCGI_CHILDREN=x) * (nbr-profiles-in-use=y) 30 QSQ jobs = (PHP_FCGI_CHILDREN=10) * (nbr-profiles-in-use=3) 150 QSQ jobs = (PHP_FCGI_CHILDREN=10) * (nbr-profiles-in-use=15) * fast is good, even mandatory, but there is no free lunch (see QSQ jobs discussion below) $conn = db2_pconnect($database,$user,$password); // persistent/pooled connection (ibm_db2) $conn = odbc_pconnect($database,$user,$password); // persistent/pooled connection (odbc) $database = "ibm:".$database; $opt = array(PDO::ATTR_PERSISTENT=>true, PDO::ATTR_AUTOCOMMIT=>true); $conn = new PDO($database, strtoupper($user), strtoupper($pass), $opt);// persistent/pooled connection (pdo_ibm)- Why PHP<>QSQ?
- DB2 allows only one transaction boundary per activation group … and well nobody runs multiple activations in a single job … so … practically speaking means one user connection/transaction per job, therefore in order for php-cgi workers to support multiple user profiles we need to “farm out” each unique profile connect/transaction to a pooled/persistent QSQ job (QSQ-jobs = php-jobs * active-user-profiles).
- CLI server mode — extended information
- Important: Common mistake - many people beginning PHP + Db2 assume that “persistent/pooled connection” will return your script to the same QSQSRVR job each time it is evoked/called on the web, this is not correct. In fact “persistent/pooled connection” will appear to randomly select a QSQSRVR job when the IBM i machine is heavily used.
- Actually what is really happening is Apache+FastCGI+php-cgi is randomly picking a child worker job that also HAPPENS to have it’s own QSQSRVR job attached (one QSQ per profile used commonly), therefore you MUST NOT rely on returning to the same “persistent/pooled connection” even though it MAY APPEAR to work when the machine is not stressed (you testing PHP by yourself). Only XMLSERVICE below allows for private connection that will return you to the same xmlservice job, seconds, minutes, hours, days later.
- Why PHP<>QSQ?
- 3) private connection - ONLY {XMLSERVICE IBM i} allows a private connection
- XMLSERVICE allows for a “private connection” via ipc routing (below). This connection will allow you to route back to the same job forever like 5250 jobs.
(1) (2) (3) (4) Apache FastCGI DB2 (server mode) XMLSERVICE ------- --------------- --------------------- ---------- -->thread--socket->php-cgi--->QSQSRVR(profile sally)---.->XMLSERVICE (sally) <--alive until stopped (or idle timemout) --->QSQSRVR(profile john)--. | -->thread--socket->php-cgi--->QSQSRVR(profile fred) | | --->QSQSRVR(profile sally)---. --->QSQSRVR(profile fred) | --->QSQSRVR(profile john)--.--->XMLSERVICE (john) <--alive until stopped (or idle timemout) $ctl = "*sbmjob"; $ipc = "/tmp/sally"; -- or -- $ipc = "/tmp/john";- See link for more about private XMLSERVICE Connections.
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.
- CLI server mode — extended information
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.).
- current user vs user - Retrieving the Current User Profile for a Prestart Job
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).
- Good news - IBM i understands issue
- Bad news - Nothing available today
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.
- IBM i machine set to global CCSID 65535 (default ship IBM)
dspsysval qccsid Display System Value System value . . . . . : QCCSID Description . . . . . : Coded character set identifier Coded character set identifier . . . . . : 65535 1-65535
- user fix - Correct using Apache configuration /www/zendsvr/conf/httpd.conf (ZENDSVR Apache instance)
If your machine DSPSYSVAL QCCSID is 65535 (means no CCSID or hex/binary), remember to set a valid CCSID web server (DefaultFsCCSID, CGIJobCCSID) in /www/zendsvr/conf/httpd.conf or NOTHING works (save hours of trouble). DefaultFsCCSID 37 CGIJobCCSID 37
- Important: Apache httpd.conf 65535 solution above is also needed to fix XMLSERVICE hanging or bad behaviour out of the box install
- XMLSERVICE transports/connections (ibm_db2 and odbc), also are dependent on good CCSID to convert from client/server character data, so when CCSID is 65535 no conversion takes place on DB2 operations and XMLSERVICE gets lost trying to understand what junk was just sent (no ipc, no control, no XML, just junk).
- Important: Apache httpd.conf 65535 solution above is also needed to fix XMLSERVICE hanging or bad behaviour out of the box install
Apache/FastCGI - DBCS bad character conversion sql (Chinese, etc)
- When running on IBM i VARGRAPHIC / VARCHAR / CHAR Chinese characters, DB2 records insert/update “bad character data”.
- user fix - setSetEnv=“CCSID=1208″ SetEnv=“LANG=C” in /www/zendsvr/conf/fastcgi.conf
; Static PHP servers for default user -- Server one line only (no LF) Server type="application/x-httpd-php" CommandLine="/usr/local/ZendSvr/bin/php-cgi.bin" StartProcesses="1" SetEnv="LIBPATH=/usr/local/ZendSvr/lib" SetEnv="PHPRC=/usr/local/ZendSvr/etc/" SetEnv="PHP_FCGI_CHILDREN=10" SetEnv="PHP_FCGI_MAX_REQUESTS=0" ConnectionTimeout="30" RequestTimeout="60" SetEnv="CCSID=1208" SetEnv="LANG=C" SetEnv="INSTALLATION_UID=20111108100706281116" SetEnv="LDR_CNTRL=MAXDATA=0x40000000" ; Where to place socket files IpcDir /myasp2/www/zend2/logs IpcPublic *RWX
- You may need ibm_db2.ini setting
ibm_db2.i5_dbcs_alloc=1to make room for extra conversion space required between client/server (up to six times greater memory requirements for these characters).
- user fix - setSetEnv=“CCSID=1208″ SetEnv=“LANG=C” in /www/zendsvr/conf/fastcgi.conf
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.
- Why LIBL not working ibm_db2 remote Linux/Windows (i5_libl)??? Linux/Windows ibm_db2 LIBL — here is why.
- Note: PHP interfaces maintained by IBM i may have new features added over time, so this evaluation is point of time as of (July, 2012).
- SQL naming (default) - select * from db2.anmials
$conn = db2_connect($db,$user,$pwd,array("i5_naming"=>DB2_I5_NAMING_OFF, 'i5_lib'=>"MYLIB"));- Setting the LIBL in this job has NO AFFECT on unqualified SQL operations
- Why is Zend Server default “i5_naming”=>DB2_I5_NAMING_OFF?
- ONLY IBM i has a concept of LIBL for unqualified SQL operations and ibm_db2 code is shared by Linux/Windows, therefore the common denominator SQL mode is default for all platforms for portability (even though often useless on IBM i)
- Why is Zend Server default “i5_naming”=>DB2_I5_NAMING_OFF?
- Setting the LIBL in this job has NO AFFECT on unqualified SQL operations
- System naming - select * from db2/animals
$conn = db2_connect($db,$user,$pwd,array("i5_naming"=>DB2_I5_NAMING_ON, 'i5_libl'=>"MYLIB YOURLIB ANYLIB"));- Setting the LIBL in this job WILL AFFECT on unqualified SQL operations
- Of course your user profile will set the rules of the LIBL
- (unless you set system LIBL override — which you probably should not do)
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