ZF2 support for DB2 on the ibmi

General discussion on Zend Server for IBM System i
Post Reply
rodneyo
Posts: 12
Joined: Sat Oct 22, 2011 12:21 am

ZF2 support for DB2 on the ibmi

Post by rodneyo » Mon Jan 27, 2014 11:00 pm

I'm having trouble determining some db configuration parameters needed in my zf2 global.php to use the native db2i driver on the as400. Here is what I have so far. Can anyone confirm or deny these settings are correct

$DbParams = array(
'db' => '*LOCAL',
'username' => 'XXX',
'password' => 'XXX',
'driver' => 'IbmDb2',
'hostname' => MYHOSTNAME',
'os' => 'i5',
'adapter' => 'db2',
'driver_options' => array(
'i5_naming' => 'DB2_I5_NAMING_OFF',
'i5_lib' => MYLIB',
'auto_commit' => 'DB2_AUTOCOMMIT_OFF',
'buffer_results' => true,
),
);

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

Re: ZF2 support for DB2 on the ibmi

Post by aseiden » Tue Jan 28, 2014 6:56 am

Hi, Rodney,

Mostly correct with these exceptions:
  • 'username' and 'password' should be in the local, not global, config
  • Remove 'hostname', 'os', 'adapter', 'buffer_results'
  • Change 'auto_commit' to 'autocommit' (do you really intend to turn it off, though? using commitment control?)
--Alan Seiden

rodneyo
Posts: 12
Joined: Sat Oct 22, 2011 12:21 am

Re: ZF2 support for DB2 on the ibmi

Post by rodneyo » Tue Jan 28, 2014 4:03 pm

Thanks Alan,

One more question. Have you ever worked with the ZF2 paginator on the ibmi? The ZF2 ibmdb2 driver does not support the LIMIT and OFFSET functions and I keep getting this error whenever I use
Paginator\DbSelect
Token LIMIT was not valid. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE. SQLCODE=-104

to be fair, I realize that LIMIT and OFFSET are not ansi SQL but is there another way besides the using rowcount with over()?

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

Re: ZF2 support for DB2 on the ibmi

Post by aseiden » Mon Feb 03, 2014 5:38 pm

Rodney, ZF2 doesn't have a limit() emulator for DB2 at this time. It may in the future, as ZF1 had.

The workaround is to use your own SQL containing the row_number(), over(), and row_number() functions, such as:

Code: Select all

SELECT code, name, address
FROM ( 
  SELECT row_number() over ( ORDER BY code ) AS rid, code, name, address
  FROM contacts
  WHERE name LIKE '%Bob%' 
  ) AS t
WHERE t.rid BETWEEN 20 AND 25;
-- Alan Seiden

Post Reply