ZendServer WinとDB2Connectで文字化け
ZendServer for Windowsで”IBM i”のDB2に接続し、データ取得すると文字化けする。
具体的なパターンだと、全マイナスと全ダッシュが、それぞれ半角になる。
一瞬Shift-JISのダメ文字かとも思ったけど、実装はUTF-8だし、”表示”は文字化けしなかったから何だろ?
●IBM i上のレコード
− ‐ ー 〜 ―
●Zend Server for Winでレコード取得結果
− ‐ ー 〜 —
●Zend Server for IBM iでレコード取得結果
− ‐ ー 〜 ―
最新のDB2 Connect PE使っても結果は同じだった。
db2_bind_paramも”DB2_PARAM_INOUT”が駄目とか解決できていない問題が幾つかある。
- db2_bind_paramのエラー
Warning: db2_execute(): Statement Execute Failed in ..\ReturnParm.php on line 126 [IBM][CLI Driver] CLI0112E 割り当てにエラーがありました。 SQLSTATE=22005 SQLCODE=-99999
Zend Server CE 5.0 for IBM iのdb2_bind_paramで
Zend Server CE 5.0 for IBM i(PHP 5.3 Ver)で、最初からインスコされているIBM_DB2のPECLはバージョン”1.8.4”です。このバージョン”1.8.4”のPECLは、既知のバグがあり、関数、クラス内で「db2_prepare → db2_bind_param → db2_execute」をすると、db2_bind_param関数で割り当てる、変数のスコープがどうもおかしいようで、以下のようなエラーになります。
Warning: db2_execute() [function.db2-execute]: Value Not Bound in hoge.php on line XX Warning: db2_execute() [function.db2-execute]: Binding Error 3 in
PECLのサポートサイトより
- http://pecl.php.net/bugs/bug.php?id=17004 「 #17004 design problem with function db2_bind_param」
- http://pecl.php.net/bugs/bug.php?id=6528 「 #6528 scoping problem in db2_bind_param」
最新のPECLでは、この既知の問題は解決しているようなので、IBM iのZend Server CEに、2011.09.28現在最新の”1.9.2”を導入しました。
いったんは、IBM i上でビルドを試みましたが、「ibm_db2.so」は出来上がるものの、ファイルのバイト数が明らかに小さく、起動時にエクステンションエラーになってしまいました。結果的にはAIX上でビルドしたエクステンションは、正常通り動作しました。「db2_prepare → db2_bind_param → db2_execute」も問題なく使えます。
もし、バイナリが欲しい方がいたら、このブログにコメント下さい。1.9.2の”ibm_db2.so”を提供します。但し、使用は自己責任で。
ビルドの際に、宣言エラーが抜けている様なので、以下の箇所を追加しました。
-- エラー1 ../ibm_db2-1.9.2/ibm_db2.c: In function '_php_db2_connect_helper': ../ibm_db2.c:2109: error: 'conn_handle' has no member named 'c_i5_allow_commit' -- 対応:「ibm_db2.c」に宣言を追加 114 long c_i5_allow_commit; -- エラー2 ../ibm_db2-1.9.2/ibm_db2.c: In function '_ibm_db_chaining_flag': ../ibm_db2-1.9.2/ibm_db2.c:6761: error: 'SQL_ATTR_CHAINING_BEGIN' undeclared (first use in this function) -- 対応:「php_ibm_db2.h」に定数を追加 302 #ifdef PASE /* i5/OS ease of use turn off/on */ 303 #ifndef SQL_ATTR_CHAINING_BEGIN- 304 #define SQL_ATTR_CHAINING_BEGIN 2464 305 #define SQL_ATTR_CHAINING_END 2465 306 #define SQL_IS_POINTER -4 307 #endif- 308 #endif /* PASE */
PECL 1.9.2の確認
とりあえず、この情報が役立つか判らないけど、PECLのサポートに上げておいた。
http://pecl.php.net/bugs/bug.php?id=24382
DB2 for i copy to DB2 LUW
DB2 for iからDB2 LUWへデータコピーをしたかったので、phpで作った。
処理自体は、簡単なものでスキーマ名とテーブル名をのfrom toを受け取って、DELETE and INSERTするだけ。
CREATE TABLEは既にされているという前提で...
<?php //Db2 for i $db2I = Zend_Db::factory($conf); //SQL $select = $db2I->select() ->from($schema . '.' . $table); $stmt = $db2I->query($select) ; $fromRows = $stmt->fetchAll(); //DB2 LUW $db2LUW = Zend_Db::factory($conf2); //削除 $count = $db2LUW->delete($schema . '.' . $table); //追加 foreach ($fromRows as $row ) { $db2LUW->insert($schema . '.' . $table, $row); } ?>
これを、Windows版 Zend Serverで動かすと、DB2 LUWにデータは書かれるのだけど、Zend Server for i上で動かすと、LUW側のコネクションも、IPアドレスやポートは、無視されて、自己参照してしまう。
今回のケースは、たまたまiもLUWもデータベース名、ユーザ、パスワード全てが、両方で同じだったので、自己参照をしたという結果になった。
DB2のドライバが違うんだろうね。確かに接続パラメータも違うしね。
と言うことは、「iの方でDRDA使えば更新出来んじゃね?」と言うことは容易に想像がつくのだが...「'分散データベースの接続の試みで認可が正常に実行されません。 SQLCODE=-30082」であっさり駄目だった。
DB2トランザクション・ログがフル (SQL0964C)
IBM i上のDB2から、Windows上のDB2 Express Cにデータをコピーする際に、あるテーブルの全レコードを"DELETE"したら、たまたま件数が多かった(12万件程度)のもあって、「SQL0964C データベースのトランザクション・ログがいっぱいです。 SQLSTATE=57011」というエラーが返された。
対象法として、
- トランザクションを細かくする
- ログ容量を増やす
が有る。「1」の方法はコピーを作る上では面倒なので、「2」のログサイズを増やしておいた。増やし方は以下の通り。
-
- 「コントロールセンター」を開き、該当するデータベースを右クリック。
- 「データベース・ロギングの構成」を選ぶ。
- 「ロギング・サイズ」の項目で、”1次ログ・ファイルの数”、”2次ログ・ファイルの数”、”各ログ・ファイルのサイズ”のページ数(ページ当たり4K)を増やす。
- 増やした後は、データベースを再起動する。
-
-
- 1次ログ・ファイル(初期値13)・・・データベース起動時に確保される。ログファイル。
- 2次ログ・ファイル(初期値4?)・・・1次ログで不足した際に、1つずつ確保されるログファイル。
- ファイルサイズ(初期値1024)・・・ログファイルのページ数(1ページ4K)
-
2次ファイルを多用するとパフォーマンス的には、1次ファイルより悪い。
Zend_Authでユーザー認証&DB2のencryption
Zend_Authを使った、ユーザー認証をやってみたので、メモをしておきます。
IBM i(AS/400)の既存データベースに存在している社員マスターのユーザーIDとパスワード情報で認証を行います。
Zend_Authは、データベース認証用に”Zend_Auth_Adapter_DbTable”というクラスが用意されています。このクラスは、コストラクタに”Zend_Db_Adapter_xxxxxx”のインスタンスを渡す必要があり、今回のケースでは”Zend_Db”を使っていなかったので、”Zend_Auth_Adapter_Interface”を実装して、オリジナルの”Zend_Auth_Adapter”を作成して認証してみました。
まず、社員マスターの認証をチェックするメソッドを実装します。認証の判定としては
-
- 認証成功
- ユーザーが存在しない
- 廃止ユーザーで認証不可
- パスワードのアンマッチ
- その他のエラー
の5つとします。
encryptionを使ったパスワードの暗号化
話はソレるのですが、先日発表されたPower7のIBM i OS 7.1では、データベース列における暗号化が可能になるそうです。しかし、自分が使っている環境のV5R4では、それが実現出来ませんので、DB2のEncryptを使って暗号化したいと思います。
暗号化したデータを保存する為には、DDLで下の様に”varchar for bit”を定義するか、DDSで”H”タイプのフィールド定義にする必要があります。
--- DDLの場合 --- ALTER TABLE EMPL ALTER COLUMN PWD SET DATA TYPE VARCHAR ( 80) FOR BIT DATA NOT NULL --- DDSの場合 --- A PWD 80H COLHDG(' パスワード ')
フィールドのLengthについては、マニュアルに以下の様な記載がありました。
パスワードストリングが指定されているが、ヒント・ストリングは 指定されていない場合は、データ・ストリングの長さ属性に16 を足し、 それに次の8バイト境界までのバイト数を足したものになります。 それ以外の場合は、データ・ストリングの長さ属性に48を足し、それに次の8 バイト境界までのバイト数を足したものになります。
だそうです。
次に暗号化から復号化までの手順です。
暗号化パスワードは、暗号化する時と復号化する時に必要となります。ヒントはパスワードを忘れた時のヒントになります。ヒントは「select gethint(pwd) from テーブル 」の様にして、見ることが出来ます。
気を付けるのは、暗号化する際に書き込むフィールドは、無変換(CCSID65535)なので、書き込むジョブのCCSIDに左右されると言う事です。パスワード認証を行う際に、”入力パスワードを暗号化したモノ”とDBの暗号化されたパスワードを比較すると、現行のCCSIDと暗号化した時のCCSIDが違うと、当然不一致になります。しかし、”decrypt_char”を使うと、現行ジョブのCCSIDを反映した状態で復号化されるので、DBの暗号化パスワードを復号化した上で、入力パスワードと比較した方が、ジョブのCCSIDに影響される事がなさそうです。
■暗号化されたパスワード
認証処理の実装
■社員のパスワードを認証するクラス
<?php class Employee { const AUTH_SUCCESS = 1; const AUTH_NOTFOUND_USER= 2; const AUTH_INACTIVE_USER= 3; const AUTH_UNMATCH_PASS = 4; const AUTH_ERROR = 9; /** * 認証をする */ public function checkAuth($code,$password){ $db = ←DB2接続処理 // 認証 $sql="select" ." code,rtrim(name) as name ,del" ." ,(case when decrypt_char(pwd)=? then 1 else 0 end) as auth_flag" ." from empl" ." where code = ?"; $result = db2_prepare($db,$sql); db2_bind_param($result ,1 ,"password",DB2_PARAM_IN); db2_bind_param($result ,2 ,"code",DB2_PARAM_IN); if(!db2_execute($result)){ $flag = self::AUTH_ERROR; //エラー return $flag; } $row = db2_fetch_assoc($result); //認証判定 if ($row) { if ($row["del"] == "D"){ $flag = self::AUTH_INACTIVE_USER; //廃止ユーザ }elseif ($row["auth_flag"] == 0){ $flag = self::AUTH_UNMATCH_PASS; //パスワード不一致 }else{ $flag = self::AUTH_SUCCESS; //成功 } }else{ $flag = self::AUTH_NOTFOUND_USER; //ユーザーなし } return $flag; } } ?>
Zend_Auth_Adapterの実装
”Zend_Auth_Adapter”では”authenticate”メソッドを実装して、”Zend_Auth_Result”のインスタンスを返して、例外に”Zend_Auth_Exception”をスローする必要があります。前述のクラスのチェックメソッド(checkAuth)を使って、こんな感じで実装します。
<?php require_once 'Employee.php'; require_once 'Zend/Auth/Adapter/Interface.php'; require_once 'Zend/Auth/Adapter/Exception.php'; require_once 'Zend/Auth/Result.php'; class EmployeeAuthAdapter implements Zend_Auth_Adapter_Interface { private $id; private $password; private $name; public function __construct($id, $password){ $this->id = $id; $this->password = $password; } /** * 認証 */ public function authenticate() { $id = $this->id; $password = $this->password; $code = null; $message = ""; try { //社員インスタンス $empl = new Employee(); $ret = $empl->checkAuth($id,$password); $msgCode = $ret; //結果 switch ($msgCode) { case Employee::AUTH_SUCCESS: $code = Zend_Auth_Result::SUCCESS; $message = "認証OK"; break; case Employee::AUTH_NOTFOUND_USER: $code = Zend_Auth_Result::FAILURE_IDENTITY_NOT_FOUND; $message = "ユーザーが見つかりません"; break; case Employee::AUTH_INACTIVE_USER: $code = Zend_Auth_Result::FAILURE_UNCATEGORIZED; $message = "ユーザーが廃止されてます"; break; case Employee::AUTH_UNMATCH_PASS: $code = Zend_Auth_Result::FAILURE_CREDENTIAL_INVALID; $message = "パスワードが違います"; break; default: $code = Zend_Auth_Result::FAILURE_UNCATEGORIZED; $message = "その他のエラー"; break; } } catch (Exception $e) { throw new Zend_Auth_Exception("予期せぬ例外で認証エラー"); } $result = new Zend_Auth_Result($code,$id,array($message)); return $result; } } ?>
AuthAdapterの使用
実際に使用する時は、先ほど実装した”authenticate”メソッドで、Zend_Auth_Resultを取得して、”isValid”メソッドで認証します。trueが返れば成功です。認証結果のメッセージは”getMessages”で、メッセージのリストが返されます。
<?php require_once 'EmployeeAuthAdapter.php'; //認証アダプタ $adapter = new EmployeeAuthAdapter($id,$password,$autologin); //認証 $ret = $adapter->authenticate(); if($ret->isValid()){ //成功の処理 }else{ //失敗の処理 $message = $ret->getMessages(); echo $message[0] ; } ?>
暗黙スキーマの挙動の違い
PCからお手軽に、既存のIBM i(AS/400)のプログラムを呼び出す方法に、SQLのCALLを投げる方法があります。こんな感じで
■PC系の文法
CALL スキーマ名.プログラム名 ('引数','引数2')
CALL スキーマ名/プログラム名 ('引数','引数2')
通常自分は、ユーザーによって実行ライブラリが異なる可能性があるので、ライブラリ名(スキーマ名)を指定しません。スキーマ名を変数にしても良いのですが、AS/400の伝統的に暗黙のオープンというのがあります。暗黙オープンの方法としては...
- ユーザージョブの”ライブラリーリスト”に入れる。
- ”set schema ライブラリ名”をする。
が、考えられます。前者は、本番運用向きとは思いますが、開発の場合は環境毎にユーザーが変わるので、自分は後者の「set schema」を使っています。
この”暗黙のスキーマ”なのですが、PHPのDB2関数で、微妙にAS/400とPCで挙動の違いが有ったので、メモをしておきます。
AS/400(Zend Core for i5 2.6.1)の場合は、”set schema”でスキーマを指定すれば、暗黙でプログラムをcall出来ます。しかし開発用PC(Zend Server CE 4.0)から発行されたSQLの場合には、次の様なメッセージが返されました。
call hoge ('fuga') SQL0204N "ユーザ名.hoge" は未定義の名前です。 SQLSTATE=42704
PHPのDB2関数かDB2 Connectなのか分かりませんが、暗黙のスキーマが見つからない場合は、自動でユーザー名を明示的スキーマに置き換えます。(間違って存在していて勝手にプログラムが動かれても困りますが...。)
今回は対応方法として、AS/400のプログラムをストアドプロシージャ定義する事にしました。方法は以下の通りです。
-
- OPMプログラムならばILE化する。(OPMだとストアド情報の保管・復元が出来ないため)
- ”create procedure”で、プログラムをストアド定義する。
■ストアド定義SQLの例(※CLプログラムで5バイト文字列の引数を1つ取る場合)
CREATE PROCEDURE スキーマ名/プロシージャ名(IN 引数名 CHAR (5 )) LANGUAGE CL NOT DETERMINISTIC CONTAINS SQL EXTERNAL NAME ライブラリ名/プログラム名 PARAMETER STYLE GENERAL
-
- 実行時に”set path スキーマ名”で関数パスを指定して、プロシージャ名をcallで呼びだす。
OPMをILE化するのは、SAVLIBやRSTLIBでストアド定義の情報が反映されないからです。検証してわかったのですが、プログラムのリネームや削除、ライブラリのリネームをしてもストアド情報と同期はされません。プログラムをSAVOBJ,RSTOBJしても同様に、ストアド情報は同期されませんでした。但し、ライブラリを削除した時だけは、ストアド情報も消えるようです。ライブラリを保管→ライブラリ削除→ライブラリ復元だと、ストアド情報も同期された状態で、復元しました。この事から、ILEオブジェクトの何処かに、ストアドに関するメタ情報を持っていると思うのですが、何処にあるか見つける事が出来ませんでした。(DSPPGMなどには無かった)
※追記:その後、DMPOBJコマンドでメタ情報を確認出来ました。SQL文が丸ごと残ってます。
ちなみに、OPMでストアド定義すると、プロシージャは作成されますが、その旨の警告メッセージが出力されました。
■注意書き(※DB2 Universal Database for iSeries SQL 解説書より)
*PGM オブジェクトが保管された後、このシステムや別のシステムに復元すると、カタログはそれらの属性を使用して自動的に更新されます。 外部プロシージャーの場合は、次の制約の範囲内で属性を保管することができます。 ・ 外部プログラム・ライブラリーは、QSYS であってはなりません。 ・ 外部プログラムは、CREATE PROCEDURE ステートメントの発行時に存在していなければなりません。 ・ 外部プログラムは、ILE *PGM オブジェクトか*SRVPGM オブジェクトにする必要があります。 オブジェクトを更新できない場合でも、それにかかわらず、プロシージャーは作成されます。
これでプロシージャ名を呼び出せば、AS/400でもPCでも暗黙的にプログラムを呼び出す事が出来ます。
但しこの場合は、プログラム名の呼出と違って、AS/400でも”set path”で関数パスを指定する必要があります。ちなみに、ジョブのライブラリーリストに入っていれば、”set path”は不要です。本番運用ならばライブラリーリストでやるのが、一番楽チンかもしれませんネ。
ストアドプロシージャ定義の一覧を確認するには、次のSQLを投げると確認出来ます。
select * from sysroutines where specific_schema = 'スキーマ名'