Moving from I5_tools to DB2 functions

General discussion on Zend Server for IBM System i
Post Reply
sten55
Posts: 4
Joined: Wed Nov 28, 2012 4:47 pm

Moving from I5_tools to DB2 functions

Post by sten55 » Wed Dec 10, 2014 9:00 am

A couple of years ago I implemented a PHP site for a customer with Zend Core and i5_tools

I have recently installed Zed Server 6 for that customer and are trying to figure out best practise to load data.

Since i5_tools no longer is in the package I prefer to use the DB2 functions, but then some questions arise.

1 how to set up the connection so it always givs me a result set in utf-8, 1208.
2 how to get the sorting according to language specifics
3 is it possible to set the decimal point as comma, I have a rutin that exports my data to excel simply by header directives and in sweden we often use comma, not period

1 Setting up the site so it always use UTF-8 is crusial especially if you use ajax with jquery to improve perfomance. Mixing UTF8 with others is a mess that causes a lot of extra work and makes everything more complex to handle. I take care of setting the code page for the scripts I write and makes shure they all are in UTF-8 or code page 1208 to minimize language problems with letters as åäö. I have tried to use header settings and also tries to convert all data with utf8_encode, but the results are unpredictable, so the best solution is to get evreything with UTF-8.

The options to connect to db2 do not include settings for UTF-8 what I can see in the manuals and in another question in this forum some has pointed to a db2 driver, but that must be for those who intend to use it not from the i-series machine. That's not an option for as I see it.

I have designed an abstract class to handle all my sql queries and in the constructor below code is fetched

$opt = array('i5_lib' => 'BISTEST');
$this->con = db2_pconnect("*LOCAL", "", "", $opt);

2 and 3

Parameter settings in I-series for DB2

Decimal point . . . . . . . . *COMMA *PERIOD, *COMMA, *JOB, *SYSVAL
Sort sequence . . . . . . . . *LANGIDSHR Name, *JOB, *JOBRUN, *LANGIDUNQ, *LANGIDSHR, *HEX
Language identifier . . . . . SVE Name, *JOB, *JOBRUN

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: Moving from I5_tools to DB2 functions

Post by mark397 » Thu Dec 11, 2014 5:24 pm

The i5 tools have been replaced by a new i5 toolkit. You can also reuse your old program calls with the compatibility wrapper. The link below explains them both (you'll need an iprodeveloper account, free sign up):
http://iprodeveloper.com/php/meet-zends-new-php-toolkit

You'll need to loop over data and convert it to UTF-8 after your fetch command. Assuming your data is in CCSID 37 (ISO-8859-1) you can do this:

Code: Select all

foreach ($row as $key => &$value) {
    $value = mb_convert_encoding($value, 'UTF-8', 'ISO-8859-1');
}

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: Moving from I5_tools to DB2 functions

Post by aseiden » Fri Dec 12, 2014 5:43 am

I believe the poster is referring to DB2 SQL access via db2_connect, etc. Anyone in Europe care to reply to his questions?

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: Moving from I5_tools to DB2 functions

Post by mark397 » Fri Dec 12, 2014 9:24 pm

I apologize for not being as specific. The 2nd portion of my answer (foreach loop) applies to DB2. There are 2 solutions actually, using CAST() on every character field in the SQL statement or the answer I posted with the loop. They effectively do the same thing (convert a charset to UTF-8), just one is done in the SQL call and the other is done in the PHP. I find the PHP loop to be easier to implement because a loop is possible. ISO-8859-1 is English, and I'm not sure if it's all English languages or just US. You can adapt the charset in my example to your required charset, or omit the "from" encoding type and the PHP will use a best-guess attempt to convert it. I find it best to be explicit when possible, but if you don't know the "from" encoding or need to support multiple this should work.

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: Moving from I5_tools to DB2 functions

Post by aseiden » Mon Dec 15, 2014 5:17 pm

The decimal separator option (part of the options array, fourth parameter of db2_connect) is documented at http://php.net/manual/en/function.db2-connect.php

i5_decimal_sep

DB2_I5_SEP_PERIOD - A period ( . ) is used as the decimal separator. This is the default.
DB2_I5_SEP_COMMA - A comma ( , ) is used as the decimal separator.
DB2_I5_SEP_JOB - The job default is used.

--Alan Seiden

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: Moving from I5_tools to DB2 functions

Post by aseiden » Mon Dec 15, 2014 5:26 pm

To sort according to the job's attributes:

set it globally via /usr/local/zendsvr6/etc/conf.d/ibm_db2.ini
ibm_db2.i5_job_sort = 1; 1=sort by JOB attributes

If you're using Zend Server 7.0 or higher, you have the new ibm_db2 version, and can set it at connect time (http://174.79.32.155/wiki/index.php/XML ... 2ChangeLog)

$opt = array('i5_job_sort'=>DB2_I5_JOB_SORT_ON);

--Alan

User avatar
rodflohr
Zend Global Support
Posts: 56
Joined: Mon Dec 29, 2008 5:28 pm

Re: Moving from I5_tools to DB2 functions

Post by rodflohr » Tue Dec 16, 2014 7:54 pm

Some general tips on settings that can help prevent problems with character translations in DB2:

https://support.zend.com/hc/en-us/articles/203748206

sten55
Posts: 4
Joined: Wed Nov 28, 2012 4:47 pm

Re: Moving from I5_tools to DB2 functions

Post by sten55 » Fri Dec 19, 2014 10:24 am

Thank you all for quick answers. I think I can manage now for the moment. I'll check all the possibities and see which aproach I will use and sum it up so others can have same privligae as I did.
Thanks again

/Sten

Post Reply