DS 3.1.1 Db2 9.7 FP5
I'm getting a java sql exception error code 4220 whenever I try to browse the data in the syscat.columns view on any db that is cataloged. Error msg is attached. I can browse other columns ok. I searched the web re the 4220 error and it suggests that bad data causes this but it's even happening in a newly created sample db. What could be the cause?
This topic has been locked.
7 replies Latest Post - 2013-02-04T21:55:16Z by SystemAdmin
Pinned topic java sql exception errorcode 4220
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-04T21:55:16Z at 2013-02-04T21:55:16Z by SystemAdmin
Re: java sql exception errorcode 42202012-06-08T19:18:02Z in response to SystemAdminI can reproduce your issue but I suspect this is not related to Data Studio, since I can also reproduce the error in a stand-alone java program running a query "select * from syscat.columns"
jcc109310420http://3.63.123 Invalid data conversion: Parameter instance SYSIBM
is invalid for the requested conversion to integer. ERRORCODE=-4220, SQLSTATE=
Let me try to pursue this with our JDBC/JCC team and I will get back to you.
Re: java sql exception errorcode 42202012-06-13T14:22:35Z in response to Ivan_CAHi,
I have not forgotten this. I'm still pursuing this issue with the JCC team. Also, working on trying to provide a alternative solution from the Data Studio if it comes to that. Hopefully I will have an update soon.
Re: java sql exception errorcode 42202012-06-15T19:39:08Z in response to Ivan_CAHi Andrew,
OK here's my take on this.
This -4220/CharConversionException exception is caused by using getString() to read data from columns LOW2KEY and HIGH2KEY in the DB2 LUW SYSIBM.SYSCOLUMNS . Even though DB2 LUW doc says these columns are VARCHAR(128), they seem to contain binary data in some cases and that throws an exception in jdbc. You can reproduce the error in a simple java program with something like this.
String var1= "";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT LOW2KEY FROM SYSCAT.COLUMNS");
// retrieve and display the result from the SELECT statement
This issue is actually not new to us. It was reported last year but in the end it was not addressed since is not a DS issue, but something between jdbc and db2 catalog.
From the DS side, there's not much we can do besides having a special treatment for syscolumns (no other table with varchar columns has this problem). If you feel strongly about having this addressed in DS, please let me know and I will try to make a case to have this fixed in the next release, pending Development approval.
Hope this helps,
Re: java sql exception errorcode 42202012-06-19T18:30:17Z in response to SystemAdminHi Andrew,
I opened new a defect for Data Studio to try to workaround the problem for the next release. This was reported to DB2 and JDBC teams last year but there were concerns about changing the db2 catalog and the risk of such change. Hopefully we will be able to do something from the DS side but, as I said before, it needs to be approved by Development.
Re: java sql exception errorcode 42202013-02-04T21:55:16Z in response to SystemAdminThere is another odd situation that will cause the JDBC driver to throw this exception. If you create a table with decimal values and then load the table with data that does not match the precision of the column, you'll get this error too. It is a bit difficult to run it down since the exception is indicating a character conversion error which you would expect to apply to character related columns in the database. Here's an example:
Create table oops1
dec_col1 decimal(14,2), -- notice precision is 2 decimal places
Then you use the load facility in Data Studio to load data like the following (CSV format for clarity):
19234.12, "Description One"
14212.135, "Description Two" -- notice precision is exceeded - 3 decimal places
These two rows will load fine without any errors. When you right click on the table name in the database object list and choose "browse data", you'll get this exception. It is interesting that if you open a SQL Editor and issue a select * from "db2admin"."oops1" you'll get the results in the SQL Results window without any Java related complaints.
Just thought I'd share this tidbit since I just spent a few hours troubleshooting this in DB2 v10.1.
Hope this helps.
Client Technical Professional
Information Management Channel Sales