Convert IXF to CSV

[Gregor Kova�]

Hi!

Is it possible to convert IXF files to some more readable format like CSV?
I'm asking this, because when I do IMPORT I sometimes get rejected rows. The
error says that there was a problem with row 1956 (for example). How do I
know what row is that, what data is in that row?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Prasad]

IXF files store definition of the table. So, create a new table while
importing and then export the data in DEL format.

[Knut Stolze]

Prasad wrote:

IXF files store definition of the table. So, create a new table while
importing and then export the data in DEL format.

Obviously, that won't work because the rejected rows wouldn't be imported.

Maybe exception tables will help?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Gregor Kova�]

Knut Stolze wrote:

Prasad wrote:

>IXF files store definition of the table. So, create a new table while
importing and then export the data in DEL format.

Obviously, that won't work because the rejected rows wouldn't be imported.

Maybe exception tables will help?

Actually his suggestion gave an idea. I created a brand new table (no
primary keys, no unique indexes, ...) and imported the file into that
table. Then a simple SELECT statement gave the result I wanted. :)
Now the problem still persists.
The docs for the IMPORT command
(http://publib.boulder.ibm.com/infoce...c/r0008304.htm)
say that when you specify INSERT_UPDATE:
"INSERT_UPDATE
Adds rows of imported data to the target table, or updates existing rows
(of the target table) with matching primary keys."

But this is not the case if you have a unique index on the table. Is this a
bug in docs or in the IMPORT command itself?

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Phil Sherman]

There is nothing wrong with the docs. INSERT_UPDATE does exactly what is
specified. If you have a unique index on the table that is not the
primary key, then inserts must have a new primary key AND satisfy the
uniqueness constraint of the unique index.

If your table does not have a primary key and has a single unique index,
then you can add the primary key using an ALTER TABLE statement. The
(new) primary key will use your existing unique index and will not
create another on the pk columns.

Take the suggestion made by Knut about using exception table when
loading and you will find the rejected rows there.

Phil Sherman

Gregor Kovac( wrote:

Knut Stolze wrote:

>Prasad wrote:

>>IXF files store definition of the table. So, create a new table while
importing and then export the data in DEL format.

Obviously, that won't work because the rejected rows wouldn't be imported.

Maybe exception tables will help?

Actually his suggestion gave an idea. I created a brand new table (no
primary keys, no unique indexes, ...) and imported the file into that
table. Then a simple SELECT statement gave the result I wanted. :)
Now the problem still persists.
The docs for the IMPORT command
(http://publib.boulder.ibm.com/infoce...c/r0008304.htm)
say that when you specify INSERT_UPDATE:
"INSERT_UPDATE
Adds rows of imported data to the target table, or updates existing rows
(of the target table) with matching primary keys."

But this is not the case if you have a unique index on the table. Is this a
bug in docs or in the IMPORT command itself?

Best regards,
Kovi

[Gregor Kova�]

Hi!

Thanks for your reply, but I still think there is a bug somewhere. The docs
for INSERT_UPDATE never mention anything for unique keys. But you are
right, since IMPORT does INSERTs behind the scene, new rows have to comply
with foreighn keys, unique indexes, ... But none of this is mentioned in
the docs.
There is also nothing about exception tables with IMPORT command. I have
found that you can use them with LOAD command, but I don't want to use it,
because it puts tables into Set Integrity Pending state and then you have
to go and find the tables that are in that state and check them, ... A mess
if you ask me.

Best regards,
Kovi

Phil Sherman wrote:

There is nothing wrong with the docs. INSERT_UPDATE does exactly what is
specified. If you have a unique index on the table that is not the
primary key, then inserts must have a new primary key AND satisfy the
uniqueness constraint of the unique index.

If your table does not have a primary key and has a single unique index,
then you can add the primary key using an ALTER TABLE statement. The
(new) primary key will use your existing unique index and will not
create another on the pk columns.

Take the suggestion made by Knut about using exception table when
loading and you will find the rejected rows there.

Phil Sherman

Gregor Kovac( wrote:

>Knut Stolze wrote:

>>Prasad wrote:

IXF files store definition of the table. So, create a new table while
importing and then export the data in DEL format.
Obviously, that won't work because the rejected rows wouldn't be
imported.

Maybe exception tables will help?

Actually his suggestion gave an idea. I created a brand new table (no
primary keys, no unique indexes, ...) and imported the file into that
table. Then a simple SELECT statement gave the result I wanted. :)
Now the problem still persists.
The docs for the IMPORT command

(http://publib.boulder.ibm.com/infoce...c/r0008304.htm)

>say that when you specify INSERT_UPDATE:
"INSERT_UPDATE
Adds rows of imported data to the target table, or updates existing
rows
(of the target table) with matching primary keys."

But this is not the case if you have a unique index on the table. Is this
a bug in docs or in the IMPORT command itself?

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Pierre Saint-Jacques]

As far as I can remember, the message file that you use when doing imports
will always have a copy of the row that is rejected with the accompanying
messages as to why it was rejected (duplicate keys or wrong data type or
.....).
This file is a flat ASCII file so you should be able to scan it for the msgs
and rows.

I think exception tables are only used in the load command and can only hold
validly parsed rows. Those rows are the result of the inseted row with a
duplicate key that happens when the data is loaded in the table in pahase
one of the load. Phase two which maintains or builds indexes causes those
rows to be physically deleted from the table and then inserted in the
exception table. They are valid rows with duplicate keys on unique indexes.
Please note that those deletes and inserts are logged operations and if yoyu
have too many you will need more log space.

HTH' Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Gregor Kovac" <gr**********@mikropis.sia écrit dans le message de news:
n7******************@news.siol.net...

Hi!

Is it possible to convert IXF files to some more readable format like CSV?
I'm asking this, because when I do IMPORT I sometimes get rejected rows.
The
error says that there was a problem with row 1956 (for example). How do I
know what row is that, what data is in that row?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-