473,718 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

copy table & rejected rows

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?
Nov 12 '05 #1
6 9400
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
Nov 12 '05 #2
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.e ye-be-em.com> wrote in message
news:c5******** **@hanover.toro lab.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

Nov 12 '05 #3
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! =-----
Nov 12 '05 #4
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.
Nov 12 '05 #5
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! =-----
Nov 12 '05 #6
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*****@mobile audio.com> wrote in message
news:40******** **@corp.newsgro ups.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! =-----

Nov 12 '05 #7

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

Similar topics

3
1573
by: A.V.C. | last post by:
Hello, I want to store two information (so 2 columns) for 2/3rd of the rows that will be in a table and only one information (1 column is suffecient) for 1/3rd of the rows of the table. ex: jb_id, previous_jb_id -------- for 2/3rd of the rows jb_id -------- for 1/3rd of the rows
2
9143
by: Dianna K. | last post by:
I have a dataview which I am assigning to a Datatable (I am doing this because currently Crystal Reports will not accept a dataview as a Reportsource -- The recommended work-around is to assign dataview to datatable). The dataview is being filtered. However when I assign it to the datatable it is not filtered. I put some debug code in and the Dataview has 8 records then I assign it to a new datatable. When I check the row count of the...
4
5095
by: Rob Freundlich | last post by:
I have some servlet-generated tabular data that I need to present, so I'm using an HTML Table. In some cases, it can be quite large. I'm flushing the servlet output every N lines to push the data to the browser as it generates, and I've used "table-layout: fixed" for the table's CSS class. It works pretty well in Netscape (7.1 and higher) - the table is drawn pretty much as the rows are received by the browser. However, Internet...
3
2374
by: Lou | last post by:
I created a small database and defined a simple table with about 12 columns. To load the table I imported data from a text file. I got a successful message after importing the data; no error messages at all. However, when I try to get a sample of the database, nothing comes up. I only the the columns I defined, but no rows at all. What did I do wrong? I'm using Personal DB2 8.1. Thanks for your help. Luis
6
4186
by: sql_server_user | last post by:
I'm trying to copy all 440 million rows from one table in my SQL Server 2005 db to another table with a different clustering scheme. After a few test inserts that were successful (up to a million rows in 20 seconds with no problem), I crossed my fingers and went whole-hog with the naive "insert into dest_table (column1, column2, ...) select column1, column2, ... from source_table" but of course it ran out of space in *both* the db log file...
10
4018
by: campos | last post by:
"Effective C++ 3rd Edition" Item 6, P39 ------------------------------------------------------- class Uncopyable { protected: // allow construction Uncopyable() {} // and destruction of ~Uncopyable() {} // derived objects... private: Uncopyable(const Uncopyable&); // ...but prevent copying
1
1592
by: jl2886 | last post by:
I have two tables in Access, one that contains Active or Pending Policies and another table that contains Rejected policies. I have a form corresponding to the table with a certain variable called "Status" that evaluates whether a policy is Pending, Active or Rejected(a regular conditional statement in VB). The form is linked to the Active or Pending Policies Table currently, however, I would like the form information to send a policy to the...
5
2786
by: Sieldan | last post by:
I'm trying to throw some data around so that I can manipulate it more easily. Unfortunately, I don't really know what I'm doing. :) So I'm creeping my way through it step by step. What I have done so far is find the rows of data I need to work with, copied them into a temporary table, and removed them from the original table. Now I am trying to send the manipulated rows back to the original table. The hitch is the original table has two...
0
3213
by: Taxman | last post by:
Windows XP, MS Office Excel 2003 If the tasks, Iím trying accomplish have been addressed previously (separately or in combination). Please, provide the links or keyword search to find them. Iíve been searching for code for each part of the task separately and trying to piece together multiple macros, that do something similar, to what Iím trying to accomplish in my over all task, but Iím not having a lot of luck. So, hereís the entire task,...
0
8827
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
8724
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9208
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...
0
9053
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
7991
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
6654
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
4481
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3182
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
2123
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.