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

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
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.