JDBC,CLI0109E,VARCHAR(2000) to VARCHAR(4000)

[gimme_this_gimme_that]

I'm driving Weblogic 8.1.3 on WinTel.
DB2 8.1.4 on Sun64.
I'm using Weblogic DB2's Type 2 driver, which has been very
reliable - so far.

I have a PreparedStatement that updates perfectly when
the table is define so that one particular column has
datatype VARCHAR(2000) .

The table was recently modified so that the data type for
the column is now VARCHAR(4000).

SQL statements continue to work perfectly when inserting data
consisting of 2000 or fewer characters.

Upon updating a column with 3990 characters the following
error occurs :

COM.ibm.db2.jdbc.DB2Exception : [IBM][CLI Driver] CLI0109E String data
right truncation. SQLSTATE=22001

I haven't a clue as to the source of the problem and that's
where your suggestions would be very helpful.

The DB2 Message Reference for CLI0190E suggests to use
SQLSetParam or SQLBindParameter, but that doesn't sound like
advice relavant for a JDBC application.

A few google posts suggest that this is a database configuration
issue and that a change in a database configuration file
might fix things. If this is the case more information on
that would be very useful.

In some Google posts developers fathomed that this was an
issue which could be resolved with a different database
driver. Does the type 2 driver "not support" varchar 4000?

Thanks.

[Joe Weinstein]

gi*******************@yahoo.com wrote:

I'm driving Weblogic 8.1.3 on WinTel.
DB2 8.1.4 on Sun64.
I'm using Weblogic DB2's Type 2 driver, which has been very
reliable - so far.

I have a PreparedStatement that updates perfectly when
the table is define so that one particular column has
datatype VARCHAR(2000) .

The table was recently modified so that the data type for
the column is now VARCHAR(4000).

SQL statements continue to work perfectly when inserting data
consisting of 2000 or fewer characters.

Upon updating a column with 3990 characters the following
error occurs :

COM.ibm.db2.jdbc.DB2Exception : [IBM][CLI Driver] CLI0109E String data
right truncation. SQLSTATE=22001
Hi. I'll be glad to help you, but the exception you show
seems to be from an IBM driver, not the one that comes
with WebLogic. We provide a type-4 (all Java) driver.
Does the same problem occur if you restart weblogic
after the table has changed?
Joe Weinstein at BEA

I haven't a clue as to the source of the problem and that's
where your suggestions would be very helpful.

The DB2 Message Reference for CLI0190E suggests to use
SQLSetParam or SQLBindParameter, but that doesn't sound like
advice relavant for a JDBC application.

A few google posts suggest that this is a database configuration
issue and that a change in a database configuration file
might fix things. If this is the case more information on
that would be very useful.

In some Google posts developers fathomed that this was an
issue which could be resolved with a different database
driver. Does the type 2 driver "not support" varchar 4000?

Thanks.

[Larry Menard]

I recently had a problem creating a varchar(4000) column using DB2's CLP.
(I later found that I could only create it if that was the only column in
the table.) I changed my app to LONG VARCHAR and it worked.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
<gi*******************@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm driving Weblogic 8.1.3 on WinTel.
DB2 8.1.4 on Sun64.
I'm using Weblogic DB2's Type 2 driver, which has been very
reliable - so far.

I have a PreparedStatement that updates perfectly when
the table is define so that one particular column has
datatype VARCHAR(2000) .

The table was recently modified so that the data type for
the column is now VARCHAR(4000).

SQL statements continue to work perfectly when inserting data
consisting of 2000 or fewer characters.

Upon updating a column with 3990 characters the following
error occurs :

COM.ibm.db2.jdbc.DB2Exception : [IBM][CLI Driver] CLI0109E String data
right truncation. SQLSTATE=22001

I haven't a clue as to the source of the problem and that's
where your suggestions would be very helpful.

The DB2 Message Reference for CLI0190E suggests to use
SQLSetParam or SQLBindParameter, but that doesn't sound like
advice relavant for a JDBC application.

A few google posts suggest that this is a database configuration
issue and that a change in a database configuration file
might fix things. If this is the case more information on
that would be very useful.

In some Google posts developers fathomed that this was an
issue which could be resolved with a different database
driver. Does the type 2 driver "not support" varchar 4000?

Thanks.

[Mark A]

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:q9********************@rogers.com...

I recently had a problem creating a varchar(4000) column using DB2's
CLP. (I later found that I could only create it if that was the only
column in the table.) I changed my app to LONG VARCHAR and it worked.

--
--------------------
Larry Menard

What was the page size of the tablespace?

[Mark A]

<gi*******************@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm driving Weblogic 8.1.3 on WinTel.
DB2 8.1.4 on Sun64.
I'm using Weblogic DB2's Type 2 driver, which has been very
reliable - so far.

I have a PreparedStatement that updates perfectly when
the table is define so that one particular column has
datatype VARCHAR(2000) .

The table was recently modified so that the data type for
the column is now VARCHAR(4000).

SQL statements continue to work perfectly when inserting data
consisting of 2000 or fewer characters.

Upon updating a column with 3990 characters the following
error occurs :

COM.ibm.db2.jdbc.DB2Exception : [IBM][CLI Driver] CLI0109E String data
right truncation. SQLSTATE=22001

I haven't a clue as to the source of the problem and that's
where your suggestions would be very helpful.

The DB2 Message Reference for CLI0190E suggests to use
SQLSetParam or SQLBindParameter, but that doesn't sound like
advice relavant for a JDBC application.

A few google posts suggest that this is a database configuration
issue and that a change in a database configuration file
might fix things. If this is the case more information on
that would be very useful.

In some Google posts developers fathomed that this was an
issue which could be resolved with a different database
driver. Does the type 2 driver "not support" varchar 4000?

Thanks.

What is the page size of your tablespace?

[Bernd Hohmann]

gi*******************@yahoo.com wrote:

I'm driving Weblogic 8.1.3 on WinTel.
DB2 8.1.4 on Sun64.

Which fixlevel is 8.1.4? There were several fixes for unexpected
CLI1019E errors during FP5 to FP10.

IY49990 could be a candidate for your problem

http://www-1.ibm.com/support/docview...cc=us&lang=all

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Larry Menard]

It was 4K.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Mark A" <no****@nowhere.com> wrote in message
news:Ju******************************@comcast.com. ..

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:q9********************@rogers.com...

I recently had a problem creating a varchar(4000) column using DB2's
CLP. (I later found that I could only create it if that was the only
column in the table.) I changed my app to LONG VARCHAR and it worked.

--
--------------------
Larry Menard

What was the page size of the tablespace?

[gimme_this_gimme_that]

Thanks for the tips everyone.

Strange. I have a javascript alert that tells me that the length of the
string coming from the textbox is 3999. But somehow, by the time
the data attempts to get saved the length of the String is 4157.

I did try out the WebLogic Type 4 driver for DB2. The message was :

[BEA][DB2 JDBC Driver][DB2]Character data, right tuncation occured; for
example, an update or insert value is a string that is too long forthe
column, or a datetimevalue cannot be assigned to a host variable,
because it is too small.

It looks like validating the length of the data on the server side
will solve my problems.

....

BTW, Attempting to save with 27000 characters and the Type 2 driver
killed
the server and crashed the JVM.

From hs_err_pid2224.log :

An unexpected exception has been detected in native code outside the
VM.
Unexpected Signal : EXCEPTION_GUARD_PAGE (0x80000001) occurred at
PC=0x6C121EDB
Function=SQLProcedureColumns+0x54993
Library=C:\PROGRA~1\IBM\SQLLIB\BIN\DB2CLI.dll

Current Java thread:
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.SQLExecu te(Native Method)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (Unknown Source)

[Bernd Hohmann]

gi*******************@yahoo.com wrote:

Strange. I have a javascript alert that tells me that the length of the
string coming from the textbox is 3999. But somehow, by the time
the data attempts to get saved the length of the String is 4157.

Sounds like a strange problem in the areas "codepage conversion" and
"unicode".

Usually a VARCHAR stores 16bit unicode data (16bit per char) but you can
create the column as UTF-8 field to save space. In this case every char
between 0x01 and 0x7F is stored as 8bit, anything else is stored as
2x8bit or 3x8bit.

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Joe Weinstein]

gi*******************@yahoo.com wrote:

Thanks for the tips everyone.

Strange. I have a javascript alert that tells me that the length of the
string coming from the textbox is 3999. But somehow, by the time
the data attempts to get saved the length of the String is 4157.

I did try out the WebLogic Type 4 driver for DB2. The message was :

[BEA][DB2 JDBC Driver][DB2]Character data, right tuncation occured; for
example, an update or insert value is a string that is too long forthe
column, or a datetimevalue cannot be assigned to a host variable,
because it is too small.

It looks like validating the length of the data on the server side
will solve my problems.

...

BTW, Attempting to save with 27000 characters and the Type 2 driver
killed
the server and crashed the JVM.
Yes. We heartily recommend sticking to a type-4 driver, which
won't be able to kill the Application Server.
Joe Weinstein at BEA

From hs_err_pid2224.log :

An unexpected exception has been detected in native code outside the
VM.
Unexpected Signal : EXCEPTION_GUARD_PAGE (0x80000001) occurred at
PC=0x6C121EDB
Function=SQLProcedureColumns+0x54993
Library=C:\PROGRA~1\IBM\SQLLIB\BIN\DB2CLI.dll

Current Java thread:
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.SQLExecu te(Native Method)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2 (Unknown Source)

[gimme_this_gimme_that]

This was the definition of the HTML control :

<textarea name="Experience" cols="50" rows="5" wrap="hard">
</textarea>

Perhaps some characters get added because of the wrap.

[gimme_this_gimme_that]

One thing that's not so great about the type-4 driver is that the
SQL statement :

"VALUES NEXTVAL FOR SCHEMA_NAME.SEQUENCE_NAME"
results in :

[BEA][DB2 JDBC Driver][DB2]UNACCEPTABLE SQL STATEMENT"

[Joe Weinstein]

gi*******************@yahoo.com wrote:

One thing that's not so great about the type-4 driver is that the
SQL statement :

"VALUES NEXTVAL FOR SCHEMA_NAME.SEQUENCE_NAME"
results in :

[BEA][DB2 JDBC Driver][DB2]UNACCEPTABLE SQL STATEMENT"

Hi. Show me the few lines of JDBC that try to use
this SQL and I'll see about getting it fixed for you.

Joe Weinstein at BEA

If you like, followups can go to the online BEA JDBC newsgroup.

[gimme_this_gimme_that]

Following up to weblogic.developer.interest.jdbc

See topic "DB2 sequences and type-4 driver"

[gimme_this_gimme_that]

Not in this case since the text consisted of 3999 'a' characters.

I fathom that it's an IE/textarea/wrap issue.

[Bernd Hohmann]

gi*******************@yahoo.com wrote:

Not in this case since the text consisted of 3999 'a' characters.

I fathom that it's an IE/textarea/wrap issue.

Needs further examination on the output. Just do a System.out.print(...)
on the String you receive from the webform to see if there are any \n or
\r in it.

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Joe Weinstein]

gi*******************@yahoo.com wrote:

Following up to weblogic.developer.interest.jdbc

See topic "DB2 sequences and type-4 driver"

Hi. I didn't see any post yet. You can also email me at
j
o
e
AT
b
e
a
DOT
c
o
m

[Mark A]

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:P9******************************@rogers.com.. .

It was 4K.

How do you expect to store a column larger than 4K in 4K page (and 4K
bufferpool)? Long varchar is OK because the data is not stored in the row
(like LOB's) and it does not make use of bufferpools (data is read and
written directly to disk).

[Larry Menard]

I know that. I wasn't asking a question, I was just sharing an experience.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Mark A" <no****@nowhere.com> wrote in message
news:2t******************************@comcast.com. ..

"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:P9******************************@rogers.com.. .

It was 4K.

How do you expect to store a column larger than 4K in 4K page (and 4K
bufferpool)? Long varchar is OK because the data is not stored in the row
(like LOB's) and it does not make use of bufferpools (data is read and
written directly to disk).