Search:  
Gentoo Wiki

HOWTO_Connect_to_iSeries_AS/400_DB_with_PHP

Contents

Note - Pelase Read :)

I'm currently very new to making wiki's. I have wrote a few howtos for work and myself and felt I should share them though. So please bear with me as I learn the markup and what not. You can find the most recent and original version of this howto at http://sqls.net/?s=article&id=9


Introduction

So, far. As I am beleiving. You need to follow these basic steps. I did this all on a Gentoo system. I am sure you could follow this for any distro with basic understanding of your distro.


Requirements

The versions are not really -required- but just here so you know what version I completed this on. Other versions might work slightly differently.

Apache v2.0.58

PHP v5.2.4

DB2 Client v9.1

DB2 Connect PE v9.1

My Assumptions

I'm not covering how to install PHP or Apache. If you haven't got that far yet you need a lot more help then I'm willing to write :). There's plenty of other howto's on those subjects.


Step 1 - Install DB2 Client

According to http://us.php.net/manual/en/ref.ibm-db2.php we need to install the client. I downloaded this file from the IBM Passport Advantage site. This site requires a login and for you to have purchased an associated product. For me we purchased DB2 Connect PE. The filename was DB2_Client_V913_LNX86.tar It doesn't uncompress into it's own folder. So here's what I did with it

#	mkdir /opt/db2client
#	mv DB2_Client_V913_LNX86.tar /opt/db2client/
#	cd /opt/db2client/
#	tar -xvf DB2_Client_V913_LNX86.tar

Now lets install it to the default folders.

#	./db2_install

Say No, at the first prompt, which tells the installer to use the default paths. Then enter CLIENT for the second prompt. Which should be your only option. That tells the installer what it is installing. Hopefully you have no errors :). I didn't have any.

Step 2 - Install DB2 Connect PE

Much like Step 1. I downloaded the file from the same place but this one is named DB2_CONPE_V913_LNXX86.tar

#	mkdir /opt/db2conpe
#	mv DB2_CONPE_V913_LNXX86.tar /opt/db2conpe
#	cd /opt/db2conpe
#	tar -xvf DB2_CONPE_V913_LNXX86.tar
#	./db2_install

Say No, then CLIENT.. Wth is the difference from CLIENT and CONPE?

Step 3 - Create users & Instance

#	useradd db2inst1
#	mkdir /home/db2inst1
#	chown db2inst1:db2inst1 /home/db2inst1

Now create an instance for the DB.

#	cd /opt//ibm/db2/V9.1/instance
#	./db2icrt -a server -u db2fenc1 db2inst1

It may complain about not being able to modify the users .profile or .login file. This is fine, for our purpose it's not needed. Just so when it finishes it states

DBI1070I Program db2icrt completed successfully.

Or something along those lines.

Step 4 - Install IBM-DB2 PECL Driver

The IBM-DB2 driver can be installed via PECL and the easist method is the pecl command.

#	pecl install ibm_db2

Now edit yout php.ini file and add

extension ibm_db2.so

Now reload apache.

#	/etc/init.d/apache2 reload

You could create a php script displaying the phpinfo(); details and check that the section ibm_db2 exists.

Step 5 - Create a connection to your iSeries DB

I am not 100% on this section - I mean.. It works and all for me in my environment but finding documentation on it was shetchy so there may be more details that I am missing for a proper setup. I am also not a IBM guy so my description of some things may be a bit off. I believe you can do this step via some gnome/kde GUI - and there's more documentation supporting that method. I, however, am doing this from the command-line. What we are doing here is creating a link from the local (linux) system to the iSeries AS/400 system.

A part that confused me is the database name. Apparently on the AS/400 there is a database name and then schema's within that database name. To me, I thought the "schema" was the database name :). You get the database name from the as/400 command WRKRDBDIRE - another anoynance is once you connect to this database you must specificy the schema within your sql statements. So, if you want all users from the table user in the schema webportal you might ask "select * from webportal.users"


# source /home/db2inst1/sqllib/db2profile
# db2
catalog tcpip node <node> remote <as400-host> server 446 remote_instance <as400-dbname> ostype os400
catalog dcs database <as400-dbname>
catalog database <as400-dbname> at node <node> AUTHENTICATION DCS

You will need to modify that slighty. The items incased with < and > symbols need to be changed for your system. Here's a break down.

<node> = Any string representation you wish to use as the "node" name for this database. I used "as400" as far as I can tell this is only used internally by DB2 Connect

<as400-host> = The host or IP address of your as400 system.

<as400-dbname> = the Database name on your as400 that you want to attach to. You can find this with the as/400 command WRKRDBDIRE

Also, you can make aliases if you wish. I kept the same naming to keep things simple. But say you have some confusing DB name on the AS/400 (like me) but want to name it as400db on the linux system for whatever reason.

catalog tcpip node <node> remote <as400-host> server 446 remote_instance <as400-dbname> ostype os400
catalog dcs database as400db as <as400-dbname>
catalog database as400db at node <node> AUTHENTICATION DCS


- Note, to remove those entries for whatever reasons
uncatalog node 
uncatalog dcs database 
uncatalog database 


Now you can test it by connecting to it

connect to S1049ZKM user username using password
select count(*) from SCHEMA.TABLE


Please change SCHEMA to a valid SCHEMA on the AS400 and TABLE to a valid table within that SCHEMA.

Now if you get data here, we should be all set.

Step 6 - Test a query within PHP

then! I tried this in php..

File: test.php
<?php
 $conn = db2_connect('<as400-dbname>', 'username', 'password');
 if (!$conn) {
	print db2_conn_errormsg();
 }
 Echo "Connected to as400db";

 $query = "select count(*) from amsdta.f4101";
 $stmt = db2_prepare($conn, $query);
 if ($stmt) {
	$rs = db2_execute($stmt);
	if ($rs);
	{
		while ($row = db2_fetch_assoc($stmt)) {
			print_r($row);
		}

	}

 } else { db2_stmt_error($conn); }

 db2_close($conn);
?>


If that works, you're all done. Please note to change <as400-dbname> to the DB name on your as400. That query could be modified to anything else to test with. If you are running JDE it will poll a bit of data from the JDE database. I don't know if it applies to this driver or the ODBC php driver only? But there is a PDO extension you might look into at http://us.php.net/manual/en/ref.pdo-ibm.php. I plan to test that out so I'll update this with whatever I learn. If you have any questions or are seriously lost I can be contacted at http://sqls.net/contact/ but there's no promise I'll respond :)

Step 7 - CleanUp

You can delete the directories /opt/db2conpe, and /opt/db2client becuase they are only used for the install of those two products.

Retrieved from "http://www.gentoo-wiki.info/HOWTO_Connect_to_iSeries_AS/400_DB_with_PHP"

Last modified: Fri, 05 Sep 2008 10:40:00 +0000 Hits: 656