Re: CPYF question

[CRPence <CRPbottle@xxxxxxxxx>]

  Of course this is effectively a case of a work-file, written as 
/extract file/, according to original scenario; difficult to infer 
otherwise.  If it were not, then the original data should be just as 
capable for most whatever operation; i.e. a copy of a subset is 
inherently a work-file, because the data could have been reported on 
directly from the original file.  And while _I agree_ with a concern 
for journaling, I disagree that making a copy of the data would be 
done better with "reading with a cursor and committing periodically" 
because...

  If the application is just going to COMMIT periodically for the 
sake of performance without regard to actual recovery purposes, then 
the better solution is to just avoid logging altogether.  Although 
in production processing commitment control is ideal to ensure 
atomicity, there is little reason for logging for [what intends to 
be] an essentially atomic operation in creating a copy of the data. 
 In the case of making a copy, either the statement completes or it 
does not. Without logging & CmtCtl avoids any need for tricks to 
speed up the processing by committing changes, as though the 
application were designed to handle recovery if the program is 
interrupted, when most probably it is not; i.e. the program will 
likely require a DROP TABLE and then starting over [ever since the 
first commit], just as if done without any logging.

  Other databases do not have this option of not logging [for DML], 
and thus such tricks are required to avoid reaching their logging 
limits.  For the DB2 for i, the reason for not logging is typically 
performance, for which turning off logging is a better resolution in 
many cases.

  If the TABLE is created into a SCHEMA or a library enabled for 
QDFTJRN automatic journaling, then issue a separate CREATE TABLE and 
then issue ENDJRNPF.  Then use a single DML statement to make the 
data copy.  Finally if journaling is of value, which I doubt from 
the /extract file/ reference [as being a subset of data], then 
STRJRNPF could be performed after the copy.

  Note:  This all assumes CPYF was originally be used or considered 
acceptable for use, a tool for which there is effectively no 
isolation support.  If one were to attempt to, with their use of 
SQL, introduce isolation to ensure integrity of the copied data, 
then the only option is commitment control which requires logging.

Regards, Chuck

Charles Wilt wrote:
> The only issue I have with this, is when the destination table is
>  journalled, there's a huge performance hit if you write to it
> without commitment control (unless you have the v5r4+ HA Journal
> Performance LICPGM or prior to v5r4 the Journal Caching PPRQ).
> If you're writing lots of records, trying to do them as a single
> commit transaction can also be a killer.
>
> At that point, you're better off reading with a cursor and
> committing periodically.
>
> The write should be done with either RPG op-codes or via SQL
> batch inserts.
>
> Granted, many times you're copying data like this for work files
> which don't need to journalled.  But if that isn't the case...
>
> On Thu, May 7, 2009 at 3:48 PM, CRPence wrote:
> > In making a final copy as TABLE, as a subset of data from a 
> > database file, there would be little reason to move any data 
> > anywhere besides directly from the source table to the target
> > table. No FETCH means no array.  Just choose an SQL statement
> > that does it all, and avoid any cursor and the additional
> > coding for array & movement of data.
> >
> > Regards, Chuck