Topic
7 replies Latest Post - ‏2013-02-04T21:55:16Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

Pinned topic java sql exception errorcode 4220

‏2012-06-08T03:51:47Z |
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?

screenshot attached
Updated on 2013-02-04T21:55:16Z at 2013-02-04T21:55:16Z by SystemAdmin
  • Ivan_CA
    Ivan_CA
    52 Posts
    ACCEPTED ANSWER

    Re: java sql exception errorcode 4220

    ‏2012-06-08T19:18:02Z  in response to SystemAdmin
    I 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=
    null

    Let me try to pursue this with our JDBC/JCC team and I will get back to you.

    Thanks,
    Ivan
    • Ivan_CA
      Ivan_CA
      52 Posts
      ACCEPTED ANSWER

      Re: java sql exception errorcode 4220

      ‏2012-06-13T14:22:35Z  in response to Ivan_CA
      Hi,

      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.

      Thanks,
      Ivan
      • Ivan_CA
        Ivan_CA
        52 Posts
        ACCEPTED ANSWER

        Re: java sql exception errorcode 4220

        ‏2012-06-15T19:39:08Z  in response to Ivan_CA
        Hi 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
        while (rs.next())
        {
        var1= rs.getString(1);
        System.out.println(var1);
        System.out.println();
        }
        rs.close();
        stmt.close();
        }
        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,

        Thanks,
        Ivan
  • SystemAdmin
    SystemAdmin
    1632 Posts
    ACCEPTED ANSWER

    Re: java sql exception errorcode 4220

    ‏2012-06-17T22:43:08Z  in response to SystemAdmin
    Ivan, I think this should be reported to Db2 as DS is now the strategic tool for DB2 (as of V10) and all functions should work.
    • Ivan_CA
      Ivan_CA
      52 Posts
      ACCEPTED ANSWER

      Re: java sql exception errorcode 4220

      ‏2012-06-19T18:30:17Z  in response to SystemAdmin
      Hi 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.

      Thanks,
      Ivan
  • SystemAdmin
    SystemAdmin
    1632 Posts
    ACCEPTED ANSWER

    Re: java sql exception errorcode 4220

    ‏2012-06-27T03:41:19Z  in response to SystemAdmin
    You're correct, the java/Db2 issue should be resolved the Db2 team
    • SystemAdmin
      SystemAdmin
      1632 Posts
      ACCEPTED ANSWER

      Re: java sql exception errorcode 4220

      ‏2013-02-04T21:55:16Z  in response to SystemAdmin
      There 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
      description varchar(30)
      )

      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.

      Dave Bernhardt
      Client Technical Professional
      Information Management Channel Sales
      dabernha@us.ibm.com