Tier 2 LIBL

(click to open)

Quick Page Table of Contents

Scanning…

PHP DB2 to IBM i — topic remote set LIBL

[goto main page]

[goto document links]

Who is this page for?

Instructions designed for IBM i developer learning PHP starting from ground zero …

The rule of IBM i

  • System naming mode on (lib/table), LIBL will be used when locating unqualified objects
  • SQL naming on (schema.table), LIBL will be ignored when locating unqualified objects

Update 2013–02–04 … DB2 Connect V10.1 and IBM i 7.1 have been enhanced to provide naming mode control.

  • Scott’s reportDB2 Connect V10.1 and IBM i 7.1
  • next step … IBM/Zend need updates PECL ibm_db2 for Linux/Unix/Windows to support this new 2-tier feature for IBM i

Why LIBL not working ibm_db2 remote Linux/Windows (i5_libl)???

As of today, Linux/Unix/Windows (LUW) DB2 Connect drivers do not support IBM i system naming mode, therefore ibm_db2 i5_libl is not supported. Of course LUW DB2 Connect and ibm_db2 are always changing so the future is unset, however many people have attempted to use i5_libl from LUW without success, so this page is intended to save you some time. Of course system naming modes and LIBL options work on IBM i, so if you need this support run PHP on IBM i (Zend Server for IBM i).

 
<?php
// i5_libl will not work from Windows/Linux
// ONLY works running ibm_db2 on IBM i 
$conn = db2_connect($db,$user,$pwd,array("i5_naming"=>DB2_I5_NAMING_ON, 'i5_libl'=>"MYLIB YOURLIB ANYLIB"));
?>

Also for the enterprising do-it-yourself people … Linux/Windows LIBL via call qsys2.qcmdexc(“CHGLIBL …”) will also NOT work for unqualified objects from ibm_db2, until such time DB2 Connect supports system naming mode to IBM i (see full example run from Linux below). Of course system naming modes and LIBL options work on IBM i, so if you need this support run PHP on IBM i (Zend Server for IBM i).

<?php
// php zzquicklibl.php 
// Set-up:
//    strsql
//    > create table adc/adconly(id integer, info varchar(32))                    
//      Table ADCONLY created in ADC.                                             
//    > insert into adc/adconly(id, info) values(1,'adc libl')                    
//      1 rows inserted in ADCONLY in ADC. 
// Works fine on IBM i (not LUW) ...                                       
//    > cd /MYASP2/www/zend2/htdocs/tests/xmlservice
//    > php zzquicklibl.php    -- $i5systemnaming = true
//      1,adc libl
//    > php zzquicklibl.php    -- $i5systemnaming = false
//      Bad exec direct (select * from adconly): ADCONLY in DB2 type *FILE not found. SQLCODE=-204

// connection
$i5systemnaming = false;
$i5persistentconnect = false;
$database = 'LP0264D';   //LINUX side (db2cli.ini)
$user = 'DB2';
$password = 'XXXXXXX';
$options = array("i5_naming"=>DB2_I5_NAMING_ON);

// connect
if ($i5persistentconnect) {
// $conn = db2_connect($database,$user,$password) // $options not valid LUW
  if ($i5systemnaming) $conn = db2_pconnect($database,$user,$password,$options);
  else $conn = db2_pconnect($database,$user,$password);
}
else {
// $conn = db2_connect($database,$user,$password) // $options not valid LUW
  if ($i5systemnaming)  $conn = db2_pconnect($database,$user,$password,$options);
  else $conn = db2_connect($database,$user,$password);
}
if (!$conn) die("Bad connect: $database,$user \n");

// **** set the IBM i libl
// Works correctly PHP on IBM i ...
// Will not work PHP Linux/Unix/Windows (LUW) vis DB2 Connect ...
// $conn = db2_(p)connect($database,$user,$password)
//   Error #1 - system naming ($i5systemnaming = true)
//   $ php zzquicklibl.php 
//   Bad exec direct (call qsys2/qcmdexc('CHGLIBL LIBL(ADC XMLSERVICE) CURLIB(ADC)',40)): 
//   [IBM][CLI Driver][AS] SQL0969N  There is no message text corresponding to SQL error 
//   "-5016" in the message file on this workstation.  The error was returned from module 
//   "QSQRPARS" with original tokens "QCMDEXC".  
//   SQLSTATE=42833 SQLCODE=-5016
$curlib = "ADC";
$libl = "ADC XMLSERVICE";
$cmd = "CHGLIBL LIBL($libl) CURLIB($curlib)";
$cmdlen = strlen($cmd);
if ($i5systemnaming) {
  $sql = "call qsys2/qcmdexc('$cmd',$cmdlen)"; // system naming '/'
}
else {
  $sql = "call qsys2.qcmdexc('$cmd',$cmdlen)"; // sql naming '.'
}
$stmt = db2_exec($conn,$sql);
if (!$stmt) die("Bad exec direct ($sql): ".db2_stmt_errormsg()."\n");


// **** fetch table only in library ADC
// Works correctly PHP on IBM i ...
// Will not work PHP Linux/Unix/Windows (LUW) vis DB2 Connect ...
// $conn = db2_(p)connect($database,$user,$password)
//   Error #2 - sql naming ($i5systemnaming = false)
//   $ php zzquicklibl.php 
//   Bad exec direct (select * from adconly): 
//   [IBM][CLI Driver][AS] SQL0204N  "DB2.ADCONLY" is an undefined name.  
//   SQLSTATE=42704 SQLCODE=-204
$sql2 = "select * from adconly";    // unqualified object
$stmt = db2_exec($conn,$sql2);
if (!$stmt) die("Bad exec direct ($sql2): ".db2_stmt_errormsg()."\n");
while ($row = db2_fetch_array($stmt)){
  echo implode(",", $row)."\n";
}

?>