Justin,
Yes. I think you want to use QSYS2.JOBLOG_INFO()
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.JOBLOG_INFO()%20UDTF
You will need latest and greatest PTF's.
I use a statement something like this - note the CCSID conversions.
Select Cast(joblog.message_text As Char(70) Ccsid 37),
Cast(joblog.message_second_level_text As Char(924) Ccsid 37)
Into :jb_message, :jb_cause
From Table(qsys2.joblog_info('*')) joblog
-- I don't think this will work for the conversion failure
Where message_id = :SQLMsgID
And severity >= 30
Order By ordinal_position Desc
Fetch First 1 Rows Only;
Also you have to convert the SQL code to the message id.
There are other posts that cover this.
Fun stuff.
Vicki Wilson
----------------------------------------------------------------------
message: 1
date: Wed, 30 Dec 2015 09:58:47 -0600
from: Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
subject: RE: SQLSTATE 23513, get constraint name?
The corresponding SQLCODE message (SQL0545) gives the same text as what GET DIAGNOSTICS EXCEPTION gave me. The message text in the joblog is also the same. The Cause listed in the joglog does include the constraint name. Any painless way to get that programmatically?
-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxx]
Sent: Wednesday, December 30, 2015 8:08 AM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: SQLSTATE 23513, get constraint name?
Take a look at the job log - there are often more messages that were logged before the one you are seeing. And getting an SQLSTATE, there will definitely be some kind of messages out there
Also check SQLCODE for a value - that value is often made part of a message ID - for example, if SQLCODE is 367, say, the message ID will be SQL0367.
But that message, if any, will be part of the job log.
This is a WAG, but based on seeing lots of messages logged in other error conditions for SQL.
HTH
Vern
------------------------------
message: 2
date: Wed, 30 Dec 2015 12:16:29 -0430
from: Luis Rodriguez <luisro58@xxxxxxxxx>
subject: Re: SQLSTATE 23513, get constraint name?
Justin,
I don't know if you got my prior post about using GET_DIAGNOSTICS...
Suppose you create the following table:
*Create table testdiag ( f1 char not null with default '', constraint
chk_testdiag check (f1 <> 'x') ) *
You can use the GET DIAGNOSTICS sentence into a program like this:
* HDebug(*yes) DC_Name S 128 Varying
/free exec sql insert into Testdiag (F1) values('X') ;
exec sql get diagnostics condition 1 :C_Name = Constraint_Name
; dump ;
return;
*
In this case, C_Name would return 'CHK_TESTDIAG'
Is this what you are asking about?
Regards,
Luis Rodriguez
midrange.com
