DB2 Connection

[Technocrat]

What I want to do is from a linux box with Zend Server 5.3 connect to an iSeries DB2 and simply do SELECT statements against it.

 

I have installed php-5.2-pdo-ibm-zend-server & php-5.2-ibmdb2-zend-server, and they show green in the server console.

 

I would think using the PDO would be the easiest. However I am having a hell of a time. Anyone have any tutorials on getting this to work?

 

I have tried:

$db = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=S104557f;HOSTNAME=10.*.*.*;PORT=8472;PROTOCOL=TCPIP;", "****", "*****");

 

But I get:

SQLSTATE= , SQLDriverConnect: -1390 [iBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".

 

Any help?

[HenryCan]

I'm a little rusty with DB2 and have no experience accessing it with PDO but I found this tutorial which seems promising:

 

http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html#4.9

 

They refer to it as IBM rather than DB2 but the information might be helpful to you.

 

If not, look at the DB2 reference manuals, particularly the Messages and Codes manual (or whatever they call it these days) for details of SQL10007N. It will tell you what reason code 3 is and might tell you what message 0 is. You should be able to find all of the iSeries manuals at the IBM website. (I mostly use the Windows/Linux/Unix version and sometimes the mainframe version so I'm not sure what the URL is for iSeries.) It's possible the iSeries manuals will also cover PDO access to the database but I can't say that for sure without seeing the manuals.

 

Hmm. I think we have a fundamental problem here. I just found the iSeries manuals - http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rzahf/rzahfli0.htm - and I'm getting the impression that iSeries runs on the AS/400, not Linux. I'm certainly not finding an SQL10007N message in the Messages and Codes book. Are you accessing an AS/400 from a Linux client? I think that's possible although I never actually tried it myself. But I can't see how it could give you a non-existent error message under those circumstances unless these manuals are very old. (I don't see any publication dates on anything at this site so it's possible these are not current manuals.)

 

If you're actually running DB2 for Windows, Unix and Linux rather than iSeries, the description for SQL10007N is here: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.messages.sql.doc%2Fdoc%2Fmsql10007n.html

 

If you enter PDO in the search box on the top of that page, it takes you to a long list of hits that describe using PDO to access DB2. The first of those hits is this: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.php.doc%2Fdoc%2Ft0023135.html

 

If that doesn't help, maybe the other hits on that list will get you going in the right direction.

 

I hope some of this helps....

 

I

[Technocrat]

Thanks for your response.

 

I think the message is trying to tell me that it can't make a connection. If I mess around with the settings the message is the same. Even with the wrong IP. So at least I understand why the error, I guess.

 

I am trying to get to the AS/400 from Linux, and you can do it as I can do the exact same thing with Windows using the Windows ODBC driver. However...its Windows and I rather get this working on Linux.

 

I did not install any clients as it looks like I can do it without, unless I am mistaken.

 

One thing I have not done is create a DSN. I would think I can use the string to get by with out it, however I might be mistaken there too.

[HenryCan]

I'm really not going to be able to do much more than I already have. I've never accessed an AS/400 from Linux and have no idea of how it is done or even if it can be done. I once had a colleague ask me for help connecting a Windows client to AS/400 but I didn't even know how to do that ;-) It seems possible that a Linux box can talk to an AS/400 but I would check that out just to be sure you're not trying to do something impossible. You could spend a lot of time chasing your tail for nothing if it isn't possible. The fact that Windows can talk to AS/400 isn't conclusive proof that Linux can too.

 

If you do some googling, you can probably find out almost anything you'd ever need to know. If it was me, I'd google on "DB2 iSeries Linux PDO" and see where that takes you.

 

Or look for iSeries forums then see if you can find someone there that has connected Linux to AS/400 for purposes of doing DB2 work.

 

If you use Usenet, there's a newsgroup there that might be helpful. The people there are mostly mainframe and Windows/Unix/Linux guys but I think there are occasionally questions about iSeries. The newsgroup is called comp.databases.ibm-db2. It's still fairly active.

[Technocrat]

Ok I got it work, sorta.

 

I followed:

http://www.feub.net/2010/11/ingredients-php-db2-and-unixodbc/

And got the error:

Can't open lib '/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so' : file not found

 

Found that I had to add /opt/ibm/iSeriesAccess/lib64/ to the LD_LIBRARY_PATH

 

Then it worked. Though I would pefer to use PDO at least this is a start

[HenryCan]

Congratulations on getting ODBC working for you! I know it's not what you ultimately want but at least it lets you get to your data with your existing infrastructure.

 

I suspect that PDO is also possible but I'm checking on the comp.databases.ibm-db2 newsgroup for you. If I forget to come back and tell you what I found out, you can access the newsgroup yourself with any newsreader and see what the responders have said. It might take a few days. The newsgroup is active but to the point of dozens of posts a day.

[Technocrat]

Thanks for assistance! If you do find something else please let me know

[HenryCan]

I'm happy to tell you that I have an answer and it's mostly good news.

 

According to a guy at IBM's Toronto Lab, where they develop DB2 for Windows, Linux and Unix, you CAN access DB2 iSeries data via PDO. That's the good news. And since you're getting it from a guy at the Toronto Lab who works for IBM, I think you can be quite certain that this is an accurate answer, not just someone indulging in wishful thinking.

 

The only wrinkle is that you apparently need to use DB2 Connect to accomplish this. I have only a very vague idea about what DB2 Connect offers and what it costs but I've found you a web page that gives you some basic information and lets you request a quote. That way, you can figure out if it is worthwhile to buy DB2 Connect or if it is better to just stay with ODBC which you seem to be able to do without buying any other products.

 

Here's the link to DB2 Connect: http://www-142.ibm.com/software/products/us/en/db2connfami/

[Technocrat]

Ugh, yeah that's not worth it. I am only fetching a few fields from a few tables so paying that wouldn't be cost prohibitive. Thanks though.

[HenryCan]

You're welcome.

[MaKR]

I'm stuck in a similar spot but unfortunately can't use PDO.  I do have a few more additions to this post too, most specifically methods of connecting.  First off, iSeries is the AS/400, IBM renamed it.  There are 3 ports to be concerned with, and 2 library sets that can be used.

 

First off, ports:

• 446 - default DB2 port on iSeries
• 8471 - default ODBC port on iSeries
• 50000 - default DB2 port on non-iSeries

Then we have to worry about libraries:

• PECL (PEAR extension) - using db2_connect
• Zend - using Zend_db_adapter (relies on PECL db2_connect)
  • Zend has advantages over straight PECL, mainly security issues, read the docs for more info

 

How to use db2_connect:

// connect by name (only works when apache and db2 are on the same box)
$db2_conn=db2_connect($db2_name, $db2_usr, $db2_pwd); // $db2_name default is '*LOCAL'

// connect by string (works on separate apache and db2 machines)
$db2_conn_str="DATABASE=*LOCAL;HOSTNAME=192.168.0.2;PORT=446;PROTOCOL=TCPIP";
$db2_conn=db2_connect($db2_conn_str, $db2_usr, $db2_pwd);

view/configure database name on iSeries by running WRKRDBDIRE

 

 

some of the better links I've found:

• server/client config: http://www.ibm.com/developerworks/data/library/techarticle/0301chong/0301chong2.html
• apache howto: http://elephpants.blog.redpill-linpro.com/2009/04/14/connecting-to-an-ibm-db2-on-system-i-from-lamp/
• pecl db2 adapter: http://pecl.php.net/package/ibm_db2
• DB2 UDB (free client from IBM): http://www-01.ibm.com/support/docview.wss?uid=swg21256235

 

 

..... and for the issues I'm having (hoping someone knows what I'm doing wrong)

 

Running Apache on Linux connecting to iSeries db2 I get same error as OP:

$db2_svr='192.168.72.2';
$db2_name='*LOCAL';
$db2_port=446;
$db2_conn_str="DATABASE=$db2_name;HOSTNAME=$db2_svr;PORT=$db2_port;PROTOCOL=TCPIP";
$db2_conn=db2_connect($db2_conn_str, $db2_usr, $db2_pwd) or die(...);

[iBM][CLI Driver] SQL10007N Message "0" could not be retrieved.  Reason code: "3". SQLCODE=-1390

 

Running Apache on same iSeries box as db2

$db2_name='*LOCAL';
$db2_conn = db2_connect($db2_name, $db2_usr, $db2_pwd) or die(...);

success

$db2_svr='192.168.72.2';
$db2_name='*LOCAL';
$db2_port=446;
$db2_conn_str="DATABASE=$db2_name;HOSTNAME=$db2_svr;PORT=$db2_port;PROTOCOL=TCPIP";
$db2_conn=db2_connect($db2_conn_str, $db2_usr, $db2_pwd) or die(...);

Error: Error occurred in SQL Call Level Interface SQLCODE=-99999

 

So it looks to me like I'm dealing with 2 problems - 1 is client side (SQLCODE=-1390), 2nd is server side (SQLCODE=-99999).  I'm having trouble finding information on the error codes, and from what I read -99999 is a generic error that gives no real information.  Hopefully I'm doing something wrong in the string and someone can catch it?

Edited February 27, 2013 by MaKR