On 5/30/2017 11:00 AM, Dan wrote:
^ This. While I appreciated the QSYS2.JOBLOG_INFO tip, I avoided it
because it would have meant writing an RPG program. The *DIAG message I
wanted to retrieve was considered helpful, but certainly not necessary, and
I could not have justified writing a new program to flush that out.
-snip-
Now that I think about it, I suppose one workaround is to do a CREATE TABLE
x WITH <QSYS2.JOBLOG_INFO query> in a RUNSQL command, and DCLF & RCVF that
table in the CL program. Is there anything that couldn't accomplish that
embedded SQL would?
Here's an example of CLP + REXX that will do what you're looking for.
Both members are imaginatively called SQLTEST4.
pgm
dcl &buffer *char 1057
dcl &first_msg *char 15 value('test_msg begins')
dcl &len *int (4) value(10)
dcl &rc *int (2)
dcl &firstkey *char 4
dcl &lastkey *char 4
dcl &from_pgm *char 10
dcl &msg_id *char 7
dcl &msg_type *char 13
dcl &msg_text *char 1024
dcl &work_msg *char 50
/* all errors must occur after this checkpoint message */
sndpgmmsg msg(&first_msg) topgmq(*same) keyvar(&firstkey)
/* rmvmsg msgkey(&firstkey) */
/* intentionally falls over */
runsql 'update NOFILE set COLA = COLB' +
commit(*none) option(*list) seclvltxt(*yes)
/* we expect this one */
monmsg cpf0000 exec(do)
sndpgmmsg msg('test_msg error') topgmq(*same) keyvar(&lastkey)
/* rmvmsg msgkey(&lastkey) */
/* tell the REXX proc where to stop looking */
chgvar &len %size(&first_msg)
call QREXQ ('A' &first_msg &len 0 &rc)
/* execute the SQL statement to get the diags */
strrexprc sqltest4 srcfile(buck/qrexsrc)
/* the diags are in the REXX queue */
/* pull them out one by one */
chgvar &len %size(&buffer)
loop_top:
call QREXQ ('P' &buffer &len 0 &rc)
if (&rc *ne 0) (goto endpgm)
chgvar &from_pgm %sst(&buffer 1 10)
chgvar &msg_id %sst(&buffer 12 7)
chgvar &msg_type %sst(&buffer 20 13)
chgvar &msg_text %sst(&buffer 34 1024)
chgvar &work_msg (&msg_id *cat ' ' *cat &from_pgm *cat ' ' +
*cat %sst(&msg_text 1 35))
sndpgmmsg &work_msg
goto loop_top
endpgm:
return
enddo
sndpgmmsg 'No errors trapped'
return
endpgm
/* SQLTEST4 */
/* example of using SQL to pass job log diag messages back to a CL
program */
/* leave mixed case */
parse pull checkpoint_message
address EXECSQL
execsql "set option commit = *none"
/* CAST to eliminate VARCHAR and CCSID(1200) */
sql_stmt = ,
"select cast(substr(from_program, 1, 10) as char(10)) as
from_program, ",
"cast(coalesce(message_id, ' ') as char(7)) as message_id, ",
"cast(message_type as char(13)) as message_type, ",
"cast(message_text as char(1024)) as message_text ",
"from table(QSYS2.JOBLOG_INFO('*')) j ",
"where ordinal_position >= ",
"(select max(ordinal_position) ",
"from table(qsys2.joblog_info('*')) m ",
"where message_text = '"checkpoint_message"') ",
"order by ordinal_position desc"
execsql "prepare S1 from :sql_stmt"
execsql "declare C1 cursor for S1"
execsql "open C1 "
do while 1 = 1
execsql "fetch C1 into :from_program, :message_id, :message_type, ",
":message_text"
buffer = from_program"*"message_id"*"message_type"*"message_text
if rc <> 0 then do
if sqlstate = "02000" then
leave
else do
say "SQLSTATE = " sqlstate
leave
end
end
/* put the value on the external queue */
/* This will allow the calling CLP to retrieve it */
queue (buffer)
end
execsql "close C1"
exit
midrange.com
