Hi Don,
this would be my take on it. The only thing missing the exception handling, which needs to test the SQLSTT/SQLCOD values and throw an appropriate exception which would be caught by the monitor block. I cannot test it with the same parameters as you as I'm not authorised to the journals on my machine, but hopefully it works.
Tim.
**FREE
ctl-opt copyright('')
datfmt(*ISO) datedit(*YMD/) timfmt(*ISO)
debug(*YES) option(*NODEBUGIO: *SRCSTMT)
main(main);
dcl-ds data_t qualified template inz;
entry_timestamp timestamp inz(z'1970-01-01-00.00.00'); // Initialise with first start time
journal_entry_type char(2);
syslog_facility int(10);
syslog_severity int(10);
syslog_event varchar(2048);
end-ds;
//**************************************************************************************************************
// Entry point
//**************************************************************************************************************
dcl-proc main;
dcl-pi *N;
end-pi;
dcl-ds data likeds(data_t) inz;
dcl-s resetCache like(*IN);
// dow process <> 'STOP';
monitor;
OpenLog(data.entry_timestamp);
dow ReadLog(data: resetCache);
// do something...
enddo;
on-error;
resetCache = *ON;
endmon;
CloseLog();
// enddo;
end-proc;
//************************************************************************************
// Open the SQL cursor for the Log data.
//************************************************************************************
dcl-proc OpenLog;
dcl-pi *N extproc(*dclcase);
startTime timestamp const;
end-pi;
exec sql
declare LOG_CURSOR cursor for
select entry_timestamp,
journal_entry_type,
coalesce(syslog_facility, 0),
coalesce(syslog_severity, 0),
coalesce(syslog_event, '')
from table(QSYS2.DISPLAY_JOURNAL(
'QSYS',
'QAUDJRN',
GENERATE_SYSLOG => 'RFC5424',
STARTING_TIMESTAMP => :startTime
)) as X
where syslog_event is not null and
substr(syslog_event, 105, 3) <> 'Low';
exec sql open LOG_CURSOR;
CheckState(SQLCOD: SQLSTT: SQLERM);
end-proc;
//**********************************************************************************
// Read log data...
// NOTE: The cache must be reset after any error during a read or when the read
// loop ended before all rows had been read.
//**********************************************************************************
dcl-proc ReadLog;
dcl-pi *N like(*IN) extproc(*dclcase);
data likeds(data_t);
resetCache like(*IN);
end-pi;
dcl-c CACHE_SIZE_ROWS 128;
dcl-ds dataCache likeds(data_t) dim(CACHE_SIZE_ROWS) static;
dcl-s i uns(5) static;
dcl-s rows uns(5) static;
// Force cache to reset.
if resetCache;
resetCache = *OFF;
rows = 0;
endif;
i += 1;
if i > rows;
exec sql fetch LOG_CURSOR for 128 rows into :dataCache; -- ensure same as cache size!
CheckState(SQLCOD: SQLSTT: SQLERM);
exec sql get diagnostics :rows = row_count;
if rows = 0;
return *OFF;
endif;
i = 1;
endif;
data = dataCache(i);
return *ON;
end-proc;
//**********************************************************************************
// Close the cursor
//**********************************************************************************
dcl-proc CloseLog;
dcl-pi *N extproc(*dclcase);
end-pi;
exec sql close LOG_CURSOR;
CheckState(SQLCOD: SQLSTT: SQLERM);
end-proc;
//**********************************************************************************
// Check SQL state - should be externalised in a service program somewhere....
//**********************************************************************************
dcl-proc CheckState;
dcl-pi *N like(*IN) extproc(*dclcase);
SQLCOD int(10) const;
SQLSTT char(5) const;
SQLERM char(70) const;
end-pi;
// Check for error and throw an exception if necessary..
// if ..error
// QMHSNDPM(....)
// endif;
// Return *OFF when no record read.
return (SQLSTT <> '02000');
end-proc;
//**********************************************************************************
________________________________
From: Don Brown <DBrown@xxxxxxxxxx>
Sent: 01 November 2021 20:55
To: Tim Fathers <tim@xxxxxxxxxxxxx>
Cc: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Prepare question
Thanks Tim,
Charles also suggested getting multiple records.
If you would like to share a working example that would be appreciated.
This is the SQL statement fyi
QAUDStmt = 'SELECT journal_entry_type, +
syslog_facility, +
syslog_severity, +
syslog_event +
FROM TABLE +
(QSYS2.DISPLAY_JOURNAL(''QSYS'',''QAUDJRN'', +
GENERATE_SYSLOG => +
''RFC5424'', +
STARTING_TIMESTAMP => ? +
)) AS X +
WHERE syslog_event IS NOT NULL +
and substr(syslog_event,105, 3) <> ''Low''';
Thank you
Don
From: "Tim Fathers" <tim@xxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: "Don Brown" <DBrown@xxxxxxxxxx>
Date: 02/11/2021 06:42 AM
Subject: Re: SQL Prepare question
________________________________
Hi Don,
The SQL isn't included, but I would guess the prepare isn't necessary at all and the select statement could just be added as part of the DECLARE CURSOR with host variable(s), which would stay inside the loop, that way you get syntax checking too. I would also put the messy SQL bits in procedures, such as Open(...), Read(...) and Close(...), which I think makes it look more readable:
dow process <> 'STOP';
OpenLog(startTime);
dow ReadLog(desc);
// do something...
enddo;
CloseLog();
enddo;
A general-purpose SQL error checker procedure is also handy, which can be called after each SQL statement. Below illustrates the structure I usually use. Also, consider fetching a set of rows on each SQL fetch, this will speed up the process considerably. I usually hide this implementation inside the Read(...) procedure, such that it reads a set of rows and passes one back on each call until that set is exhausted and it reads another set. I can post an example of that if it would help.
Tim.
**FREE
ctl-opt copyright('')
datfmt(*ISO) datedit(*YMD/) timfmt(*ISO)
debug(*YES) option(*NODEBUGIO: *SRCSTMT)
main(main);
dcl-ds data_t qualified template inz;
//..log fields...
end-ds
//**************************************************************************************************************
// Entry point
//**************************************************************************************************************
dcl-proc main;
dcl-pi *N;
end-pi;
dcl-s startTime packed(8: 0);
dcl-ds data likeds(data_t) inz;
dow process <> 'STOP';
OpenLog(startTime);
dow ReadLog(desc);
// do something...
enddo;
CloseLog();
enddo;
end-proc;
//************************************************************************************
// Open the SQL cursor for the Log data.
//************************************************************************************
dcl-proc OpenLog;
dcl-pi *N extproc(*dclcase);
startTime packed(8: 0) const;
end-pi;
exec sql
declare LOG_CURSOR cursor for
select -- ...log data
from LOG_FILE...
where LOG_TIME >= startTime;
exec sql open LOG_CURSOR;
CheckState(SQLCOD: SQLSTT: SQLERM);
end-proc;
//**********************************************************************************
// Read log data...
//**********************************************************************************
dcl-proc ReadLog;
dcl-pi *N like(*IN) extproc(*dclcase);
data likeds(data_t);
end-pi;
exec sql fetch LOG_CURSOR into :data;
return CheckState(SQLCOD: SQLSTT: SQLERM);
end-proc;
//**********************************************************************************
// Close the cursor
//**********************************************************************************
dcl-proc CloseLog;
dcl-pi *N extproc(*dclcase);
end-pi;
exec sql close LOG_CURSOR;
CheckState(SQLCOD: SQLSTT: SQLERM);
end-proc;
//**********************************************************************************
// Check SQL state
//**********************************************************************************
dcl-proc CheckState;
dcl-pi *N like(*IN) extproc(*dclcase);
SQLCOD int(10) const;
SQLSTT char(5) const;
SQLERM char(70) const;
end-pi;
// Check for error and throw an exception if necessary..
// if ..error
// QMHSNDPM(....)
// endif;
// Return *OFF when no record read.
return (SQLSTT <> '02000');
end-proc;
//**********************************************************************************
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Don Brown via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Sent: 01 November 2021 20:16
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: RE: SQL Prepare question
Thanks Rob,
I am reading the audit journal - don't think I can attach a trigger ?
Yes I have a properly prepared SLQ statement in SQLStmt.
So you are saying I can omit the Prepare and Declare in the loop - only
need them once ?
Thanks
Don
From: "Rob Berendt" <rob@xxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date: 01/11/2021 09:17 PM
Subject: RE: SQL Prepare question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
Since you're using "open using" you probably have a properly prepared sql
statement. With that in mind you probably do not need your prepare or
declare statements within your loop.
But from a higher level I would question why not a trigger or some other
technique.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com<
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.dekko.com&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=6WwLRJQIgMD-3r5HaeD36oaAX4zWF5-NF1Hm9NDr2Cw&e=>
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Monday, November 1, 2021 4:50 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: SQL Prepare question
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
I have a program that reads a log, sleeps for a while then checks if there
are any more records.
I am doing;
DoW process <> 'STOP';
exec SQL prepare S0 from :SQLstmt;
exec SQL declare C0 cursor for S0;
exec SQL open C0 using :startTimeStamp;
exec SQL fetch C0 INTO :LogDS;
DOW SQLCOD = *ZEROS;
<processing entries received>
ENDDO;
exec SQL close C0;
enddo;
The value for startTimeStamp is incremented for any logs found and the
latest value will be used when the program wakes up.
Is this the correct and efficient way to do this ? ( I think I am just too
used to SETLL ==> Read )
Thank you to any comments
Cheers
Don
--
This email has been scanned for computer viruses. Although MSD has taken
reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use
of this email or attachments..
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=qQ4bRCVOkmkGfFlFA4d7UOoxkoEfBlrYUydx7gA2_vw&e=>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l<
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=sAE6VswO4cHK4ofrM-lgLHmyKLMkWYdqBmq_-8jbdvc&e=>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com<
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=yOUht_yAPwGV3DidUBMeFdDwnxMrVr_Ka9Mx8WmSp7E&e=>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=qQ4bRCVOkmkGfFlFA4d7UOoxkoEfBlrYUydx7gA2_vw&e=>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l<
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=sAE6VswO4cHK4ofrM-lgLHmyKLMkWYdqBmq_-8jbdvc&e=>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com<
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=yOUht_yAPwGV3DidUBMeFdDwnxMrVr_Ka9Mx8WmSp7E&e=>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l<
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=qQ4bRCVOkmkGfFlFA4d7UOoxkoEfBlrYUydx7gA2_vw&e=>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l<
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=sAE6VswO4cHK4ofrM-lgLHmyKLMkWYdqBmq_-8jbdvc&e=>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com<
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwMFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=x3rBwxMTlO3UcnVchBJYlkqPD8BpO91HbuZYQ_ZepUs&m=07_k4saCNyfe_SNC4XSSKSVbJHPbzqapsMgbt3AO8uQ&s=yOUht_yAPwGV3DidUBMeFdDwnxMrVr_Ka9Mx8WmSp7E&e=>
---
This email has been scanned for computer viruses. Although MSD has taken reasonable precautions to ensure no viruses are present in this email, MSD cannot accept responsibility for any loss or damage arising from the use of this email or attachments. sb
As an Amazon Associate we earn from qualifying purchases.