[웹서버 구축] Apache + PHP + DB2 연동

Red Hat 기반 리눅스에 PHP 와 DB2 연동 테스트를 해 본 것이 2007년인데, 몇 일전 새롭게 테스트를 해 봤다가 마무리를 하지는 못했다.

차후 재 테스트를 하여 완료가 되는 경우 다시 정리하도록 한다.

 

1. 설치 환경 정보

   - 리눅스: CentOS 4.x  x86

   - 웹서버: Apache 2.2.6

   - 웹 Language: PHP 5.2.5

 

2. 설치 순서

   (1) DB2 V8.x 32bit Client 설치 및 구성

   (2) Apache 서버 설치 및 구성

   (3) PHP 설치

 

3. 다운로드

   - PHP : http://www.php.net/

   - 아파치: http://httpd.apache.org/

   - DB2 : http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg27007053

 

4. DB2 Client 설치 정보 (설치 작업은 Skip)

   - 엔진 설치 위치: /opt/IBM/db2/V8.1/

   - 인스턴스 홈 : /home/db2inst1

 

5. Apach 서버 설치

    - 압축 풀기:  tar –xzvf httpd-2.2.6.tar.gz –C /usr/local/src

    -  설정

#> cd /usr/local/src/httpd-2.2.6 #> ./configure --prefix=/usr/local/apache2 \     --enable-mods-shared=all \     --enable-rewrite \     --enable-so ............ config.status: creating build/pkg/pkginfo config.status: creating build/config_vars.sh config.status: creating include/ap_config_auto.h config.status: executing default commands

 

    - 컴파일 및 설치

#> make && make install ….. mkdir /usr/local/apache/man/man1 mkdir /usr/local/apache/man/man8 mkdir /usr/local/apache/manual make[1]: Leaving directory `/usr/local/httpd-2.2.6

 

     - 검증

#> cd /usr/local/apache2/bin #> ./apachectl start #> ps -ef | grep httpd root 12184 1 0 11:18 ? 00:00:00 /usr/local/apache2/bin/httpd -k start daemon 12185 12184 0 11:18 ? 00:00:00 /usr/local/apache2/bin/httpd -k start daemon 12186 12184 0 11:18 ? 00:00:00 /usr/local/apache2/bin/httpd -k start daemon 12187 12184 0 11:18 ? 00:00:00 /usr/local/apache2/bin/httpd –k

 

    - 브라우저(firefox) 접속 확인 (http://localhost/)

      “It works” 문구가 담긴 웹 페이지 확인

 

    - 설정 (httpd.conf)

      위치: /usr/local/apache2/conf/httpd.conf

# Dynamic Share Object (DSO) Support LoadModule php4_module modules/libphp4.so   #ServerName www.example.com:80 ServerName localhost:80   AddType application/x-httpd-php .php .php3 .php4 .php5 .html .htm

 

      위치: /usr/ld.so.conf

/usr/local/apache2/lib , /usr/local/apache2/modules 경로 추가

      확인: ldconfig 실행

 

 

6. PHP 설치 및 설정

   - 압축 풀기: tar –xzvf php-5.2.5.tar.gz –C /usr/local/src

   - DB2 환경변수 적용 (인스턴스_홈/sqllib/db2profile)

#> . /home/db2inst1/sqllib/db2profile

 

   - 설정

#> cd /usr/local/src/php5.2.5 #> ./configure --enable-cli \      --disable-cgi \      --with-apxs2=/usr/local/apache2/bin/apxs \      --with-ibm-db2=/home/db2inst1/sqllib \      --with-pdo-odbc=ibm-db2,/opt/ibm/db2/v8.1/ \      --with-config-file-path=/usr/local/apache2/conf \      --with-exec-dir=/usr/local/apache2/bin \      --with-zlib --enable-sockets … +--------------------------------------------------------------------+ | License: | | This software is subject to the PHP License, available in this | | distribution in the file LICENSE. By continuing this installation | | process, you are bound by the terms of this license agreement. | | If you do not agree with the terms of this license, you must abort | | the installation process at this point. | +--------------------------------------------------------------------+ Thank you for using PHP.

 

     - 컴파일 및 설치

#> make && make install pear/PEAR can optionally use package "pear/XML_RPC" (version >= 1.4.0) [PEAR] PEAR - installed: 1.6.1 Wrote PEAR system config file at: /usr/local/etc/pear.conf You may want to add: /usr/local/lib/php to your php.ini include_path Installing PDO headers: /usr/local/include/php/ext/pdo/

     pecl 설치 프롬프트에 대해 ALL 선택 후, DB2의 인스턴스 경로 입력

 

     - php.ini 설정

#> cp php.ini-recommended /usr/local/apache2/conf/php.ini #> vi /usr/local/apache2/conf/php.ini include_path 에 /usr/local/lib/php , /usr/local/include/php/ext/pdo/ 추가 pdo_odbc.db2_instance_name=db2inst1  추가

 

     - 검증

       (1) libphp5.so 라이브러리 존재 확인 ( ls –al /usr/local/apache2/modules )

       (2) php 파일 작성

#> cd /usr/local/apache2/htdocs #> vi phpinfo.php3 <? phpinfo(); ?>

 

      (3) 아파치 서버 재기동

#> cd /usr/local/apache2/bin/apachectl stop #> cd /usr/local/apache2/bin/apachectl start

 

      (4) 브라우저(firefox) 확인

           http://localhost/phpinfo.php3

 

 

7. DB2 연동

   (1) DB2 서버 카탈로그 작업

#> su – db2inst1 $> db2 catalog tcpip node db2node1 remote 127.0.0.1 server 50000 remote_instance DB2INST $> db2 attach to db2node1 user db2inst using db2inst $> db2 detach $> db2 catalog db sample at node db2node1 $> db2 connect to sample user db2inst using db2inst

 

    * DB2 서버 접속 계정: db2inst

    * DB2 서버 DB 이름: sample

    * 통신 포트 번호: 50000

 

   (2) ODBC 함수를 통한 연동

    - 파일명: /usr/local/apache2/htdocs/odbc.php3

    - 코드

<? $dsn="sample"; $user="db2inst"; $passwd="db2inst"; $sql="select * from employee"; if ($conn_id=odbc_connect("$dsn","$user","$passwd")){ echo "$dsn 연결 성공"; if($result=odbc_do($conn_id, $sql)) { echo "'$sql'문 실행"; echo "result:: "; odbc_result_all($result); echo "freeing result"; odbc_free_result($result); } else { echo "'$sql' 실행에러"; } echo "$conn_id : 연결닫음"; odbc_close($conn_id); }else { echo "$dsn연결실패"; } ?>

 

       - 확인 (http://localhost/odbc.php3)

 

  (3) PDO를 통한 연동

     - 파일명: /usr/local/apache2/htdocs/pdo.php3

     - 코드

<?php $dbh = new PDO('odbc:SAMPLE','db2inst','db2inst'); echo "Connected\n"; $stmt= $dbh->prepare("call sp_test1(?)"); $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); $stmt->execute(); print ":: sp_test1 returned ret_val: $return_value\n"; ?>

 

Stored Procedure 코드

db2 +p -td"!" << EOF Connect to sample user db2inst1 using db2inst1 ! drop procedure sp_test2 ! create procedure sp_test2(OUT out_par varchar(100)) language SQL begin declare v_cnt integer; select count(midinit) into v_cnt from employee where midinit='' ; set out_par = char(v_cnt); end ! drop procedure sp_test1 ! create procedure sp_test1(OUT out_par varchar(100)) language SQL begin call sp_test2(out_par); end ! terminate ! EOF

 

     - 확인 (http://localhost/pdo.php3)