Re: commit inserts made in SQL procedure

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.


On Tue, Sep 5, 2017 at 12:36 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

COMMIT = *CHG

Doesn't auto commit.

You'd need to issue the commit yourself.

The system doesn't have an auto-commit AFAIK, closest is COMMIT = *NONE.

I believe there's an "auto commit" in various tools. For instance, ACS

Run

SQL scripts has a "enable true autocommit" setting. But that's a function
of the JDBC driver.
; basically after every successful statement, a COMMIT is implicitly run

by

the driver.



On Tue, Sep 5, 2017 at 10:19 AM, Steve Richter <stephenrichter@xxxxxxxxx

wrote:

running an SQL procedure that creates a new table from the resulting

rows

of a select statement. The code runs, but then when I signoff the

system

is deleting the table. I guess because of commitment control.

how do I commit changes made in an SQL procedure?

I am using "set option commit = *chg" . Which I read says the sql
procedure should auto commit its changes. But the auto commit is not
happening.

Running the COMMIT command, the system comes back and says COMMIT defn

not

found. But I WRKJOB option 16 commitmet control status, and there are

two

committment definitions.

Commitment
Definition Text
APLUS Activation-group-level
QDBAGILE Activation-group-level


here is the SQL procedure:
CREATE or replace PROCEDURE SRICHTER/cca_hsdetCca(
)
LANGUAGE SQL
MODIFIES SQL DATA
set option commit = *chg
BEGIN atomic

declare vSqlCode decimal(5,0) ;
declare sqlCode int DEFAULT 0 ;
DECLARE VMSG CHAR(256) ;
DECLARE VCMDSTR CHAR(256) ;
DECLARE DUMMY INTEGER ;
declare vPrCatNum int ;

create table srsql/hsdetcca as (
select a.*, b.cmcscl cscl, b.cmcssc cssc
from hsdetv1 a
join cusms b on a.csno = b.cmcsno and b.cmcono = 1
where b.cmcscl = '05' and year(a.ivdate) >= 2016
and a.litp = 'I' or ( a.litp = 'C' and a.itno = '&6' )
) with data ;

END
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD