Points of consistency are not getting created when running Load with Savecount!

[Venkat G]

$ 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


$ 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 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?

[vijay2082]

Hi Venkat,

Your output has got all answers hidden.

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".

As you see first consistency point is created with record count 0, after which you get 3 consecutive errors/warning and your load gets terminated. Try to load from a good del file and provide fresh records to avoid and error/warning for first 3 record and then see the consistency being created.

Cheers, Vijay

[Venkat G]

$ more venkat.del
6,"a"
7,"b"
8,"c"
9,"d"
10,"e"
11,"a"
12,"b"----------->My first 7 records are good,they satisfy the table definition.
1a,"c"
2a,"d"
13,"e"
14,"a"
3a,"b"
4a,"c"
15,"d"
16,"e"

If you look at the error output you can see the first record that is failing is record 8 (1a,"c"). So there are already 7 good records, so as per savecount 3 there should have been 2 points of consistency already being created but it is not happening. Can you please tell me why?

$ 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 anINTEGER 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.