Re: commit inserts made in SQL procedure

On 9/5/2017 1:03 PM, Steve Richter wrote:

the problem with the system deleting the table occurs when I run the SQL
procedure from STRSQL. When I run it from an SQLRPGLE program there is no
problem.

Why did you put set commit = *CHG in the stored procedure?

My question is intended to be a bridge to this perhaps controversial
statement: One cannot spray commitment control onto an existing
application. The app must be designed to use it from the start.

If the application you're working with uses CC, you'd know it by dint of
the COMMIT and ROLLBACKs all over the code. If that's the case, then
what you want is for your job to match the database. If STRSQL is where
the issue is noticeable, then STRSQL and press F4. There, you'll see a
parameter for setting the appropriate commitment control level. It
needs to match the stored procedure. And the SP really ought to have a
COMMIT or ROLLBACK rather than let the system try to decide what to do.

If it turns out that like so many of us, you have a 'naked' DB2, without
commitment control, then the STRSQL and SP /still/ need to match, but in
this case you'd change the SP to *NONE and not worry about COMMIT.