Symfoware

Symfowareについての考察blog

DB2 10.5にDebian + PHP5から接続する

CentOS 6.5にDB2 10.5をインストールしてみました。
CentOS 6.5にDB2 10.5 Express-Cをインストールする

Debian + Apache2 + PHP5の環境から接続してみます。




Apache、PHPの準備



DebianにApacheとPHPをインストールします。


# apt-get install apache2
# apt-get install libapache2-mod-php5




DB2に接続するモジュールをインストールするときに必要なものを追加でインストール。


# apt-get install php5-dev
# apt-get install make




インストールはこちらを参考に行いました。
http://www.php.net/manual/ja/ibm-db2.installation.php

説明の中で「pecl」というキーワードが出てきますが、これがよくわからなかった。
「php-pear」をインストールすると、合わせて「pecl」がインストールされます。


# apt-get install php-pear




これで準備OKです。





接続ドライバ



PythonでDB2に接続するとき「IBM Data Server Driver Package」をダウンロードしました。
DB2 10.5 Express-CにUbuntu + Pythonから接続する(ibm_db使用)

今回もこれが必要です。
ダウンロードサイトから
「ibm_data_server_driver_package_linuxx64_v10.5.tar.gz」
をダウンロードしておきます。


ダウンロードしたファイルを「/usr/local/src/」にコピーしました。
解凍します。


# cd /usr/local/src/
# tar zxf ibm_data_server_driver_package_linuxx64_v10.5.tar.gz




解凍して出来た「dsdriver」に移動。
bashでinstallDSDriverを実行します。
これでドライバの解凍が行われます。


# cd dsdriver/
# bash installDSDriver

-
- 'installDSDriver' script has started ...
-
- Unzipping and Copying each driver files to install path ...
--> SUCCESS
- Generating db2profile & db2cshrc files ...
- Generating db2profile32 & db2cshrc32 files ...
--> SUCCESS
- Performing post-installation clean up ...
--> SUCCESS
-
- 'installDSDriver' completed successfully.
- Check /usr/local/src/dsdriver/./installDSDriver.log file for complete details.
-




これでドライバが
「/usr/local/src/dsdriver」
に展開されました。







IBM DB2 関数



IBM DB2、Cloudscape および Apache Derby
こちらの関数が使えるようにします。


pecl install ibm_db2



でインストールを実行。

途中、ドライバを展開したパスの入力を求められるので
「/usr/local/src/dsdriver」と入力します。


# pecl install ibm_db2
downloading ibm_db2-1.9.5.tgz ...
Starting to download ibm_db2-1.9.5.tgz (157,720 bytes)
.................................done: 157,720 bytes
5 source files, building
running: phpize
Configuring for:
PHP Api Version:         20100412
Zend Module Api No:     20100525
Zend Extension Api No: 220100525
DB2 Installation Directory? : /usr/local/src/dsdriver
building in /tmp/pear/temp/pear-build-rootYxR7Ah/ibm_db2-1.9.5

(略)

Build process completed successfully
Installing '/usr/lib/php5/20100525/ibm_db2.so'
install ok: channel://pecl.php.net/ibm_db2-1.9.5
configuration option "php_ini" is not set to php.ini location
You should add "extension=ibm_db2.so" to php.ini




これでインストール出来ました。
iniファイルを編集し、「ibm_db2.so」をロードするよう修正します。


# vi /etc/php5/conf.d/10-pdo.ini




以下の行を追記


extension=ibm_db2.so




Apacheを再起動


# /etc/init.d/apache2 restart




phpinfoを表示してみると、ibm_db2が認識されました。

374_01.png





接続テスト



http://www.php.net/manual/ja/function.db2-connect.php
ここのサンプルのまんまですが、こんなプログラムで接続を確認しました。


  1. <?php
  2. $database = 'SAMPLE';
  3. $user = 'db2inst1';
  4. $password = 'P@ssw0rd';
  5. $hostname = '192.168.1.103';
  6. $port = 50000;
  7. $conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" .
  8. "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
  9. $conn = db2_connect($conn_string, '', '');
  10. if ($conn) {
  11.     echo "接続に成功しました。";
  12.     db2_close($conn);
  13. }
  14. else {
  15.     echo "接続に失敗しました。";
  16. }




上手くインストールできているようです。

374_02.png






データの登録、検索



こんなテーブルを作成しておきます。


CREATE TABLE test (id int, name varchar(100))





データの登録、検索はこんな感じになりました。


  1. <?php
  2. $database = 'SAMPLE';
  3. $user = 'db2inst1';
  4. $password = 'P@ssw0rd';
  5. $hostname = '192.168.1.103';
  6. $port = 50000;
  7. $conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" .
  8. "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
  9. $conn = db2_connect($conn_string, '', '');
  10. # 登録
  11. db2_exec($conn, "INSERT INTO test (id, name) VALUES(1, 'PHPから登録テスト')");
  12. # 登録したデータを検索
  13. $stmt = db2_exec($conn, "SELECT * FROM test");
  14. while ($row = db2_fetch_array($stmt)) {
  15.     echo $row[0] . '<br/>';
  16.     echo $row[1] . '<br/>';
  17. }
  18. db2_close($conn);




とりあえず動いてくれているようです。

374_03.png


テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/16(日) 16:55:38|
  2. DB2
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

DB2 10.5にPythonからXML列へデータを登録する(ibm_db使用)

Ubuntuにibm_dbをインストールして、PythonからDB2に接続してみました。
DB2 10.5 Express-CにUbuntu + Pythonから接続する(ibm_db使用)

今回は、XML列にデータを登録してみます。



テーブルの準備



登録のテスト用に、こんなテーブルを作成しました。


CREATE TABLE test (doc XML)






XMLを文字列として登録



SQL文にXMLな文字列を含めて登録してみます。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. sql = u"""INSERT INTO test (doc) VALUES
  7. ('<root>
  8.     <test>テストデータです</test>
  9. </root>')"""
  10. cur.execute(sql)
  11. con.commit()





あっさり登録出来ました。

ちなみに、不正なXMLを登録しようとすると、こんなエラーが発生します。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. # XMLの閉じタグが不正
  7. sql = u"""INSERT INTO test (doc) VALUES
  8. ('<root>
  9.     <test>テストデータです</test>
  10. </root_dummy>')"""
  11. cur.execute(sql)
  12. con.commit()




発生するエラーは以下のとおり。


$ python sample.py
Traceback (most recent call last):
File "sample.py", line 17, in <module>
    cur.execute(sql)
File "/usr/local/lib/python2.7/dist-packages/ibm_db-2.0.5-py2.7-linux-x86_64.egg/ibm_db_dbi.py",
 line 1335, in execute
    self._execute_helper(parameters)
File "/usr/local/lib/python2.7/dist-packages/ibm_db-2.0.5-py2.7-linux-x86_64.egg/ibm_db_dbi.py",
    line 1260, in _execute_helper
    raise self.messages[len(self.messages) - 1]
ibm_db_dbi.OperationalError: ibm_db_dbi::OperationalError
Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL10007N Message "0" could not be retrieved.
Reason code: "3". SQLCODE=-16124







プレースフォルダを使用してXML文字列を登録



SQLに直接XMLを含めるのではなく、プレースフォルダを指定して登録してみます。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. # XML
  7. xml = u"""<root>
  8.     <test>テストデータです</test>
  9. </root>"""
  10. cur.execute(u"INSERT INTO test (doc) VALUES (?)", [xml])
  11. con.commit()




エラーになるかな?と思ったのですが登録出来ました。






XML DOMを使用して登録



何かやり方があるのかもしれませんが、DOMオブジェクトを直接渡す方法を
見つけられませんでした。

toxml()でXML文字列を取得し、登録してみました。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.dom.minidom
  4. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  5. # カーソル取得
  6. cur = con.cursor()
  7. # ----- 登録用のDOMオブジェクト生成 -----
  8. # DOM 実装を取得
  9. impl = xml.dom.minidom.getDOMImplementation()
  10. # 「root」というルートノードをもつドキュメントを作成
  11. doc = impl.createDocument(None, 'root', None)
  12. # 最上位のエレメントを取得
  13. top_element = doc.documentElement
  14. # 「test」というエレメントを作成
  15. test = doc.createElement('test')
  16. # 「test」のテキストノード作成
  17. test_text = doc.createTextNode(u'テスト')
  18. # 作成したテキストノードを設定
  19. test.appendChild(test_text)
  20. # ルートノードに、作成したnode1を設定
  21. top_element.appendChild(test)
  22. cur.execute(u"INSERT INTO test (doc) VALUES (?)", [doc.toxml()])
  23. con.commit()








XMLデータの検索



これが地味に苦労しました。
どうも登録したXML列を取得すると


<?xml version="1.0" encoding="UTF-16" ?><root><test>テスト</test></root>



という文字列が帰ってきます。

xml.etree.ElementTree.fromstringを使用してXMLオブジェクトに変換しようと思ったのですが、

まず直接unicodeオブジェクトを渡すパターン。


  1. xml_str = row[0]
  2. elem = xml.etree.ElementTree.fromstring(xml_str)



エラー


UnicodeEncodeError: 'ascii' codec can't encode character u'\ufeff' in position 0: ordinal not in range(128)




utf-16に変換。


  1. xml_str = row[0]
  2. elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16'))



エラー


xml.etree.ElementTree.ParseError: not well-formed (invalid token): line 1, column 1




じゃあ、utf-16beに変換。


  1. xml_str = row[0]
  2. elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))




これで通りました。
ちなみに、「utf-16le」を指定しても正しく処理されます。何なんだ?


試行錯誤の末、登録したXMLデータのtestノードのテキストを取得する
サンプルはこんな感じになりました。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  5. # カーソル取得
  6. cur = con.cursor()
  7. cur.execute(u"SELECT doc FROM test")
  8. row = cur.fetchone()
  9. # XMLオブジェクトに変換
  10. xml_str = row[0]
  11. elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  12. print(elem.find('test').text)








Javaで登録したXMLデータの検索



DB2 10.5のXML列にJavaからデータの検索を行うサンプル
ここでXML列にJavaから郵便番号情報を登録してみました。

このデータをPython + ibm_dbで検索してみます。

xqueryを使用して、郵便番号が「0600000」のデータを検索。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""xquery
  16. for $input in db2-fn:xmlcolumn("POST_XML.ADDRESS")
  17. where ($input/doc[郵便番号='0600000'])
  18. return $input"""
  19. cur.execute(sql)
  20. for row in cur:
  21.     dic = xml_to_dict(row[0])
  22.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)






XMLCASTを使用してWHERE句を記述するパターン。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""SELECT address FROM post_xml
  16. WHERE
  17. XMLCAST(XMLQUERY('$ADDRESS/doc/郵便番号') AS CHAR(7)) = '0600000'"""
  18. cur.execute(sql)
  19. for row in cur:
  20.     dic = xml_to_dict(row[0])
  21.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)





どちらも実行結果はこんな感じになります。


$ python sample.py
北海道札幌市中央区以下に掲載がない場合







住所の一部を指定して検索



町域名に「銀座」という文字列が含まれるデータを検索してみます。

まずはxquery使用版。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""xquery
  16. for $input in db2-fn:xmlcolumn("POST_XML.ADDRESS")
  17. where fn:contains($input/doc/町域名, '銀座')
  18. return $input"""
  19. cur.execute(sql)
  20. for row in cur:
  21.     dic = xml_to_dict(row[0])
  22.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)






XMLCASTを使用した場合。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. import xml.etree.ElementTree
  4. def xml_to_dict(xml_str):
  5.     """ XML文字列から辞書型に変換 """
  6.     dic = {}
  7.     elem = xml.etree.ElementTree.fromstring(xml_str.encode('utf-16be'))
  8.     for node in elem:
  9.     dic[node.tag] = node.text
  10.     
  11.     return dic
  12. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  13. # カーソル取得
  14. cur = con.cursor()
  15. sql = u"""SELECT address FROM post_xml
  16. WHERE
  17. XMLCAST(XMLQUERY('$ADDRESS/doc/町域名') AS VARCHAR(100)) LIKE '%銀座%'"""
  18. cur.execute(sql)
  19. for row in cur:
  20.     dic = xml_to_dict(row[0])
  21.     print(u"%(都道府県名)s%(市区町村名)s%(町域名)s" % dic)





実行結果はこちら。


$ python sample.py
北海道夕張郡長沼町銀座
栃木県鹿沼市銀座
埼玉県熊谷市銀座
埼玉県本庄市銀座
東京都中央区銀座
富山県富山市新庄銀座
長野県岡谷市銀座
長野県岡谷市東銀座
長野県飯田市銀座
静岡県静岡市清水区銀座
静岡県熱海市銀座町
静岡県伊東市銀座元町
愛知県半田市銀座本町
愛知県刈谷市銀座
滋賀県彦根市銀座町
京都府京都市伏見区銀座町
山口県周南市銀座
山口県周南市みなみ銀座
徳島県徳島市銀座
福岡県北九州市戸畑区銀座





特に計測してなくて体感でしかないのですが、
PostgreSQLのXML列より高速な気がします。

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/16(日) 12:27:23|
  2. DB2
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

DB2 10.5にPythonから画像データを登録する(ibm_db使用)

Ubuntuにibm_dbをインストールして、PythonからDB2に接続してみました。
DB2 10.5 Express-CにUbuntu + Pythonから接続する(ibm_db使用)

今回は、画像データをBLOB列に登録してみます。


テーブルの準備



画像データ登録用に、こんなテーブルを作成しておきました。


CREATE TABLE test(image BLOB)







画像の登録



ibm_db_dbiを使用してお手軽に登録出来ました。
デフォルトでオートコミットfalseなので、最後のcommitを忘れずに。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. # 画像データ取得
  7. f = open('Lenna.jpg', 'rb')
  8. bytes = f.read()
  9. f.close()
  10. # 登録実行
  11. cur.execute(u"INSERT INTO test (image) VALUES (?)", [bytes])
  12. con.commit()






画像の取得



登録したデータを復元してみます。


  1. # -*- coding:utf-8 -*-
  2. import ibm_db_dbi
  3. con = ibm_db_dbi.connect("DATABASE=sample;HOSTNAME=192.168.1.103;","db2inst1","P@ssw0rd")
  4. # カーソル取得
  5. cur = con.cursor()
  6. cur.execute(u"SELECT image FROM test")
  7. row = cur.fetchone()
  8. f = open('result.jpg', 'wb')
  9. f.write(row[0])
  10. f.close()





ちゃんと復元出来ました。


373_01.png


テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/16(日) 10:58:10|
  2. DB2
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

DB2 10.5のXML列にJavaからデータの検索を行うサンプル

DB2のXML列にJavaからデータを登録してみました。
DB2 10.5にJavaからXMLデータを登録・検索する

もう少し具体的なサンプルデータを登録・検索してみようと思います。



郵便番号データの登録



ここでPostgreSQLのXML列に郵便番号情報を登録してみました。
PostgreSQLのXML列をあいまい検索する(contains)

同じ事をDB2でもやってみようと思います。

登録用のテーブル作成クエリです。


CREATE TABLE post_xml(address XML)




郵便番号情報の登録に使用したプログラムはこんな感じになりました。


  1. import java.io.BufferedReader;
  2. import java.io.FileInputStream;
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.io.InputStreamReader;
  6. import java.sql.Connection;
  7. import java.sql.DriverManager;
  8. import java.sql.PreparedStatement;
  9. import java.sql.SQLXML;
  10. import javax.xml.parsers.DocumentBuilder;
  11. import javax.xml.parsers.DocumentBuilderFactory;
  12. import javax.xml.transform.dom.DOMResult;
  13. import org.w3c.dom.Document;
  14. import org.w3c.dom.Element;
  15. public class DB2XMLPostUpdate {
  16.     public static void main(String... args) throws Exception {
  17.         String url = "jdbc:db2://192.168.1.103:50000/SAMPLE";
  18.         String user = "db2inst1";
  19.         String pass = "P@ssw0rd";
  20.         
  21.         String query = "INSERT INTO post_xml(address) values(?)";
  22.         
  23.         try (Connection con = DriverManager.getConnection(url, user, pass)) {
  24.             
  25.             con.setAutoCommit(false);
  26.             
  27.             try (PreparedStatement stmt = con.prepareStatement(query)) {
  28.                 
  29.                 load(con, stmt);
  30.                 
  31.             } catch (Exception e) {
  32.                 e.printStackTrace();
  33.             }
  34.             
  35.             con.commit();
  36.             
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         }
  40.     }
  41.     private static void load(Connection con, PreparedStatement stmt)
  42.             throws Exception {
  43.         try (InputStream in = new FileInputStream("data.csv");
  44.                 InputStreamReader isr = new InputStreamReader(in, "utf8");
  45.                 BufferedReader reader = new BufferedReader(isr)) {
  46.             DocumentBuilder builder = DocumentBuilderFactory.newInstance()
  47.                     .newDocumentBuilder();
  48.             String line;
  49.             while ((line = reader.readLine()) != null) {
  50.                 String[] ary = line.split(",");
  51.                 SQLXML xml = con.createSQLXML();
  52.                 // DOMResultを生成し、そこにDocumentを設定する
  53.                 DOMResult dom = (DOMResult) xml.setResult(DOMResult.class);
  54.                 // 登録用のXMLドキュメントを生成
  55.                 Document doc = builder.newDocument();
  56.                 // docタグをルートに設定
  57.                 Element root = doc.createElement("doc");
  58.                 doc.appendChild(root);
  59.                 Element em = doc.createElement("全国地方公共団体コード");
  60.                 em.setTextContent(ary[0]);
  61.                 root.appendChild(em);
  62.                 em = doc.createElement("郵便番号");
  63.                 em.setTextContent(ary[1]);
  64.                 root.appendChild(em);
  65.                 em = doc.createElement("都道府県名カナ");
  66.                 em.setTextContent(ary[2]);
  67.                 root.appendChild(em);
  68.                 em = doc.createElement("市区町村名カナ");
  69.                 em.setTextContent(ary[3]);
  70.                 root.appendChild(em);
  71.                 em = doc.createElement("町域名カナ");
  72.                 em.setTextContent(ary[4]);
  73.                 root.appendChild(em);
  74.                 em = doc.createElement("都道府県名");
  75.                 em.setTextContent(ary[5]);
  76.                 root.appendChild(em);
  77.                 em = doc.createElement("市区町村名");
  78.                 em.setTextContent(ary[6]);
  79.                 root.appendChild(em);
  80.                 em = doc.createElement("町域名");
  81.                 em.setTextContent(ary[7]);
  82.                 root.appendChild(em);
  83.                 dom.setNode(doc);
  84.                 stmt.setSQLXML(1, xml);
  85.                 stmt.execute();
  86.                 stmt.clearParameters();
  87.             }
  88.         } catch (IOException e) {
  89.             e.printStackTrace();
  90.         }
  91.     }
  92. }




データは一括コミットです。
最初はエラーになりましたが、トランザクションログの量を増やすことで解消しました。
DB2 10.5にJavaから一括登録時「SQL Error: SQLCODE=-964, SQLSTATE=57011」





郵便番号を指定して検索



郵便番号が「0600000」のデータを検索します。

過去の自分を参考にしました。
IBM DB2でXQueryを使用した検索を行う
IBM DB2 XQueryであいまい検索(LIKE)を行う


まず、xqueryを使用するパターン。


  1. import java.io.InputStream;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLXML;
  6. import java.sql.Statement;
  7. import javax.xml.parsers.DocumentBuilder;
  8. import javax.xml.parsers.DocumentBuilderFactory;
  9. import org.w3c.dom.Document;
  10. import org.w3c.dom.Node;
  11. public class DB2XMLPostSearch {
  12.     public static void main(String... args) throws Exception {
  13.         String url = "jdbc:db2://192.168.1.103:50000/SAMPLE";
  14.         String user = "db2inst1";
  15.         String pass = "P@ssw0rd";
  16.         
  17.         // XML列解析用
  18.         DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  19.         
  20.         try (Connection con = DriverManager.getConnection(url, user, pass);
  21.                 Statement stmt = con.createStatement()) {
  22.             
  23.             StringBuilder query = new StringBuilder();
  24.             query.append("xquery");
  25.             query.append(" for $input in db2-fn:xmlcolumn(\"POST_XML.ADDRESS\")");
  26.             query.append(" where ($input/doc[郵便番号='0600000'])");
  27.             query.append("return $input");
  28.             
  29.             
  30.             // nameタグの値を取得
  31.             ResultSet rs = stmt.executeQuery(query.toString());
  32.             
  33.             while(rs.next()) {
  34.                 SQLXML xml = rs.getSQLXML(1);
  35.                 InputStream stream = xml.getBinaryStream();
  36.                 Document doc = parser.parse(stream);
  37.                 
  38.                 StringBuilder sb = new StringBuilder();
  39.                 sb.append(getNodeValue(doc, "都道府県名"));
  40.                 sb.append(getNodeValue(doc, "市区町村名"));
  41.                 sb.append(getNodeValue(doc, "町域名"));
  42.                 
  43.                 System.out.println(sb.toString());
  44.             }
  45.             
  46.             
  47.         } catch (Exception e) {
  48.             e.printStackTrace();
  49.         }
  50.     }
  51.     
  52.     private static String getNodeValue(Document doc, String tagName) {
  53.         Node node = doc.getElementsByTagName(tagName).item(0);
  54.         return node.getTextContent();
  55.     }
  56.     
  57. }




次に、XMLCASTを使用してWHERE句を記述するパターン。


  1. import java.io.InputStream;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLXML;
  6. import java.sql.Statement;
  7. import javax.xml.parsers.DocumentBuilder;
  8. import javax.xml.parsers.DocumentBuilderFactory;
  9. import org.w3c.dom.Document;
  10. import org.w3c.dom.Node;
  11. public class DB2XMLPostSearch {
  12.     public static void main(String... args) throws Exception {
  13.         String url = "jdbc:db2://192.168.1.103:50000/SAMPLE";
  14.         String user = "db2inst1";
  15.         String pass = "P@ssw0rd";
  16.         
  17.         // XML列解析用
  18.         DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  19.         
  20.         try (Connection con = DriverManager.getConnection(url, user, pass);
  21.                 Statement stmt = con.createStatement()) {
  22.             
  23.             /*
  24.             StringBuilder query = new StringBuilder();
  25.             query.append("xquery");
  26.             query.append(" for $input in db2-fn:xmlcolumn(\"POST_XML.ADDRESS\")");
  27.             query.append(" where ($input/doc[郵便番号='0600000'])");
  28.             query.append("return $input");
  29.             */
  30.             
  31.             StringBuilder query = new StringBuilder();
  32.             query.append("SELECT address FROM post_xml");
  33.             query.append(" WHERE");
  34.             query.append(" XMLCAST(XMLQUERY('$ADDRESS/doc/郵便番号') AS CHAR(7)) = '0600000'");
  35.             
  36.             
  37.             // nameタグの値を取得
  38.             ResultSet rs = stmt.executeQuery(query.toString());
  39.             
  40.             while(rs.next()) {
  41.                 SQLXML xml = rs.getSQLXML(1);
  42.                 InputStream stream = xml.getBinaryStream();
  43.                 Document doc = parser.parse(stream);
  44.                 
  45.                 StringBuilder sb = new StringBuilder();
  46.                 sb.append(getNodeValue(doc, "都道府県名"));
  47.                 sb.append(getNodeValue(doc, "市区町村名"));
  48.                 sb.append(getNodeValue(doc, "町域名"));
  49.                 
  50.                 System.out.println(sb.toString());
  51.             }
  52.             
  53.             
  54.         } catch (Exception e) {
  55.             e.printStackTrace();
  56.         }
  57.     }
  58.     
  59.     private static String getNodeValue(Document doc, String tagName) {
  60.         Node node = doc.getElementsByTagName(tagName).item(0);
  61.         return node.getTextContent();
  62.     }
  63.     
  64. }





ちなみにxqueryを使用したほうが3倍程度高速です。

3倍の速度差はいくら何でもおかしいだろと思い、色々試してみると


<div style="margin:10px;background-color:#ccffcc">
SELECT address FROM post_xml
WHERE
XMLCAST(XMLQUERY('$ADDRESS/doc/郵便番号') AS CHAR(7)) LIKE '0600000'

</div>



「=」比較より「LIKE」比較のほうが高速です。
これでxqueryとほぼ同等になりました。


しかし、やはりxqueryのほうが高速です。
頑張ってxquery書いたほうがいいです。









住所の一部を指定して検索



町域名に「銀座」という文字列が含まれるデータを検索してみます。

まずはxquery使用版。


  1. import java.io.InputStream;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLXML;
  6. import java.sql.Statement;
  7. import javax.xml.parsers.DocumentBuilder;
  8. import javax.xml.parsers.DocumentBuilderFactory;
  9. import org.w3c.dom.Document;
  10. import org.w3c.dom.Node;
  11. public class DB2XMLPostSearch {
  12.     public static void main(String... args) throws Exception {
  13.         
  14.         String url = "jdbc:db2://192.168.1.103:50000/SAMPLE";
  15.         String user = "db2inst1";
  16.         String pass = "P@ssw0rd";
  17.         
  18.         // XML列解析用
  19.         DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  20.         
  21.         try (Connection con = DriverManager.getConnection(url, user, pass);
  22.                 Statement stmt = con.createStatement()) {
  23.             
  24.             StringBuilder query = new StringBuilder();
  25.             query.append("xquery");
  26.             query.append(" for $input in db2-fn:xmlcolumn(\"POST_XML.ADDRESS\")");
  27.             query.append(" where fn:contains($input/doc/町域名, '銀座')");
  28.             query.append("return $input");
  29.             
  30.             
  31.             // nameタグの値を取得
  32.             ResultSet rs = stmt.executeQuery(query.toString());
  33.             
  34.             while(rs.next()) {
  35.                 SQLXML xml = rs.getSQLXML(1);
  36.                 InputStream stream = xml.getBinaryStream();
  37.                 Document doc = parser.parse(stream);
  38.                 
  39.                 StringBuilder sb = new StringBuilder();
  40.                 sb.append(getNodeValue(doc, "都道府県名"));
  41.                 sb.append(getNodeValue(doc, "市区町村名"));
  42.                 sb.append(getNodeValue(doc, "町域名"));
  43.                 
  44.                 System.out.println(sb.toString());
  45.             }
  46.             
  47.         } catch (Exception e) {
  48.             e.printStackTrace();
  49.         }
  50.     }
  51.     
  52.     private static String getNodeValue(Document doc, String tagName) {
  53.         Node node = doc.getElementsByTagName(tagName).item(0);
  54.         return node.getTextContent();
  55.     }
  56.     
  57. }




XMLCASTを使用するとこんな感じです。


  1. import java.io.InputStream;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLXML;
  6. import java.sql.Statement;
  7. import javax.xml.parsers.DocumentBuilder;
  8. import javax.xml.parsers.DocumentBuilderFactory;
  9. import org.w3c.dom.Document;
  10. import org.w3c.dom.Node;
  11. public class DB2XMLPostSearch {
  12.     public static void main(String... args) throws Exception {
  13.         
  14.         String url = "jdbc:db2://192.168.1.103:50000/SAMPLE";
  15.         String user = "db2inst1";
  16.         String pass = "P@ssw0rd";
  17.         
  18.         // XML列解析用
  19.         DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
  20.         
  21.         try (Connection con = DriverManager.getConnection(url, user, pass);
  22.                 Statement stmt = con.createStatement()) {
  23.             
  24.             StringBuilder query = new StringBuilder();
  25.             /*
  26.             query.append("xquery");
  27.             query.append(" for $input in db2-fn:xmlcolumn(\"POST_XML.ADDRESS\")");
  28.             query.append(" where fn:contains($input/doc/町域名, '銀座')");
  29.             query.append("return $input");
  30.             */
  31.             
  32.             query.append("SELECT address FROM post_xml");
  33.             query.append(" WHERE");
  34.             query.append(" XMLCAST(XMLQUERY('$ADDRESS/doc/町域名') AS VARCHAR(100)) LIKE '%銀座%'");
  35.             
  36.             
  37.             // nameタグの値を取得
  38.             ResultSet rs = stmt.executeQuery(query.toString());
  39.             
  40.             while(rs.next()) {
  41.                 SQLXML xml = rs.getSQLXML(1);
  42.                 InputStream stream = xml.getBinaryStream();
  43.                 Document doc = parser.parse(stream);
  44.                 
  45.                 StringBuilder sb = new StringBuilder();
  46.                 sb.append(getNodeValue(doc, "都道府県名"));
  47.                 sb.append(getNodeValue(doc, "市区町村名"));
  48.                 sb.append(getNodeValue(doc, "町域名"));
  49.                 
  50.                 System.out.println(sb.toString());
  51.             }
  52.             
  53.         } catch (Exception e) {
  54.             e.printStackTrace();
  55.         }
  56.     }
  57.     
  58.     private static String getNodeValue(Document doc, String tagName) {
  59.         Node node = doc.getElementsByTagName(tagName).item(0);
  60.         return node.getTextContent();
  61.     }
  62.     
  63. }




こちらもxqueryを使用したほうが高速です。

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/03/16(日) 00:59:57|
  2. DB2
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集

DB2 10.5 表データの一括削除(TRUNCATE TABLE)

データを全件削除するとき


DELETE FROM [表名]



みたいなクエリを実行すると、トランザクションログが作られます。

[DB2 LUW] 大量の行を削除する時のログ量を減らす方法
こちらを参考に、


TRUNCATE TABLE 表名 IMMEDIATE



トランザクションログを生成せず削除。
速いです。

特にトランザクションログの容量が少ない場合は歴然の差が出ると思います。

テーマ:データベース - ジャンル:コンピュータ

  1. 2014/03/16(日) 00:24:00|
  2. DB2
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
次のページ