Exporting large tables with a lob column

[Janne Ruuttunen]

Hello DB2 people,

I'm having problems exporting >= 250000 lobs to IXF files with the
LOBSINFILE option, using a legacy DB2 2.1 system on Win NT.

If I don't specify a path for the lobs, defining more than 250 base
names for the lobs leads to an error message something like "DB2
encountered an unexpected error when sending the query to the backend
process" (I don't have the exact message at hand). It doesn't matter
how short the base names are. The documentation does not mention this
kind of a restriction, it only says "one or more base names".

If I instead try a to distribute the lobs to separate directories by
specifying paths using the LOBPATH syntax, the export fails as follows
(using a bit more manageable amounts of data for the example's
purposes):

---8<---

db2 "export to sometable.ixf of ixf lobs to lobs1\,lobs2\,lobs3\
lobfile a0,a1,a2,a3,a4,a5,a6,a7,a8,a9 modified by lobsinfile select
foo,bar from sometable"

SQL3104N The Export/Unload utility is beginning to export/unload data
to file "sometable.ixf".

SQL3040N Unable to use the lobpath/lobfile parameters as
specified. Reason code: "4".

SQL3185W The previous error occurred while processing data from row
"10000" of the input file.

SQL3105N The Export/Unload utility has finished exporting/unloading
"9999" rows.

---8<---

After this failure, the directory lobs1 contains the 10000 lob files
exported so far (separate files), and rest of the specified lob
directories are empty.

The reason code 4 means that there aren't enough lobfile names, which
suggests that the export utility cannot use the rest of the specified
directories when the name space for the first one is
exhausted. (Apparently, a reason code 5 (pathnames over 255 bytes?!)
has also once occurred in the similar situation).

In effect, I can't get more than 250000 rows exported, which is
unacceptable. Do I have to partition the data in some obscure way and
export them (and again, load them) in several steps, or is there a
workaround for this? Is this a know problem? Or am I missing
something?

Please help,

--
Janne Ruuttunen

[Janne Ruuttunen]

Anyone?

Once more in a nutshell:

In my DB2 2.1/NT, an export command with more than 250 lob base names
fails with the following message:

DB21016E The Command Line Processor encountered a system error while sending
the command to the backend process.

And, can't split them to several directories either. :(

A workaround or any insight greatly appreciated.
Thanks,
Janne
Janne Ruuttunen <jr******@vipunen.hut.fi> writes:

Hello DB2 people,

I'm having problems exporting >= 250000 lobs to IXF files with the
LOBSINFILE option, using a legacy DB2 2.1 system on Win NT.

If I don't specify a path for the lobs, defining more than 250 base
names for the lobs leads to an error message something like "DB2
encountered an unexpected error when sending the query to the backend
process" (I don't have the exact message at hand). It doesn't matter
how short the base names are. The documentation does not mention this
kind of a restriction, it only says "one or more base names".

If I instead try a to distribute the lobs to separate directories by
specifying paths using the LOBPATH syntax, the export fails as follows
(using a bit more manageable amounts of data for the example's
purposes):

---8<---

db2 "export to sometable.ixf of ixf lobs to lobs1\,lobs2\,lobs3\
lobfile a0,a1,a2,a3,a4,a5,a6,a7,a8,a9 modified by lobsinfile select
foo,bar from sometable"

SQL3104N The Export/Unload utility is beginning to export/unload data
to file "sometable.ixf".

SQL3040N Unable to use the lobpath/lobfile parameters as
specified. Reason code: "4".

SQL3185W The previous error occurred while processing data from row
"10000" of the input file.

SQL3105N The Export/Unload utility has finished exporting/unloading
"9999" rows.

---8<---

After this failure, the directory lobs1 contains the 10000 lob files
exported so far (separate files), and rest of the specified lob
directories are empty.

The reason code 4 means that there aren't enough lobfile names, which
suggests that the export utility cannot use the rest of the specified
directories when the name space for the first one is
exhausted. (Apparently, a reason code 5 (pathnames over 255 bytes?!)
has also once occurred in the similar situation).

In effect, I can't get more than 250000 rows exported, which is
unacceptable. Do I have to partition the data in some obscure way and
export them (and again, load them) in several steps, or is there a
workaround for this? Is this a know problem? Or am I missing
something?

Please help,

--
Janne Ruuttunen