[adrotate group=”4,3″]
As part of our project to externalize the JobQGenie and RAP data for use in a new PHP interface plus allow webservices to be created which will consume the data we came across a number of challenges, one of the challenges was how to select all of the fields within a number of files while still being able to call UDF’s for individual fields in a particular file. Those of you that have been following the PHP for ‘i’ posts will remember how we had to convert 8 char date fields into full date strings using a User Defined Function (UDF). The blog entry which covered that is here.
We had been trying for some time on how to return all of the fields in the table while still being able to have the UDF’s called at the same time, a lot of the information we could find on the web said we should be able to call the UDF in a manner similar to
“select *, unstamp(enterts), unstamp(startts), unstamp(endedts) from JQ0”
however this always return an error stating the * was an invalid key.
The code we ended up using was
$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”
which worked just fine.
The next problem came when we needed to look over a number of files, we would have to list over 40 fields just to get the information back that we needed. That was obviously a lot of effort to manage as well as extra effort for SQL as it would probably have to sort through all of the fields available just to return the ones we had selected?
We posted a couple of requests on the iSeries Network forums to see if anyone had any ideas, the posts back seemed to suggest that we should be able to use the ‘*’ to select all of the fields. While we were waiting for the repsones we decided to try out a different request, this time we would qualify the file we wanted all of the fields returned for. Using
select JQ0.*, unstamp(enterts), unstamp(startts), unstamp(endedts) from JQ0
worked, we received all of the data back. Now we wanted to do the same for the multiple file request so the code to pull all of the data back became
$query = “select unstamp(jq0.ENTERTS) as TS, unstamp(jq0.STARTTS) as STR, unstamp(jq0.ENDEDTS) as END, jq0.*,jq4.*,jq5.*,jq6.* FROM jq0 INNER JOIN jq4 ON jq0.intjobid = jq4.intjobid INNER JOIN jq5 ON jq0.intjobid = jq5.intjobid INNER JOIN jq6 ON jq0.intjobid = jq6.intjobid where jq0.jobid = {$_REQUEST[‘id’]}”;
You will see that we also used an INNER JOIN to link the files by the key value, we had also used
$query = “select * from jq0, jq4, jq5, jq6 where jq0.jobid = {$_REQUEST[‘id’]} and jq0.intjobid = jq4.intjobid and jq0.intjobid = jq5.intjobid and jq0.intjobid = jq6.intjobid”;
which while it works is consider old style coding? (Thats what we were told!)
So we now have another part of the jigsaw for being able to display the data collected by the JobQGenie product which will eventually result is a PHP based interface to the product. This data is also used in our RAP product so the code used to display the data can be migrated to work in both products pretty easily..
The next step will be extracting the status for the processes involved particularly for RAP as it will have a number of status to pull from separate systems.
Hope you find the information useful, we are certainly learning lots of things about PHP and some of its peculiarities where DB2 and SQL is concerned as we progress through this exercise..
Chris…