473,725 Members | 2,422 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 12 '05 #1
6 4317

"JS" <fy***@hotmail. com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...
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
Nov 12 '05 #2
JS
"Rhino" <rh****@NOSPAM. sympatico.ca> wrote in message news:<gL******* **************@ news20.bellglob al.com>...
"JS" <fy***@hotmail. com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...
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)
>>>>>>>NU LL 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.
Nov 12 '05 #3

"JS" <fy***@hotmail. com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...
"Rhino" <rh****@NOSPAM. sympatico.ca> wrote in message news:<gL******* **************@ news20.bellglob al.com>...
"JS" <fy***@hotmail. com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...
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)>>>>>>>>NU LL 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
Nov 12 '05 #4
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=X2 0) 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?
Nov 12 '05 #5

"JS" <fy***@hotmail. com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...
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=X2 0) 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
Nov 12 '05 #6
JS
"Rhino" <rh****@NOSPAM. sympatico.ca> wrote in message news:<J%******* ************@ne ws20.bellglobal .com>...
"JS" <fy***@hotmail. com> wrote in message
news:e9******** *************** ***@posting.goo gle.com...
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=X2 0) 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
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
6685
by: Donald Tyler | last post by:
Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to be able to place the PHP file on the server, and I guess you probably can't do that either. Talk about catch 22... The only other way I can think of is to install MySQL on a machine you control, then import the data there using the method I...
1
8537
by: R. Clausen | last post by:
During the night we replicate multiple tables from a production database to a test database. Due to a server disk space full condition occurring while replication was running, a tablespace was left in 'Load Pending' status. This tablespace has about 20 tables in it. I ran the load/terminate command and have now come to understand that the message 'SQL3508N Error in accessing a file or path of type "RESTART/TERMINATE INFO" during...
3
4701
by: Arti Potnis | last post by:
Hi, I am trying to execute the DB2 load utility from a C program. lPid = fork(); execl(csExePath, csExeName,csCmd, NULL); the contents of the parameter variables are as follows:-
7
7904
by: DB_2 | last post by:
Hello, I was trying to load a comma-separated text file to a DB2 table. I believe I have the syntax rigt for the LOAD command. My first question is, how do you actually run it? It is not a SQL command; so it doesn't run from the command center/SQL window. It looks like a utility tool but I didn't find a load.exe or db2load.exe file in the installation directory.
3
2313
by: claus.hirth | last post by:
When I execute the following statment using the Command Center it works: LOAD CLIENT FROM 'Y:\\TheFixedWidthData.txt' OF ASC METHOD L ( 1 1,2 11,12 17) REPLACE INTO S00.TheTargetTable NONRECOVERABLE WITHOUT PROMPTING Yet when I try to execute the same statement in a PreparedStatement it does not work, but gives me a SQLException:
3
2749
by: nsh | last post by:
mailing.database.mysql, comp.lang.php subject: does "LOAD DATA" EVER work?!? I've tried EVERYTHING! version info: my isp is running my web page on a linux box with php ver. 4.4.1 according to phpinfo, the "mysql api client is ver. 4.0.25" - I have no idea how this relates, if at all, to the mysql engine's version. background:
1
5958
by: huyuhui | last post by:
The following is a question of LOAD utility. Question: How does the DB2 enforce table check constraints for data added to table with the LOAD utility? A. With the BUILD phase of LOAD B. With the SET INTEGRITY statement C. With the DELETE phase of the LOAD D. With the UPDATE CONSTRAINTS statement Answer is A
1
7266
by: Justin | last post by:
We had a load that failed. Now we have a pending load. When executing load terminate, we receive the following error: db2 load terminate SQL0104N An unexpected token "terminate" was found following "LOAD". Expected tokens may include: "QUERY". SQLSTATE=42601 db2 query load terminate DB21034E The command was processed as an SQL statement because it was
2
3043
by: mike.lopiano | last post by:
There is a significant gap (~ 3.5 minutes) in the time our LOAD command indicates that the BUILD phase is complete and the time we have control returned to our script. I ran the test below on a dedicated system with no other processes running. Our tablespaces are on Veritas filesystems. Any insight/ suggestions welcome... From the LOAD output:
5
6078
by: danfan46 | last post by:
Hi. I'm on DB2 9.5.0.1 on Linux AMD 64 and the database has four partitions where catalog is on partion 0 and user data on 1 thru 3. After a load failure (path to load file was wrong) I cannot access the tables, even though the loads have been rerun with success. set integrity for ..table..
0
8878
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9250
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9165
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9097
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8079
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6699
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6006
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3216
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2153
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.