Re: Record count issue

On 19-Feb-2015 17:17 -0600, Darryl Freinkel wrote:

On 19-Feb-2015 16:17 -0600, Darryl Freinkel wrote:

We are trying to extract a file of 6.5 million records.

I have used SQL to split the table into 500K files splitting the
file by RRN.

Example:

from 1 to 500,000
from 500,000 to 1,000,000
from 1,000,001 to 1,500,000 and so on.

There are some deleted records in the file.

The big file has 435,802 delete records. We use the re-use of
deleted record space.

Question:

Has anyone had the issue where the record counts do not add up?

This is a V5R4 system.

The file is first cleared using the SQL DELETE FROM statement. It is
then populated by a SQL INSERT statement.

I then split that file into smaller files of 500,000 records to load
into excel.
<<SNIP>>

In response to the first question posed, I have only had an issue with the accuracy of record counts when a query was producing incorrect results; I do not recall ever having an issue with the count of active rows or the count of deleted rows.

I am unsure what the OP tried to explain was the problem and even unsure what was actually being done; the followup reply seems not to help me understand any better. Describing the scenario as scripted SQL requests with comments describing the data [active and deleted record counts] of the files being referenced in each statement probably would be much clearer and require few guesses\inferences about what is being done to which file and when.

However, based on other replies offered, perhaps the issue is that the "file is first cleared using the SQL DELETE" is the source of the problem. If the file [member] should be _cleared_ so as to be devoid of any records, with the effect being zero deleted records and zero active records, then use the Clear Physical File Member (CLRPFM) command instead of SQL. The SQL DELETE statement does not [necessarily] effect a _clear_ of all data from the member of the file, that statement should be expected merely to change the status of active records into deleted records; the so-called /fast delete/ features can be an exception.