Results 1 to 12 of 12
  1. #1
    Join Date
    May 2012
    Posts
    7

    Unanswered: Why didn't occur same error on DB2 V8.2?

    Hello,

    DB2 upgraded version from V8.2 to V9.7 and applications that were used migrated on New System recently.

    After that, the application occured some error when they was ran with DB2 V9.7.

    Error message are as follows :

    1) Invalid data conversion: Wrong result column type for requested conversion ERRORCODE=-4461, SQLSTATE=42815815

    [12. 5. 16 10:27:46:402 KST] FFDC Exception:com.ibm.db2.jcc.am.io SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet .getBytes ProbeId:931 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccResultSet@ 40e9852
    com.ibm.db2.jcc.am.io: [jcc][t4][1092][11638][3.57.82] 유효하지 않은 데이터 변환: 요청된 변환에 대해 잘못된 결과 컬럼 유형입니다. ERRORCODE=-4461, SQLSTATE=42815
    at com.ibm.db2.jcc.am.bd.a(bd.java:676)
    at com.ibm.db2.jcc.am.bd.a(bd.java:60)
    at com.ibm.db2.jcc.am.bd.a(bd.java:120)
    at com.ibm.db2.jcc.am.gc.G(gc.java:1377)
    at com.ibm.db2.jcc.am.zl.getBytes(zl.java:1077)
    at com.ibm.db2.jcc.am.zl.getBytes(zl.java:1491)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getBytes (WSJdbcResultSet.java:913)

    2) Illegal conversion: can not convert from "java.lang.String" to "byte[]" ERRORCODE=-4474, SQLSTATE=null
    [12. 5. 16 17:43:01:600 KST] FFDC Exception:com.ibm.db2.jcc.am.SqlException SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedS tatement.setBytes ProbeId:654 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccPreparedSt atement@3b52789
    com.ibm.db2.jcc.am.SqlException: [jcc][1083][10406][3.57.82] 잘못된 변환: "byte[]"에서 "java.lang.String"(으)로 변환할 수 없음 ERRORCODE=-4474, SQLSTATE=null
    at com.ibm.db2.jcc.am.bd.a(bd.java:660)
    at com.ibm.db2.jcc.am.bd.a(bd.java:60)
    at com.ibm.db2.jcc.am.bd.a(bd.java:94)
    at com.ibm.db2.jcc.am.fc.a(fc.java:686)
    at com.ibm.db2.jcc.am.lm.a(lm.java:1299)
    at com.ibm.db2.jcc.am.lm.setBytes(lm.java:1264)
    at com.ibm.ws.rsadapter.spi.InternalGenericDataStoreH elper.psSetBytes(InternalGenericDataStoreHelper.ja va:695)

    I found out the reason that occured error when a column that defined varchar(2000) retrieved using getByte() in JAVA application.

    In DB2 maual, the column defined VARCHAR retrieved using getString() in JAVA application.

    But the application worked well on DB2 V8.2 without error.

    so, I would like to know that why the same error didn't occur on DB2 V8.2.

    Please let me know. Thanks.
    Last edited by eslee; 05-21-12 at 05:59.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Please post your operating-system version that runs the DB2 v9.7.

    There are many changes (new things, changed things, discontinued things) between DB2 v8.2 and v9.7.

    Any of these changes can cause the results that you see (and some results that you will see in future).

    When changing DB2 versions you must *expect* problems
    like these, and plan and test carefully for them.

    IBM documents every change in the Infocentre for each new DB2 version.
    You must carefully impact-assess every change and include a
    plan in your migration-plan.


    Your post says "migrated on new system", please clarify:

    Did you 'create database' on V9.7 and then import/load data files that you exported from v8.2 ?

    Did you 'restore database' on V9.7 from a backup taken on V8.2?

    Did you install DB2 v9.7 on the same machine that ran V8.2 and then did 'migrate database'?

    The second problem (your (2)) happens when the
    territory or code-page of the database changed.
    Compare the 'db2 get db cfg' output from both V8.2 and V9.7.

  3. #3
    Join Date
    May 2012
    Posts
    7
    Thanks for your reply.

    It is precheck on test machine Before final migration.

    Did you 'create database' on V9.7 and then import/load data files that you exported from v8.2 ?
    YES, I made the database using command on V9.7 and used db2move command on both DB2 system.

    Did you install DB2 v9.7 on the same machine that ran V8.2 and then did 'migrate database'?
    No, It's different OS.
    V8.2 installed on AIX 5 and V9.7 installed on Windows XP.

    Of course, I think that the code page was changed on Test system(=Windows XP).

    Is DB2 code page changed affected by Java application ?

    I thinks that If it is the cause of the DB2 code page changed , All applications must not work well.
    But some application works well.
    It is happned using a particular java method like getByte().

    I know that JAVA application have to fix method.
    I wonder Why same error didn't occur on DB2 V8.2.
    Didn't check strictly Java method on DB2 V8.2 ?
    Last edited by eslee; 05-21-12 at 07:35.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Do you really mean Windows-XP, or some different server version of Windows like Windows-Server-2008 etc.

    Please post the values of the following items from both the V8.2 and the V9.7 databases:

    Database territory
    Database code page
    Database code set

  5. #5
    Join Date
    May 2012
    Posts
    7
    I'm Sorry. My English is not perfect, so I made you confuse.

    I rechecked TEST Envrionment.

    <Source System>
    AIX 5 , DB2 V8.2

    Database territory = KR
    Database code page = 970
    Database code set = IBM-eucKR
    Database country/region code = 82


    <Test System>
    Windows 7 Professional, DB2 9.7

    데이터베이스 지역 = KR
    데이터베이스 코드 페이지 = 1208
    데이터베이스 코드 세트 = UTF-8
    데이터베이스 국가/지역 코드 = 82
    Last edited by eslee; 05-22-12 at 02:01.

  6. #6
    Join Date
    May 2012
    Posts
    7
    I had a test.

    I made two databases configure same codepage and codeset like the source and the test system.

    If codepage or codeset is problem,
    it will has a different result on each databases when a sample application run.

    But same error occured on both databases.

    I thought that it's a application developer's fault.
    but he said, it's a DB2's defect.
    so, I want to know what it's the really DB2's defect.

    If I'm wrong, please let me know.

    please refer the result.

    Thanks

    ================================================== ==

    $ db2 get db cfg for testdb | more

    Database Configuration for Database testdb

    Database configuration release level = 0x0d00
    Database release level = 0x0d00

    Database territory = kr
    Database code page = 970
    Database code set = IBM-eucKR
    Database country/region code = 82
    Database collating sequence = UNIQUE

    ================================================== ===================
    $ db2 get db cfg for sample | more

    Database Configuration for Database sample

    Database configuration release level = 0x0d00
    Database release level = 0x0d00

    Database territory = KR
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 82
    Database collating sequence = IDENTITY

    ================================================== ===================

    $ db2 connect to testdb

    Database Connection Information

    Database server = DB2/SUNX8664 9.7.5
    SQL authorization ID = DB2INST1
    Local database alias = TESTDB

    $ db2 "create table tb1 ( id int , c2 varchar(2000) ,c3 varchar(2000) for bit data )"
    DB20000I The SQL command completed successfully.
    $ db2 list tables

    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------
    TB1 DB2INST1 T 2012-05-22-11.05.57.975463

    1 record(s) selected.

    $ db2 " insert into tb1 values ( 1 , '0x1234567890','0x1234567890') "
    DB20000I The SQL command completed successfully.

    ================================================== ====================================
    $ db2 connect to sample

    Database Connection Information

    Database server = DB2/SUNX8664 9.7.5
    SQL authorization ID = DB2INST1
    Local database alias = SAMPLE

    $ db2 "create table tb1 ( id int , c2 varchar(2000) ,c3 varchar(2000) for bit data )"
    DB20000I The SQL command completed successfully.

    $ db2 list tables

    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------
    TB1 DB2INST1 T 2012-05-22-11.05.33.431226

    1 record(s) selected.

    $ db2 " insert into tb1 values ( 1 , '0x1234567890','0x1234567890') "
    DB20000I The SQL command completed successfully.

    ================================================== =====================================
    TESTDB>
    $ java JdbcTest
    DB2 Query result: 1
    com.ibm.db2.jcc.am.SqlException: [jcc][1093][10418][3.63.75] 유효하지 않은 데이터 변환: 매개변수 인스턴스 0x1234567890이(가) byte(으)로 요청된 변환에 대해 유효하지 않습니다. ERRORCODE=-4220, SQLSTATE=null

    ================================================== ===================================
    SAMPLE>
    $ java JdbcTest
    DB2 Query result: 1
    com.ibm.db2.jcc.am.SqlException: [jcc][1093][10418][3.63.75] 유효하지 않은 데이터 변환: 매개변수 인스턴스 0x1234567890이(가) byte(으)로 요청된 변환에 대해 유효하지 않습니다. ERRORCODE=-4220, SQLSTATE=null


    <TEST Java Source>
    ==============================================
    import java.sql.Connection ;
    import java.sql.DriverManager ;
    import java.sql.ResultSet ;
    import java.sql.Statement ;
    import java.sql.SQLException;

    class JdbcTest {
    public static void main (String args[]) {
    try {
    Class.forName("com.ibm.db2.jcc.DB2Driver");
    }
    catch (ClassNotFoundException e) {
    System.err.println (e) ;
    System.exit (-1) ;
    }
    try {
    Connection connection = DriverManager.getConnection(
    "jdbc:db2://10.0.2.15:50000/sample", "db2inst1", "abcd1234");

    String query = "select id,c2,c3 from tb1" ;

    Statement statement = connection.createStatement () ;
    ResultSet rs = statement.executeQuery (query) ;

    while ( rs.next () )
    {
    System.out.println ("DB2 Query result: " + rs.getInt (1) ) ;
    System.out.println ("DB2 Query result: " + rs.getByte(2) ) ;
    System.out.println ("DB2 Query result: " + rs.getByte(3) ) ;
    }
    connection.close () ;
    }
    catch (java.sql.SQLException e) {
    System.err.println (e) ;
    System.exit (-1) ;
    }
    }
    }
    Last edited by eslee; 05-22-12 at 02:02.

  7. #7
    Join Date
    Apr 2012
    Posts
    7
    have you changed your database drivers (jcc jars) to the ones supplied with db v 9.7 or still using v 8 drivers? It can cause this behavior.

  8. #8
    Join Date
    May 2012
    Posts
    7
    I changed the database dirvers (=db2 v9.7) on test system(=windows 7 pro.)

    The last test result(=#5) used only db2 v9.7.

    Thanks.

  9. #9
    Join Date
    Apr 2012
    Posts
    7
    As for me the problem exactly in drivers...Try to use v8 drivers...I think with it it should run ok. And if so we will try to read ibm to find out what is the reason for that or just a bug.

  10. #10
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    If you move data from a fixed double-byte codeset to a variable-length encoding UTF-8 then expect data conversions and length-changes.

    Your source database has codepage 970 which is a double-byte codepage
    where every character needs 2-bytes.

    Your target database is UTF-8 (variable-length encoding).

    The Table-32 on this page:
    http://publib.boulder.ibm.com/infoce.../r0004565.html
    has no line for UTF-8 for territory identifier KR for Windows operating-system (if your DB2-server runs on Windows, but it has a line for
    UTF-8 on Solaris).

    If your DB2-server runs on Windows the Table-32 shows that for territory KR with Windows, the
    DB2 supports codepage 1363 and codeset 1363, territory-code 82
    and also this is compatible with codepage 970.

    So when moving from 970 to 1363 I expect full compatibility (no length changes) since both are double-byte and in group D-3.

    So if your new DB2-server is on Windows, then maybe you should make a test with a Windows database created with codepage 1363 and territory-code 82...

    Your post shows DB2-server running on Solaris x64.
    Previously you mentioned only AIX or Windows.
    So I am confused - does your DB2-server run on Windows-7 or Solaris-X64.

    ---

    for JdbcTest - you get 'error occurred during data conversion' (-4420).

    In "JdbcTest" the connection string is hard-coded
    Connection connection = DriverManager.getConnection(
    "jdbc:db2://10.0.2.15:50000/sample", "db2inst1", "abcd1234");

    So this code will always connect to the same database (sample) which is the UTF-8 database.

    I think that the cause of the problem is that rs.getByte() is not
    the correct method to use for variable-length encodings.

    Instead try rs.getCharacterStream()

  11. #11
    Join Date
    May 2012
    Posts
    7
    Thanks for your answer.

    I tried to use v8 jdbc driver on my test environment.(=vmware)
    But same error occured.
    Last edited by eslee; 05-22-12 at 06:10.

  12. #12
    Join Date
    May 2012
    Posts
    7
    I'm sorry.

    I will try to test your advise.



    Thanks.


    # 1,2,3,4 POST
    <source system> <test system>
    AIX 5.0 Windows 7 pro.


    I made another test environment on my laptop.
    I changed database name when the application ran.

    # 5 POST
    < my labtop(vmware)>
    solaris 10 x86
    Last edited by eslee; 05-22-12 at 06:16.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •