Ibuildings - Blog - ibuildings.nl

Blog

Ibuildings, blog

Blog

Harrie's blog

ing. Harrie Verveer ing. Harrie Verveer
Software Engineer
 Zend PHP5 Certified Engineer MySQL Certified Engineer
Harrie is a software engineer at our Vlissingen office. Besides working from the Vlissingen office he occasionally does on-site projects for our customers. In his spare time he is into photography and guitar playing.

Friday, 8 June 2007

PHP for IBM i5: how to control your library list from PHP

When building applications on an IBM i-series platform you probably want to use the DB2 database. Zend Core for IBM i-series supports this database since the db2_ functions were introduced. These functions allow you to use the DB2 database like you would any other database, but lacks the support for manipulating librarylists. It turns out this is possible by using the i5_ functions. Below an example of how to do so.

Unlike (for example) MySql, DB2 keeps its tables in libraries. A database contains several libraries and each library can contain multiple tables (and a table can only be in one library). A query like "SELECT library.tablename.fieldname FROM library.tablename" will run just fine on a DB2 database, but most of the time you don't want that. Libraries often represent several environments, like dev-libraries for the development environment, acc-libraries for the staging environment and prd-libraries for the production environment. You don't want these libraries to be hardcoded in your queries, because this would force you to change every single query that is in your application when you move it from one environment to another.

On the i-series there is a common solution for this problem called a librarylist. A librarylist is a list that tells the system which libraries to use and in what order they should be searched. When I have a librarylist like: libA, libB, libC and I run a query like: "SELECT * FROM mytable" the system will search the 3 libraries to find the right table. This allows us to setup a librarylist for every environment, so switching to a different environment would mean we only need to change the librarylist instead of the entire application.

At first, it seems impossible to specify this librarylist for the userprofile you use to connect to the database, as it will always use the librarylist that is configured for the user 'NOBODY' (apache). Besides that you can't specify your librarylist when connecting using db2_connect. Of course you can set the default library using the i5_lib parameter, but you can't set 2 libraries (that's why this parameter is called 'default library' I guess... it makes sense that you can't have 2 defaults).

There didn't seem to be any solution for this problem, until Sam Pinkhasov from Zend visited us. He told us about the i5_query() function, a function that wasn't documented anywhere (php.net/Zend Studio etc.) but was an existing function that could be used just like you would use mysql_query() or db2_query(). Suddenly the solution for our problem became pretty obvious. Below a working example of 2 queries that do a select statement from 2 different libraries without naming the library in the query itself:

PHP:

<?php

  
  // Username and password for the i5-user
  $conn = i5_connect($hostname,$username,$password);
  add_to_librarylist(array("LIBRARY1","LIBRARY2","LIBRARY3"),$conn);
  
  function add_to_librarylist($libraries,&$conn)
  {
    $curlibl = "";
    
    // get current librarylist
    if (!i5_command("rtvjoba",array(),array("usrlibl"=>"curlibl"),$conn))
      die("Could not retrieve current librarylist:" . i5_errormsg($conn));
    
    // add our libraries to the librarylist
    $curlibl .= " " . implode(" ",$libraries);
    if(!i5_command("chglibl",array("libl"=>$curlibl),array(),$conn))
      die("Could not change current librarylist:" . i5_errormsg($conn));
    
    // not necessary but is here to see if we correctly added the libraries
    i5_command("rtvjoba",array(),array("usrlibl"=>"curlibl"),$conn);
    echo $curlibl;
  }
  
  // Select from a table in library1
  $result = i5_query("SELECT * FROM MYTABLE");
  while($row = i5_fetch_assoc($result))
    print_r($row);
    
  echo("<hr />");
  
  // Select from a table in library3
  $result = i5_query("SELECT * FROM ANOTHERTABLE");
  while($row = i5_fetch_assoc($result))
    print_r($row);

?>



This solved the entire problem, allowing us to add libraries to the current librarylist and do queries after that. It took me some time to figure this out so I hope I can help some people by sharing it.
Geplaatst door Harrie Verveer in PHP op 09:29 | Reacties (5) | Trackback (1)
Tags voor deze bijdrage: , , , ,
Share

Trackbacks
Trackback-URI voor dit artikel

PHP on IBM's System-i
Some of the developers at Ibuildings are working with PHP on IBM's System-i platform. If that name doesn't ring a bell, it has been called 'iSeries' for a while and it's basically a new name for the old AS/400. If you're old enough to remember Happy Days
Weblog: ATK rules...
Gevolgd: Jun 08, 19:36

Reacties
Geeft reacties weer als (Lineair | Samengevoegd)

Thank you for the information.
I Tried it on our i5.
I have just one question. It seems, that the added libs are at the bottom of the liblist.
It their any way to put the added libs to the top?
Thanx
Bernd
#1 Bernd Dworrak op 12-07-2007 12:41 (Beantwoorden)
Hi Bernd, thanks for your reply. The command that is executed is:

CODE:
i5_command("chglibl",array("libl"=>$curlibl),array(),$conn)


This works the same way as the 'chglibl' command on the i5 green screen. The parameter I pass ($curlibl) is a string containing "existinglib1 existinglib2 newlib1 newlib2", but you could just as easy change the order of that in anything you like. If you want to swap them (first the new libraries and than the old ones) you whould have to alter the line:

CODE:
$curlibl .= " " . implode(" ",$libraries);


into:

CODE:
$curlibl = implode(" ",$libraries) . " " . $curlibl;


I haven't tried this but I suppose this should work just as well. If you are planning to try this could you let me know if it worked?
#1.1 Harrie Verveer op 12-07-2007 13:58 (Beantwoorden)
Harrie, I hope this response works ok. I have tried your code and it works... Thank you very much for posting this. I tried with a I5 connection without db name, user name, password, and it didn't work. So I tried with connection parameters and it did! This will be extremely helpful for us, I am sure. Again, many thanks.

As a note to others, during my testing, I realized that this is allowing me to add libraries to those that are already in the system value QUSRLIBL. If those libraries are already in the user library list, then this CHGLIBL command will fail.

Chuck
#1.1.1 Chuck Bower (Homepage) op 31-07-2007 04:28 (Beantwoorden)
Very useful post. Thanks.

In Java, if the JDBC connection uses "naming=system" then the Library List of the user the connection signed on with will be used. This worked well because the code didn't know about library lists. If the DB connection is made with TESTuser, then the test library list associated with that user profile is used. Getting the DB connection using PRODuser gets the production library list associated with PRODuser's profile.

To change the library list, use the command CHGJOBD for the job description associated with the user profile.

Will that scheme work in PHP? (I'm just starting to code with PHP. I'm coding at home using MySQL)
#2 Greg (Homepage) op 12-08-2007 01:17 (Beantwoorden)
Hi Greg, interesting question. Just like you are new to PHP, I'm new to IBM i5 so I hope I get your question right.

You can pass the option 'I5_OPTIONS_SQLNAMING' to the i5_connect function to specify what naming to use, like this:

CODE:
$conn_resource = i5_connect('127.0.0.1',$user,$password,array(I5_OPTIONS_SQLNAMING=>"SQL"));

(use SQL naming)

or

CODE:
$conn_resource = i5_connect('127.0.0.1',$user,$password,array(I5_OPTIONS_SQLNAMING=>"SYS"));

(use SYSTEM naming)

Also, I believe you can assign certain librarylists to different users on the IBM i5. I think it should work fine if you create a user called 'test' with a certain librarylist and use this username in your i5_connect() command. The librarylist should be the same as the librarylist specified for that user.

You could try this very easy by creating or modifying a user and define a librarylist for that user. Than in PHP connect using that user and do this to check if it works:

CODE:
i5_command("rtvjoba",array(),array("usrlibl"=>"curlibl"));
echo ("Current librarylist: " . $curlibl);


It should display the librarylist you have defined for that user. If that is the case, the queries you execute using i5_query() use the same librarylist.

This off course is the ultimate reason why librarylists exist: if you move your application to 'production' the only thing that you have to do is changing the username that you use, and automatically the correct libraries will be used. In addition, you could do something like:

CODE:
if (strstr($_SERVER['HTTP_HOST'],"staging"))
  $username = "testuser";
else
  $username = "productionuser";

i5_connect("127.0.0.1",$username ....


so when your domainname has the word 'staging' in it (like staging.mydomain.com) your application will use the 'testuser' username to connect, and when it doesn't (www.mydomain.com) it will use the 'productionuser' to connect. This way you don't need to change code at all when moving your application from staging to production or back - but your application will auto-detect what username to use (and so the librarylist will be correct as well).

I hope this answered your question.
#2.1 Harrie Verveer (Homepage) op 12-08-2007 17:47 (Beantwoorden)

Reactie toevoegen

BBCode opmaak toegestaan
Met sterretjes wordt tekst *vet* gemaakt. Met underscores kan een _woord_ onderstreept worden.
Standaard emoticons zoals :-) en ;-) worden geconverteerd naar plaatjes.
Email adressen worden niet getoond en zijn alleen zichtbaar voor de blogger.

Om het posten door robots tegen te gaan, gelieve de letters die je in het plaatje ziet over te typen. Je commentaar wordt enkel gepost wanneer de letters overeen komen. Je browser dient cookies te ondersteunen (standaard staat dit aan), of je commentaar kan niet geverifieerd worden.
CAPTCHA
 
Ingestuurde reacties zullen eerst worden gecontroleerd voor deze geplaatst worden.
 

Benelux (wijzig / change)