selecting data based on rowid

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

uthuras@hotmail.com - 29 Apr 2005 11:19 GMT

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

Reply to this Message

Rhino - 29 Apr 2005 13:37 GMT

> Machine : AIX 5.2
> Product : UDB DB2 Release 8.1 FP4a
[quoted text clipped - 36 lines]
> 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

Reply to this Message

Jurgen Haan - 29 Apr 2005 15:09 GMT

> Machine : AIX 5.2
> Product : UDB DB2 Release 8.1 FP4a
[quoted text clipped - 36 lines]
> 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-

Reply to this Message

Tino Lecomte - 29 Apr 2005 15:37 GMT

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.

Reply to this Message

Philip Sherman - 29 Apr 2005 15:23 GMT

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

> Machine : AIX 5.2
> Product : UDB DB2 Release 8.1 FP4a
[quoted text clipped - 36 lines]
> Regards,
> Uthuras

Reply to this Message

Knut Stolze - 30 Apr 2005 06:16 GMT

> Machine : AIX 5.2
> Product : UDB DB2 Release 8.1 FP4a
[quoted text clipped - 23 lines]
> 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.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message