Nov 22

Walking through a DB2 PF data and allow update.

I was thinking about some of the tools I would like to see on the IBMi and written in PHP that allow you to carry out the same functionality as the PHPMyAdmin tool on MySQL. Not that I wanted to write the entire product but at times I would like the ability to just review and/or update the data in a particular file. Sometime ago we started to publish sample programs in PHP that show the very effective capabilities of running PHP against IBMi data an objects, one such project was being able to display file data and the file column names in a table see this post. So we decided to take that one step further and add the ability to output the data to a web interface and then allow you to update that data or move to the next record.

The final solution is very simple in terms of code required to generate the interface (we did not make it pretty just functional) but it is a good stepping stone to take it to the next stage. This first iteration simply retrieves the file data and displays the column heading along-side the actual data in the field. We have provided a button to move to the next entry but not added the move back or update buttons although these will be very simple to add. The process works by having a sign in screen (index.php) fronting the process, if a request is made that has no valid credentials a sign on screen is displayed. If Valid credentials are supplied it will automatically pass in a file name and start point to the called page which in-turn calls the function shown below.

/*
* function Dsp_Pfm()
* display the contents of a file
* @parms
* File
* File Library
* Starting record
* returns the number of errors.
*/

function Dsp_Pfm_2($conn,$file,$flib,$id) {

// query to get the data frm the file
$query = "SELECT rrn(a) as RRN, a.* FROM " .rtrim($flib) ."/" .rtrim($file) ." a WHERE RRN(a) > '" .$id ."' FETCH FIRST ROW ONLY";
$result = i5_query($query,$conn);
if(!$result){
echo("Failed to get the data<br>" .$query);
$_SESSION['ErrMsg'] = "Error code: " .i5_errno($result) ." Error message: " .i5_errormsg($result);
}
$rec = i5_fetch_assoc($result);
echo("<table>");
// the assoc array contains the field names so we can use those as the headers
$i = 0;
foreach($rec as $key => $value) {
echo("<tr><td>" .$key ."</td><td><input type=text name=data" .$i ." id=data" .$i ." value='" .$value ."' /></td></tr>");
$i++;
}
echo("</table>");
// button to get the next record
$id++;
echo("<input type=button value=NEXT OnClick=location='dsp_pfm.php?file=" .$file ."&flib=" .$flib ."&id=" .$id ."' />");
i5_free_query($result);
return;
}

That code will produce something similar to the following.

Sample output of edit panel

Output from the function

As I have said we have not added the ability to edit and update the data in the file, but that would be a fairly simple option to add.

I would encourage those developers out there who have not experimented with PHP yet to start! The power provided by the ability to call SQL and provide interfaces which are not limited by 10 character column names and 80 column displays has to be something you want to use.. Our HA4i High Availability product is using some of the technology we have demonstrated in the various Blog posts to it best advantage.

Happy PHP’ing..

Chris…

Aug 22

PHP i5_Toolkit from Aura Equipments and the QSYSOPR messages

One of the questions we are being asked regularly is why am I getting messages in the QSYSOPR message queue when using the i5_toolkit from Aura. The answer is very simple, before Zend upgraded to their latest version (5.6) they had an agreement with Aura equipments for the use and support of the i5_toolkit. Since 5.6 Zend have stopped the agreement with Zend and no longer ship the i5_toolkit from Aura but are pushing an open source i5_toolkit called XMLSERVICE. They also provide a set of wrappers within Zend Server which allow the original i5_toolkit() functions to be left in your code and they are automatically mapped to the XMLSERVICE functions. There are some deficiencies in the XMLSERVICE so not all of the i5_toolkit() functions are supported yet. We have already made our position clear on the performance of the XMLSERVICE versus the Aura i5_toolkit in may previous posts.

Aura have agreed to continue to support their i5_toolkit for customers who installed it as part of the Zend Server installs prior to version 5.6. This is done via the forums at http://forums.iampserver.com, you can post any questions or problems you have with your version on the forums and the technical support team at Aura will respond as soon as they can. If you have migrated to version 5.6 you need to consider retaining an Aura support contract, Zend do not support the i5_toolkit anymore and no longer ship the product as part of their 5.6 install. The cost of support is based on the IBM CPU tier of your system and in our opinion is more than fairly priced.

Why should you buy the Aura support, after all today you are getting it for free. Well there are a number of reasons.

First of all you will be entitled to the new features which are being added to the i5_toolkit, this is over and above the existing functionality which is not available in the XMLSERVICE toolkit. The product is going to be part of your business applications, so having skilled technical staff who can fix problems is something you won’t get with an open source offering. I am not against open source but when you consider the limited install base this is going to be running on and the developer network who are going to be available to maintain and improve it, it is not going to be the same as other open source product we all use and love. We have already mentioned that the i5_toolkit is better and more efficient than the XMLSERVICE, I know they have been working on improving the performance and a recent announcement did say it has improved significantly. We have not tested the latest version so cannot be sure if it is as good as the i5_toolkit from Aura, but the gap was so large we are not sure if its even worth it? Another big benefit from our standpoint is the ability to run the EASYCOM server on one IBMi and the PHP code on another IBMi or platform. This gives the user lots of benefits and may provide you with even more performance benefits over running the HTTP services on the IBMi. The latest version of the i5_toolkit from Aura is packed with additional features which make integration with IBMi objects and data much more effective, without a support contract these are not going to be available to you. At the moment Aura are also offering support for their iAMP server included with the i5_toolkit support, this means you can have full support for the entire PHP stack from one vendor and at a price that is hard to beat.

So the answer is clear, taking a support contract with Aura is going to give you access to a superior product and at a price you can afford. No up front license costs, just an annual maintenance fee. Why would you not protect your business if it is this affordable?

Contact us directly to discuss your options, you will be surprised just how affordable this is. Plus no more annoying messages in the QSYSOPR message queue.

Chris…

Jun 15

Adding CGI capabilities to iAMP

We have been working with a client who wanted to be able to install a new website on their IBMi. The new website was developed on another OS using cake PHP by a development company that had no idea about how to set up a web server on an IBMi so we were asked to help them implement the new site.

The companies old site did in fact run on the IBMi but was not written in PHP and was mainly composed of static pages with some parts that needed CGI access to run. The initial request stated that these CGI generated pages were no longer needed so we did not have to worry about setting them up. They had looked at Zend Server in the past and rejected using it due to its complexity so we had the opportunity to start fresh and just install the iAMP server.

At first we set up test sites just to make sure the server ran and we had no problems setting up VirtualHosting. We did this on a new IP address so it did not impact the current site which was being run from the same saver. We could have used the same IP address and a different port but that is just plain ugly! We also needed a MySQL instance because the content management used the MySQL database to store the page data, an admin feature allowed the content to be changed via an editor which updated in the MySQL database when it was finished.

Everything went OK and we managed to import the MySQL database which contained all of the web page content so far with a few issues which we soon worked around. On starting the web server everything looked OK but we found the links in the navigation objects were all screwed up, a quick fix from the developers soon fixed that and the site appeared to be running just fine.

At this point the client said OK lets go live! They wanted the new site up and running in the wild straight away. Although this was quite a leap of faith all we had to do at this point was change the new configurations to the run on the original servers IP address, stop the existing server and bring the new iAMP server online. This went through without any hitches and the new site was up and running in the wild.

We thought everything was OK but when we tried to use the Admin functions (which are important to the client because it allowed them to control the content of their webpages through a user friendly interface) we hit our first problem. In the code they had a function which uses sockets to talk back to the HTTP server, this request hung every time it was called. The messages in the logs did not help much in finding out exactly what the problem was so it was Google to the rescue. We found a post to a forum about a different problem but the description of the resolution gave us the idea of where to look. The problem was the socket tried to connect to the external interface of the HTTP server, the firewall (like all good firewalls) stopped the connection because it determined the request was coming from the LAN to the WAN and back to the same LAN. We did not want to change the firewall rules to allow this connectivity so we had to come up with another option. The solution was pretty simple, we added a host table entry to the IBMi that pointed all requests for the website address to the internal IP address, this meant any request from the HTTP server to the company website would go to the internal IP address of the IBMi not the external one defined in the DNS. After this the admin function worked and we move on.

The next challenge came when we found out that despite being told otherwise, one of the functions in the website required a call to a CGI script. We thought we could just configure the iAMP server to run the request by configuring the CGI module etc. That did not work. The output said it was a permissions error which lead us down a long and fruitless path of looking at the permission on each of the objects etc. but as we discovered the problem was because PASE programs cannot call a Native IBMi program.

This meant we needed a copy of the IBM HTTP server running which we could use to serve the CGI generated content. Setting up the IBM HTTP server was pretty simple as we had just removed the old site from the configs and another internal site was still running on it. We just added a new configuration and ran it against a separate port. The version of the iAMP Server we had installed did not have proxy support so we had to come up with an alternative while Aura could create and test the additional modules required for proxy support. The solution we eventually came up with had one draw back in that we had to open the port the new website was using in the firewall, this allowed the request to be routed from the iAMP server back to the correct port on the IBM HTTP server but we did not like having to open more ports in the firewall. Once we had everything configured the CGI processes all worked and the client was again happy with their solution.

When we received the new code from Aura for iAMP with Proxy support we upgraded the server and set up the link to the IBMi server using the ProxyPass and ProxyReverse statements in just the same way you had to configure ZendCore. We also added the new modules into the base config and after some false starts we had the site back up and running with the proxy support and we could remove the additional open port from the firewall.

If you need to have an AMP stack on the IBMi you now have the option of using the iAMP Server or Zend Server, iAMP is a free download from the Aura Equipments website. Support for iAMP is provided free of charge via the Aura forums but you can also request full support for the product from Aura for a very low fee. As an incentive Aura are also offering to inlcude the support costs for iAMP with Easycom so you will have one support structure or all your AMP stack plus the Easycom i5_toolkit for one very low fee. The same setup for Zend requires you to enter into a support contract with Zend for their Server, a support contract with another company for ZendDBi (MySQL) plus a non supported open source solution for the i5_toolkit server. I know which one I think is the better option.

If you have any questions about running iAMP or would like to know more about Easycom for PHP i5_Toolkit gives us a call, you may surprised just how cost effective our solution for running AMP on the IBMi is. Even if you are running an AMP stack on Linux or Windows, Easycom will allow the same i5_toolkit functions to run on those stacks in exactly the same way they do on the IBMi, don’t just integrate, innovate…

Chris…

May 30

PHP ZipCode to TimeZone

I have been building a database of customers to call and found one slight issue, I needed to be able to workout which timezone each of the contacts are in so I only call when its suitable for them!

I started off by using one of the online ZipCode to TimeZone converters and manually adding the data to the existing database, but soon ran into difficulties because the websites limit the number of requests that could be run (Its cookie based so while I could probably have worked round it I needed a better solution). I also looked at using the telephone area code in the database and the various online converters which provide the TimeZone based on it, again after a number of requests the pages stopped working.

Having to cut and paste the Zipcode between the pages was also very painful, I wanted something I could just program and run. There are a number of API’s out there that will allow you to run requests against web pages and receive XML data back. These tended to return data which needed more massaging and required conversion of XML structures to allow the database to be updated.

I wanted to have a database of ZipCodes which had the Timezones included, I found one at the following link. I did find others but this one appeared to have been updated more recently. I started by importing the sql into a new MySQL database called ziptotz. On reviewing the data I found that the TimeZone was in a format such as ‘America/Alaska’. I needed to take this data and work out the time offset.

My solution was to simply add a new field to the end of each row which would hold the offset from GMT, I could have made it simpler by determining the offset of my current timezone from GMT and subtracting it from the result but the data would then be location specific so I decided offset from GMT would work just fine.

This is the script I used to add the new values.


// connect to the server
$con = mysql_connect("my_host","my_user","my_pwd");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
// select the database
mysql_select_db("ziptotz",$con);
// list the timezones supported by PHP for the US only
$timeZones = DateTimeZone::listIdentifiers(DateTimeZone::PER_COUNTRY, 'US');
// loop through the returned array
foreach ($timeZones as $key => $zoneName ) {
// create a timezone object
$tz = new DateTimeZone($zoneName);
// create a new datetime object using the timezone object time is current
$dateTime = new DateTime("now",$tz);
// create the offset char string
$timeoffset = date_format($dateTime, 'P');
// add the offset to the list of zipcodes
$query = "UPDATE timezonebyzipcode SET offset_gmt = '" .$timeoffset ."' WHERE timezone = '" .$zoneName ."'";
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
}
// close the server connection
mysql_close($con);

Now I have a full list of ZipCodes with the gmt offset stored in a database table. Now I can use the data in this new table to add a new field to the contact database which will show the time offset allowing me to know when I should be able to call without waking them at 5AM in the morning.

Have fun..

Chris..

May 15

Zend moves support for ZendDBi to alternative supplier.


A recent article in IT Jungle just announced that Zend is going to be moving support and future development for Zend DBi off to another supplier. I did a quick review of the website for Percona and the pricing for their support for MySQL and various add-ons they have created and found the following price page. This means you should now expect to pay for support for Zend DBi separate to any support contract you decide to take with Zend.

This is not a bad move for Aura Equipments and their iAMP server solution, with iAMP you get the MySQL engine built in and support via the forums is still going to be free. You can purchase support for iAMP which will cover all of the installed products (Apache, MySQL and PHP) and have that support in one place. Add to that the ability to include the Easycom product as part of that support contract and it be comes very much a better option than the Zend stack which now requires the support with Percona and Zend plus the no fee based support for the open source XMLSERVICE to provide the same solution.

I am not sure why Zend took the move or how the IBMi community will respond. I do know this is not going to be a bad thing for Aura, who can capitalize on the fact that they provide a single point of contact for the total solution and at a price which is far below that of the total package required to run the various elements for Zend, Percona and the Open Source XMLSERVICE.

You can download and install the iAMP server from the Aura Equipments website.

Chris…

Mar 20

New version of iAMP Server available with updated content

A new version of iAMP Server is available from Aura Equipments which updates the PHP and MySQL content. See the full details on the Forums using the following Link.

Always get the latest versions by using iAMP Server and do not be forced to wait for IBM or Zend updates to get to the latest and greatest technology.

Chris…

Feb 27

Installing iAMP in 5 minutes or less.

Thought we would create a video showing just how easy it is to install iAMP server on the IBMi. This is a fully functioning Apache,MySQL,PHP server with Easycom providing access to the IBMi data and objects using the i5_toolkit functions.

You will see from previous posts about installing PHP on the IBMi it has never been as easy as this was, and yes it only takes 5 minutes to install.

If you have been thinking about PHP and the IBMi and were put off by the complexity of other installs iAMP server is for you. Take it for a test drive today, you don’t have to remove any existing HTTP servers for this install and removal is just as simple as the install.

If you need an un-install script here is one we generated for a base install, just compile the CL and run.


PGM
MONMSG MSGID(CPF0000)
ENDSBS SBS(EASYCOM) OPTION(*IMMED)
ENDSBS SBS(IAMPSVR) OPTION(*IMMED)
DLYJOB DLY(5)
RMVLNK OBJLNK('/etc/iamp.tab')
RMVDIR DIR('/usr/local/iamp') SUBTREE(*ALL)
RMVDIR DIR('/usr/local/Easycom') SUBTREE(*ALL)
RMVDIR DIR('/var/mysql') SUBTREE(*ALL)
RMVDIR DIR('/www/iamp') SUBTREE(*ALL)
DLTLIB LIB(EASYCOM)
DLTLIB LIB(EASYCOMXMP)
DLTLIB LIB(IAMPSVR)
ENDPGM

If you need any help or advice on setting up and running iAMP server let us know, we are happy to help. You can also follow the links provided from the installed webserver welcome.html page to the Forums and documentation.

Chris…

Jan 16

New Apache, MySQL PHP stack for IBMi called iAMP Server

Aura Equipments of France has just announced a new Apache, MySQL and PHP stack for the IBMi, the new stack has been compiled from standard open source code and is available for free download from the Aura website .

iAMP server is a fully integrated stack which runs in the PASE environment on IBMi, this means the standard Apache Server is also running in the PASE environment allowing a close connection between it and the extensions and modules provided by other parties. While you still have the option of connecting to the IBM HTTP server via a FASTCGI port you now have the option of running the entire stack in the PASE environment. Why is this important you may ask, well one of the reasons for the new stack is a growing concerns from a number of IBMi users about the slow introduction of some of the newer Apache releases on the IBMi with its Powered by Apache server. IBMi does a very good job of making the security releases available but sometimes lacks the additional features Apache provides especially in the earlier version of iOS.

Aura have built the entire stack from the freely available open source code, this means it follows the standards you will find in many of the other AMP Stacks out there for Linux and Windows. If you have your own modules you can add them to the stack in the same way as you can with other Linux and Windows stacks without any problems. Many of the restrictions found where proprietary modules are included with a stack do not exist in the iAMP server, everything is from the open source community and will work with any other modules they provide. While this release of iAMP server does not have every module available, it does have the main ones that are used by most shops. As more modules are compiled they will be added to the stack by Aura and will be supported in the same manner as the existing modules. If there is a module which is not provided and you need it before Aura gets round to making it available you have the ability to add the module yourself or may even find a compatible binary elsewhere, that’s the beauty of having a standards based open source stack.

Shield Advanced solutions will provide support for the iAMP server for North America via a fee based support contract or on a consultancy basis if required. Free community based support will be available via forums which will be dedicated to the iAMP server which is just as you get with any other AMP stack available on Linux or Windows.

One item we need to clarify is the inclusion of the i5_toolkit, this is a proprietary solution from Aura equipments and is not shipped as part of the iAMP stack. The original i5_toolkit which shipped with the Zend product will still be available to the users of the Zend product and you can update the version using the downloads available from the Aura Equipments website. However this version of the toolkit only accepts requests from the local server (Zend HTTP Server) and cannot be accessed from remote HTTP servers. Aura will continue to support the users of the i5_toolkit for FREE using the forums which are to be setup for this purpose. However for a small fee you can upgrade that support to premium level which will entitle you to full technical support from the Aura support team. Anyone who has registered with Aura will be receiving a notification which will include a special reduced rate for support for the i5_toolkit and the iAMP server. If you have not registered your copy of Easycom with Aura now would be a good time to do it!

Any new customer which requires the i5_toolkit will be offered full support for the toolkit PLUS the iAMP server for a single price, this offer will be especially important for the smaller customer who needs support for their entire stack not just the i5_toolkit. The FREE support will still be available via the forums and will be monitored by the Aura tech support team.

iAMP is a new alternative and one which customers should look into, the support costs and level of technical support you will get should make this an easy decision if you are looking at developing a web based interface to your IBMi data and objects.

If you would like to see a demo of the iAMP server in action or have any questions please feel free to contact us, this is a major step in bringing a standards based alternative AMP stack to the IBMi.

Chris…

Jul 20

Finally getting ahead of myself


Its been sometime since I last posted so thought that I would take a couple of minutes to update on what has been happening.

5733-SC1 is still seeing a lot of activity, we are not seeing the same number of downloads as we did previously but that could be because of the others who have posted links to the download but still plenty of activity. We are still trying to get the EasyCom product up and running which we think is a far better solution than going through the hoops to get PHP running directly on the IBM i. There are a number of reasons we think people should take this route instead of setting up a full blown Apache/PHP server on the IBM i, adding MySQL to the IBM i also seems to be a bit backward when it already runs one of the best Databases on the planet! Keep watching our website, we hope to be making some marketing announcements related to the EasyCom server in the near future.

DR4i is now available and can be downloaded for trials from our website, the new PHP interface seems to be a preferred method of monitoring the processes and the next release of our HA product will take this as its main interface moving forward. The DR4i product is already running in customer settings with no problems reported todate and we have a number of prospects who are looking to give it a spin once new hardware is available.

RAP is being developed further and will be renamed HA4i when the next release comes out, we are taking the new technology shipped in the DR4i product and building additional functionality into the PHP interface to allow the monitoring of the replication processes from a single screen. It will also pick up the new apply process which splits out the journals into individual apply streams allowing more granular control over the data apply process. Some of the technology we built into RAP will be removed as it added unnecessary clutter to the process and provided little benefit to most users. Our aim is to provide a much simpler and more manageable product for the user.

Marketing is progressing with our commitment to the ITJ newsletter continuing, hope you have all been getting your copies of the ITJ and seen our advertising? Let us know what you think…

We had a very good trip to Europe with a number of new prospects already engaged in discussions about our products, a flurry of activity just before we left saw a number of decisions made which increased our install base significantly. This did surprise us as we normally see a lull during this period and the recession is still hurting many.

We have finally finished the project to set up a new Wifi Network at the local TeenRanch Christian Camp, the main installers still have a number of activites to finish up before the hotspots are live and the access controls are in place but in the main its ready for use.

Thats about it, still looking for partners to help us sell our products or commission only sales people so if you are interested let us know..

Chris…

Mar 10

PHP and DB2 with System *DTS columns


We have not posted much more on the C for ‘i’ or PHP for ‘i’ threads as we have been struggling with a problem within the files we use for our JobQGenie product. The problem is the files store system date and time stamps (*DTS) which are 8 character fields. When we used the PHP functions to extract the data we would always end up with 8 characters of junk!

We had thought the PHP routines which deal with TimeStamps would be able to convert the timestamps but unfortunately they only work with UNIX style time stamps! So we had to find out how to convert them before they were received in the PHP script. We had been looking at User Defined Functions (UDF’s) but never really understood what benefits they would bring for PHP. As usual we asked on the forums for suggestions on how to best manage these timestamps and UDF’s seemed to be the best solution. So we took the information from the manuals and wrote a UDF which would simply convert the timestamp to a pre-formatted string. We use the QWCCVTDT API in our UIM programs for displaying the dates so we created a UDF which would do the same thing.


#include <qusec.h> /* Error Code Structs */
#include <stdio.h> /* sprintf etc */
#include <string.h> /* string functions */
#include <qwccvtdt.h> /* convert timestamp */

typedef _Packed struct EC_x {
Qus_EC_t EC;
char Exception_Data[1024];
} EC_t;

typedef struct DateTime_x {
char Year[4];
char Month[2];
char Day[2];
char Hour[2];
char Minute[2];
char Second[4];
}DateTime_t;

#define _ERR_REC sizeof(_Packed struct EC_x)

void UNSTAMP(char * timeStamp,
char * cvtTimeStamp,
short *inIndicator,
short *outIndicator,
char *sqlState,
char *funcName,
char *specName,
char *msgText) {
char Input_Fmt[10] = "*DTS "; /* Time stamp input fmt */
char Output_Fmt[10] = "*YYMD "; /* Time stamp input fmt */
DateTime_t buf;
EC_t Error_Code = {0}; /* err struct */

Error_Code.EC.Bytes_Provided = _ERR_REC;

QWCCVTDT(Input_Fmt,
timeStamp,
Output_Fmt,
&buf,
&Error_Code);
if(Error_Code.EC.Bytes_Available > 0) {
/* create the message to be returned */
memset(cvtTimeStamp,'0',26);
memcpy(sqlState,"38999",5);
}
sprintf(cvtTimeStamp,"%.4s/%.2s/%.2s %.2s:%.2s:%.2s",
buf.Year,buf.Month,buf.Day,buf.Hour,buf.Minute,
buf.Second);
return;
}

This program will take the *DTS time stamp passed and convert it to a Character string via a predefined structure. We compiled this as a module and then as a service program with EXPORT *ALL.

Next we had to let SQL know we would be using it. The information on how to create the UDF can be found in the IBM Infocenter but here is our script for the function.


Drop Function UNSTAMP;

Create Function UNSTAMP(timestamp char(8))
returns char(26)
external name 'CHLIB/UNSTAMP(UNSTAMP)'
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
DETERMINISTIC
DISALLOW PARALLEL;

To add the function to SQL we ran the following request
RUNSQLSTM SRCFILE(QSQLSRC) SRCMBR(UNSTAMP) COMMIT(*NONE) ERRLVL(20)

Running an SQL interactive session we were able to determine that the code did in fact work and the returned string was formatted as we wanted. Next we had to add it to a php script, after a few attempts we came up with a script that worked. Our biggest problem which we are still working on a solution was how to limit the number of entries returned, our test file had approximately 25,000 records
so the data build for the browser took some time! MySQL has the ability to use LIMIT passing in the start record and the number to fetch, DB2 requires the use of FETCH which does not have the same capabilities as far as we can tell. A big thanks to Scott Klement who found a stupid error that was driving us nuts!

Here is the script we ran, you will notice we have restricted the number of records to fetch and display. Eventually we will use this with page limits to allow control over the returned data.

<?php
session_start();
// register the next record variable
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<style type="text/css">
tr.d0 td {
background-color: #CC9999;
}
tr.d1 td {
background-color: #9999CC;
}
</style>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type></HEAD>
<BODY>
<?php
//include("i5toolkit/Toolkit_classes.php");
// include the file which holds the user info
include("../scripts/config.php");
// connect to the i5
$options = array("i5_naming"=>DB2_I5_NAMING_ON,"i5_lib"=>"CHLIB");
$conn = db2_connect("","","",$options);
if (is_bool ( $conn ) && $conn == FALSE) {
die ( "No Connection " .db2_conn_errormsg($conn) );
}
// maximum page size
$size = 200;
// where to display records from
$start = 0;
if(isset($_SESSION['next']))
$next = $_SESSION['next'];
else
$next = 0;
if(isset($_SESSION['previous']))
$next = $_SESSION['previous'];
else
$previous = 0;
$query = "select
jobname,
usrname,
jobid,
jobq,
jobqlib,
unstamp(ENTERTS) as TS,
unstamp(STARTTS) as STR,
unstamp(ENDEDTS) as END,
jobtype,
subtype,
endcde,
prcused,
jobstate from jq0
where JOBID > " .$next
." FETCH FIRST " .$size ." ROWS ONLY";
$result = db2_exec($conn,$query);
if(!$result) {
die("db2_exec " .db2_stmt_errormsg());
}

?>
<a href="db2test1.php?id=<?php echo($start);?>">Next</a>
<table border="1">
<tr class="<?php echo("d1"); ?>">
<td><?php echo("Job ID"); ?></td> <!-- Jobid -->
<td><?php echo("Job Name");?></td> <!-- JobName -->
<td><?php echo("User Name");?></td> <!-- UserName -->
<td><?php echo("Job Queue");?></td> <!-- Job Queue -->
<td><?php echo("JobQ Library");?></td> <!-- JobQ Library -->
<td><?php echo("Entered Time");?></td> <!-- Entered TS -->
<td><?php echo("Started Time");?></td> <!-- Start TS -->
<td><?php echo("Ended Time");?></td> <!-- Ended TS -->
<td><?php echo("Job Type");?></td> <!-- Job Type -->
<td><?php echo("Sub Type");?></td> <!-- SubType -->
<td><?php echo("End Code");?></td> <!-- EndCode -->
<td><?php echo("Processor Used");?></td> <!-- Proc Used -->
<td><?php echo("Job State");?></td> <!-- JobState -->
</tr><?php
for($i = 0; $i < $size; $i++) {
$rec = db2_fetch_both($result) ?>
<tr class="<?php echo("d" .($i & 1)); ?>">
<td><a href=""><?php echo($rec[2]); ?></a></td>
<td><?php echo($rec[0]);?></td>
<td><?php echo($rec[1]);?></td>
<td><?php echo($rec[3]);?></td>
<td><?php echo($rec[4]);?></td>
<td><?php echo($rec['TS']);?></td>
<td><?php echo($rec['STR']);?></td>
<td><?php echo($rec['END']);?></td>
<td><?php echo($rec[8]);?></td>
<td><?php echo($rec[9]);?></td>
<td><?php
if($rec[13] == 0) echo("Completed Normally");
else if ($rec[10] == 10) echo("Completed Normally During Controlled Ending");
else if ($rec[10] == 20) echo("Exceeded End Severity");
else if ($rec[10] == 30) echo("Ended Abnormally");
else if ($rec[10] == 40) echo("Ended before becoming Active");
else if ($rec[10] == 50) echo("Ended while Active");
else if ($rec[10] == 60) echo("Subsystem ended while job was Active");
else if ($rec[10] == 70) echo("System ended abnormally while job was Active");
else if ($rec[10] == 80) echo("Job ended (ENDJOBABN");
else if ($rec[10] == 90) echo("Forced end after ENDJOBAN");
else if ($rec[10] == 999) echo("On Job Queue");
?></td>
<td><?php $prcused = $rec[11]/10000; echo(round((float)$prcused,2));?></td>
<td><?php
if($rec[12] == 0) echo("Ended");
else if($rec[12] == 1) echo("Failed");
else if($rec[12] == 2) echo("Job Queue");
else if($rec[12] == 3) echo("Unknown");?></td>
</tr><?php
}
db2_free_result($result);
db2_close($conn);
?>
</table>
</BODY>
</HTML>

This results in output similar to the following.

When you consider the UDF is required 3 times for every row, even 25,000 rows took milliseconds to run locally on the IBM ‘i’ in an interactive SQL session, thats pretty impressive..

Chris…