DB2 AS/400 query via JDBC?

Question

I'm trying to work on a Java project that needs to view data from an existing DB2 AS/400 server, but keeps on telling me this error:

com.ibm.db2.jcc.am.to: [jcc][t4][10379][11959][4.8.87] Client disconnect exception encountered: [jcc][10275][10261]Unsupported ccsid, encoding, or locale: "65535". ERRORCODE=-4499, SQLSTATE=null
    at com.ibm.db2.jcc.am.gd.a(gd.java:321)
    at com.ibm.db2.jcc.am.gd.a(gd.java:347)
    at com.ibm.db2.jcc.t4.fc.e(fc.java:2245)
    at com.ibm.db2.jcc.t4.fc.e(fc.java:2134)
    at com.ibm.db2.jcc.t4.db.c(db.java:4439)
    at com.ibm.db2.jcc.t4.db.b(db.java:4384)
    at com.ibm.db2.jcc.t4.db.a(db.java:4370)
    at com.ibm.db2.jcc.t4.eb.oc(eb.java:349)
    at com.ibm.db2.jcc.t4.fb.o(fb.java:830)
    at com.ibm.db2.jcc.t4.fb.g(fb.java:143)
    at com.ibm.db2.jcc.t4.fb.a(fb.java:40)
    at com.ibm.db2.jcc.t4.t.a(t.java:32)
    at com.ibm.db2.jcc.t4.ub.i(ub.java:135)
    at com.ibm.db2.jcc.am.wm.hb(wm.java:1949)
    at com.ibm.db2.jcc.am.wm.a(wm.java:2968)
    at com.ibm.db2.jcc.am.wm.a(wm.java:659)
    at com.ibm.db2.jcc.am.wm.executeQuery(wm.java:643)
    at Main.main(Main.java:78)
Caused by: java.io.UnsupportedEncodingException: [jcc][10275][10261]Unsupported ccsid, encoding, or locale: "65535".
    at com.ibm.db2.jcc.am.bb.a(bb.java:1125)
    at com.ibm.db2.jcc.t4.fc.e(fc.java:2242)
    ... 15 more

These are my libraries:

 db2jcc4.jar
 db2jcc_license_cisuz.jar

My Code:


    public class Main
    {
        public static void main(String[] args) throws UnsupportedEncodingException
        {
            try
            {

                Class.forName("com.ibm.db2.jcc.DB2Driver");

            }
            catch (ClassNotFoundException e)
            {
                e.printStackTrace();
                return;
            }

            //DB2DataSource
            System.out.println("DB2 driver is loaded successfully");
            Connection conn = null;
            PreparedStatement pstmt = null;
            Statement statement = null;
            ResultSet rset = null;
            boolean found = false;

            try
            {
                Properties properties = new Properties();
                properties.put("user", "USER");         // Set user ID for connection
                properties.put("password", "password");     // Set password for connection
                String url = "jdbc:db2://myserver:446/mydb";

                conn = DriverManager.getConnection(url, properties);


                if (conn != null)
                {
                    System.out.println("DB2 Database Connected");
                }
                else
                {
                    System.out.println("Db2 connection Failed ");
                }


                String sql = "SELECT * FROM ZIPFILES.POLHDR;";
                statement = conn.createStatement();
                rset = statement.executeQuery(sql);


            }
            catch (SQLException e)
            {
                System.out.println("DB2 Database connection Failed");
                e.printStackTrace();
                return;
            }
        }
    }

additional info:

the error is pointing at this line
```
rset = statement.executeQuery(sql);
```
DB2 AS/400 version is V4R4
Table's encoding is CCSID 37, but I tried changing everything to that encoding (ex. CP037, IBM037), but no luck! I also tried looping every possible encoding (haha), but still no luck!

Why don't you use the JTOpen jdbc driver instead of the DB2 connect driver -- see jt400.sourceforge.net — jweberhard
@jweberhard i also tried JTOpen, it says (General security error.:USER), is there something to do in server side? — Kevin
Sorry, I don't know why you are getting a security error. I'm sure you've verified that your user id and password are correct. For the original CCSID error, have you tried setting the CCSID on the user profile that you are connecting with to 37? — jweberhard

mustaccio mustaccio · Accepted Answer · 2015-04-08T11:50:01

According to the error message, the file encoding appears to be 65535 (that is, binary data), and not 37.

Try appending the following properties to the connection string: translate binary=true;ccsid=37;.

DB2 AS/400 query via JDBC?

1 Answers