ushidayの日記

主に「IBMi」のメモに・・・

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_DB2PECLはバージョン”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のサポートサイトより

最新の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」であっさり駄目だった。

とりあえず、Windows鯖をかまして、バッチコピーは出来たから良いか。(  ̄σ・・  ̄ )ホジ

DB2トランザクション・ログがフル (SQL0964C)

IBM i上のDB2から、Windows上のDB2 Express Cにデータをコピーする際に、あるテーブルの全レコードを"DELETE"したら、たまたま件数が多かった(12万件程度)のもあって、「SQL0964C データベースのトランザクション・ログがいっぱいです。 SQLSTATE=57011」というエラーが返された。
対象法として、

  1. トランザクションを細かくする
  2. ログ容量を増やす

が有る。「1」の方法はコピーを作る上では面倒なので、「2」のログサイズを増やしておいた。増やし方は以下の通り。

    • 「コントロールセンター」を開き、該当するデータベースを右クリック。
    • 「データベース・ロギングの構成」を選ぶ。
    • 「ロギング・サイズ」の項目で、”1次ログ・ファイルの数”、”2次ログ・ファイルの数”、”各ログ・ファイルのサイズ”のページ数(ページ当たり4K)を増やす。
    • 増やした後は、データベースを再起動する。
      • 1次ログ・ファイル(初期値13)・・・データベース起動時に確保される。ログファイル。
      • 2次ログ・ファイル(初期値4?)・・・1次ログで不足した際に、1つずつ確保されるログファイル。
      • ファイルサイズ(初期値1024)・・・ログファイルのページ数(1ページ4K)

2次ファイルを多用するとパフォーマンス的には、1次ファイルより悪い。

db2 get db cfg for | grep LOG」でコマンドラインでも確認可能

db2cli.iniじゃなくて構成アシスタントで出来る

以前書いたこの記事で、トランザクション分離レベルの指定をdb2cli.iniでなんて書いてあったのですが、構成アシスタントの「CLI設定」で簡単に出来たので今更ながら補足です。

  • 構成アシスタントを開く
  • 設定したいインスタンスの横を右クリックで「CLI設定」を選択

  • 設定タブを選択

  • <追加>ボタンをクリック
  • 一覧から「TXNIsolation」パラメータを選択

  • 適宜分離レベルを選択
  • <適用>ボタンをクリック

以上です。簡単ですね。

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
バイト境界までのバイト数を足したものになります。

だそうです。

次に暗号化から復号化までの手順です。

    • 暗号化パスワード&ヒントの設定「SET ENCRYPTION PASSWORD 'hoge' WITH HINT 'fuga'」これをやると以降のSQLセッションで、グローバル設定になります。若しくは、SQL発行時に列毎にencrypt_rc2やdecrypt_charの第二引数にパスワードを取る事も出来ます。
    • 暗号化処理「encrypt_rc2('暗号化したい値')」又は「encrypt_tdes('暗号化したい値')」
    • 復号化処理「decrypt_char(復号対象列)」

暗号化パスワードは、暗号化する時と復号化する時に必要となります。ヒントはパスワードを忘れた時のヒントになります。ヒントは「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')

AS/400の対話式SQL

CALL スキーマ名/プログラム名 ('引数','引数2')

通常自分は、ユーザーによって実行ライブラリが異なる可能性があるので、ライブラリ名(スキーマ名)を指定しません。スキーマ名を変数にしても良いのですが、AS/400の伝統的に暗黙のオープンというのがあります。暗黙オープンの方法としては...

  1. ユーザージョブの”ライブラリーリスト”に入れる。
  2. ”set schema ライブラリ名”をする。

が、考えられます。前者は、本番運用向きとは思いますが、開発の場合は環境毎にユーザーが変わるので、自分は後者の「set schema」を使っています。
この”暗黙のスキーマ”なのですが、PHPDB2関数で、微妙に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

PHPDB2関数か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 = 'スキーマ名'