Re: How to retrieve second level text from message in SQL stored procedure -- MIDRANGE-L

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):

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 for
clarifications in the comments but never received a response.

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 elements?

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 related
questions.