Newbie question: Insert (not very) big XML document in DB2 9 Express-C

Message has been deleted

Mike Meng

unread,

Mar 26, 2007, 12:33:55 PM3/26/07

to

Hi all,
I encountered a problem while INSERTing a 160KB well formed XML
document into the XML field. I heard that DB2 9 can store up to 2G
bytes XML document, but my insertion was failed with just 160KB XML.
DB2 complains that the "string constant is too long" (SQLSTATE 54002).
I inserted the data via SQL command.

Would you please tell me how to insert big XML into DB2 9?

I'm using DB2 v9.1 Express-C.

Thanks in advance!

Mike
Mar. 26, 2007

Knut Stolze

unread,

Mar 26, 2007, 3:07:06 PM3/26/07

to

Mike Meng wrote:

> Hi all,
> I encountered a problem while INSERTing a 160KB well formed XML
> document into the XML field. I heard that DB2 9 can store up to 2G
> bytes XML document, but my insertion was failed with just 160KB XML.
> DB2 complains that the "string constant is too long" (SQLSTATE 54002).
> I inserted the data via SQL command.
>
> Would you please tell me how to insert big XML into DB2 9?

What's the exact INSERT statement and what's the exact error message that
you got?

Also, what's your environment (OS, DB2 release level)?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Mike Meng

unread,

Mar 26, 2007, 9:12:29 PM3/26/07

to

Hi Knut, thank!

I use DB2 9 Express-C on Windows 2003 Server. The table "talk" was
create by:

CREATE TABLE talk (tid BIGING, content XML) IN reldata LONG IN xmldata

And reldata is a tablespace with 4k pagesize, xmldata is a tablespace
with 32k pagesize.

The inserting command is issued by a C# program with DB2Command
object. Here is the code snippet:

DB2Command cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO talk (tid, content) VALUES (4,
XMLPARSE(DOCUMENT'" + sb.ToString() + "'))";

The sb object is a .NET StringBuilder object, in which stored a 160KB
XML document.

The error message is:
ERROR [54002] [IBM][DB2/NT] SQL0102N The string constant beginning
with "blah blah" is too long。SQLSTATE=54002

Thanks again.

Mike Meng

unread,

Mar 26, 2007, 10:07:39 PM3/26/07

to

Hi Knut,
I tried again with host variable. That is,

DB2Command cmd = connection.CreateCommand();
cmd.CommandText "INSERT INTO talk (tid, content) VALUES (5, @xmldoc)";
cmd.Parameters.Add("@xmldoc", sb.ToString());

This time it did not complain SQLSTATE 54002, instead, it says:

ERROR [22001] [IBM] CLI0109E String data right truncation.
SQLSTATE=22001

How can I do?

Thanks!

On 3月27日, 上午3时07分, Knut Stolze <sto...@de.ibm.com> wrote:

frod...@gmail.com

unread,

Mar 27, 2007, 6:11:20 AM3/27/07

to

Hi all,

Try to use:

String query = "INTO talk (tid, content) VALUES (?, ?)";
PreparedStatement insertStmt = conn.prepareStatement(query);
insertStmt.setInt(1, 4);
insertStmt.setString(2, sb.ToString());
insertStmt.executeUpdate();

What is else I recommend for all DB2 9 pureXML Guidebook. You can
download it free from http://www.redbooks.ibm.com/abstracts/SG247315.html

Marcin Molak
Warsaw University of Technology, Faculty of Physics student

Mike Meng

unread,

Mar 27, 2007, 10:39:09 AM3/27/07

to

Hi Marcin,
Thank you very much. The problem has been solved.

Mike
March. 27

> Try to use:
>
> String query = "INTO talk (tid, content) VALUES (?, ?)";
> PreparedStatement insertStmt = conn.prepareStatement(query);
> insertStmt.setInt(1, 4);
> insertStmt.setString(2, sb.ToString());
> insertStmt.executeUpdate();
>
> What is else I recommend for all DB2 9 pureXML Guidebook. You can

> download it free fromhttp://www.redbooks.ibm.com/abstracts/SG247315.html

Knut Stolze

unread,

Mar 27, 2007, 12:02:13 PM3/27/07

to

Mike Meng wrote:

> Hi Knut,
> I tried again with host variable. That is,
>
> DB2Command cmd = connection.CreateCommand();
> cmd.CommandText "INSERT INTO talk (tid, content) VALUES (5, @xmldoc)";
> cmd.Parameters.Add("@xmldoc", sb.ToString());
>
> This time it did not complain SQLSTATE 54002, instead, it says:
>
> ERROR [22001] [IBM] CLI0109E String data right truncation.
> SQLSTATE=22001
>
> How can I do?

I checked with the manuals, and the serialized (string) representation of
XML documents is limited to 2GB.
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.embed.doc/doc/c0023366.htm

So that shouldn't be an issue in your case. My guess is that you exceed the
total SQL statement length - not knowing what exactly "cmd.Parameters.Add"
is doing.

Knut Stolze

unread,

Mar 27, 2007, 12:02:59 PM3/27/07

to

Mike Meng wrote:

> Thank you very much. The problem has been solved.

How did you resolve it?