Bug in LOAD command?

[JS]

EE instance DB2 v7.2.0 fixpack 3 on WIN2K.
I select some data from a table A and write it to a file using the COALESCE
function and whitespace as the null character:
eg coalesce(col1, ' '). This column has 10,000 nulls in table A.
Select count(*) from table A where col1 is null=10,000
Using the load utlity, I load the data file as follows into table B which
has not null constraint on col1:
LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
The load only generates 9,954 exceptions. Would like to understand why.
Thank you

[Rhino]

"JS" <fy***@hotmail.com> wrote in message
news:e9**************************@posting.google.c om...

EE instance DB2 v7.2.0 fixpack 3 on WIN2K.
I select some data from a table A and write it to a file using the COALESCE function and whitespace as the null character:
eg coalesce(col1, ' '). This column has 10,000 nulls in table A.
Select count(*) from table A where col1 is null=10,000
Using the load utlity, I load the data file as follows into table B which
has not null constraint on col1:
LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
The load only generates 9,954 exceptions. Would like to understand why.
I'm not clear on what you're trying to do, mostly because you haven't fully
described tables A and B, specifically what columns are in each and how
those columns are defined.

It *sounds* like you're saying that you are only selecting one column, col1,
from Table A and are trying to load it into a Table B which also has only 1
column. Is that right? If so, what is the difference between Table A and
Table B in the first place; if they are the same table, why copy from one to
another?

Also, this: Select count(*) from table A where col1 is null=10,000 is definitely going to be a problem. The expression count(*) is going to
return a single number, the number of rows in Table A that satisfy the where
clause. The where clause doesn't make sense though: do you want the rows
where col1 is null or the rows where col1 = 10000? I suspect you really want
to copy over *all* of the rows, (not the NUMBER of rows), so that you don't
want that count(*) expression at all and you don't want the where clause
either. But I'm really not sure.

I don't understand this remark at all: The load only generates 9,954 exceptions. Would like to understand why.

By saying "only", it sounds like you are complaining because you wanted
*more* exceptions. Most people would be trying to find out how to get the
number of exceptions down to 0 to show that all of their data got
transferred over successfully.

Again, I'm really not sure what you're trying to do. Perhaps if you could
explain that in a bit more detail, someone could help you accomplish that.

Rhino

[JS]

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<gL*********************@news20.bellglobal.co m>...

"JS" <fy***@hotmail.com> wrote in message
news:e9**************************@posting.google.c om...

EE instance DB2 v7.2.0 fixpack 3 on WIN2K.
I select some data from a table A and write it to a file using the

COALESCE

function and whitespace as the null character:
eg coalesce(col1, ' '). This column has 10,000 nulls in table A.
Select count(*) from table A where col1 is null=10,000
Using the load utlity, I load the data file as follows into table B which
has not null constraint on col1:
LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
The load only generates 9,954 exceptions. Would like to understand why.

I'm not clear on what you're trying to do, mostly because you haven't fully
described tables A and B, specifically what columns are in each and how
those columns are defined.

It *sounds* like you're saying that you are only selecting one column, col1,
from Table A and are trying to load it into a Table B which also has only 1
column. Is that right? If so, what is the difference between Table A and
Table B in the first place; if they are the same table, why copy from one to
another?

Also, this:

Select count(*) from table A where col1 is null=10,000

is definitely going to be a problem. The expression count(*) is going to
return a single number, the number of rows in Table A that satisfy the where
clause. The where clause doesn't make sense though: do you want the rows
where col1 is null or the rows where col1 = 10000? I suspect you really want
to copy over *all* of the rows, (not the NUMBER of rows), so that you don't
want that count(*) expression at all and you don't want the where clause
either. But I'm really not sure.

I don't understand this remark at all:

The load only generates 9,954 exceptions. Would like to understand why.

By saying "only", it sounds like you are complaining because you wanted
*more* exceptions. Most people would be trying to find out how to get the
number of exceptions down to 0 to show that all of their data got
transferred over successfully.

Again, I'm really not sure what you're trying to do. Perhaps if you could
explain that in a bit more detail, someone could help you accomplish that.

Rhino

Perhaps badly explained, load command should have read:
LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)

>>>>>>>NULL INDICATORS (0)<<<<<<<<<<<<<<<<

ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL

Select count(*) from table A where col1 is null
Result=10,000
This means the result of the sql statement is 10,000, that is 10,000
rows in table A have null values for col1

Doesnt matter what the difference is between the two columns as long
as the two tables have the same structure, the point is once the load
is completed, I should get 10,000 exceptions in the exception table,
instead I get only 9,954.

[Rhino]

"JS" <fy***@hotmail.com> wrote in message
news:e9**************************@posting.google.c om...

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<gL*********************@news20.bellglobal.co m>...

"JS" <fy***@hotmail.com> wrote in message
news:e9**************************@posting.google.c om...

EE instance DB2 v7.2.0 fixpack 3 on WIN2K.
I select some data from a table A and write it to a file using the

COALESCE

function and whitespace as the null character:
eg coalesce(col1, ' '). This column has 10,000 nulls in table A.
Select count(*) from table A where col1 is null=10,000
Using the load utlity, I load the data file as follows into table B which has not null constraint on col1:
LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL
The load only generates 9,954 exceptions. Would like to understand

why.

I'm not clear on what you're trying to do, mostly because you haven't fully described tables A and B, specifically what columns are in each and how
those columns are defined.

It *sounds* like you're saying that you are only selecting one column, col1, from Table A and are trying to load it into a Table B which also has only 1 column. Is that right? If so, what is the difference between Table A and
Table B in the first place; if they are the same table, why copy from one to another?

Also, this:

Select count(*) from table A where col1 is null=10,000

is definitely going to be a problem. The expression count(*) is going to
return a single number, the number of rows in Table A that satisfy the where clause. The where clause doesn't make sense though: do you want the rows
where col1 is null or the rows where col1 = 10000? I suspect you really want to copy over *all* of the rows, (not the NUMBER of rows), so that you don't want that count(*) expression at all and you don't want the where clause
either. But I'm really not sure.

I don't understand this remark at all:

The load only generates 9,954 exceptions. Would like to understand

why. By saying "only", it sounds like you are complaining because you wanted
*more* exceptions. Most people would be trying to find out how to get the number of exceptions down to 0 to show that all of their data got
transferred over successfully.

Again, I'm really not sure what you're trying to do. Perhaps if you could explain that in a bit more detail, someone could help you accomplish that.
Rhino

Perhaps badly explained, load command should have read:
LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)>>>>>>>>NULL INDICATORS (0)<<<<<<<<<<<<<<<<

ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL

Select count(*) from table A where col1 is null
Result=10,000
This means the result of the sql statement is 10,000, that is 10,000
rows in table A have null values for col1

Okay, I get this part now.
Doesnt matter what the difference is between the two columns as long
as the two tables have the same structure, the point is once the load
is completed, I should get 10,000 exceptions in the exception table,
instead I get only 9,954.

Well, perhaps it shouldn't matter, in theory, how the tables look but it's
easier to debug a problem if you understand what the other guy is doing. If
you want to tie our hands behind our backs, it may hurt the quality of the
answer you get ;-)

Frankly, I'm still scratching my head because what you're describing doesn't
seem to add up. On one hand, I'm getting the impression that LOAD.TEST
contains 10,000 lines/rows, each containing a single blank. (I'm assuming
that col1 is a char/varchar column because all the arguments in the COALESCE
function need to be the same class of data, e.g. character, numeric, etc.
The second argument is a char so the first must also be. The result of
coalesce(null, '') is ''.) The LOAD statement says you are only reading one
column from LOAD.TEST. But this doesn't make a lot of sense to me since you
need a key to tell LOAD which row of Table B it is being replaced by each
row of LOAD.TEST. (If all of the values in LOAD.TEST are blank, they are
obviously not primary key values.) You are also telling the LOAD statement
that you have 2 million rows, not 10,000.
If you can clarify that, maybe I can suggest a solution to your problem. If
not, maybe someone else can make sense of what you're trying to do.

Rhino

[JS]

LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
NULL INDICATORS (0)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL

Ok, its like this:
TABLE A has 10,000,000 rows, I only want to load the first 2,000,000.
Among these 2 million rows, 10,000 of them have space
(NULLINDCHAR=X20) or null values.
If the column value is less than varchar(20), I want the load to strip
the
blanks when replacing in TABLE B.
NULL INDICATORS (0) means that the col1 in TABLE B cannot accept null
values, therefore the load utility should reject any row that has null
as it value.
The original question still remains:
Why does the load utility reject less than 10,000 rows?

[Rhino]

"JS" <fy***@hotmail.com> wrote in message
news:e9**************************@posting.google.c om...

LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
NULL INDICATORS (0)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL

Ok, its like this:
TABLE A has 10,000,000 rows, I only want to load the first 2,000,000.
Among these 2 million rows, 10,000 of them have space
(NULLINDCHAR=X20) or null values.
If the column value is less than varchar(20), I want the load to strip
the
blanks when replacing in TABLE B.
NULL INDICATORS (0) means that the col1 in TABLE B cannot accept null
values, therefore the load utility should reject any row that has null
as it value.
The original question still remains:
Why does the load utility reject less than 10,000 rows?

Okay, that helps - somewhat. If I understand you, you really only want to
load the 1,990,000 rows (2 million minus 10,000) that don't have a blank in
columns 1-11 of LOAD.TEST but you are actually loading 1,990,046 records and
want to know why your technique is failing to exclude those extra 46
records.

It seems very likely that those extra 46 rows actually have values that you
aren't expecting. As an experiment, put only the 10,000 rows in LOAD.TEST
and see if they all get excluded or if some of them slip through. If some
slip through, read LOAD.TEST in a hex editor and see if you can find any
values that aren't what you're expecting. I expect that the values in Table
A aren't quite what you think they are and therefore the values in LOAD.TEST
aren't quite what you're expecting either.

If all 10,000 records get excluded, I'm not sure what to suggest but you've
probably eliminated the possibility that LOAD has a bug....

Rhino

[JS]

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<J%*******************@news20.bellglobal.com> ...

"JS" <fy***@hotmail.com> wrote in message
news:e9**************************@posting.google.c om...

LOAD FROM LOAD.TEST OF ASC
MODIFIED BY STRIPTBLANKS NULLINDCHAR=X20
METHOD L (1 11)
NULL INDICATORS (0)
ROWCOUNT 2000000
MESSAGES LOAD.REPORT
REPLACE INTO TABLE2
FOR EXCEPTION TABLEX2
STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL

Ok, its like this:
TABLE A has 10,000,000 rows, I only want to load the first 2,000,000.
Among these 2 million rows, 10,000 of them have space
(NULLINDCHAR=X20) or null values.
If the column value is less than varchar(20), I want the load to strip
the
blanks when replacing in TABLE B.
NULL INDICATORS (0) means that the col1 in TABLE B cannot accept null
values, therefore the load utility should reject any row that has null
as it value.
The original question still remains:
Why does the load utility reject less than 10,000 rows?

Okay, that helps - somewhat. If I understand you, you really only want to
load the 1,990,000 rows (2 million minus 10,000) that don't have a blank in
columns 1-11 of LOAD.TEST but you are actually loading 1,990,046 records and
want to know why your technique is failing to exclude those extra 46
records.

It seems very likely that those extra 46 rows actually have values that you
aren't expecting. As an experiment, put only the 10,000 rows in LOAD.TEST
and see if they all get excluded or if some of them slip through. If some
slip through, read LOAD.TEST in a hex editor and see if you can find any
values that aren't what you're expecting. I expect that the values in Table
A aren't quite what you think they are and therefore the values in LOAD.TEST
aren't quite what you're expecting either.

If all 10,000 records get excluded, I'm not sure what to suggest but you've
probably eliminated the possibility that LOAD has a bug....

Rhino

NULL INDICATORS (0)
changed it to NULL INDICATORS (1), now it works, thanks anyways, still
Im at a loss to explain why it should work this way