Search code examples
sqldb2ibm-midrangedb2-400rpgle

DB2 SQL How to get the last executed SQL-Statement with GET DIAGNOSTICS?

I want to call a procedure in RPG on IBM i with SQLSTATE and with a variable text.

getSQLMessage(SQLSTT: text)

The variable text should be the last executed sql statement before the procedure call. Is there a opportunity to get it like this:

EXEC SQL GET DIAGNOSTICS CONDITION 1 :text = last executed sql statement

Or maybe someone knwos another solution for my problem?

Thanks a lot!

Solution

  • You can't use GET DIAGNOSTICS, but you can first get the JobLog

    DSPJOBLOG OUTPUT(*OUTFILE) OUTFILE(QTEMP/ERR_LOG)

    then get the last SQL Error:

    Select Qmhmf, 
       Qmhmid, 
       Qmhmdt                                  
  From Qtemp.Err_Log                                         
 Where Qmhsev >= 20                                           
   And Substr(Qmhmid, 1, 3) In ('CPA' , 'CPD' , 'CPF' , 'SQL')
Order By Rrn(Err_log) Desc                                   
Fetch First 1 Rows Only