We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to write to DB2 on iseries AS400 with DB2Connect using PHP ibm_db2 pecl on Linux without Journaling

cyrex56
cyrex56 asked
on
Medium Priority
1,752 Views
Last Modified: 2013-12-13
I'm trying to write to files on an iSeries using DB2 connect with PHP (using the pecl ibm_db2 package). I can write successfully only if the file I am writing to is journaled.
It appears that using the db2 command line that if I run the command: change isolation to NC, then connect to my database and run an insert SQL, it works, but i have found no way to do this from PHP the autocommit options appears to be useless and the i5 specific features on ibm_db2, do not apply since we are running db2 on an iseries
Comment
Watch Question

Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
I'm not much of an expert on accessing DB2 from PHP, but I'll give it a shot:

1) Autocommit doesn't help with a non-journaled table, since you can only have an isolation level of no-commit without journaling.
2) You need v1.5.1 or later of pecl ibm_db2 package to use the php.ini setting ibm_db2.i5_allow_commit==0               or db2_connect option "i5_commit"=>DB2_I5_TXN_NO_COMMIT.  I don't know if there is an OS version dependency, since these are long-standing connection capabilities in every various DB2/400 database connectivity tool I've ever used.

Assuming it is a version issue, is there any reason you can't just create a stored procedure on the AS/400 that sets the proper isolation level and handles the insert?

If that isn't a good solution for you, then please post your code, the OS version on the AS/400, and the pecl ibm_db2 version.

- Gary Patterson
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
> the i5 options only work for IBM i5 servers (not iseries)...

That doesn't make total sense to me. My understanding is that the practical meaning of "i5" relates to a series of POWER5 and POWER5+ processor-based systems, and has little bearing on currently supported software. That's noted in recent 'System Builder' handbooks from IBM.

AFAIK, practically all iSeries models are capable of at least V6R1 (IBM i 6.1) as well as all software products supported by that OS version/release.

The next release will probably cut support for some models, which is common.

Now, if the quoted statement means that those options are not supported for the OS release on your system, then that's understandable. As Gary noted, info about your OS release could be helpful.

Tom

Author

Commented:
If you review the source of the ibm_db2 pecl package you can see that all i5 options are close in a conditional requiring you to use PASE.
>  ...requiring you to use PASE.

I don't doubt that, but I'm curious why it's relevant. (I'm also not a PHP expert.) Why wouldn't you use PASE? PASE is an optionally installed component of the operating system, as Qshell is also an optionally installed component. It's not relevant to i5, iSeries, etc., as far as whether or not it can be used.

I would understand if the response is that use of PASE is restricted at your site due to security concerns or various business reasons. PASE provides a more or less native capability for running AIX binaries within native applications without needing AIX itself. It also includes support for a couple common and useful AIX shells and a lot of utilities.

Tom

Author

Commented:
Machine 1: Linux Apache PHP DB2 Connect
Machine 2: iSeries

It appears that the i5 options will only work if you are using DB2 Connect on an AS400 with PASE

PASE would need to be on Machine 1 in this case, but that is not our setup.
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
We've really got two different mostly-unrelated points that were are discussing here:

pecl i5 options only availalbe in PASE

I checked the pecl ibm_db2 code, and was surprised like you were to find that the i5 (AS/400) do appear to be restricted to the PASE environment.  I wonder why?

iSeries vs i5

As far as the other point (i5 vs iSeries), I think Tom is probably right - i5 and iSeries just designate two different generations of IBM midrange hardware.  To further confuse matters, iSeries and i5 hardware both run i5/OS operating system.  I think that the "i5" designation you keep seeing probably just refers to the OS, rather than the hardware that shares the same name.  If you look at the pecl ibm_db2.c (v1.80), you'll see, for example, the "i5_naming" option has a comment that refers to "iSeries" at line 1247:

http://cvs.php.net/viewvc.cgi/pecl/ibm_db2/ibm_db2.c?revision=1.80&view=markup

Anyway, thanks for following up and posting your findings.  Appreciated.

- Gary Patterson

1246     } else if (!STRCASECMP(opt_key, "i5_naming")) {
1246 	/* i5_naming - SQL_ATTR_DBC_SYS_NAMING
1247 	DB2_I5_NAMING_ON value turns on DB2 UDB CLI iSeries system naming mode. Files are qualified using the slash (/) delimiter. Unqualified files are resolved using the library list for the job..
1248 	DB2_I5_NAMING_OFF value turns off DB2 UDB CLI default naming mode, which is SQL naming. Files are qualified using the period (.) delimiter. Unqualified files are resolved using either the default library or the current user ID.
1249 	*/ 

Open in new window

>  Machine 1: Linux Apache PHP DB2 Connect

<Smacks his forehead!> Ah! I should've re-read the entire question and thread each time! I got side-tracked because I wasn't staying focused on the PHP and went too far in the wrong direction. It _finally_ soaks in that the PHP is elsewhere and _that's_ the root of why PASE/i5/iSeries/etc is useless even to discuss. Doh!

Now the deal is (1) how can DB2 Connect submit updates to files that aren't journaled, and (2) how can that be exposed in PHP (Linux)? Or is it even necessary to expose it in PHP? Once the remote database is defined in DB2 Connect, accesses through PHP shouldn't even need to know about that attribute.


Is DB2 Connect the Enterprise or Personal edition? Is DB2 Connect already in use by other clients to access your iSeres database?

Tom
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
Tom,

Cyrex56 said above that he just modified the pecl ibm_db2 driver to make it work, I take that to mean that he inserted a little code to change the isolation level to NC during or after connection.

Looks like this is really just a lack of functionality in the pecl ibm_db2 drver,and and not a DB2 Connect limitation.

- Gary Patterson
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
angelIII,

I think the author resolved his/her own issue.  

ID:24749785 Author:cyrex56 is the closest thing here to a correct answer - requires modification of the PECL driver to resolve.

- Gary Patterson
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.