By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,012 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

Exporting large tables with a lob column

P: n/a

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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

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


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.