Ever run a query that calls a CL, RPG or SQL and fails somewhere in the process? Then you have to modify the PHP code to dump out your variables or go to a 5250 session to view the job log? Wish you could just see the information from the job log on the webpage or have it email you the information in an email (if your in production)? Well with QSYS2.JOBLOG_INFO(*) you can!

Below is a demo code on how to accomplish this using a PHP’s set_error_handler. The demo code will try to run a SQL SELECT on a nonexistent table that will invoke myErrorHandler and it will call QSYS2.JOBLOG_INFO(*) on the current connection to get all the information from IBMi DB2 and then output it to the screen for you. You could change the echo statement to a mail statement if you’d rather it send you an email in case you are in production. You could have it dump out other information such as the $_REQUEST, $_SESSION to get more information on what was happening when PHP failed

Note: There’s alot more information obtained than what you get from db2_stmt_error() and db2_stmt_errormsg()

db2_ibmi_callstack

<?
//only output the error if we're in the development environment
$APPLICATION_ENV="dev";
//set error handler to the callback function defined later
set_error_handler("myErrorHandler");
//connect to db
$db2Connection = db2_connect( '', '' , '',array('i5_lib' => 'QSYS'));
if (!$db2Connection)
echo "false – Connection failed.";
//Demo – create a DB2 SQL error that will call our error handler
$stmt = db2_exec($db2Connection,"SELECT * FROM NONEXISTENTTABLE");
function myErrorHandler($errno, $errstr, $errfile, $errline) {
//::NOTE:: use of global is not recommended and only shown for demo purposes.
//This should come from $_SERVER['APPLICATION_ENV'] a global var set by apache
global $APPLICATION_ENV;
//if this is not the development environment don't show the error in the browser
if(!isset($APPLICATION_ENV)||$APPLICATION_ENV!="dev")
return;
//Output the error string and let them know the file and line # that caused the issue
echo "Error: $errstr in $errfile line# $errline<br/>";
//Check if a statement call caused the error
if(db2_stmt_error()!="")
{
echo "DB2 statement error: ".db2_stmt_error()." ".db2_stmt_errormsg();
}
//Check if a connection problem caused the error
if(db2_conn_errormsg!="")
{
echo db2_conn_errormsg();
echo db2_conn_error();
}
//SQL query to pull job log info on this connection
$sql="SELECT * FROM table(QSYS2.JOBLOG_INFO('*')) a";
//Could add a where clause to narrow down to only things that caused the program to crash
//$sql.=" WHERE a.SEVERITY>20";
//::NOTE:: use of global is not recommended and only shown for demo purposes.
//this should come from your "singleton" database connection that your app uses.
global $db2Connection;
$stmt = db2_exec($db2Connection,$sql);
//Loop through and echo out the job log information
$i=0;
$htmlDB2CallStack="";
while($row = db2_fetch_assoc($stmt))
{
if($i==0)
{
$htmlDB2CallStack .= '<h1>Full DB2 Call Stack</h1>';
$htmlDB2CallStack .= '<table class="db2errortable">';
$htmlDB2CallStack .= '<tr >';
$htmlDB2CallStack .= '<th>#</th>';
$htmlDB2CallStack .= '<th>MSG_ID</th>';
$htmlDB2CallStack .= '<th>MSG_TYPE & SUBTYPE</th>';
$htmlDB2CallStack .= '<th>SEVERITY</th>';
$htmlDB2CallStack .= '<th>MSG_TIMESTAMP</th>';
$htmlDB2CallStack .= '<th>FROM LIB|PGM|MOD|PROC|INSTR</th>';
$htmlDB2CallStack .= '<th>TO LIB|PGM|MOD|PROC|INSTR</th>';
$htmlDB2CallStack .= '<th>FROM_USER</th>';
$htmlDB2CallStack .= '<th>MSG_LIB</th>';
$htmlDB2CallStack .= '<th>MSG_FILE</th>';
// $htmlDB2CallStack .= '<th>MSG_TKN_LENGTH</th>';
// $htmlDB2CallStack .= '<th>MSG_TEXT</th>';
// $htmlDB2CallStack .= '<th>MSG_SECOND_LEVEL_TEXT</th>';
$htmlDB2CallStack .= '</tr>';
}
$htmlDB2CallStack .= '<tr class="detailrow">';
$htmlDB2CallStack .= '<td>'.$row['ORDINAL_POSITION'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['MESSAGE_ID'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['MESSAGE_TYPE'].' – '.$row['MESSAGE_SUBTYPE'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['SEVERITY'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['MESSAGE_TIMESTAMP'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['FROM_LIBRARY'].'|'.$row['FROM_PROGRAM'].'|'.$row['FROM_MODULE'].'|'.$row['FROM_PROCEDURE'].'|'.$row['FROM_INSTRUCTION'].'/</td>';
$htmlDB2CallStack .= '<td>'.$row['TO_LIBRARY'].'|'.$row['TO_PROGRAM'].'|'.$row['TO_MODULE'].'|'.$row['TO_PROCEDURE'].'|'.$row['TO_INSTRUCTION'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['FROM_USER'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['MESSAGE_LIBRARY'].'</td>';
$htmlDB2CallStack .= '<td>'.$row['MESSAGE_FILE'].'</td>';
// $htmlDB2CallStack .= '<td>'.$row['MESSAGE_TOKEN_LENGTH'].'</td>';
$htmlDB2CallStack .= '</tr>';
$htmlDB2CallStack .= '<tr class="messagerow">';
$htmlDB2CallStack .= '<td colspan="11">'.$row['MESSAGE_TEXT'].'</td>';
if($row['MESSAGE_SECOND_LEVEL_TEXT']!="")
{
$htmlDB2CallStack .= '<tr>';
$htmlDB2CallStack .= '<td colspan="11">'.$row['MESSAGE_SECOND_LEVEL_TEXT'].'</td>';
$htmlDB2CallStack .= '</tr>';
}
$htmlDB2CallStack .= '</tr>';
$i=1;
//var_dump($row);
}
$htmlDB2CallStack .= '</table>';
//lets dump the library list as well
$sql="SELECT * FROM QSYS2.LIBRARY_LIST_INFO";
$stmt = db2_exec($db2Connection,$sql);
//Loop through and echo out the library list info
while($row = db2_fetch_assoc($stmt))
{
var_dump($row);
}
echo $htmlDB2CallStack;
/* Execute PHP internal error handler by returning false */
return FALSE;
}
?>
<style type="text/css">
.db2errortable
{
/*Make it green and black :-) */
background-color: black;
color: green !important;
font-size: 1.1em !important;
}
table, th {border: 1px solid black;}
.messagerow {border-bottom:1px solid #008999}
.detailrow {color:#7FFF00}
table {border-collapse: collapse;}
</style>

view raw
DB2ErrorHandler.php
hosted with ❤ by GitHub