copy table & rejected rows

[Fan Ruo Xin]

I try to copy a table from production system (DB2 UDB EEE V7.2 + fixpak5)
to the testing system (DB2 UDB V8.1 + fixpak4a).
I moved the data from productions system by using the following steps:
On production system:
$ db2 "export to xxxxx.del of del select * from xxxxxx"

On testing system:
I use db2 utility autoload, because I use the autoload cfg script for a long
time.

The autoload result:
Summary of Partitioning Agents:
Rows Read = 9059213
Rows Rejected = 0
Rows Partitioned = 9059213
Summary of LOAD Agents:
Number of rows read = 9059213
Number of rows skipped = 0
Number of rows loaded = 8940829
Number of rows rejected = 118384
Number of rows deleted = 0
Number of rows committed = 9059213
The message log of autoload - on target db partition #2
====
SQL3109N The utility is beginning to load data from file
"/xxx/xxx/xxx/xxxx.del".
SQL3500W The utility is beginning the "LOAD" phase at time "04-07-2004
15:57:15.971066".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3114W Some data following """" in row "30214" and column "1" was
not loaded.
SQL3114W Some data following """" in row "30705" and column "1" was
not loaded.
SQL3114W Some data following """" in row "50891" and column "1" was
not loaded.
SQL3114W Some data following """" in row "58369" and column "1" was
not loaded.
SQL3114W Some data following """" in row "61489" and column "1" was
not loaded.
SQL3114W Some data following """" in row "61645" and column "1" was
not loaded.
SQL3125W The character data in row "77083" and column "2" was
truncated because the data is longer than the target database column.
SQL3125W The character data in row "77085" and column "2" was
truncated because the data is longer than the target database column.
SQL3114W Some data following """" in row "92903" and column "1" was
not loaded.
SQL3114W Some data following """" in row "93068" and column "1" was
not loaded.
SQL3114W Some data following """" in row "107218" and column "1" was
not loaded.
SQL3125W The character data in row "111066" and column "2" was
truncated because the data is longer than the target database column.
SQL3125W The character data in row "111067" and column "2" was
truncated because the data is longer than the target database column.
SQL3114W Some data following """" in row "112687" and column "1" was
not loaded.
SQL3114W Some data following """" in row "112688" and column "1" was
not loaded.
SQL3125W The character data in row "116593" and column "2" was
truncated because the data is longer than the target database column.
SQL3114W Some data following """" in row "120511" and column "1" was
not loaded.
SQL3114W Some data following """" in row "124154" and column "1" was
not loaded.
SQL3125W The character data in row "124510" and column "2" was
truncated because the data is longer than the target database column.
SQL3125W The character data in row "128187" and column "2" was
truncated because the data is longer than the target database column.
SQL3114W Some data following """" in row "128892" and column "1" was
not loaded.
SQL3125W The character data in row "134004" and column "2" was
truncated because the data is longer than the target database column.
......
==================
I CAN'T UNDERSTAND WHY THERE ARE SOME ROWS WERE REJECTED??? WHY IS IT OK
THAT THEY CAN BE EXISTED IN THE PRODUCTION DB???
I don t think the issue is because my target db server is Version8.1.
Does anyone have any idea about this?

[Serge Rielau]

Just a guess. When you look at the DEL fiel for the appropriate rows.
Could it be theer are soem odd characters in there? Such as quotes or
line feeds?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Fan Ruo Xin]

Thank you, Serge.
I tried it. But because the number of db partition of the source db and
target db is different. It is not so easy to find out those rows. But I will
try again.
Regards,
FRX

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:c5**********@hanover.torolab.ibm.com...

Just a guess. When you look at the DEL fiel for the appropriate rows.
Could it be theer are soem odd characters in there? Such as quotes or
line feeds?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

Fan Ruo Xin wrote:

I try to copy a table from production system (DB2 UDB EEE V7.2 + fixpak5)
to the testing system (DB2 UDB V8.1 + fixpak4a).
I moved the data from productions system by using the following steps:
On production system:
$ db2 "export to xxxxx.del of del select * from xxxxxx"

On testing system:
I use db2 utility autoload, because I use the autoload cfg script for a long
time.

The autoload result:
Summary of Partitioning Agents:
Rows Read = 9059213
Rows Rejected = 0
Rows Partitioned = 9059213
Summary of LOAD Agents:
Number of rows read = 9059213
Number of rows skipped = 0
Number of rows loaded = 8940829
Number of rows rejected = 118384
Number of rows deleted = 0
Number of rows committed = 9059213
The message log of autoload - on target db partition #2
====
SQL3109N The utility is beginning to load data from file
"/xxx/xxx/xxx/xxxx.del".
SQL3500W The utility is beginning the "LOAD" phase at time "04-07-2004
15:57:15.971066".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3114W Some data following """" in row "30214" and column "1" was
not loaded.
SQL3114W Some data following """" in row "30705" and column "1" was
not loaded.
SQL3114W Some data following """" in row "50891" and column "1" was
not loaded.
SQL3114W Some data following """" in row "58369" and column "1" was
not loaded.
SQL3114W Some data following """" in row "61489" and column "1" was
not loaded.
SQL3114W Some data following """" in row "61645" and column "1" was
not loaded.
SQL3125W The character data in row "77083" and column "2" was
truncated because the data is longer than the target database column.
...
==================
I CAN'T UNDERSTAND WHY THERE ARE SOME ROWS WERE REJECTED??? WHY IS IT OK
THAT THEY CAN BE EXISTED IN THE PRODUCTION DB???

As Serge suggested, it's possible that the rows may have some embedded
control characters. Export is very good about maintaining the proper
format for delimited files -- the only exception is when your data
has embedded newline characters. The errors you show above look like
what you'd see if this is the case.

You can use the MODIFIED BY DELPRIORITYCHAR option, which allows you to
load data with embedded newlines.

If this does not solve the problem, you can use IMPORT (i.e. by running it
long enough to give you the first couple of errors, since import will give
you row numbers from the source file.)

You could also run the load in SPLIT_ONLY mode for 1 partition that fails,
and then run a LOAD_ONLY load for the same partition. This will allow you
to find the row in the split data file for the individual partition.
Good luck,
Ian


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Dave Hughes]

On 2004-04-12, Ian scribbled:

Fan Ruo Xin wrote:

I try to copy a table from production system (DB2 UDB EEE V7.2 +
fixpak5) to the testing system (DB2 UDB V8.1 + fixpak4a).
I moved the data from productions system by using the following
steps: On production system:
$ db2 "export to xxxxx.del of del select * from xxxxxx"

[Snip]
As Serge suggested, it's possible that the rows may have some embedded
control characters. Export is very good about maintaining the proper
format for delimited files -- the only exception is when your data
has embedded newline characters. The errors you show above look like
what you'd see if this is the case.

[Snip]

I may be asking a really dumb question here (I'm not familiar with
partitioned databases, or the auto-loader), but would using IXF format
instead of delimited format not solve this problem? Given that it's a
(mostly) binary format that wouldn't care about newlines and other
control characters.

Dave.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.

[Ian]

>

I may be asking a really dumb question here (I'm not familiar with
partitioned databases, or the auto-loader), but would using IXF format
instead of delimited format not solve this problem? Given that it's a
(mostly) binary format that wouldn't care about newlines and other
control characters.

Dave.

That's a good thought, but unfortunately IXF files can't be loaded to
multi-partition environments (the split utility does not support them,
even in V8) without jumping through some hoops.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Fan Ruo Xin]

Ian,
Thank you so much. That is so great. I will try later when I get time. I
need to find out the problem.
Regards,
FRX

"Ian" <ia*****@mobileaudio.com> wrote in message
news:40**********@corp.newsgroups.com...

Fan Ruo Xin wrote:

I try to copy a table from production system (DB2 UDB EEE V7.2 + fixpak5) to the testing system (DB2 UDB V8.1 + fixpak4a).
I moved the data from productions system by using the following steps:
On production system:
$ db2 "export to xxxxx.del of del select * from xxxxxx"

On testing system:
I use db2 utility autoload, because I use the autoload cfg script for a long time.

The autoload result:
Summary of Partitioning Agents:
Rows Read = 9059213
Rows Rejected = 0
Rows Partitioned = 9059213
Summary of LOAD Agents:
Number of rows read = 9059213
Number of rows skipped = 0
Number of rows loaded = 8940829
Number of rows rejected = 118384
Number of rows deleted = 0
Number of rows committed = 9059213
The message log of autoload - on target db partition #2
====
SQL3109N The utility is beginning to load data from file
"/xxx/xxx/xxx/xxxx.del".
SQL3500W The utility is beginning the "LOAD" phase at time "04-07-2004
15:57:15.971066".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3114W Some data following """" in row "30214" and column "1" was
not loaded.
SQL3114W Some data following """" in row "30705" and column "1" was
not loaded.
SQL3114W Some data following """" in row "50891" and column "1" was
not loaded.
SQL3114W Some data following """" in row "58369" and column "1" was
not loaded.
SQL3114W Some data following """" in row "61489" and column "1" was
not loaded.
SQL3114W Some data following """" in row "61645" and column "1" was
not loaded.
SQL3125W The character data in row "77083" and column "2" was
truncated because the data is longer than the target database column.
> ...
==================
I CAN'T UNDERSTAND WHY THERE ARE SOME ROWS WERE REJECTED??? WHY IS IT OK
THAT THEY CAN BE EXISTED IN THE PRODUCTION DB???

As Serge suggested, it's possible that the rows may have some embedded
control characters. Export is very good about maintaining the proper
format for delimited files -- the only exception is when your data
has embedded newline characters. The errors you show above look like
what you'd see if this is the case.

You can use the MODIFIED BY DELPRIORITYCHAR option, which allows you to
load data with embedded newlines.

If this does not solve the problem, you can use IMPORT (i.e. by running it
long enough to give you the first couple of errors, since import will give
you row numbers from the source file.)

You could also run the load in SPLIT_ONLY mode for 1 partition that fails,
and then run a LOAD_ONLY load for the same partition. This will allow you
to find the row in the split data file for the individual partition.
Good luck,
Ian


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----