|
Hi Marco
Very nice example, something I'm glad to see here.
The problem with either solution, I believe, is you have no way to get
the actual live data, *as Dan requested*. MESSAGE_FILE_DATA returns the
definition of the message, such as the formats of the substitution
variables, not the replacement values at the time.
SQL0551, the message in this case, has 3 substitution variables. GET
DIAGNOSTICS has only 2 in the DB2_TOKEN_COUNT. I just looked up
DB2_TOKEN - this is an SQL thing, and especially for DB2 in its several
flavors. I don't know a way to map it to MSGDTA.
Rob Berendt had suggested using the JOBLOG_INFO table function. With the
data you pull using GET DIAGNOSTICS, it should be fairly easy to get the
most recent instance of the message and get anything you want.
As to the CAAC, I'm not active on it at the moment, I've applied to
return. I suggest that you add a comment in the Idea and address your
concerns there with the CAAC response. It will be read, I am certain.
*Regards*
*Vern Hamberg*
IBM Champion 2025 <cid:part1.fcAnvIgL.507zvhVb@centurylink.net> CAAC
(COMMON Americas Advisory Council) IBM Influencer 2023
On 6/12/2025 3:19 PM, Marco Facchinetti wrote:
Or better (and tested):for
Dcl-s xSqe varchar(200) dim(*auto:200);
Dcl-s xDta varchar(200) dim(*auto:200);
Dcl-s xDtaR varchar(200) dim(*auto:200);
Dcl-s i int(10) inz;
Dcl-s i1 int(10) inz;
Dcl-s i2 int(10) inz;
Dcl-s p int(10) inz;
Dcl-s wString varchar(32000);
Dcl-s eS varchar(32000);
Dcl-s tMsg varchar(5000);
Dcl-s wKey varchar(20) inz('DB2_MESSAGE_ID=');
Dcl-s wTok varchar(20) inz('DB2_TOKEN_STRING=');
Dcl-s wMsg varchar(20);
exec sql SET OPTION COMMIT =*NONE , DATFMT =*ISO;
wString = 'DROP TABLE MULTEST/AUTMVE';
Exec Sql Execute Immediate :wString;
EXEC SQL GET DIAGNOSTICS :eS = ALL;
xSqe = %split(eS:';');
sorta xSqe;
for i = 1 to %elem(xSqe);
snd-msg xSqe(i) %target(*caller:1);
if %len(xSqe(i)) > %len(wKey) and
%subst(xSqe(i):1:%len(wKey)) = wKey;
wMsg = %subst(xSqe(i):%len(wKey) + 1);
exec sql SELECT char(coalesce(MESSAGE_SECOND_LEVEL_TEXT ,
MESSAGE_TEXT))
INTO :tMsg
FROM QSYS2.MESSAGE_FILE_DATA
WHERE MESSAGE_FILE_LIBRARY = 'QSYS'
AND MESSAGE_FILE = 'QSQLMSG'
AND MESSAGE_ID = 'SQL0204';
EndIf;
if %len(tMsg) > 0 and
%len(xSqe(i)) > %len(wTok) and
%subst(xSqe(i):1:%len(wTok)) = wTok;
xDta = %split(%subst(xSqe(i):%len(wTok) + 1):x'FF');
for i1 = 1 to %elem(xDta);
%elem(xDtaR) = 0;
xDtaR = %split(xDta(i1):'./');
for i2 = %elem(xDtaR) downto 1;
p += 1;
tMsg = %scanrpl('&' + %char(p):xDtaR(i2):tMsg);
EndFor;
EndFor;
snd-msg tMsg %target(*caller:1);
EndIf;
EndFor;
*inlr = *on;
return;
BTW looks like you're in CAAC board. I hope I am not disturbing you if I
ask how to contact Mrs. Ruvalcaba to get clarifications on the opinions
expressed on behalf of the CAAC on the IBM Ideas portal? I tried to ask
clarifications in the comments but never received a response.elements?
Best regards
--
Marco Facchinetti
Mr S.r.l.
Tel. 035 962885
Cel. 393 9620498
Skype: facchinettimarco
Il giorno gio 12 giu 2025 alle ore 21:28 Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> ha scritto:
Dcl-s xDta varchar(200) dim(*auto:200);
Dcl-s tMsg varchar(5000) inz('');
xDta = %split( xSqe(i):'./' + X'FF');
exec sql SELECT char(coalsce(MESSAGE_SECOND_LEVEL_TEXT , MESSAGE_TEXT)I
INTO :tMsg FROM QSYS2.MESSAGE_FILE_DATA
WHERE MESSAGE_FILE_LIBRARY = 'QSYS' AND MESSAGE_FILE = 'QSQLMSG' AND
MESSAGE_ID = 'SQL0204';
for i = 1 to %elem(xDta);
tMsg = %scanrpl('&' + %char(i):xDta(i):tMsg);
endfor;
--
Marco Facchinetti
Mr S.r.l.
Tel. 035 962885
Cel. 393 9620498
Skype: facchinettimarco
Il giorno gio 12 giu 2025 alle ore 18:50 Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> ha scritto:
Hi Marco
Interesting information. I tried your code on a box that doesn't have
MULTEST/AUTMVE. I got this stuff from the diagnostics -
DB2_MESSAGE_ID=SQL0204
DB2_ORDINAL_TOKEN_1=MULTEST.AUTMVE
DB2_ORDINAL_TOKEN_2=TABLE
DB2_TOKEN_COUNT=+2
DB2_TOKEN_STRING=MULTEST.AUTMVE█FILE
MESSAGE_TEXT=AUTMVE in MULTEST type *FILE not found.
SQL0204 has 3 substitution variables, as shown here -
SQL0204
QSQLMSG
QSYS
&1 in &2 type *&3 not found.
So have you been able to show 2nd level message text using these
related
I find it interesting, too, that maybe there are as many
DB2_ORDINAL_TOKEN_n as TOKEN_COUNT, so there could be another way to
pull the tokens out, although I don't yet see how to use them to form
MSGDTA for the system message.
*Regards*
*Vern Hamberg*
IBM Champion 2025<cid:part1.6PLlD1wW.b23hcerH@centurylink.net> CAAC
(COMMON Americas Advisory Council) IBM Influencer 2023
On 6/12/2025 10:57 AM, Marco Facchinetti wrote:
Dcl-s xSqe varchar(200) dim(*auto:200);--
Dcl-s i int(10) inz;
Dcl-s wString varchar(32000);
Dcl-s eS varchar(32000);
exec sql SET OPTION COMMIT =*NONE , DATFMT =*ISO;
wString = 'DROP TABLE MULTEST/AUTMVE';
Exec Sql Execute Immediate :wString;
EXEC SQL GET DIAGNOSTICS :eS = ALL;
xSqe = %split(eS:';');
sorta xSqe;
for i = 1 to %elem(xSqe);
snd-msg xSqe(i) %target(*caller:1);
EndFor;
Since I'm not authorized to AUTMVE I get:
DB2_MESSAGE_ID=SQL0551
DB2_MESSAGE_ID1=CPF2207
DB2_TOKEN_COUNT=+2
DB2_TOKEN_STRING=MULTEST.AUTMVE█FILE
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
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.
Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription
--questions.
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
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
