Hi Darren,
There are 2 ways:
1. Create your temporary table with DECLARE GLOBAL TEMPORARY TABLE. UPDATE,
INSERT and DELETE statements against these tables are performed without
commitment control
2. Add WITH NC (= With No Commit) at the end of the INSERT, UPDATE, DELETE
Statements against your temporary tables (independent whether they are
created with CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE.
3. Additionally ... check whether a temporary table is really necessary. In
my experience 90 % of the formerly used temporary tables can be replaced
with other methods, mostly Views or MQT (Maintained Temporary Tables)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
darren@xxxxxxxxx
Gesendet: Thursday, 17.12 2015 21:00
An: midrange-RPG RPG message board
Betreff: Actually using commitment control and QTEMP
I've actually found a use for turning on commitment control in my SQLRPGLE
program. So I did that easy enough with:
exec sql set option COMMIT=*CHG;
Then I tried to build the workfile that will be the source for a number of
updates and inserts. It failed with SQL7008, which basically says I can't
run commitment control on a file that's not journaled. Thing is, this is
QTEMP, so it will never be journaled. I used both a "create table as" and
tried building the table and then using "insert". Both gave me the same
failure.
Do I have to turn off commitment control to work with QTEMP? If so, how do
I do that? I'd like it on for when my program updates the production files
its designed to work with.
Snippet:
exec sql create table QTEMP/PARTLIST as
(select *
from FRTL01 FRT
where exists
(select * from FRT b
where RWRKC in (31380,31385)
and RPROD=FRT.RPROD and RTWHS=FRT.RTWHS)
) with data;
if SQLCOD<>0;
return;
endif;
exec sql update FRTL01 FRT
set ROPNO=ROPNO*10
where exists
(select * from QTEMP/PARTLIST PL
where RPROD=FRT.RPROD and RTWHS=FRT.RTWHS)
;
if SQLCOD<>0;
exec sql rollback;
return;
endif;
exec sql Commit;
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
