IBM Software Group 웹기반의 DB2 개발환경구축및 DB2 Information Integrator 를이용한정보통합데모 한국 IBM 소프트웨어사업부 정진영대리 (jyjeong@kr.ibm.com)
Agenda Preparation JAVA ENV JAVA CONNECTION PHP ENV PHP CONNECTION
Preparation Installation DB2 /usr/opt/db2_08_01 DB2 INSTANCE # /usr/opt/db2_08_01/instance/db2icrt -u [fencedid] [userid] /etc/services db2c_db2inst1 50000/tcp # db2 update dbm cfg SVCENAME db2c_db2inst1 # db2set DB2COMM=TCPIP DATABASE create, TABLE create # db2sampl (sample database create)
JAVA ENV JDK version All version supported /usr/java13_64 PATH # PATH=$PATH:/usr/java13_64/bin Installation Apache /usr/local/apache Installation WebApplication Server Tomcat, Resin WebSphere(IBM http server), WebLogic, Jeus
JAVA CONNECTION CLASSPATH All connection users to DB2 configured classpath in their profile /home/db2inst1/sqllib/db2profile # CLASSPATH=$CLASSPATH:/home/db2inst1/sqllib/java/db2java.zip Type4 => db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar
JAVA CONNECTION app Driver(Type 2) Driver Com.ibm.db2.jdbc.app.DB2Driver URL jdbc:db2:sample net Driver(Type 3) Driver Com.ibm.db2.jdbc.net.DB2Driver ORACLE oracle.jdbc.driver.oracledriver URL jdbc:db2://211.63.66.113:6789/sample ORACLE jdbc:oracle:thin:@ 211.63.66.113:1521:sample Execute command in server installed DB2 # db2jstrt if not CLI0621E! Default port open : 6789 port jcc Driver(Type 4) Driver com.ibm.db2.jcc.db2driver URL jdbc:db2://211.63.66.113:50000/sample
JAVA CONNECTION DB2Driver Load Load DB DB에대한연결 Statement 객체객체생성 DB2 + JAVA Query Query 실행실행 연결에연결에관련된관련된객체객체종료
JAVA source(1) import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; public class DB2Connect extends HttpServlet { public void service(httpservletrequest req, HttpServletResponse res) throws ServletException, IOException { String URL = "jdbc:db2://211.63.66.113:6789/sample"; String userid = "db2inst1"; String passwd = "db2inst1"; Connection con = null; Statement stmt = null; ResultSet rs = null; String query = null; * IP 와 PORT,DB 명을포함하는정확한 URL 작성 * URL 의 DB 에인증할수있는사용자계정과패스워드 * Connection 에관련된객체선언 PrintWriter out = new PrintWriter(new OutputStreamWriter(res.getOutputStream(), "EUC-KR"));
JAVA source(2) try { Class.forName("COM.ibm.db2.jdbc.net.DB2Driver"); } catch (ClassNotFoundException e) { out.println("couldn't Load Database Driver : " + e.getmessage()); } try { res.setcontenttype("text/html; charset=euc-kr"); con = DriverManager.getConnection(URL, userid, passwd); * DB2Driver 를 Load 하고등록하는과정으로 Load 할수없는경우에는에러메시지를화면에보여준다. * 위에서미리정의해놓은 URL, userid, passwd 를이용해서데이터베이스에대한연결을수행한다. query = "select empno from employee"; stmt = con.createstatement(); rs = stmt.executequery(query); out.println("<html><body>"); while(rs.next()) { out.println(rs.getstring("empno")+"<br>"); } out.println("</body></html>"); out.flush(); out.close(); * 연결된 Connection 객체를이용해서실제적으로 db 의세션을하나가져오는 Statement 객체를생성한다 *query 를실행하고결과를 Result 객체에담는다.
JAVA source(3) } } rs.close(); stmt.close(); con.close(); } catch (SQLException e) { out.println("<html><body>"); out.println("sqlstate : "+e.getsqlstate()+"<br>"); out.println("message : "+e.getmessage()+"<br>"); out.println("errorcode : "+e.geterrorcode()+"<br>"); out.println("query : "+query+"<br>"); out.println(e); out.println("</body></html>"); out.flush(); out.close(); e.printstacktrace(); } finally { if (stmt!= null) try{ stmt.close(); }catch(exception e){} if (con!= null) try{ con.close(); }catch(exception e){} } * 연결에관련된객체종료 * 중요한부분으로생략하지말고꼭수행해주어야한다. * 프로그램실행중에러가발생한경우정확한에러메시지를화면에보여준다. *e.printstacktrace() 를걸면에러가발생한경우 trace 역순으로추적하여에러발생원인을잡아낼수있으므로유용하다. *finally() 문에 Statement 객체와 Connection 객체가존재하는지확인하는체크를해서 close() 해주어야한다.
PHP ENV Installation Apache Install Apache 1.x Apache2.x not supported yet /usr/local/apache Installation PHP /usr/lib/php4 They are good for rapid prototyping They offer excellent support for dynamic Web applications Native support for many Databases They work well in environments involving multiple architectures
PHP CONNECTION odbc function use odbc_connect() Installation unixodbc ODBC Manager system DSN ENV /etc/odbc.ini /etc/odbcinst.ini # /etc/odbc.ini [SAMPLE] Description = IBM DB2 SAMPLE DB Driver = /home/db2inst1/sqllib/lib/libodbc.so.1 DBALIAS = SAMPLE # /etc/odbcinst.ini [ODBC] Trace = Yes Trace File = /tmp/sql.log [IBM] Description = IBM DB2 ODBC Driver Driver = /home/db2inst1/sqllib/lib/libodbc.so.1 Setup = /home/db2inst1/sqllib/lib/libodbcinst.so.1 File Usage = 1
PHP CONNECTION ODBC ODBC setting setting DB DB에대한연결 odbc_connect() DB2 + PHP Query Query 실행실행 odbc_exec() DB DB연결연결종료 odbc_close()
PHP source(1) <? $dsn="sample" ; $userid= db2inst1"; $passwd= db2inst1"; $dbconn = odbc_connect($dsn, $userid, $passwd) or die(" 데이터베이스연결에실패하였습니다."); if (!$dbconn) { $err_no = odbc_error(); $err_msg = odbc_errormsg(); $error_msg = "ERROR CODE ". $err_no. " : $err_msg"; $error_msg = addslashes($error_msg); echo("$error_msg"); exit; }?> *odbc_connect() 함수를이용해서데이터베이스에연결을한다. * 연결에실패한경우정확한에러메시지를화면에보여준다.
PHP source(2) <html> <body> <? $query = "select empno from employee"; *odbc_exec() 함수를이용해서 query 를실행한다. $Result = odbc_exec($dbconn, $query); for($i=0; $i <= odbc_fetch_row($result); $i++) { $empno = odbc_result($result,"empno"); echo("$empno<br>"); }?> </body> </html> <? odbc_close($dbconn);?> *odbc_result() 함수를이용해서 query 에대한결과를얻는다. *odbc_close() 함수를이용해서 DB 에대한연결을 close() 해준다
DEMO 1 : Web Programming Oracle 의 emp table 을 DB2 의 emp_ora table 로 federation 으로구성한다 웹프로그램 (servlet) 으로 DB2 에연결하여 federation 으로구성한 emp_ora table 에대해 insert,update,delete 처리를한다. 실제적으로 Oracle 의 emp table 에트랜잭션처리가된걸확인한다.
DEMO 1 : Web Programming Data Source Oracle : emp SQL> desc emp 이름널? 유형 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
DEMO 1 : Web Programming Oracle Federation Wrapper CREATE WRAPPER "net8" LIBRARY 'db2net8.dll'; Server CREATE SERVER ORA9DB TYPE ORACLE VERSION '9' WRAPPER "NET8" OPTIONS( ADD NODE 'ora9', PASSWORD 'Y'); User mapping CREATE USER MAPPING FOR ADMINISTRATOR" SERVER "ORA9DB" OPTIONS ( ADD REMOTE_AUTHID 'scott', ADD REMOTE_PASSWORD tiger'); Nickname CREATE NICKNAME ADMINISTRATOR.EMP_ORA FOR ORA9DB.SCOTT.EMP;
DEMO 1 : Web Programming Data Source DB2 : emp_ora db2 => describe table emp_ora 컬럼 유형 유형 이름 스키마 이름 길이 크기 널 ------------------------------ --------- ------------------ -------- ----- ------ EMPNO SYSIBM SMALLINT 2 0 아니오 ENAME SYSIBM VARCHAR 10 0 예 JOB SYSIBM VARCHAR 9 0 예 MGR SYSIBM SMALLINT 2 0 예 HIREDATE SYSIBM TIMESTAMP 10 0 예 SAL SYSIBM DECIMAL 7 2 예 COMM SYSIBM DECIMAL 7 2 예 DEPTNO SYSIBM SMALLINT 2 0 예
DEMO 1 : Web Programming
DEMO 1 : Web Programming
DEMO 2 : Data Federation A 이벤트사는고객사로부터의뢰받은행사를개최하여당첨된고객에게사은품을전달하고있다 A 이벤트사의데이터베이스는 DB2 를사용하고, 고객사는 Oracle 을사용한다 행사기간의당첨고객리스트는 excel 파일로주기적으로수집된다 고객사 excel Call Center A 이벤트사 고객정보 (Oracle) 이벤트정보 (DB2)
DEMO 2 : Data Federation Data Source Oracle : custmain SQL> desc custmain 이름널? 유형 ----------------------------------------- -------- ---------------------------- SNO NOT NULL VARCHAR2(13) NAME VARCHAR2(20) TEL1 VARCHAR2(16) TEL2 VARCHAR2(16) EMAIL VARCHAR2(50) ADDR1 VARCHAR2(50) DB2 : event db2 => describe table event 컬럼 유형 유형 이름 스키마 이름 길이 크기 널 ------------------------------ --------- ------------------ -------- ----- ------ RATE SYSIBM INTEGER 4 0 예 DESCRIPTION SYSIBM VARCHAR 100 0 예 SDATE SYSIBM DATE 4 0 예 EDATE SYSIBM DATE 4 0 예
DEMO 2 : Data Federation Excel : file1.xls
DEMO 2 : Data Federation Oracle Federation Wrapper CREATE WRAPPER "net8" LIBRARY 'db2net8.dll'; Server CREATE SERVER ORA9DB TYPE ORACLE VERSION '9' WRAPPER "NET8" OPTIONS( ADD NODE 'ora9', PASSWORD 'Y'); User mapping CREATE USER MAPPING FOR ADMINISTRATOR" SERVER "ORA9DB" OPTIONS ( ADD REMOTE_AUTHID 'scott', ADD REMOTE_PASSWORD tiger'); Nickname CREATE NICKNAME ADMINISTRATOR.CUSTMAIN FOR ORA9DB.SCOTT.CUSTMAIN;
DEMO 2 : Data Federation Excel Federation Wrapper CREATE WRAPPER "excel2000" LIBRARY 'db2lsxls.dll'; Server CREATE SERVER EXCELDEMO WRAPPER "EXCEL2000"; Nickname CREATE NICKNAME FILE ( SNO VARCHAR(13), NAME VARCHAR(20), RATE INTEGER ) FOR SERVER EXCELDEMO OPTIONS ( FILE_PATH 'C: temp file1.xls');
DEMO 2 : Data Federation Data Source DB2 : custmain db2 => describe table custmain 컬럼 유형 유형 이름 스키마 이름 길이 크기 널 ------------------------------ --------- ------------------ -------- ----- ------ SNO SYSIBM VARCHAR 13 0 아니오 NAME SYSIBM VARCHAR 20 0 예 TEL1 SYSIBM VARCHAR 16 0 예 TEL2 SYSIBM VARCHAR 16 0 예 EMAIL SYSIBM VARCHAR 50 0 예 ADDR1 SYSIBM VARCHAR 50 0 예 DB2 : file db2 => describe table file 컬럼 유형 유형 이름 스키마 이름 길이 크기 널 ------------------------------ --------- ------------------ -------- ----- ------ SNO SYSIBM VARCHAR 13 0 예 NAME SYSIBM VARCHAR 20 0 예 RATE SYSIBM INTEGER 4 0 예
DEMO 2 : Data Federation
DEMO 2 : Data Federation Query SELECT F.SNO, F.NAME, C.TEL1, C.TEL2, SUBSTR(C.EMAIL,1,20), SUBSTR(C.ADDR1,1,20), F.RATE, SUBSTR(E.DESCRIPTION,1,20) FROM FILE F, CUSTMAIN C, EVENT E WHERE F.SNO = C.SNO AND F.RATE = E.RATE AND CURRENT DATE BETWEEN E.SDATE AND E.EDATE ORDER BY F.RATE; Excel 파일 Oracle 데이터 DB2 데이터
DEMO 2 : Data Federation
DEMO 2 : Data Federation Excel : file2.xls
DEMO 2 : Data Federation Excel Federation Nickname DROP NICKNAME FILE; CREATE NICKNAME FILE ( SNO VARCHAR(13), NAME VARCHAR(20), RATE INTEGER ) FOR SERVER EXCELDEMO OPTIONS ( FILE_PATH C: temp file2.xls');
DEMO 2 : Data Federation