Re: CPYF question

sjl wrote:

We are using a CPYF to select a subset of our G/L detail file
(that has 140,000,000 records) into an extract file. It is
taking a loooong time, as you can imagine. The record selection
criteria will be different each quarter, but it is basically
selecting the posted G/L records for the quarter (a range of
range of period numbers) for the current fiscal year.

Anyone have some suggestions for speeding it up? Maybe using
OPNQRYF with a CPYFRMQRYF ?

Why not just form an SQL [INSERT INTO SELECT FROM or CREATE TABLE AS SELECT] statement and let the optimizer decide how best to get the data, instead of the more brute-force method of CPYF? Of course OPNQRYF similarly has such intelligence, but limited to the CQE versus what SQE might be able to improve with data\index statistics.

Anyhow... Such a question is best accompanied by some details which better describe both the file, data, and selection. Could a bare-bones CREATE TABLE, a description of any effective CREATE INDEX for the data, the cardinality of the data being selected, and the actual\full CPYF invocation be included to better describe what is done presently? If the TOFILE() is an existing file, then the same details as for the FROMFILE() [except of course no data information].?

FWiW given an approximation of the amount of data to be selected, the TOFILE() can be pre-allocated using the SIZE() and ALLOCATE() parameters. That can improve performance by eliminating /extents/ at run-time. Pre-allocation is more important for concurrent copy. Concurrent copiers is another way to improve some requests, especially knowing something about the cardinality of key data, or at least relative logical groupings of keyed data; logical groupings of sequential\arrival for full scans is an alternative. With SQL parallel access, that improvement effected automatically by the DB engine instead of multiple copy requests across logical groups.

Regards, Chuck