Description:
------------
I am unable to connect to databases across two DB2 instances within a single application run (e.g. using PHP-CGI with apache module).
I can create two php programs one connecting to a db on one instance, and one connecting to a db in the other. However, I am unable to combine the run into one program, leading me to believe that db2 can associate one and only one instance to a process (I am currently using the putenv("DB2INSTANCE=$instance") technique).
Googling, I could find nothing about how to re-attach instances, indeed I found that once attached it is apparently impossible to re-attach: http://dbforums.com/t366790.html 
Is there a way to do such context switching in ibm_db2 (as suggested in that link) using php, and if so, how?  If not, is there another way to use multiple DB2 instances from one php program (I'm trying to think of fork/ipc as the last resort).
Patches
Add a Patch
Pull Requests
Add a Pull Request
History
AllCommentsChangesGit/SVN commitsRelated reports            




    

 [2006-03-03 18:16 UTC] kfbombar at us dot ibm dot com
Phil, can you add the reproducible code, expected results, and actual results.  I would be happy to look into this for you.  I just needing some more information on how you are going about this issue.  Thank you.
 [2006-03-04 21:09 UTC] phil at redthreeconsulting dot com
The code is quite simple.  I have two connections:
putenv('DB2INSTANCE=instance1');
$c1 = odbc_connect('a','b','c');
putenv('DB2INSTANCE=instance2');
$c2 = odbc_connect('d','e','f');
Only the first one ($c1) connects, the second one returns "database 'D' not found".
If I switch them around:
putenv('DB2INSTANCE=instance2');
$c2 = odbc_connect('d','e','f');
putenv('DB2INSTANCE=instance1');
$c1 = odbc_connect('a','b','c');
Then the first one ($c2) connects, the second one returns "database 'A' not found".
The expected result is that I have two connections, regardless of the order.
 [2006-03-05 00:20 UTC] kfbombar at us dot ibm dot com
Phil, maybe I am thinking of a different route but I had no trouble with using db2_connect on two separate cataloged databases:
$conn = db2_connect('catalog_db1', 'uname1', 'pass1');
$conn2 = db2_connect('catalog_db2', 'uname2', 'pass2');
if ($conn) {
        print "success\n";
if ($conn2) {
        print "success\n";
My results were two separate connections on two separate cataloged databases in the same application.  
Please let me know if this helps or if you need any further information.
 [2006-03-05 21:20 UTC] phil at redthreeconsulting dot com
Yes, I am able to do this also.  I am only unable to connect the second time when the connections are on different instances of DB2.
 [2006-03-08 15:48 UTC] kfbombar at us dot ibm dot com
Phil,  I see your issue now that I have two instances set up but I have also found a work around.  You can connect through two instances at the same time if you use the full connection string.  For example:
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=dbname1;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=uname;PWD=pass;";
$conn = db2_connect($conn_string, '', '');
$conn2_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=dbname2;HOSTNAME=localhost;PORT=50001;PROTOCOL=TCPIP;UID=uname;PWD=pass;";
$conn2 = db2_connect($conn2_string, '', '');
This way you can simply connect to both instances through different ports.  I hope this works out for what you need. Let me know if you need any other assistance.  Thanks.
 [2006-03-09 11:58 UTC] phil at redthreeconsulting dot com
I want to thank you for sticking with me so far.  However, I have not been able to reproduce your success.
Here is what I am trying:
putenv("DB2INSTANCE=db2lawt");
$db = 'dev';	$uname = 'db2lawt';	$pass = 'passdb';	$port='50000';	$localhost='dfking12';
$connstring = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$db;HOSTNAME=$localhost;PORT=$port;PROTOCOL=TCPIP;UID=$uname;PWD=$pass;";
print "$connstring\n";$conn = odbc_connect($connstring, '', '');
var_dump($conn);
$db = 'prod';	$uname = 'db2lawp';	$pass = 'passdb';	$port='50001';	$localhost='dfking12';
$connstring = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$db;HOSTNAME=$localhost;PORT=$port;PROTOCOL=TCPIP;UID=$uname;PWD=$pass;";
print "$connstring\n";$conn2 = odbc_connect($connstring, '', '');
var_dump($conn2);
$db = 'dfk01';	$uname = 'username';	$pass = 'pass1';	$port='50002';	$localhost='dfking01';
$connstring = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$db;HOSTNAME=$localhost;PORT=$port;PROTOCOL=TCPIP;UID=$uname;PWD=$pass;";
print "$connstring\n";$conn3 = odbc_connect($connstring, '', '');
var_dump($conn3);
And here is the output:
DRIVER={IBM DB2 ODBC DRIVER};DATABASE=dev;HOSTNAME=dfking12;PORT=50000;PROTOCOL=TCPIP;UID=db2lawt;PWD=lawson;
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "connect".  Protocol specific error code(s): "79", "*", "*".  SQLSTATE=08001
, SQL state 08001 in SQLConnect in /usr/apache/htdocst/scripts/db2test.php on line 7
bool(false)
DRIVER={IBM DB2 ODBC DRIVER};DATABASE=prod;HOSTNAME=dfking12;PORT=50001;PROTOCOL=TCPIP;UID=db2lawp;PWD=lawson;
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "connect".  Protocol specific error code(s): "79", "*", "*".  SQLSTATE=08001
, SQL state 08001 in SQLConnect in /usr/apache/htdocst/scripts/db2test.php on line 12
bool(false)
DRIVER={IBM DB2 ODBC DRIVER};DATABASE=dfk01;HOSTNAME=dfking01;PORT=50002;PROTOCOL=TCPIP;UID=promov;PWD=adam1;
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL30081N  A communication error has been detected.  Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "".  Communication function detecting the error: "connect".  Protocol specific error code(s): "79", "*", "*".  SQLSTATE=08001
, SQL state 08001 in SQLConnect in /usr/apache/htdocst/scripts/db2test.php on line 17
bool(false)
================
Here are some things that do work:
putenv("DB2INSTANCE=db2lawp");
$db = 'dev';	$uname = 'db2lawt';	$pass = 'passdb';	$port='50000';	$localhost='dfking12';
$conn = odbc_connect($db, $uname, $pass);
var_dump($conn);
$db = 'prod';	$uname = 'db2lawp';	$pass = 'passdb';	$port='50001';	$localhost='dfking12';
$conn = odbc_connect($db, $uname, $pass);
var_dump($conn);
$db = 'dfk01';	$uname = 'username';	$pass = 'pass1';	$port='50002';	$localhost='dfking01';
$conn = odbc_connect($db, $uname, $pass);
var_dump($conn);
Output:
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL1013N  The database alias name or database name "DEV" could not be found.  SQLSTATE=42705
, SQL state 08001 in SQLConnect in /usr/apache/htdocst/scripts/db2test.php on line 6
bool(false)
resource(2) of type (odbc link)
resource(3) of type (odbc link)
================
Same as above, only the first line is changed:
putenv("DB2INSTANCE=db2lawt");
$db = 'dev';	$uname = 'db2lawt';	$pass = 'passdb';	$port='50000';	$localhost='dfking12';
$conn = odbc_connect($db, $uname, $pass);
var_dump($conn);
$db = 'prod';	$uname = 'db2lawp';	$pass = 'passdb';	$port='50001';	$localhost='dfking12';
$conn = odbc_connect($db, $uname, $pass);
var_dump($conn);
$db = 'dfk01';	$uname = 'username';	$pass = 'pass1';	$port='50002';	$localhost='dfking01';
$conn = odbc_connect($db, $uname, $pass);
var_dump($conn);
Output:
resource(2) of type (odbc link)
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL1013N  The database alias name or database name "PROD" could not be found.  SQLSTATE=42705
, SQL state 08001 in SQLConnect in /usr/apache/htdocst/scripts/db2test.php on line 10
bool(false)
resource(3) of type (odbc link)
 [2006-03-09 12:01 UTC] phil at redthreeconsulting dot com
Just for reference:
"dev" is catalogued under the instance "db2lawt"
"prod" is catalogued under the instance "db2lawp"
"dfk01" is catalogued twice: once on one instance and once on the other
 [2006-03-09 13:02 UTC] phil at redthreeconsulting dot com
I followed your lead and also some random googling (I've been googling on this issue for days now).  Just now I got a little farther, but I am stuck now still.  I am so close, I can taste it :) ...  Here is the progress I have made:
Following some tips from google about SVCENAME, I logged into my db2 instances, and did a 'db2 get db manager cfg | grep -i svcename' this gave me the service names, I then checked /etc/services for their configurations.  Turns out I had the ports wrong on the previous update.  The ports are:
db2c_db2lawp (prod) is port: 50002
db2c_db2lawt (dev) is port:  50003
db2lawp without db2c_ prefix is port 50000 and db2lawt is port 50001, there are also some entries with DB2_ prefix in the 6000x range, but I was only able to connect via DB2 ODBC to the db2c_ prefixed ones (50002,50003)
However, when I do establish the ODBC connection, I am getting the following error:
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL30082N  Attempt to establish connection failed with security reason "24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001
, SQL state 08001 in SQLConnect in /usr/apache/htdocst/scripts/db2multi.php on line 6
The username and password is quite valid ...
I tried prefixing the username with 'db2c_' and I get:
Warning:  odbc_connect(): SQL error: [IBM][CLI Driver] SQL1046N  The authorization ID is not valid.  SQLSTATE=28000
, SQL state 28000 in SQLConnect in /usr/apache/htdocst/scripts/db2multi.php on line 6
 [2006-03-09 14:07 UTC] kfbombar at us dot ibm dot com
Is there a reason that you don't want to use db2_connect like in my example?  If you can, can you please test the db2_connect in your needs and see if that allows you to create connections to multiple instances at the same time.  Let me know.  Thanks.
 [2006-03-09 14:24 UTC] phil at redthreeconsulting dot com
Sorry, I thought I explained why I am doing that.  I am using odbc_connect because when I use db2_connect it says that function is not defined.  I compiled ibm_db2 by using --with-ibm-db2 flag when ./configure-ing PHP 4.3.11
 [2006-03-09 17:23 UTC] tessus at evermeet dot cx
The ibm_db2 driver is a PECL extension. If you have used the --with-ibm-db2 configure option, then the odbc interface has been compiled into PHP. You have to use --with-IBM_DB2. Please have a look at http://www.php.net/manual/en/install.pecl.php how to install / compile PECL extensions.
Furthermore, if you use the full connection string as Kellen suggested, no databases have to be catalogued. Another possibility would be to catalog the database from instance2 in instance1.
 [2006-03-13 17:24 UTC] kfbombar at us dot ibm dot com
Phil,
Have you had any luck getting ibm_db2 built for PHP, and have you resolved the multiple db2 instances issue with the sample code I sent you?  Please let us know.  Thanks.
 [2006-03-14 11:11 UTC] phil at redthreeconsulting dot com
I am currently getting a wholly new problem -- Random "SQL error: [IBM][CLI Driver] SQL1013N The database alias name or database name "DEV" could not be found. SQLSTATE=42705 , SQL state 08001 in SQLConnect" errors -- sometimes they happen, and sometimes they dont and everything works fine.  This is unrelated to ibm_db2 (I suppose), and I am currently trying to install ibm_db2 (--with-IBM_DB2 instead of --with-ibm-db2)
P.S.  Would it hurt if I did both --with-IBM_DB2 and --with-ibm-db2 ?
 [2006-03-14 13:08 UTC] phil at redthreeconsulting dot com
Ok.  'pear list' shows ibm_db2 1.1.6 is installed, so I did ./configure and make, phpinfo() confirms:
Configure Command =>  './configure' '--with-ibm-db2=/db2/v81t/sqllib' '--with-IBM_DB2=/db2/v81t' '--with-apxs=/usr/apache/bin/apxs'
But 'php -m' still does not list ibm_db2.  db2_connect() is still undefined...
 [2006-03-14 14:02 UTC] kfbombar at us dot ibm dot com
Hi Phil,
Instead of going back and forth with this, can you give us exactly the commands you used to configure, install, and set up PHP with ibm_db2.  Then I will look them over and let you know the path to go to make this work.  Thanks.
 [2006-03-14 14:14 UTC] phil at redthreeconsulting dot com
I used:
pear list
// this showed ibm_db2 1.1.6 is installed
cd /usr/local/php-4.3.11
./configure --with-ibm-db2=/db2/v81t/sqllib --with-IBM_DB2=/db2/v81t --with-apxs=/usr/apache/bin/apxs
./make
./make install
 [2006-03-14 16:08 UTC] kfbombar at us dot ibm dot com
Hi Phil.  Here are steps that I just completed:
wget http://www.pecl.php.net/get/ibm_db2-1.2.0.tgz
wget http://www.php.net/distributions/php-5.1.2.tar.gz 
tar -zxvf ibm_db2-1.2.0.tgz
tar -zxvf php-5.1.2.tar.gz
cd php-5.1.2/ext
cp -R ../../ibm_db2-1.2.0 ibm_db2
cd ../..
./buildconf --force
./configure --with-IBM_DB2=/db2/install/loc
./make
./make install
php -m
Then ibm_db2 is shown.
Please let us know if you missed one of the steps, or if this helps.  Thanks.
 [2006-03-14 17:58 UTC] phil at redthreeconsulting dot com
Ok.  Thanks for sticking with me so far.  At the step ./buildconf --force, I get:
************ BEGIN CODE ***************
>./buildconf --force
Forcing buildconf
using default Zend directory
buildconf: checking installation...
buildconf: autoconf version 2.53 (ok)
buildconf: Your version of autoconf likely contains buggy cache code.
           Running cvsclean for you.
           To avoid this, install autoconf-2.13.
"build/build2.mk", line 32: make: 1254-055 Dependency line needs colon or double
 colon operator.
"build/build2.mk", line 33: make: 1254-055 Dependency line needs colon or double
 colon operator.
"build/build2.mk", line 35: make: 1254-055 Dependency line needs colon or double
 colon operator.
make: 1254-058 Fatal errors encountered -- cannot continue.
make: 1254-004 The error code from the last command is 2.
Stop.
************ END CODE ***************
I tried to continue with make, make install, but on make install I get:
************ BEGIN CODE ***************
>make install
        echo '\
Installing PHP SAPI module:       apache
[activating module `php5' in /usr/apache/conf/httpd.conf]
cp libs/libphp5.so /usr/apache/libexec/libphp5.so
chmod 755 /usr/apache/libexec/libphp5.so
cp /usr/apache/conf/httpd.conf /usr/apache/conf/httpd.conf.bak
cp /usr/apache/conf/httpd.conf.new /usr/apache/conf/httpd.conf
rm /usr/apache/conf/httpd.conf.new
Installing PHP CLI binary:        /usr/local/bin/
cp: sapi/cli/php: A file or directory in the path name does not exist.
make: 1254-004 The error code from the last command is 1.
Stop.
************ END CODE ***************
 [2006-03-15 10:38 UTC] kfbombar at us dot ibm dot com
This sounds like an issue with your environment/make.  Are you using GNU make or AIX's make?
 [2006-03-15 11:15 UTC] phil at redthreeconsulting dot com
Yes I believe it is.  I do not think I have the option of using GNU tools on this site.
strings `which make` | egrep -i "gnu|ver"
The above returns nothing, and 'make' does not respond to traditional GNU commands i.e. --help or --version, and man make is quite silent about it's nature, so I can only assume it is not GNU...
 [2006-04-25 11:35 UTC] phil at redthreeconsulting dot com
Ok well, after opening a ticket with IBM, it has turned out that this is simply not possible with DB2 in the way I was trying to do.  (It might be possible using JDBC).
Recall: we have instance1 with db1a and db1b on it.  And we have instance2 with db2a and db2b on it.  The problem is accessing all 4 databases from one instance.  The incorrectly hypothesized solution was to re-attach httpd between instances as necessary, or to attach to both at the same time.
The correct solution is to catalog all 4 dbs on one instance (doesnt matter which one).  Then everything else is straightforward.  (For detailed instructions on how to catalog, google the IBM site, or see: http://www-128.ibm.com/developerworks/db2/library/techarticle/0301chong/0301chong2.html )
Cheers,