Trafodion 使用odb加载中文

在之前的一篇文章中，我们介绍如何安装并使用odb工具，请见http://blog.csdn.net/post_yuan/article/details/54631881
在另外一篇文章我们也通过几个简单的例子介绍如何用odb进行基本的数据加载、抽取、转换工作，请见http://blog.csdn.net/post_yuan/article/details/52671874

现在我们考虑一下如果原始数据文件中有中文，使用odb加载会怎么样，

1 创建测试表

>>create table test_chinese(a int, b varchar(20) character set utf8);

--- SQL operation complete.
>>showddl test_chinese;

CREATE TABLE TRAFODION.SEABASE.TEST_CHINESE
  (
    A                                INT DEFAULT NULL NOT SERIALIZED
  , B                                VARCHAR(20 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.TEST_CHINESE TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

2 创建测试文件

[trafodion@n12 bin64]$ cat test_chinese.txt
1,我爱中国
2,你好中国
3,安徽卫视

3 odb加载测试文件

[trafodion@n12 bin64]$ ./odb64luo -u trafodion -p traf123 -d traf -l src=test_chinese.txt:tgt=trafodion.seabase.test_chinese:fs=,:truncate
odb [2017-04-21 14:19:25]: starting ODBC connection(s)... 0
Connected to Trafodion
[0.0.0]--- command executed in 13.673s (prep 0.001s, exec 13.672s, fetch 0.000s/0.000s)
[0] 3 records inserted [commit]
[0] odb version 1.1.0 Load(2) statistics:
        [0] Target table: TRAFODION.SEABASE.TEST_CHINESE
        [0] Source: test_chinese.txt
        [0] Pre-loading time: 22.000 s (00:00:22.000)
        [0] Loading time: 0.174 s(00:00:00.174)
        [0] Total records read: 3
        [0] Total records inserted: 3
        [0] Total number of columns: 2
        [0] Total bytes read: 53
        [0] Average input row size: 17.7 B
        [0] ODBC row size: 91 B (data) + 16 B (len ind)
        [0] Rowset size: 100
        [0] Rowset buffer size: 10.45 KiB
        [0] Load throughput (real data): 0.297 KiB/s
        [0] Load throughput (ODBC): 1.532 KiB/s
odb [2017-04-21 14:19:47]: exiting. Session Elapsed time 22.211 seconds (00:00:22.211)

4 trafci查询加载数据

SQL>select * from test_chinese;

A           B
----------- --------------------------------------------------------------------------------
          1 ��中�
          2 ä½ å¥½ä¸­å�½
          3 �徽��

--- 3 row(s) selected.

从上面结果可以看出，默认情况下odb插入中文，通过trafci/jdbc查询乱码，这可以通过以下方法解决。

1 在odbc.ini配置中添加ClientCharSet = UTF8

[trafodion@n12 bin64]$ cat /etc/odbc.ini
[ODBC]
AppUnicodeType=utf16

[traf]
Description                 = Trafodion DSN
Driver                      = Trafodion
Catalog                     = TRAFODION
Schema                      = SEABASE
DataLang                    = 0
FetchBufferSize             = SYSTEM_DEFAULT
Server                      = TCP:10.10.11.12:23400
SQL_ATTR_CONNECTION_TIMEOUT = SYSTEM_DEFAULT
SQL_LOGIN_TIMEOUT           = SYSTEM_DEFAULT
SQL_QUERY_TIMEOUT           = NO_TIMEOUT
ServiceName                 = TRAFODION_DEFAULT_SERVICE
ClientCharSet               = UTF8

2 重新加载数据

3 trafci查询加载数据

SQL>select * from test_chinese;

A           B
----------- --------------------------------------------------------------------------------
          1 我爱中国
          2 你好中国
          3 安徽卫视

--- 3 row(s) selected.