Machine : AIX 5.2
Product : UDB DB2 Release 8.1 FP4a
I have problem loading data into destination table. The data file is
huge with more than 6 Million records. This what i have done
1. Export the data into flat file (del format)
2. use the load command to load the data
At step 2, i found that there are some errors registered in the load
message files for a particular rowid. Please refer to the error below
SQL3125W The character data in row "6483140" and column "8" was
truncated
because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "11" was
truncated
because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "13" was
truncated
because the data is longer than the target database column.
SQL0180N The syntax of the string representation of a datetime value
is
incorrect. SQLSTATE=22007
As you can see at row 6483140 there are some errors at column level. To
look for this error, i cannot edit the data file as it is very huge and
not feasible to look for the row. The best part is that the source
table does not have any key field or index for me to manipulate the
data retrieval.
How do i extract the data of the row?
Please help.
Regards,
Uthuras 5 7303
<ut*****@hotmai l.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. . Machine : AIX 5.2 Product : UDB DB2 Release 8.1 FP4a
I have problem loading data into destination table. The data file is huge with more than 6 Million records. This what i have done
1. Export the data into flat file (del format) 2. use the load command to load the data
At step 2, i found that there are some errors registered in the load message files for a particular rowid. Please refer to the error below
SQL3125W The character data in row "6483140" and column "8" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "11" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "13" was truncated because the data is longer than the target database column.
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
As you can see at row 6483140 there are some errors at column level. To look for this error, i cannot edit the data file as it is very huge and not feasible to look for the row. The best part is that the source table does not have any key field or index for me to manipulate the data retrieval.
How do i extract the data of the row?
Please help.
Regards, Uthuras
I believe that the row number cited in the error messages is just the
ordinal number representing the position of that row within the input file,
i.e. it is the 6,483,140th record within the file; I don't think the message
is referring to any value that is on the row itself. Therefore, all you need
is an editor that can read a file that large and that shows line numbers.
I'm certain that the editor I use, Programmers File Editor (PFE), can do
both of those things, although there must be other editors with this capabil
ity. PFE only runs on Windows so if you want to give it a try, Google on
'Programmers File Editor': although it is no longer being developed, it is
still available on some archives, including http://www.winsite.com/bin/Info?500000017700. If you are running another
operating system, ask on a newsgroup dedicated to that OS for
recommendations for an editor that can handle large files.
Rhino ut*****@hotmail .com wrote: Machine : AIX 5.2 Product : UDB DB2 Release 8.1 FP4a
I have problem loading data into destination table. The data file is huge with more than 6 Million records. This what i have done
1. Export the data into flat file (del format) 2. use the load command to load the data
At step 2, i found that there are some errors registered in the load message files for a particular rowid. Please refer to the error below
SQL3125W The character data in row "6483140" and column "8" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "11" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "13" was truncated because the data is longer than the target database column.
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
As you can see at row 6483140 there are some errors at column level. To look for this error, i cannot edit the data file as it is very huge and not feasible to look for the row. The best part is that the source table does not have any key field or index for me to manipulate the data retrieval.
How do i extract the data of the row?
Please help.
Regards, Uthuras
If you can, use VIM, it can read 6MB+ files, but it takes some time to
read though. Also what you could try: 'cat -n <file> | grep <rownum>'
this only works if al the rows are on one line.
I don't know if you can execute em this way on AIX, but perhaps...
Also, in DB2 you could try a select on a subselect with rownum, even
though the line is truncated, it was still inserted in the database. And
with the content of this record you could grep on the file to see the
entire line...
-R-
Since your input to load is type DEL; you can add the "MODIFIED BY
DUMPFILE=...... ..." clause to your load command. this will put the
rejected input record in its own file where you can easily edit it. Once
edited; you should have no problem inserting it into the table.
I believe that AIX, like most UNIX-like systems comes with the "sed"
command. You can use it to extract the specific record from the file and
examine it.
If you need to modify the file, dropping the "bad" record; then you can
get an exact count of the lines and use the "head" and "tail" commands
to rebuild the file without the bad one.
I've run into this situation before and have usually traced it to an
unexpected comma or quote in the offending record.
Phil Sherman ut*****@hotmail .com wrote: Machine : AIX 5.2 Product : UDB DB2 Release 8.1 FP4a
I have problem loading data into destination table. The data file is huge with more than 6 Million records. This what i have done
1. Export the data into flat file (del format) 2. use the load command to load the data
At step 2, i found that there are some errors registered in the load message files for a particular rowid. Please refer to the error below
SQL3125W The character data in row "6483140" and column "8" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "11" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "13" was truncated because the data is longer than the target database column.
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
As you can see at row 6483140 there are some errors at column level. To look for this error, i cannot edit the data file as it is very huge and not feasible to look for the row. The best part is that the source table does not have any key field or index for me to manipulate the data retrieval.
How do i extract the data of the row?
Please help.
Regards, Uthuras
Back to your problems with SQL3025W. I got this problem a short time ago.
The reason in my case was that the delimiter priority has changed with one
of the V8 Fixpacks. So if your data contains line breaks in a varchar
column db2 now interprets this as regular delimiter for the import and cuts
your row.
If this could be your problem the easy answer is to use the import with
'MODIFIED BY DELPRIORITYCHAR '. This will force the import to use the old
priority - character, record, column.
Maybe this will help. ut*****@hotmail .com wrote: Machine : AIX 5.2 Product : UDB DB2 Release 8.1 FP4a
I have problem loading data into destination table. The data file is huge with more than 6 Million records. This what i have done
1. Export the data into flat file (del format) 2. use the load command to load the data
At step 2, i found that there are some errors registered in the load message files for a particular rowid. Please refer to the error below
SQL3125W The character data in row "6483140" and column "8" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "11" was truncated because the data is longer than the target database column.
SQL3125W The character data in row "6483140" and column "13" was truncated because the data is longer than the target database column.
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
What's the order in which you exported the rows? Using that order you can
find the rows in question in the original table:
SELECT *
FROM ( SELECT row_number() over(order by ...), ...
FROM <your_table> ) AS t(rn, ...)
WHERE rn IN ( 6483140, ... )
The "order by ..." should be the very same order by that you also used
during the export operation.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ilan |
last post by:
Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though the records are sorted by row numbers.
(I had to split the fields to different sheets because Excel has a
limit of 256 fields in each sheet)
My sheets are quite large (~55,000 rows and 200 columns each) and I'll
have to repeat this action many...
|
by: gustavo_randich |
last post by:
Hello,
I'm looking for a DB2 workaround on a topic already solved in Oracle:
the problem of mutating tables (which states that a trigger action
cannot read the triggering table's data). Yes, I know the trivial
answer: "just pass the triggering table's data as parameters to the
stored procedures called in the triggers, etc."; but it's a long story
to tell why I can't.
Below is the mentioned workaround's template I'm using for an UPDATE
|
by: Daryl J. Edwards |
last post by:
I'm trying to set up a database that will do the following for me:
1) store part numbers, descriptions, and costs in one table (the easy
part)
2) store "kits" - kits are groups of parts from the above table (ie 1
of part A, 0 of part B, and 3 of part C) that are used for certain
construction jobs.
3) for some jobs, multiple kits will need to be used, so i need to
show the total cost of a job that uses eg 2 of kit A, 1 of kit B, and
3...
|
by: George |
last post by:
Hi all,
I am having trouble with updating my data in an Access database. here is my
code:
Imports System.Data.OleDb
Dim AppPath As String = Mid(Application.ExecutablePath, 1,
Len(Application.ExecutablePath) - 14)
|
by: h0ffman |
last post by:
Hi
Hope you can help, been racking my brains on this.
How can I pick a field from a group, based on the order of another field in the group? Not sure if that explains it so..
I have a table
RowID (Int, ID)
ProductCode (Varchar)
| |
by: gregerly |
last post by:
Hello,
I think this should be a simple answer, but I haven't found any good resource yet. I have an element that onclick I run a function, passing a reference to the object clicked. I then grab the id of that object which is a number, 1, 2 ,15, 37, whatever Looks like below:
<img src='img/src.gif' id='12' onclick='somefunction(this)' />
Here is the somefunction()
somefunction(el){
var rowId = el.getAttribute('id');
|
by: Amit |
last post by:
Hello ,
Is there any way i can import data from datagrid to sqlserver.
please advise .
thanks & Regards
|
by: Blacky |
last post by:
Hi,
I am using c# asp.net application.I have datagrid which binds column dynamically and i make certain columns say visibility to false in my itemdatabound event EMPID AS E.ITEM.CELLS.VISIBLE = FALSE.Now i want to perform validations based on this value in cell using javascript...
By clicking on one rows checkbox in datagrid, i need to color all other rows which has the same value in the hidden column EMP... IE cells..
function...
|
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...
|
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,...
|
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...
| |
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |