$ db2 describe table venkat

Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMPID SYSIBM INTEGER 4 0 Yes
EMPNAME SYSIBM VARCHAR 30 0 Yes

2 record(s) selected.

$ db2 "select * from venkat"

EMPID EMPNAME
----------- ------------------------------
1 a
2 b
3 c
4 d
5 e

5 record(s) selected.


$ more venkat.del ------------------------> created a file venkat.del with following lines.
6,"a"
7,"b"
8,"c"
9,"d"
10,"e"
11,"a"
12,"b"
1a,"c"
2a,"d"
13,"e"
14,"a"
3a,"b"
4a,"c"
15,"d"
16,"e"

$ db2 "load from venkat.del of del savecount 3 warningcount 3 insert into venkat"
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N The utility is beginning to load data from file
"/home/db2inst1/venkat.del".

SQL3500W The utility is beginning the "LOAD" phase at time "03/06/2011
21:31:58.541317".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3119W The field value in row "8" and column "1" cannot be converted to an
INTEGER value. A null was loaded.

SQL3119W The field value in row "9" and column "1" cannot be converted to an
INTEGER value. A null was loaded.

SQL3185W The previous error occurred while processing data from row "9" of
the input file.

SQL3502N The utility has encountered "3" warnings which exceeds the total
number of warnings allowed.

SQL3502N The utility has encountered "3" warnings which exceeds the total
number of warnings allowed.

As you can see i don't see any points of consistency being created or its messages displayed as per "savecount 3"!


Restarting load:
$ db2 "load from venkat.del of del savecount 3 warningcount 3 restart into venkat"
SQL3531I A LOAD RESTART has occurred.

SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N The utility is beginning to load data from file
"/home/db2inst1/venkat.del".

SQL3500W The utility is beginning the "LOAD" phase at time "03/06/2011
21:33:24.685058".

SQL3519W Begin Load Consistency Point. Input record count = "0".----------------------------------------> As you can see load operation is restarting from record 1, it was the last consistency point.

SQL3520W Load Consistency Point was successful.

SQL3119W The field value in row "8" and column "1" cannot be converted to an
INTEGER value. A null was loaded.

SQL3119W The field value in row "9" and column "1" cannot be converted to an
INTEGER value. A null was loaded.

SQL3185W The previous error occurred while processing data from row "9" of
the input file.

SQL3502N The utility has encountered "3" warnings which exceeds the total
number of warnings allowed.

SQL3502N The utility has encountered "3" warnings which exceeds the total
number of warnings allowed.


My Question:

1. Why there are no consistency points created as per "savecount 3" when i first ran the load operation?

VenKatG. From the Command Reference manual for the Load Utility:

Savecount n

Specifies that the load utility is to establish consistency points after every n rows. This value is converted to a page count, and rounded up to intervals of the extent size.

DB2 is converting the value to a value large enough to encompass the small amount of data being loaded so you never reached the converted savecount value.

So what you mean is , for example, if "Extent size=4" and each pagesize is big enough to hold 10 records.

Then when you do load operation with "Savecount 3" , DB2 will convert this into 3 pages and round up to Extent size which is 4. So point of consistency will be created after , (no.of pages in extent * no.of records per page)=4*10=40 records. So the point of consistency is created after every 40 records are inserted even though when the "savecount is 3"?

Please let me know if the above is true?

That is how I interpret the manual. Your math is correct ( 10 rows per page * number of pages in an extent) but the part about 'with Savecount 3, DB2 will convert this into 3 pages' isn't. With a Savecount 3 (rows), DB2 will be use the number of rows on a page * number of pages in an extent to come up with the actual Savecount (number of rows before a savecount).