Monthly Archives: December 2014

Common Virtual Conference – Notes from a PHP on IBM i Developer

Just a quick summary of things I found useful about the Common Virtual conference.

Modernizing Applications – Getting the next Gen Devs able to maintain and develop on the IBM i with the programming language you’re used to.

RPG IV is a modern language that you could have a “new generation developer” program in.  It’s similar to the structure of commonly used programming languages.  The con is they have to learn a new language and pay for the RDi IDE as RPG is proprietary so no other IDE supports it.  Its based on Eclipse which many people use but i prefer not to use as its very complex and not simple and intuitive as netbeans.  I did like all the hooks that they have into the IBM i, but you can also get those via IBM Navigator for i. The pro is that your current development staff and new developers can work on the same code base and somewhat “future proofs” your ability to maintain and update your programs.

To upgrade your non-RPG4 programs you can use tools such as Arcard, and Linoma RPG Toolbox.  These tools will convert the source to RPG4.  Also you can use Arcad Observer and Fresche xAnalysis to help with modernizing.

There’s also an alternative approach where you develop new projects in SQL and PHP.  This allows you to use many things that have been developed on LAMP (Linux Apache MySQL, PHP) onto your iADP (IBMi, Apache, DB2, PHP) stack.  Thanks to the open source community and PHP’s 20 years of history.  This is what I’ve been doing.

Creating SQL Stored procedures

  1. To denote a passed in Parameter you can use “P_” such as P_NAME
  2. You can break free of system naming (DSPMSG) and use full names (i.e. CREATE PROCEDURE DISPLAY_MESSAGE)
  3. IBM provides the following tables to find information about your stored procedures
    1. QSYS2/SYSROUTINE – List of Stored procedures
    2. QSYS2/SYSPARMS – List of parameters to the stored procedures
    3. QSYS2/SYSRTNDEP – Dependencies of a stored procedure
    4. QSYS2/SYSPROCS – A view built over SYSROUTINE
  4. You can return multiple result sets from 1 SQL stored procedure.  I find this interesting as a PHP dev because I can make 1 call to the database and have all the information needed to be displayed on a webpage.  This is different from how many PHP frameworks handle retrieving data from the database layer, but I think it may help in increasing a PHP apps performance and simplicity.  My blog post about how to access multiple result sets in PHP.
  5. Use Program Type Sub to create a srvpgm (service program) that should increase performance
  6. Arrays are supported but I’m unsure if you can pass a PHP array to a db2 stored procedure.  I’m guessing the PECL ibm_db2 driver may need to be updated to support this.

I only talked about what i found relevant to me you should see the full presentation slides and notes at:

PHP can handle multiple result sets from a DB2 SQL Stored Procedures on IBM i

Did you know that a DB2 SQL stored procedure can pull back multiple result sets?  Its as simple as adding this to your Create Procedure statement:  DYNAMIC RESULT SETS 2.  You can use any integer value but I used 2.

To access the additional result sets in PHP you can use either odbc_next_result or db2_next_result.  The main advantage of pulling back multiple result sets is that you only have to issue 1 call to a stored procedure to pull back all the information that you may need.