date: Mon, 19 Oct 2015 17:08:13 +0000
from: Justin Dearing <zippy1981@xxxxxxxxx>well, by creating a table UDF wrapper to run your SQL commands:
subject: Re: GENERATE_SQL(): Was: Learning SEU, Source members . . .
On Mon, Oct 19, 2015 at 12:33 PM Charles Wilt <charles.wilt@xxxxxxxxx>
So, that least me to a more generic version of my last question. If a SQL
statement fails, whats the best way to associate that with particular rows
in the joblob? Capturing timestamps before the message, or can i get the
Here is a better way to get your error feedback that will likely work
1) Create an SQL Table UDF, named something like RUN_SQL, that accepts a
long VARCHAR input parameter SQL_STATEMENT. The Table UDF should return
columns SQL_CODE, SQL_STATE, and MESSAGE_TEXT as output. Inside the SQL
Table UDF, feed input parameter SQL_STATEMENT to EXECUTE IMMEDIATE. After
the EXECUTE IMMEDIATE, run GET DIAGNOSTICS to get the SQL_CODE, SQL_STATE,
and MESSAGE_TEXT resulting from the EXECUTE IMMEDIATE. Perform "return
values ( SQL_CODE, SQL_STATE, MESSAGE_TEXT )" to return the results to the
2) When you want to generate SQL, pass your "CALL QSYS2.GENERATE_SQL..."
string to the RUN_SQL table UDF in the form of a table UDF query, it will
run your SQL command, and return the results, thereby negating the need to
look at the job log in most cases.
Table function RUN_SQL would be able to handle most SQL commands, but not
a SELECT query.