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

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
cyrex56Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gary PattersonVP Technology / Senior Consultant 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
0
cyrex56Author Commented:
the i5 options only work for IBM i5 servers (not iseries) also, the documentation fails to mention that those options also only work from PASE (running PHP on an ibm server rather than linux).

I just went ahead and modified the pecl ibm_db2 driver to do what I wanted, but it seems there should be an easier way.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tliottaCommented:
> 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
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

cyrex56Author 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.
0
tliottaCommented:
>  ...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
0
cyrex56Author 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.
0
Gary PattersonVP Technology / Senior Consultant 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

0
tliottaCommented:
>  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
0
Gary PattersonVP Technology / Senior Consultant 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
0
Gary PattersonVP Technology / Senior Consultant 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.