XMLSERVICE 123 PHPIBMDB 2

(click to open)

Quick Page Table of Contents

Scanning…

XMLSERVICE PHP ibm_db2

Goto Main Page
Goto Documents

PHP ibm_db2 was used for this XMLSERVICE test (included with Zend Server downloads 1/2 tier). This is not the Zend PHP Toolkit interface, instead this is what happens below that “nice” PHP interface wrapper.

Step 1) Install XMLSERVICE

Download:
1) XMLSERVICE main page has download "latest" and install instructions.

IMPORTANT: If you are running a machine with CCSID 65535 (and nothing works), please read and follow the documentation (main XMLSERVICE page), setting valid CCSID like 37 for Apache (web) and/or command line PHP (pear tests).

/www/zendsvr/conf/httpd.conf (web admin GUI port 2001 - ZENDSVR):
DefaultFsCCSID 37  ... or 280 (Italian) ... or so on ...
CGIJobCCSID 37     ... or 280 (Italian) ... or so on ...

Step 2) Configure PHP ibm_db2

Option 1-tier)
PHP 1-tier on IBM i you only need to check “Relational Database Directory Entries” valid for your desired connection (WRKRDBDIRE).

  • db2_(p)connect(“LP0164D”,”MYID”,”MYPWD”) - local DB2 work this machine
  • db2_(p)connect(“I5B520″,”MYID”,”MYPWD”) - local DB2 work IASP
  • db2_(p)connect(“RCHAS2M3″,”MYID”,”MYPWD”) - remote DB2 work another machine
    • Note: You also need to decide how fast (hits/second) you want to run calls to XMLSERVICE. Where db2_pconnect will reuse connections faster (tougher to design site-wide PHP scripts) or db2_connect full open/close a connection is slower (easy PHP scripts).
> WRKRDBDIRE
                Work with Relational Database Directory Entries
 Position to  . . . . . .                     
 Type options, press Enter.
   1=Add   2=Change   4=Remove   5=Display details   6=Print details
                             Remote
 Option  Entry               Location                  Text                           
         ENOSPC              LOOPBACK                  Entry added by system
         LP0164D             *LOCAL                    php examples
         RCHAS2M3            rchas2m3.rchland.ibm.c >  RCHAS2M3
         I5B520              i5b520                    database i5B520

Option 2-tier)
2-tier from Linux/Windows to IBM i you need DB2 Connect product/drivers from IBM including a license to call IBM i (WRKRDBDIRE).

  • db2_(p)connect(“LP0164D”,”MYID”,”MYPWD”) - remote DB2 work IBM i machine
Q: What are the packages for DB2 Connect?
A: 4 packages ...
1) IBM Data Server Driver Package (ds driver) 
   (small package)- best practices client for deployment (drivers only)
2) IBM Data Server Runtime Client (runtime client)
   (large package)- superset Data Server Driver (+ utilities)
3) IBM Data Server Client (client)
   (enormous)- drivers and libraries for programming (+ GUI, all) 
4) IBM Data Server Driver for ODBC and CLI (cli driver)
   (tiny)- solely for ISVs to embed application
Note 1: In terms of DB2 Connect - all of these client packages 
        CAN BE enabled for z/OS and IBM i servers by applying 
        a DB Connect license key. 
Note 2: IBM’s Call Level Interface (CLI) is a superset of the ODBC API. 
Download: http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217

Q: How do i catalog a connection IBM i? 
A: use the db2 command line (IBM Data Server Runtime Client ++)
>su db2inst1
>db2
catalog tcpip node LP0164D remote LP0164D.lab.com server 446
catalog db LP0164D at node LP0164D authentication dcs
catalog dcs db LP0164D as LP0164D
terminate
Note: "LP0164D" - can be found using WRKRDBDIRE (local RDB name / database name)
-- or packages without db2 utility --
> sudo gedit /opt/ibm/cli/cfg/db2cli.ini
[LP0164D]
Database=LP0164D
Protocol=tcpip
Hostname=LP0164D.lab.com
Servicename=446

Q: What steps do I take to resolve conf issues?
A: common problem:
If db2 catalog fails, likely you need to install missing license.
> db2licm -a db2/license/license_filename
where license_filename represents (see Passport Advantage):
  db2conee.lic - DB2 Connect Enterprise Edition
  db2conpe.lic - DB2 Connect Personal Edition
  db2conue.lic - DB2 Connect Unlimited Edition
  db2conas.lic - DB2 Connect Application Server Edition
-- or packages without db2licm utility --
> sudo -s
> unzip DB2_CUEi_QS_Activation_97.zip  (wherever you get .lic file)
> ls db2/consv_is/db2/license/
db2consv_is.lic  db2jcc_license_cisuz.jar  sam31.lic  sam32.lic  UNIX  Windows
> cp db2/consv_is/db2/license/* /opt/ibm/cli/license/. (wherever installed)

Q: how do i test my db2 connect connection?
A: use db2 test your connection.
> db2 connect to LP0164D user myUid using myPassword

Q: Why does my DB2 Connect hang port 8471 (database)?
A: Because you need to use port 446 (DDM/DRDA).

Step 3 - Example - PGM complex data with DS

Example calling a typical RPG PGM with a few parameters, one of which is a data structure. In this case all the parameters are io=‘both’ (input/output), so the XML going in looks exactly like the XML coming out of XMLSERVICE, except the return XML data values will change to whatever the called function writes into the parameter variables.

XMLSERVICE io stored procedures available in various sizes:

$stmt = db2_prepare($conn, "call $libxmlservice.iPLUG4K(?,?,?,?)");
$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);

DB2 in/out parameters (connections supporting in/out parameters):
... sizes: 4K, 32K, 65K, 512K, 1M, 5M, 10M up to 15M  (see crtsql in download) ...
iPLUG4K(IN IPC CHAR(1024), IN CTL CHAR(1024),IN CI CHAR(4064), OUT C0 CHAR(4064))
iPLUG32K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(32000), OUT CO CLOB(32000)) 
iPLUG65K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(65K), OUT CO CLOB(65K)) 
iPLUG512K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(512K), OUT CO CLOB(512K)) 
iPLUG1M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(1M), OUT CO CLOB(1M)) 
iPLUG5M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(5M), OUT CO CLOB(5M)) 
iPLUG10M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(10M), OUT CO CLOB(10M)) 
iPLUG15M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(15M), OUT CO CLOB(15M)) 

connection.inc - connection information

<?php
// database
$database    = "LP0164D"; 
$user        = "ADCU"; 
$password    = "NICEXXXX";
$internalKey = "/tmp/rangerusr";

// misc test settings
$i5persistentconnect = false;
$libxmlservice       = 'XMLSERVICE'; // ZENDSVR
$toolkitdir          = ".";          // zend ship also
$i5rest              = "http://lp0164d.rchland.ibm.com/cgi-bin/xmlcgi.pgm";
$i5restdatabase      = "*LOCAL";     // only *LOCAL tested

// call parms
$ipc        = $internalKey;
$ctl        = "*sbmjob";
$clobIn     = "";
$clobOut    = "";

function test_lib_replace($xml) {
  global $libxmlservice;
  $was = array("xyzlibxmlservicexyz");
  $now = array("$libxmlservice");
  $out = str_replace($was,$now,$xml);
  return $out;
}
?>

test_ibm_db2_io_proc.php - test stored procedure call XMLSERVICE

<?php
require_once('connection.inc');
// call IBM i
if ($i5persistentconnect) $conn = db2_pconnect($database,$user,$password);
else $conn = db2_connect($database,$user,$password);
if (!$conn) die("Bad connect: $database,$user");
$stmt = db2_prepare($conn, "call $libxmlservice.iPLUG4K(?,?,?,?)");
if (!$stmt) die("Bad prepare: ".db2_stmt_errormsg());
$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);
if (!$ret) die("Bad execute: ".db2_stmt_errormsg());
// -----------------
// output processing
// -----------------
// dump raw XML (easy test debug)
var_dump($clobOut);

//     D  INCHARA        S              1a
//     D  INCHARB        S              1a
//     D  INDEC1         S              7p 4        
//     D  INDEC2         S             12p 2
//     D  INDS1          DS                  
//     D   DSCHARA                      1a
//     D   DSCHARB                      1a           
//     D   DSDEC1                       7p 4      
//     D   DSDEC2                      12p 2            
//      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//      * main(): Control flow
//      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//     C     *Entry        PLIST                   
//     C                   PARM                    INCHARA
//     C                   PARM                    INCHARB
//     C                   PARM                    INDEC1
//     C                   PARM                    INDEC2
//     C                   PARM                    INDS1
function getxml() {
$clob = <<<ENDPROC
<?xml version='1.0'?>
<script>
<pgm name='ZZCALL' lib='xyzlibxmlservicexyz'>
 <parm  io='both'>
   <data type='1A' var='INCHARA'>a</data>
 </parm>
 <parm  io='both'>
   <data type='1A' var='INCHARB'>b</data>
 </parm>
 <parm  io='both'>
   <data type='7p4' var='INDEC1'>11.1111</data>
 </parm>
 <parm  io='both'>
   <data type='12p2' var='INDEC2'>222.22</data>
 </parm>
 <parm  io='both'>
  <ds>
   <data type='1A' var='INDS1.DSCHARA'>x</data>
   <data type='1A' var='INDS1.DSCHARB'>y</data>
   <data type='7p4' var='INDS1.DSDEC1'>66.6666</data>
   <data type='12p2' var='INDS1.DSDEC2'>77777.77</data>
  </ds>
 </parm>
 <return>
  <data type='10i0'>0</data>
 </return>
</pgm>
</script>
ENDPROC;
return test_lib_replace($clob);
}

Step 4 - Example - Use PHP ibm_db2 to Kill XMLSERVICE job

The keywords used to route the kill order are ctl=“*immed” and ipc=“/tmp/rangerusr” (same ipc used above example).

XMLSERVICE stored procedures with result set available in various sizes:

$stmt = db2_prepare($conn, "call $libxmlservice.iPLUGR4K(?,?,?)");
$ret=db2_execute($stmt,array($ipc,$ctl,$clobIn));
while ($row = db2_fetch_array($stmt)){
  $clobOut .= $row[0];
}

DB2 Result set returned (connections not supporting in/out parameters):
... sizes: 4K, 32K, 65K, 512K, 1M, 5M, 10M up to 15M (see crtsql in download) ...
CREATE PROCEDURE XMLSERVICE.iPLUGR4K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CHAR(4096)) 
iPLUGR32K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CHAR(32000)) 
iPLUGR65K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(65K)) 
iPLUGR512K(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(512K)) 
iPLUGR1M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(1M)) 
iPLUGR5M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(5M)) 
iPLUGR10M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(10M)) 
iPLUGR15M(IN IPC CHAR(1024), IN CTL CHAR(1024), IN CI CLOB(15M)) 

test_ibm_db2_kill.php - kill XMLSERVICE job

<?php
require_once('connection.inc');
// call IBM i
if ($i5persistentconnect) $conn = db2_pconnect($database,$user,$password);
else $conn = db2_connect($database,$user,$password);
if (!$conn) die("Bad connect: $database,$user");
$ctlKill="*immed";
$clobInKill = '<?xml version="1.0"?>';
$sql = "call $libxmlservice.iPLUGR4K('$ipc','$ctlKill','$clobInKill')";
$ret=db2_exec($conn,$sql);
?>

Author(s)

Tony “Ranger” Cairns - IBM i PHP / PASE