Re: Record count issue

Daryl,
If you are using the Ruse Deleted Records function on your table, you can
not accurately select by rrn as the deleted records are still physically in
the file and taking up an RRN even though you can not access them.
If you have a file of 100 records and records 5, 10 and 30 are deleted,
selecting by RRN for RRN = 1 to 20 will only give you 18 records.

Jeff Young
Sr. Programmer Analyst

On Fri, Feb 20, 2015 at 7:33 AM, <rob@xxxxxxxxx> wrote:

CREATE TABLE ROB.DARRYL (MYCOL CHAR (5 ) NOT NULL WITH DEFAULT)
INSERT INTO ROB.DARRYL VALUES('A')
INSERT INTO ROB.DARRYL VALUES('B')
INSERT INTO ROB.DARRYL VALUES('C')
delete from rob.darryl
3 rows deleted from DARRYL in ROB.
SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER,
NUMBER_ROWS, NUMBER_DELETED_ROWS
FROM qsys2.syspartitionstat
WHERE system_table_schema='ROB' and system_table_name='DARRYL'


....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...1

LIBRARY FILE SYSTEM_TABLE_MEMBER NUMBER_ROWS
NUMBER_DELETED_ROWS
NAME NAME
ROB DARRYL DARRYL 0
3
******** End of data ********

delete from, even without a where clause, is not equal to clrpfm

But, are you're saying that the reuse deleted rows should have consumed
those deletions, right? For example if I insert 5 rows I should see:

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...1
LIBRARY FILE SYSTEM_TABLE_MEMBER NUMBER_ROWS
NUMBER_DELETED_ROWS
NAME NAME
ROB DARRYL DARRYL 5
0
******** End of data ********

Or, are you saying that the NUMBER_ROWS from OriginalTable is not equal to
the sum of the NUMBER_ROWS of each of the table splits?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Darryl Freinkel <dhfreinkel@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 02/19/2015 06:17 PM
Subject: RE: Record count issue
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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.

No records are added, updated or deleted after the initial insert.
On Feb 19, 2015 5:30 PM, "Jim It" <jim_it@xxxxxxxxxxx> 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.

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

Darryl,

The question that must be asked to determine the answer; Is the deletion
of records from the file, a normal part of the operations there? If so,
the file must be in a quiescent state for this method to work.

Jim
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.