Solved

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

Posted on 2009-06-30
12
1,506 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
0
Comment
Question by:cyrex56
  • 4
  • 3
  • 3
12 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 24749583
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
 

Accepted Solution

by:
cyrex56 earned 0 total points
ID: 24749785
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
 
LVL 27

Expert Comment

by:tliotta
ID: 24757579
> 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
 

Author Comment

by:cyrex56
ID: 24757720
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
 
LVL 27

Expert Comment

by:tliotta
ID: 24757958
>  ...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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:cyrex56
ID: 24758323
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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 24759202
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
 
LVL 27

Expert Comment

by:tliotta
ID: 24759407
>  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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 24759763
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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 24937302
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now