[adrotate group=”3,7,8″]
After a slow start we have now started to create a PHP based interface for the JobQGenie product. The problem we were struggling with was how to best display the list of jobs that JobQGenie has captured information for. In some customer environments this could be millions of jobs so the data had to be paged in and out efficiently. We also wanted to be able to move from one page to the other using links which we would store at the bottom of the list.
The initial test showed promise and we could effectively display the list of jobs and page through them without any issues. But we then had to add filtering, obviously this would require a number of filters such as job state or which job queue they ran on or who ran them, this is where the fun started. Our initial SQL code would fetch all of the records and display them as we needed, the order of the jobs listed was controlled by a time stamp, so a sort using another value would effect how the next set of entries would be retrieved. This is because the key on the file is by a time stamp of when the job was loaded, if I sorted the list by lets say the user name time stamps would be moved to further down in the list as the user name was the list content. This meant I could not retrieve the time stamp and list from this time stamp onwards before sorting by user name, the sorted list cause a number of entries to be missed.
I had seen some examples in the IBM manuals using the WITH clause, this would create a pre-selected list which I could order in the manner I needed and then select from that list using the time stamp as the key. This is because the time stamp would still be in the right place in the pre-selected list. Unfortunately this did not work, for some reason the resulting list was missing a number of entries. We are not sure why (probably a coding issue) but it seemed we had hit a brick wall. I decided to post a question on the forums to see if anyone had any better ideas. Fortunately we got an answer from one of the forum members, he suggested we use the RANK() function to help us create the list in the correct format, this required a unique key to base the request on which was pretty easy to arrange. So after some trial and error we finally found a SQL string which met all of our needs and provides us with a complete and sorted list and allows us to successfully page through.
Here is the actual code which generated this list. The BETWEEN numbers and the ORDER BY values are computed by the program based on the selection made by the user, this is only the initial request and subsequent requests would change the BETWEEN numbers. As far a speed is concerned we saw no real degradation to our initial method so we hope its pretty efficient.
WITH SubQ as (
SELECT
JOBNAME,
USRNAME,
JOBID,
JOBQ,
JOBQLIB,
unstamp(ENTERTS) as TS,
unstamp(STARTTS) as STR,
unstamp(ENDEDTS) asEND,
JOBTYPE,
SUBTYPE,
ENDCDE,
PRCUSED,
JOBSTATE,
RANK() OVER(ORDER BY JOBID) as RN FROM JG_SHIELD2/JQ0 where
JOBSTATE = '2' ORDER BY JOBID )
SELECT * FROM SubQ WHERE RN BETWEEN 1 AND 20
Here is a sample of the output, you will notice we have a drop down box for one filter which allows us to filter by job state, the other filter is a Javascript function attached to the cell on the header row. Eventually we will add arrows or some other image to show the column selected for the sort but for now a simple text label will do.
Once you have built the list you can also display the detail for the job as shown below.
This is still early days as we have to build new functions to allow searches against the job list and allow jobs to be re-submitted from the browser list but we have what we felt was the hardest part completed and Bryan who gave us the pointers certainly helped move that forward a lot faster than we would have by reading the manuals.
Now we can concentrate on getting the initial interface designed and coded before we look at the search requirements and job re-submission.
If you are interested in JobQGenie take a look at the products page, its gaining a lot of popularity as people start to understand just how exposed there High Availability environments are without this kind of solution.
Chris…