Zend Framework DB2 adapter for IBM i
I recently received a question about how to use DB2 with Zend Framework on IBM i. Thomas wrote:
I would like to start with Zend Framework on i5 [IBM i] with Zend Server and ZF’s DB2 database adapter. I got this error message:
Qualified object name SYSCOLUMNS not valid. SQLCODE=-5016
I told Thomas about an improved DB2 adapter, optimized for IBM i, that I’d created in cooperation with Zend. Its usage is explained in my presentation, “From Zero to ZF.”
Thomas wrote back to say this adapter worked perfectly for him, eliminating the error and improving performance. I believe this DB2 adapter (or something similar) will eventually be included with Zend Framework 2.0, but until then, it works well as a custom adapter.
Peter,
Does the full error message look like this?
“Column qualifier or table PIZZAS undefined. SQLCODE=-5001”
You probably added
protected $_SCHEMA = ‘PELE’;
in your Pizzas.php table class.
The $_SCHEMA (‘PELE’ in your case) causes the problem. The schema, when specified, is used by SQL in the FROM table reference, in your case “PELE”.”PIZZAS”. In the SELECT, however, “PIZZAS” is used without a schema, and is therefore undefined.
Instead of specifying a schema, I suggest using library lists and system naming, which is more natural for IBM i anyway.
Try these two steps:
1. Comment out your $_SCHEMA line from Pizzas.php.
2. Add these two lines to application.ini:
resources.db.params.driver_options.i5_naming = DB2_I5_NAMING_ON
resources.db.params.driver_options.i5_libl = “PELE”
Then let us know how it goes.
Alan
Hi Alan!
It works!!! :-)
Thank you so much!
Now I can start producing some useful code, hopefully. ;-)
Regards Peter
Sure! Thanks for the quick reply!
The application.ini looks like this:
[production]
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1
includePaths.library = APPLICATION_PATH “/../library”
bootstrap.path = APPLICATION_PATH “/Bootstrap.php”
bootstrap.class = “Bootstrap”
appnamespace = “Application”
resources.frontController.controllerDirectory = APPLICATION_PATH “/controllers”
resources.frontController.params.displayExceptions = 1
resources.layout.layoutPath = APPLICATION_PATH “/layouts/scripts/”
resources.db.adapter = “Db2”
resources.db.os = “i5”
resources.db.params.adapterNamespace = Strategic_Db_Adapter
resources.db.params.dbname = “SYSTEM”
resources.db.params.username = “USERNAME”
resources.db.params.password = “PASSWORD”
resources.db.isDefaultTableAdapter = true
;resources.db.params.driver_options.i5_naming = DB2_I5_NAMING_ON
;resources.db.params.options.autoQuoteIdentifiers = false
The code :
public function indexAction()
{
// action body
$mapper = new Application_Model_PizzasMapper();
$this->view->pizzaList = $mapper->fetchAll()->toArray();
}
We do not sell pizzas, this is just a test. ;-)
So when I debug this, the statement looks like this:
SELECT “PIZZAS”.* FROM “PELE”.”PIZZAS”
db2_prepare has the error: Qualifier PIZZAS not found
The statement SELECT “PIZZAS”.* FROM “PELE”.”PIZZAS” “PIZZAS”
would work, but I do not know what I can do that it sets the qualifier.
In greenscreen sql it works without the qualifier.
Regards Peter
Peter, if you would like, post your application.ini and the code you are running. (Remove any passwords, of course).
Hi Alan!
Am trying to get things working, but I have a problem with Zend_Db_Table.
The fetchAll() function prepares the statement as
SELECT “TABLE”.* FROM “SCHEMA”.”TABLE”
and db2_prepare cannot handle this.
I have tried this without Zend_Db_Table also and it doesn´t work.
SELECT “P”.* FROM “SCHEMA”.”TABLE” “P” would work, but this is not how Zend_Db_Table does it.
Am running on V7R1 and ZendServer 5.6
Do you have any ideas?
Regards
Peter
Hi, Peter,
Did you install my custom DB2 adapter as described in the “Zero to ZF” slides?
Alan
Hi Alan!
Yes I did, but the result is the same.
It is interesting that OperationsNavigator can handle this sql-statement, so the machine understands it, but db2_prepare() has the errors.
Regards Peter
Excellent, thanks Alan. Do I have any other performance considerations?
I am now using Zend_db_table etc. for my queries. The code is so clean! But would these have the same performance as regular old db2_prepare and db2_execute?
Steph, regarding performance: if you use Zend_Db_Table, make sure you cache its metadata lookups. Other than that, enjoy Zend_Db. There is some small overhead from using framework components rather than plain db2_ functions, but you gain the ability to use the Zend_Db query profiler, so I’d say it’s a net benefit.
For web and PHP performance tips, check out my webcast this Thursday (3/29/12): “Web performance first aid”
You can also see the slides from my presentation “Performance Tuning with Zend Framework”
Alan
Hi Alan,
I really appreciate the information on this blog. I’ve just created my first app with Zend Framework on the IBM i. One question I have: Am I able to use db2_pconnect to get the speed advantages if I’m using Zend Framework to create the connection?
Thanks!
Steph
Hi, Steph,
Yes, building on the instructions in my “Zero to ZF” presentation, you can add the following directive to application.ini:
resources.db.params.persistent = true
Be sure to follow the rules for DB2 persistence (options must be consistent for a given db/user/password combination). I explain those further in “DB2 and PHP Best Practices on IBM i”.
Alan