いろいろなデータベースのバージョンの調べ方

いろいろなデータベースのバージョンの調べ方をまとめてみました。（追加すべき情報がありましたら情報提供をお待ちしております)

■MySQL
select version();

■PostgreSQL
select version();

■Oracle
select BANNER from SYS.V_$VERSION;

■SQL Server 2005
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL Server 2005 SP3: 2005.90.4035
SQL Server 2005 SP2: 2005.90.3042
SQL Server 2005 SP1: 2005.90.2047

■SQL Server 2000
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL Server 2000 SP4: 2000.8.00.2039
SQL Server 2000 SP3a: 2000.80.760.0
SQL Server 2000 SP3: 2000.80.760.0
SQL Server 2000 SP2: 2000.80.534.0
SQL Server 2000 SP1: 2000.80.384.0

MySQLのデータをシフトJISからUTF-8に変換した際の記録

今回のエントリーは、先日Shift JISで作っていたデータを多言語対応にすべくUNICODEに変換しようとしたとき苦労した際の記録です。

今回対象としたのは、MySQLからdumpした100MB近くあるデータです。

【一通りの手順】
まずMySQLからデータをdumpします。

% mysqldump [database name] > dump.sql

次にdump.sqlをmoreしてみると「DEFAULT CHARSET=sjis」という記述があるのでこれをutf8に変更します。

% perl -p -w -e 's/sjis/utf8/g' dump.sql > dump2.sql

ただいくらDEFAULT CHARSETをutf8に変えても実際のデータがシフトJISなので、データ自体もUTF-8に変えます。

% iconv -c -f shift_jis -t utf-8 dump2.sql > dump3.sql

こうして完成したデータをMySQLに読ませてみてエラーが起きなければとりあえず作業完了です。

% mysql < dump3.sql

手順をまとめてしまうとまぁ上記の通りなんですが、文字コードをシフトJISからUTF8に変えてからMySQLに読み込ませてみると通らないという点非常に苦労しました。いろいろなところで説明されているようにシフトJISからUTF8に変換する際100%全く同じに変換できない、というのがその理由です。いろいろな方が何かしらの工夫で100%に近づけるよう努力してくださった成果がiconvやnkfなどの変換コマンドなわけですが、それらのコマンドを用いてもどうもSQL文の実行に影響ある箇所で文字化けが起こってしまっていたようです。データ量が少なければ発生箇所の特定まで行いましたが今回データ量が多くかつ重要なデータでもなかったのでそこまで特定せず、iconvコマンドに-cオプション（無効な文字を無視して無視して続行することができる）をつけてごまかしています。ちなみにiconvを試す前にはnkfを試しましたが、-cオプションに相当するオプションが存在しなかったようですのでiconvを使いました。

データベースで大文字と小文字の区別に注意を

経験豊富なシステム管理者であれば過去にきっと1度は経験するトラブルの一つに「DBで大文字と小文字の区別をしてくれない」というものがあります。これに関連するトラブルでよくあるのは「ユーザ登録時にUSERというユーザ名でアカウントを作ったはずなのにuserでもログインできてしまう」というものがあります。予めその現象をシステム管理者が知っていれば仕様という一言で済みますが、このことを知らなかったらきっと大騒ぎとなることでしょう。

もし大文字と小文字をしっかりと区別したいのであればその解決方法がしっかり用意されています。注：ただしこの辺り実は私はあまり詳しくないのでひょっとしたら間違っている、もしくはそれは昔のことで現在のバージョンでは別の解決方法が必要になっているかもしれないので参考程度に読んでください）。

・MySQLの場合はカラム型毎に挙動が違っているらしいです。例えばTEXT型は大文字と小文字を区別しないがBOB型は区別する。varcharは大文字と小文字を区別しないがcreate tableするときにvarcharの後にBINARYをつける。

・ORACLEの場合はselectするときにUPPERやLOWERを使う。(例:select * from test_tbl where upper(name) like '%D%';)

・SQLServerの場合はデータベース作成時に照合順序をバイナリに変更してください。

など。

また同様に半角・全角の区別にも気をつけてください(ORACLEであればTO_MULTI_BYTEやTO_SINGLE_BYTEを使う)。DBって面倒くさいですね。

XMLデータベース

先週、XMLデータベース「Cyber Luxeon」を製造・販売しておられるサイバーテックさんの事務所に遊びに行きました。そこで話しをお伺いしたところ、XMLデータベースはまだまだ用途が確立されていなくて何に使ったらよいのか模索している段階とのこと。うんうん、よくわかります。

そういうこともあり、Cyber Luxeon V2.0からは開発者ライセンスを無償で提供し、裾野を広げる戦略を取るとのこと。うん、これはとてもよいことだと思います！

というわけで私もちょっとXMLデータベースをいじってみようかと思います。ただしV2.0は06/12/25から出荷開始とのことです。

PS: 今回別に宣伝費をもらっているわけではありません。が、いただけるのであれば遠慮なくいただきます>Sさん（笑）。

DBの時刻合わせに気をつけよう

サーバ設定に慣れた人であれば、ついついDBサーバにもNTP等を使った自動時刻をしてしまいたくなるものである。しかしDBの中には下手に時刻合わせをしてしまうとダウンしてしまうものもあるので気をつけよう。

【時刻合わせとDBダウンの関係性】
時刻合わせと言っても、現在時刻より先に進むパターンと過去の時刻に戻るパターンがある。ほとんどのDBでは前者のパターンでは問題がない。しかし気をつけなければならないのは後者のパターンである。時刻が過去に戻るとDBによってはシステム不整合と検知されダウンしてしまうものがある(ORACLEなど)。

【DBの時刻の合わせ方】
一番安全なのは時刻合わせをする場合はメンテ日に全システムを止めた上で時刻を合わせることである。

【裏技】
しかし、そうは言ってもとめられないシステムというものもあるだろう。そういった場合はこういう裏技がある。それは時刻を止めるかもしくは時刻の進行をゆっくりにしてしまうことである。無論OSやDBにそういう機能はないはずなので、こういった場合は何かしらのアプリケーションを探すか自作する必要がある。

※追記(2005.9.22)：ntpdにはslewモードというのがありそれを使うと良いとのこと。石坂さん、上美谷さん、教えてくださってありがとうございました。

※追記(2005.9.22)：WindowsTimeサービスも
時刻のずれが一定時間内であれば slew モードのように動作するそうです。上美谷さん、教えてくださってありがとうございました。

効率的なSQL文かどうかの調査方法

ORACLE限定ですが、効率的なSQL文かどうかの調査方法をご紹介します。

SQLPlusにて

1. set autotrace on
2. set timing on

上記のコマンドをたたいてからSQLを実行すると、そのSQLの実行計画やインデックスの乗り具合が確認できます。

DB通信の危険性

開発者やDBAの方は普段何気なくDBにSQL文を発行してデータを取得していることが多いと思いますが、これは大変危険な行為です。そこで今回はDB通信の暗号化について記してみたいと思います。

【何が危険か】
社内にいる社員の人がデータセンターにあるDBに対して気軽にSQL文してデータを取得することの何が危険なのでしょうか。他にもあるかもしれませんが私が知る限り一番危険なのはSQL文と取得データがインターネット上を平文で流れることです。このことを気にしない、もしくは知らなかった人も多いのでは？

【施策】
おすすめする方法は次の3つです。1つだけでもいいですし、いくつか組み合わせてもよいです。

　・VPN回線経由でSQL文発行&データ取得。
　・踏み台サーバにSSHで接続し、そのマシン上でSQL文発行&データ取得。
　・DB通信を暗号化することができるソリューションが導入されたクライアント&DBサーバでSQL文発行&データ取得。

【終わりに】
これだけ個人情報保護が叫ばれている中、DB通信の危険性が世の中であまり知られていないことを最近知りショックでした。皆さんどうぞお気をつけください。

ORACLEのanalyzeコマンド

DBは苦手なのですが、最近必要に迫られて勉強しつつあります。その流れの中で最近DBパフォーマンスを上げることができるanalyzeコマンドというものを知りましたのでご紹介。（これひょっとして開発者だったら常識？）

【analyzeコマンドとは】
OracleのオプティマイザがコストベースでSQLを解析・実行する際、実行計画を立てるために使用する統計情報を作成する SQLコマンドです。

-- 表の全データのうちの10％を使用して、統計情報を作成
ANALYZE TABLE テーブル名 ESTIMATE STATISTICS SAMPLE 10 PERCENT;

-- 表の全データを使用して統計情報を作成（時間がかかります）
ANALYZE TABLE テーブル名 COMPUTE STATISTICS;

-- 表の統計情報を削除する
ANALYZE TABLE テーブル名 DELETE STATISTICS;

これを行うことでディスクI/O使用率100%だったものが5%まで軽減したという経験もあります。

【参考文献】
http://www.ok24.jp/tech/oracle_analyze.html

システム管理者に必須なDBの知識とは何か？

開発経験がなくとも、システム管理者であるならば必須の知識は何か？それは「インデックス」と「NOT NULL制約」です。これらを正確に理解しておかないと障害発生時に問題の切り分けができず、とても苦しむことになります。知らなかった人は今からきちんと勉強しておきましょう。僕はたまたま開発経験もあるので知ってましたが、ずっとシステム管理畑で育っている人は知りようがないですからね。

（無論ORACLEやSQLServerの違いを理解することとかもそれなりに必須なDBの知識なんですが、そんなことはインデックスとNOT NULL制約を知ることと比べたら些細なことです）

コネクションプールが可能な開発言語を使うべし

開発がし易く生産性が高いという理由でサイト構築にPHP言語やASP言語を選択するサイトが多いと思います。しかしこれらの言語にはコネクションプール機能がないため大規模サイトには不適切と言われています。そこで今回はコネクションプールの考え方と、大規模サイトに相応しい開発言語について考えてみましょう。

【コネクションプールとは？】
DBにアクセスするためには基本的に①DBとコネクションを張り②SQL文を送り③レスポンスを受け取り④DBコネクションを切る、という流れが必要になります。この中で①と④のDBコネクション周りは実はとても負荷が高く、アプリケーションを作成する際はSQL文のチューニングと同じくらいDBコネクション処理コストの削減が重要になります。そこでコネクションを一度張ったらずっとコネクションを切らずに使いまわす（コネクションをプールする）という考え方が生まれました。

【コネクションプールが可能な開発言語とは？】
私の知っている限り、JAVA言語とASP.NETでコネクションプールが実現できます。JAVA言語の場合はどのコンテナを使うかによってコネクションプールの実現方法が変わってきます。TOMCATを使う場合は自分でコネクションプール機能を実装しなければなりません（簡単ですし一般的です。検索すれば該当ソースがたくさん出回っています）。BEA社のWEBLOGIC等のアプリケーションサーバを使う場合はそれ自身にコネクションプール機能が実装されているので邯鄲にコネクションプールが実現できる、らしいです（使ったことないので・・・）。ASP.NETの場合は予めコネクションプール機能がついている、らしいです（これも実はまだ使ったことないです・・・）。

逆にコネクションプールが不可能と言われている開発言語で有名どころではPHP、Perl、ASPあたりでしょうか。Ruby、Python等は知りません。（誰か教えてください）

※ただしテクノロジーは常に進化しているので今日不可能だったことは明日には可能になっているかもしれません。

質問！

どなたかWindowsのODBCでコネクションプール機能を持つものって存在するかご存知ないですか？開発言語レベルでなくODBCレベルでコネクションプールを実現しているものがきっとあるに違いないと思って探したんですがみつからなかったんですよね～。

【SQLServer】WITH(NOLOCK)

DBを管理する上で、lockに関する理解はとても重要です。厄介なことに
lockのかかりかたはOracle、SQLServer、MySQL等DBによってばらばらで、
DBを変えた途端にlockの処理方法の違いでレスポンスがかなり重くなる
というのはよくある話しです。

以前SQLServerで掲示板のようなシステムを運用していたことがあります。
ユーザ数が伸びるに従いレスポンスが明らかに低下していきました。
CPUやメモリ不足という考えがありましたが、そのときはどうやらそうでは
なさそうでした。調査してみたところ、SQLServerではselectの度に
lockがかかるようなことが原因でした。この対応策を探したところ、
SQL文中に「WITH(NOLOCK)」という記述を追加してあげることでした。

※DBはあまり詳しくないので書き方が中途半端でスミマセン。