[adrotate group=”3,7,8,9″]
IBM has brought out a nice new option which allows users to display a journal content out using SQL. Before you can use the functionality there are a couple of PTF’s you need if you are running on V6R1 or before (It is only supported back to V5R4 as well). The PTF’s you need are group PTF’s for DB2 for i and are as follows.
V5R4 SF99504 Level 27
V6R1 SF99601 Level 15
Once you the PTF’s installed you should be able to find the Display_Journal function defined in the QSYS2/SYSROUTINE data file. We first tested using a sample from IBM which can be run from the STRSQL interface or the Operations Navigator for i SQL interface. The following is the code IBM supplied.
set path system path, mjatst; -- Change mjatst to your library you chose above
-- Select all entries from the *CURRENT receiver of journal mjatst/qsqjrn.
select * from table (
Display_Journal(
'MJATST', 'QSQJRN', -- Journal library and name
'', '', -- Receiver library and name
CAST(null as TIMESTAMP), -- Starting timestamp
CAST(null as DECIMAL(21,0)), -- Starting sequence number
'', -- Journal codes
'', -- Journal entries
'','','','', -- Object library, Object name, Object type, Object member
'', -- User
'', -- Job
'' -- Program
) ) as x;
Couple of things you need to do is change the library and journal information to match your environment. This worked fine and we managed to retrieve the entries as expected. So the next task was to put the request into a PHP script which would run from our Linux Box and pull the data back from the IBMi. Initially we had a problem because the SQL translator complained about LOB objects and commitment control. After contacting Aura Equipments for advice they told us we needed to use the i5_transaction() function to start commitment control and ensure we did a i5_rollback before we finished. Unfortunately the examples provided in the manual were wrong so we still saw the error, it was only after doing some additional investigation that we found out how to correctly code the request. Below is the code we ran which did return the data we needed.
$conn = 0;
if(connect($conn) == -1) {
header('Location: /index.php');
exit(0);
}
if(!i5_transaction(I5_ISOLEVEL_CHG, $conn)) {
echo("Failed to set transaction level CHG");
}
$query = "SELECT *
FROM table(
DISPLAY_JOURNAL(
'HA4IJRN',
'HA4IJRN',
'',
'',
CAST(null as TIMESTAMP),
CAST(null as DECIMAL(21,0)),
'',
'',
'',
'',
'',
'',
'',
'',
'')
) as x";
$result = i5_query($query,$conn);
echo($query);
if(!$result) {
$_SESSION['ErrMsg'] = "Error code: " .i5_errno($result) ." Error message: " .i5_errormsg($result);
header("Location: /index.php");
exit(-1);
$rec = i5_fetch_assoc($result);
var_dump($rec);
i5_rollback($conn);
if(!i5_transaction(I5_ISOLEVEL_NONE, $conn)) {
echo("Failed to set transaction level NONE");
}
i5_free_query($result);
All that we did was dump out the returned array for the first record. We will build a couple of interfaces around the data collection for the journal to be displayed etc and provide a loop around displaying the data returned, but for now this proves the process works and we can move to the next stage when we are ready. The important thing to remember is to make sure you start and end the commitment control properly, we just did a start and when we refreshed the data not return with a complaint about the commit being started yet it could not run the SQL because it was not started for this request. The solution was to ensure we did a ISOLEVEL_NONE before we returned from the function. I have asked Aura if they could create an i5_toolkit function which would return the current commit status and its level, not sure when and if that will be supplied.
Here is the output from our journal (we did layout the data so it was readable)
array(40) {[“ENTRY_TIMESTAMP”]=> string(26) “2011-11-23-15.01.50.659424”
[“SEQUENCE_NUMBER”]=> string(1) “1”
[“JOURNAL_CODE”]=> string(1) “J”
[“JOURNAL_ENTRY_TYPE”]=> string(2) “PR”
[“COUNT_OR_RRN”]=> string(1) “1”
[“ENTRY_DATA”]=> int(256)
[“NULL_VALUE_INDICATORS”]=> string(0) “”
[“OBJECT”]=> string(0) “”
[“OBJECT_TYPE”]=> string(0) “”
[“OBJECT_TYPE_INDICATOR”]=> string(0) “”
[“FILE_TYPE_INDICATOR”]=> string(0) “”
[“JOURNAL_IDENTIFIER”]=> string(0) “”
[“CURRENT_USER”]=> string(10) “QSYS “
[“JOB_NAME”]=> string(10) “SCPF “
[“JOB_USER”]=> string(10) “QSYS “
[“JOB_NUMBER”]=> string(6) “000000”
[“THREAD”]=> string(0) “”
[“PROGRAM_NAME”]=> string(10) “QWCISCFR “
[“PROGRAM_LIBRARY”]=> string(0) “”
[“PROGRAM_LIBRARY_ASP_DEVICE”]=> string(0) “”
[“PROGRAM_LIBRARY_ASP_NUMBER”]=> string(0) “”
[“COMMIT_CYCLE”]=> string(1) “0”
[“NESTED_COMMIT_LEVEL”]=> string(0) “”
[“XID”]=> string(0) “”
[“LUW”]=> string(0) “”
[“REMOTE_PORT”]=> string(0) “”
[“REMOTE_ADDRESS”]=> string(0) “”
[“SYSTEM_NAME”]=> string(8) “SHIELD3 “
[“SYSTEM_SEQUENCE_NUMBER”]=> string(0) “”
[“REFERENTIAL_CONSTRAINT”]=> string(1) “0”
[“TRIGGER”]=> string(1) “0”
[“IGNORE_ON_APPLY”]=> string(1) “0”
[“MINIMIZED_ENTRY_DATA”]=> string(1) “0”
[“MINIMIZED_ON_FIELD_BOUNDARY”]=> string(0) “”
[“INDICATOR_FLAG”]=> string(1) “0”
[“RECEIVER_NAME”]=> string(10) “Z000000009”
[“RECEIVER_LIBRARY”]=> string(10) “HA4IJRN “
[“RECEIVER_ASP_DEVICE”]=> string(10) “*SYSBAS “
[“RECEIVER_ASP_NUMBER”]=> int(1)
[“ARM_NUMBER”]=> int(1) }
One thing we did notice was the ENTRY_DATA field changed every time we refreshed so not exactly sure what that relates to? So if you want to display journal entries out to a HTML page using PHP the above should give you a good start into doing so!
Hope the content we publish is useful to you? We do not hear back very often so its like talking to a brick wall and hoping some are listening anyhow. If you need any more information let us know.
Chris…