XMLSERVICE 123 PHPIBMDB 2
(click to open)
Quick Page Table of Contents
Scanning…
XMLSERVICE PHP ibm_db2
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