Re: GENERATE_SQL(): Was: Learning SEU, Source members . . .

On Mon, Oct 19, 2015 at 11:13 AM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

date: Mon, 19 Oct 2015 17:08:13 +0000

from: Justin Dearing <zippy1981@xxxxxxxxx>
subject: Re: GENERATE_SQL(): Was: Learning SEU, Source members . . .

On Mon, Oct 19, 2015 at 12:33 PM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:



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
rowid somehow?

Here is a better way to get your error feedback that will likely work

well, by creating a table UDF wrapper to run your SQL commands:
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
caller.
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.

Mike

P.S. I do a lot of processing like that, but not executing a CALL command
to stored procedures. On second thought, you may have difficulty forming a
string to perform a CALL to a procedure that uses other than VARCHAR
parameters. If that is the case for GENERATE_SQL, make yourself a specific
SQL Table UDF designed solely as a wrapper for procedure GENERATE_SQL.