Re: Actually using commitment control and QTEMP

On 17-Dec-2015 14:00 -0600, darren wrote:

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.

The DML can act on the non-journaled file having coded the WITH NC clause. In that way, although the TABLE is under isolation, the data is not under CmtCtl; if that is desirable. Or the workfile(s) can be created early, a COMMIT issued, and then the rest of the work performed under isolation; again, if desirable -- for lack of WITH NC capability on the DDL, the earlier COMMIT is required.

Thing is, this is QTEMP, so it will never be journaled.

The file *can be* journaled. Start Journal Physical File (STRJRNPF) can be invoked via a stored procedure or native CL command interpreter or bound CL.

As Luis suggests, use a Global Temporary Table (GTT) to get that effect by default. Be sure to read the full details of the DECLARE GLOBAL TEMPORARY TABLE, however, to ensure the best understanding of the effects of COMMIT and ROLLBACK [according to the specifications on the statement. IIRC, the GTT under isolation will implicitly journal to the QSQTTJRN in QRECOVERY [in the QRCY#### of the iASP].

I used both a "create table as" and tried building the table and
then using "insert". Both gave me the same failure.

The alternative to the GTT is to create the Default Journal (QDFTJRN) Data Area [AFaIK that *DTAARA feature was and is available for the QTEMP library, but I doubt there would be support for Start Journal Library (STRJRNLIB) for a QTEMP library; seems not for the library, but cleverly, probably due to the name starting with 'Q' per rc3 of msg CPD7006].

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.

For the DML, as noted above, using the WITH NC clause. But the file will have been created under isolation, so the ROLLBACK will /drop/ the table and thus lose all of the data, despite the data was not entered and updated under isolation.

Snippet:

exec sql call crtQDFTJRNtemp ; // to avoid WITH NC

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 drop table qtemp/PARTLIST ; // probably.?
// without the DROP, the commit will keep the work file

exec sql Commit;

The above edits are possible approaches. But really, there are a variety of options; depends on what should be the final effect for the /work file/ after a COMMIT or ROLLBACK, then code accordingly using what was noted, for either establishing journaling or not.