PHP and DB2 with System *DTS columns

[adrotate group=”3,4″]
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…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.