title: DB2 insert UTF-8 characters on non unicode database with ALT_COLLATE UNICODE
body: <p>I am trying to insert Chinese text in a DB2 Database but not working.</p>
  
  <p>The database is configured by default as ANSI (en_US 819) (and it's a requirement for other applications that use the dame databse) ALT_COLLATE IDENTITY_16BIT is defined and UNICODE tables are created using CCSID UNICODE but unicode characters for Chinese or Korean are not inserted.</p>
  
  <p><strong>Example table:</strong></p>
  
  <pre><code>CREATE TABLE LANGS (
      IDIOMA  char(2) NOT NULL,
      PAIS    char(2) NOT NULL,
      TRADUC  long varchar NOT NULL,
  ) CCSID UNICODE;
  </code></pre>
  
  <p><strong>Example insert:</strong></p>
  
  <pre><code>INSERT INTO LANGS (IDIOMA,PAIS,TRADUC) VALUES ('zh','TW','其他');
  </code></pre>
  
  <p><strong>System Information:</strong></p>
  
  <ul>
  <li>Server: DB2 9.7 on Ubuntu 64bit (en_US)</li>
  <li>Client: Windows 7 32bit (es_ES) Java 7 with db2jcc.jar</li>
  </ul>
  
  <p><strong>Example Java extract:</strong></p>
  
  <pre><code>Class.forName("com.ibm.db2.jcc.DB2Driver");
  
  ...
  
  Properties props = new Properties();
  props.setProperty("user", user);
  props.setProperty("password", pass);
  props.setProperty("DB2CODEPAGE", "1208");
  props.setProperty("retrieveMessagesFromServerOnGetMessage", "true");
  
  con = DriverManager.getConnection(url, props);
  
  ...
  
  Statement statement = con.createStatement();
  statement.execute(sql);
  
  ...
  statement.close();
  con.close();
  </code></pre>
  
  <p><strong>DB cfg get</strong></p>
  
  <p>DB2 Database locale configuration</p>
  
  <pre>
  Territorio de base de datos                             = en_US;
  Página de códigos de base de datos                      = 819 
  Conjunto de códigos de base de datos                    = iso8859-1 
  Código de país/región de base de datos                  = 1 
  Secuencia de clasificación de base de datos             = UNIQUE 
  Orden de clasificación alternativo        (ALT_COLLATE) = IDENTITY_16BIT
  Tamaño de página de base de datos                       = 4096
  </pre>
  
  <p>Statements are executed correctly and rows appears correctly in the database for:</p>
  
  <ul>
  <li>en_GB </li>
  <li>en_US</li>
  <li>es_ES</li>
  <li>pt_PT</li>
  </ul>
  
  <p>but not for:</p>
  
  <ul>
  <li>cy_GB </li>
  <li>ko_KR</li>
  <li>zh_TW</li>
  </ul>
  
  <p>Insert from command line with db2cmd also does not work for this languages (Inserts but with only 1 byte.</p>
  
  <p>Insert from command line in a Linux environment localized as zh_TW <strong>works</strong>. 
  Insert from command line in a Linux environment localized as en_US.utf-8 <strong>works</strong>. </p>
  
  <p>Never work on Java on these environments.</p>
  
  <hr>
  
  <p>Using "X" as prefix form the VARCHAR field is not an option due some restrictions and the SQL works on two environments.</p>
  
  <p>I think it may be some encoding problem on Client, or server due to configuration, file or sql encoding.</p>
  
  <hr>
  
  <p><strong>Update:</strong></p>
  
  <p>I tried also to load a UTF-8 file with the SQLs. the file loads correctly and debugging the SQL with UTF-8 characters is correctly passed to the Statement but the result is the same.</p>
  
  <pre><code>new InputStreamReader(new FileInputStream(file),"UTF-8")
  
  ...
  
  private void executeLineByLine(Reader reader) throws SQLException {
      StringBuffer command = new StringBuffer();
      try {
          BufferedReader lineReader = new BufferedReader(reader);
          String line;
          while ((line = lineReader.readLine()) != null) {
              command = handleLine(command, line);
          }
          checkForMissingLineTerminator(command);
      } catch (Exception e) {
          String message = "Error executing: " + command + ".  Cause: " + e;
          printlnError(message);
          throw new SQLException(message, e);
      }
  }
  
  
  private StringBuffer handleLine(StringBuffer command, String line) throws SQLException, UnsupportedEncodingException {
      String trimmedLine = line.trim();
      if (lineIsComment(trimmedLine)) {
          println(trimmedLine);
      } else if (commandReadyToExecute(trimmedLine)) {
          command.append(line.substring(0, line.lastIndexOf(delimiter)));
          command.append(LINE_SEPARATOR);
          println(command);
          executeStatement(command.toString());
          command.setLength(0);
      } else if (trimmedLine.length() &gt; 0) {
          command.append(line);
          command.append(LINE_SEPARATOR);
      }
      return command;
  }
  
  private void executeStatement(String command) throws SQLException, UnsupportedEncodingException {
      boolean hasResults = false;
      Statement statement = connection.createStatement();
      hasResults = statement.execute(command);
      printResults(statement, hasResults);
      statement.close();
  }
  </code></pre>
  
  <p><strong>Update2:</strong></p>
  
  <p>It's not possible to change the data types. The database is part of other systems and already with data.</p>
  
  <p>The database is installed on 7 different servers on three of it that the data is inserted using Linux in a UTF-8 shell the data was inserted correctly from db2 command line. </p>
  
  <p>From windows db2 command line or using Java it's not possible to insert the characters correctly.</p>
  
  <p>Changing the Java sources to UTF-8 source makes the System.out prints the SQL correctly like i see debugging the sql variable.</p>
  
  <p>When i insert this test SQL. It is shown correctly with chines characters in the System.out and in the Statement internal variable</p>
  
  <pre><code>INSERT INTO LANGS (IDIOMA,PAIS,TRADUC) VALUES ('zh','TW','TEST1 其他 FIN TEST1');
  </code></pre>
  
  <p>But in the database the test appears as:</p>
  
  <pre><code>TEST3  FIN TEST3
  </code></pre>
  
  <p>HEX reprentation:</p>
  
  <pre><code>54 45 53 54 33 20 1A 1A 1A 1A 1A 1A 1A 1A 20 46 49 4E 20 54 45 53 54 33
  T  E  S  T  3  _  ?  ?  ?  ?  ?  ?  ?  ?  _  F  I  N  _  T  E  S  T  3
  </code></pre>
  
  <p>I think that probably DB2 Java client is using allways Windows codepage (in this case is ISO-8859-1 or cp1252) instead of UTF-8 or the server is converting the data using the main collate instead the alternative collate of the table.</p>
  
  <p><strong>Update3:</strong></p>
  
  <p>I installed a Java SQL tool called DbVisualizer and using this tool on windows when a paste in the SQL panel the SQL and run it is inserted correctly in the databse.</p>
  
  <p>This makes me to suspect that is not a problem of installation or data types. Probably are one of this three factors.</p>
  
  <ul>
  <li>Client configuration</li>
  <li>Server properties sended when client connects</li>
  <li>Driver type of version used</li>
  </ul>