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,” on my Presentations page: http://www.alanseiden.com/presentations/.

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.

This entry was posted in db2, IBM i, iSeries, Performance, PHP, System i, Zend Framework and tagged . Bookmark the permalink.

11 Responses to Zend Framework DB2 adapter for IBM i

  1. Steph says:

    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

    • Alan Seiden says:

      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

  2. Steph says:

    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?

  3. Peter says:

    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

    • Alan Seiden says:

      Hi, Peter,

      Did you install my custom DB2 adapter as described in the “Zero to ZF” slides?

      Alan

      • Peter says:

        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

  4. Alan Seiden says:

    Peter, if you would like, post your application.ini and the code you are running. (Remove any passwords, of course).

  5. Peter says:

    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

  6. Alan Seiden says:

    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

    • Peter says:

      Hi Alan!

      It works!!! :-)

      Thank you so much!

      Now I can start producing some useful code, hopefully. ;-)

      Regards Peter

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>