[adrotate group=”3,4″]
Previously we had been looking at how to manage the display of a DB2 table out to a HTML page using various PHP functions. One of the problems was the size of the table and how long it would take to display all of the records out to a page. The initial tests just looked at how to speed up the extraction of the data and didn’t really look at how the display of the data would work.
In this task we set about looking into how we could create pages of data and allow the user to page backwards and forwards through the data without causing a huge delay in the final presentation of the data. Our initial thoughts were to have a couple of forward and backward buttons which would allow you to page through the data, that was OK but in a file with 25,000 records having to page through each and every page would become tedious at best. As an interim stage we decided that as we paged through the data we would store the information that would allows us to go back to a specific page from the last page, eventually we will add functionality that will allows us to jump between pages once they have been read through and allow a page list to be built which is determined by the user passing in a starting point. We would simply read through the DB setting the relevant page information without actually displaying the interim data.
So we had a plan, now we just needed to code up the variables which would allow us to track our progress through the table. This would be done by storing the information required in a multi-dimensional array in the session variables. PHP arrays are not like ‘C’ arrays, you can really cause yourself a lot of anguish when building the array especially if you are storing different types of data. We have used structures a lot in ‘C’ and we thought this would be a similar process as addressing an array of structures. That proved to be a bit more misleading than we understood, so a lot of time was spent learning about arrays in PHP and how you can loop through the information. Initial trails used the foreach() function but eventually we went back to good old for() loops.
Each array element would store the page number and the record displayed in the first row of the table, this would allow us to rebuild this page should the user select the page links we were going to provide. Another link would be supplied which allowed the user to get the next set of rows from the data base. We did not use the native file access routines but instead opted to use db2_exec() as it would allow the query optimizer to build the best path to the data we needed.
The data we would be displaying is from our JobQGenie product which is used to recover after a system role swap. This will eventually form part of all of our products modernization projects so this technology will be used across many data files in many products. Being able to build the paging technology was really what we wanted to test in this exercise. The layout and content of the file is not important, we just needed to use a file with lots of data stored in it. The tests we ran without any paging took many seconds to build the page and strained the memory resources on both the IBM ‘i’ and the PC. I feel if we added much more data in the files the process would eventually break.
I have added lots of comments in the file so you can see what I am trying to do, if you have any suggestions on a better way to create the pages let us know.
<?php
/*
* There are 3 ways to call this file, with no parameters this will
* be the initial call, with nextid which is when the NEXT link is
* selected and finally with the recid which is called when one of the
* page numbers is selected.
*/
// start the session to allow session variables to be stored and addressed
session_start();
/*
* function to strip the array contents back to a selected page.
* takes in the address of the stored array so it can be unset and
* the id of the record to be searched for. The id is the first job
* ID which was stored for a particular page.
* returns when the id is matched to an array element
*/
function remove_to(&$pages,$id) {
$num_pages = count($pages);
for($i = $num_pages; $i > 0; $i--) {
if($pages[$i][1] == $id) {
return;
}
else
unset($pages[$i]);
}
// resequence the arrray
$pages = array_values($pages);
return;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<!-- The table rows will alternate in color -->
<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 the file which holds the user info for the connection plus any configurable
* information such as the number of rows to show per page.
*/
include("../scripts/config.php");
/* check if this is a refresh of the same URL, we need to do this to determine
* if a new page element is to be added to the stored array.
*/
$refresh = false;
if($_SERVER[REQUEST_URI] == $_SESSION['uri'])
$refresh = true;
else
// store the requested URI
$_SESSION['uri'] = $_SERVER[REQUEST_URI];
// 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) );
}
// couple of variables used to address the array and set up the query
$p = 0;
$next = 0;
// if the session pages array is set copy to the local pages array
if(isset($_SESSION['pages']))
$pages = $_SESSION['pages'];
/* if recid is the request paramter a page link has been selected
* set up the relevant query and clean up the stored pages array
*/
if(isset($_REQUEST['recid'])) {
// remove the page array entries beyond the value passed
$pages = $_SESSION['pages'];
remove_to($pages,$_REQUEST['recid']);
$_SESSION['pages'] = $pages;
// clean up the array should not be necessary as we removed the end indexes
$next = $_REQUEST['recid'];
$add_page = false;
$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 ." ORDER BY enterts"
." FETCH FIRST " .$size ." ROWS ONLY";
}
/* if nextid is the request variable the next link has been selected
* need to add another page if not a refresh plus set up the query
*/
else if(isset($_REQUEST['nextid'])) {
if($refresh == true )
$add_page = false;
else
$add_page = true;
$next = $_REQUEST['nextid'];
$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 ." ORDER BY enterts"
." FETCH FIRST " .$size ." ROWS ONLY";
}
/* first page in the request so will not have recid or nextid
* request variables set. If a refresh request set the query to
* start from the relevant record.
*/
else {
if($refresh == true) {
// must be a refresh
$next = $_SESSION['first'];
$add_page = false;
}
else {
$next = 0;
$add_page = true;
}
$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 ." ORDER BY enterts"
." FETCH FIRST " .$size ." ROWS ONLY";
}
// connect to the database
$result = db2_exec($conn,$query);
if(!$result) {
die("db2_exec " .db2_stmt_errormsg());
}
?>
<!-- Build the table and add the records as required -->
<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);
// store the first record in the page array
if(!$rec)
break;
if($i == 0)
$_SESSION['first'] = $rec[2];
?>
<tr class="<?php echo("d" .($i & 1)); ?>">
<td><?php echo('<a href="display_det.php?id=' .$rec[2] .'">' .$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
} ?>
</table>
<!-- Only display next link if max records read -->
<?php
// store the last record read
$_SESSION['last'] = $rec[2];
// count the array size
$num_pages = count($pages);
// if add_page set add a new element to the array
if($add_page){
$pages[] = array($num_pages += 1,$_SESSION['first']);
$_SESSION['pages'] = $pages;
}
// if there are more than 1 pages show the page selection links
if($num_pages > 1) {
// do not show last page
$num_pages--;
//loop throught the pages stored
for($p = 0; $p < $num_pages; $p++) {
echo('<a href="' .$_SERVER[PHP_SELF] .'?page=' .$pages[$p][0] .'&recid=' .$pages[$p][1] .'">' .$pages[$p][0] .'</a>' .' ');
}
}
// if displayed max records may be more so show next link
if($i == $size) ?>
<a href="<?php echo($_SERVER[PHP_SELF] ."?nextid=" .$rec[2]); ?>">Next</a>
<?php
// close down the connection and destroy the $result
db2_free_result($result);
db2_close($conn); ?>
</BODY>
</HTML>
This produces output similar to the following, we have purposely paged through a number of pages so you can see the page links etc at the bottom of the page.
We have set the number of rows to 30 which produces the pages sub second, we did test with a page size of 1,000 records which ran just as quickly so we are pretty happy with the results achieved.
Next we will add the display functionality of the job detail which is already linked in the table and possibly provide a submit button to re-submit the job directly to the IBM ‘i’??
Let us know if you have any questions or need clarification on what we did and why.
Chris…