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

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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