php
Add note for connecting to IBM i Db2 locally
It took me over a week and contacting my good friend, @kadler, to finally figure out why my local connection kept failing. Sure, one could just pass the database name and not a DSN when using these functions locally, but frameworks like Doctrine force pass a DSN, and it's not optional. (See IBMDB2/Driver.php and IBMDB2/DataSourceName.php).
After a lot of digging, it turns out that locally on IBM i, the connection string accepts only DSN, UID, and PWD. Using the string in the docs and the string that Doctrine builds from configurations does not work locally on IBM i. I plan to update their docs as well, but I believe this should be reflected in PHP's docs first.
Is there IBM documentation we can cite here? Keep in mind with local connections can be a bit weird (i.e. running SQL/CLI in-job).
IIRC, the DSN should correspond to the relational database entries that you can modify with WRKRDBDIRE.
Sure, there is. When using db2_connect on IBM i with a connection string instead of an explicit database name, it uses SQLDriverConnect. See SQLDriverConnect's Usage docs, where it names the parameters as DSN (not DATABASE), UID, and PWD.
Furthermore, it's quite easy to test. On an IBM i:
<?php
// Fails
db2_connect('DATABASE=dbname;HOSTNAME=localhost;UID=user;PWD=password;', '', '');
// Succeeds
db2_connect('DSN=dbname;UID=user;PWD=password;', '', '');
I suspect this never really came up, because when people use a connection string here, they almost always are doing it for LUW systems or remotely, which uses whatever native driver they're using at the time, which should accept DATABASE just fine. It's only with the SQLDriverConnect on IBM i that this happens.
Only reason I ran into it is, like I said, Doctrine forces a DSN connection string with no option of doing it the typical way.
Further details from Kevin, explaining how the two different parameter formats map and behave.
SQLConnect takes separate parameters for the DSN, user name, and password. This is what db2_connect('database', 'user', 'password') maps to.
The alternative is to use SQLDriverConnect which takes a single input parameter which is a connection string with semicolon-separated options. In this option there is no separate username/password fields so you must pass those (if applicable) in the UID/PWD options in the connection string. This is what you get when you use the db2_connect('DSN=dsn;UID=user;PWD=password;', '', '') syntax.