Preferred Export Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stabbert - 28 Jan 2006 00:02 GMT

I am trying to setup a preferred export format for users when exporting
data. Are there any pros/cons to using one format over another or is
it really just personal preference?
Spencer

Reply to this Message

Mark A - 28 Jan 2006 00:06 GMT

>I am trying to setup a preferred export format for users when exporting
> data. Are there any pros/cons to using one format over another or is
> it really just personal preference?
> Spencer

For DB2, IXF is the preferred format.

Reply to this Message

stabbert - 28 Jan 2006 00:11 GMT

Mark thanks. Could you elaborate a bit on why?

Reply to this Message

Mark A - 28 Jan 2006 00:28 GMT

> Mark thanks. Could you elaborate a bit on why?

If you use a delimited export file, then there is always a chance the data
will have the delimiter within the boundaries of a particular column. Fixed
length exports solve that problem, but when you go to import the data, you
must define where each column starts and stops, which is fairly tedious.

IXF files solve both of these problems. The IXF file can automatically
create the table and primary key (but does not include certain things like
foreign keys, indexes, etc) if the table does not already exist at the same
time the data is imported/loaded. Or you load an IXF file to an existing
table.

Reply to this Message

Serge Rielau - 28 Jan 2006 11:36 GMT

>>Mark thanks. Could you elaborate a bit on why?
>
[quoted text clipped - 8 lines]
> time the data is imported/loaded. Or you load an IXF file to an existing
> table.

To me the main advantage of IXF is speed. It's nearly twice as fast to
load as some of the other formats. While it also more fluffy in
filesize, in my experience zip makes up for it by compressing IXF very
well. We have seen ~50GB/h/CPU LOAD performance.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

stabbert - 28 Jan 2006 13:37 GMT

Appreciate the input guys!
Spencer

Reply to this Message

Jonathan Leffler - 28 Jan 2006 19:11 GMT

>>> Mark thanks. Could you elaborate a bit on why?
>>
[quoted text clipped - 14 lines]
> filesize, in my experience zip makes up for it by compressing IXF very
> well. We have seen ~50GB/h/CPU LOAD performance.

I've just been looking at a definitions of PC/IXF (version 1) format at:

http://os2ports.com/docs/DB2/db2n0/db2n0147.htm

In the T (table) records, the limits on the IXFTNAML and IXFTQUAL fields
are 18 and 8 - the old limits on table name length and schema name
length. These days (on non-DB2 systems at least - but I think DB2 has
similar limits), the name length can be up to 128 bytes, and the
qualifier (schema) name length can be up to 32 bytes. There's a similar
problem with the length of IXFCNAML in the C (column) records.

Is there a more recent definition of PC/IXF that allows for these longer
identifiers?

The first page of Googling 'ibm db2 ixf format' didn't show much else
that looked promising, but I didn't look at everything.

Signature

Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

PS: Gosh - it is hard work training fingers to type IXF instead of IFX!

Reply to this Message

Wolfgang Riedel - 30 Jan 2006 09:52 GMT

<snip>

> I've just been looking at a definitions of PC/IXF (version 1) format at:
>
> http://os2ports.com/docs/DB2/db2n0/db2n0147.htm

<snip>
this is quite old (1997) and for ixf version 0001.
You'll find version 0002 f.e. here:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/admin/t0004626.htm

(IXFTNAME 256-BYTE CHARACTER name of data
and
IXFTQUAL 256-BYTE CHARACTER qualifier -
the length fields have an increased size too)

hth
Wolfgang

Reply to this Message