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, Commented Apr 8, 2015 at 16:07
@jweberhard i also tried JTOpen, it says (General security error.:USER), is there something to do in server side? — Kevin
– Kevin, Commented Apr 10, 2015 at 0:38
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
– jweberhard, Commented Apr 11, 2015 at 20:31

mustaccio · Accepted Answer · 2015-04-08 11:50:01Z

2

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;.

answered Apr 8, 2015 at 11:50

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kevin Over a year ago

how to add it if my connection string is String url = "jdbc:db2://myserver:446/mydb";? thanks

mustaccio Over a year ago

Just like any other properties: String url = "jdbc:db2://myserver:446/mydb;translate binary=true;ccsid=37;"

mustaccio Over a year ago

Sorry, a typo: properties are separated from the rest of the URL by a colon: `tring url = "jdbc:db2://myserver:446/mydb:translate binary=true;ccsid=37;"

Abs Over a year ago

For me it was a matter of downloading and setting in path latest db2 driver jars www-01.ibm.com/support/docview.wss?uid=swg21363866

Collectives™ on Stack Overflow

DB2 AS/400 query via JDBC?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related