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.

11 replies
  1. Alan Seiden
    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

    Reply
    • Peter
      Peter says:

      Hi Alan!

      It works!!! :-)

      Thank you so much!

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

      Regards Peter

      Reply
  2. Peter
    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

    Reply
  3. Peter
    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

    Reply
      • Peter
        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

        Reply
  4. Steph
    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?

    Reply
    • Alan Seiden
      Alan Seiden says:

      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

      Reply
  5. Steph
    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

    Reply
    • Alan Seiden
      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

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.