설치및환경설정 JDBC 접속세션구분 / 확인 2013. 11. 01
개요오라클과티베로에서 JDBC 를통해접속한세션을구분할수있도록 JDBC 접속시 ConnectionProperties 를통해구분자를넣어줄수있다. 하나의 Node 에다수의 WAS 가있을경우 DB 에서 Session Kill 등의동작수행시원하는 Session 을선택할수있다. 사용하기 JEUS 에서설정방법 1 Oracle JEUSMain.xml 파일에다음항목을추가한다. <database> <vendor>oracle</vendor> <export-name>orads</export-name> <data-source-class-name>oracle.jdbc.pool.oracleconnectionpooldatasource</data-source-classname> <data-source-type>connectionpooldatasource</data-source-type> <database-name>ora11g</database-name> <port-number>1521</port-number> <server-name>192.168.70.155</server-name> <user>scott</user> <password>tiger</password> <name>drivertype</name> <type>java.lang.string</type> <value>thin</value> <name>connectionproperties</name> <type>java.util.properties</type> <value>v$session.program=was1_orads</value> <connection-pool> <pooling> <min>2</min> <max>20</max> <period>500000</period> </pooling> <wait-free-connection> <enable-wait>true</enable-wait> <wait-time>60000</wait-time> </wait-free-connection> <check-query>select 1 FROM DUAL</check-query> <check-query-period>120000</check-query-period> 2
</connection-pool> </database> 위의빨간부분과같이 ConnectionProperties 에 v$session.program 으로정의해주면된다. DB 쪽에서다음과같은 SQL 문을통해서확인이가능하다. SQL> select username, program, status, osuser, machine from v$session where username='scott' order by program; USERNAME PROGRAM STATUS OSUSER MACHINE ---------- -------------------- --------------- ---------- --------------- SCOTT JDBC Thin Client INACTIVE ozke901 kkam SCOTT JDBC Thin Client INACTIVE ozke901 kkam SCOTT was1_orads INACTIVE ozke901 kkam SCOTT was1_orads INACTIVE ozke901 kkam SQL> 해당설정을하지않은경우는 PROGRAM 이 JDBC Thin Client 로나온다. 만약 v$session 의조회권한이없을시 sys or system 계정에서다음명령을통해권한을부여해주면확인이가능하다. SQL> grant select on v_$session to scott; v$session 이아닌 v_$session 에권한을부여할수있다. 2 Tibero JEUSMain.xml 파일에다음항목을추가한다. <database> <vendor>tibero</vendor> <export-name>tbds3</export-name> <data-source-class-name> com.tmax.tibero.jdbc.ext.tbconnectionpooldatasource </data-source-class-name> <data-source-type>connectionpooldatasource</data-source-type> <database-name>tibero</database-name> <port-number>8629</port-number> <server-name>192.168.80.10</server-name> <user>tibero</user> <password>tmax</password> <name>connectionproperties</name> 3
<type>java.util.properties</type> <value>program_name=was1_tbds</value> <connection-pool> <pooling> <min>2</min> <max>10</max> <step>1</step> <period>300000</period> </pooling> <wait-free-connection> <enable-wait>true</enable-wait> <wait-time>60000</wait-time> </wait-free-connection> <check-query>select 1 from dual</check-query> </connection-pool> </database> 위의빨간부분과같이 ConnectionProperties 에 program_name 으로정의해주면된다. DB 쪽에서다음과같은 SQL 문을통해서확인이가능하다. SQL> select username, prog_name, status, osuser, machine from v$session where username='tibero' order by prog_name; USERNAME PROG_NAME STATUS OSUSER MACHINE ---------- -------------------- --------------- ---------- --------------- TIBERO JDBC Thin Client ACTIVE ozke901 kkam TIBERO JDBC Thin Client ACTIVE ozke901 kkam TIBERO tbsql ACTIVE tb5 public1 TIBERO tbsql ACTIVE tb5 public1 TIBERO was1_tbds ACTIVE ozke901 kkam TIBERO was1_tbds ACTIVE ozke901 kkam 해당설정을하지않은경우는 PROGRAM 이 JDBC Thin Client 로나온다. 만약 v$session 의조회권한이없을시 sys 계정에서다음명령을통해권한을부여해주면확인 이가능하다. SQL> grant select on v$session to tibero 4
Tibero 의경우는 JDBC Driver 버전이 Tibero 5 rev.67823 이상, Tibero 5 이상에서사용이가능하다. 버전확인은다음명령을통해확인할수있다. 1) JDBC Driver 버전확인 $ java -jar tibero5-jdbc.jar Tibero JDBC Driver 5.0 (Rev.68181M) 2) DB 버전확인 $ tbboot -v Tibero 5 (Build 68328, DB 5.0) 드라이버버전과 DB 버전모두조건을만족해야사용이가능하다. 다음과같은에러가발생한다면버전을확인해보기바란다. [2013.02.22 17:44:50][0][0_576] [container1-15] [JDBC-0352] Could not find the property [ConnectionProperties] in the driver's class [com.tmax.tibero.jdbc.ext.tbconnectionpooldatasource] for the data source [tbds]. Please check if it is valid property for this data source. << Exception >> java.lang.nosuchmethodexception: com.tmax.tibero.jdbc.ext.tbconnectionpooldatasource.setconnectionproperties( java.util.properties) at java.lang.class.getmethod(class.java:1581) at jeus.jdbc.datasource.dbdsbinder.invokemethod(dbdsbinder.java:98) at jeus.jdbc.datasource.datasourcegenerator.getdatasource(datasourcegenerator.java:64) at jeus.jdbc.connectionpool.connectionpool.getdatasource(connectionpool.java:328) at jeus.jdbc.connectionpool.connectionpool.getpooledconnection(connectionpool.java:452) at jeus.jdbc.connectionpool.connectionstore.createpooledconnection(connectionstore.java:424) at jeus.jdbc.connectionpool.connectionstore.sizeup(connectionstore.java:418) at jeus.jdbc.connectionpool.connectionstore.getjeuspooledconnectionusermatched(connectionstore.java:178) at jeus.jdbc.connectionpool.connectionpool.prepareconnection(connectionpool.java:854) at jeus.jdbc.connectionpool.connectionpool.innergetconnection(connectionpool.java:736) at jeus.jdbc.connectionpool.connectionpool.getconnection(connectionpool.java:662) at jeus.jdbc.datasource.datasourcewrapper.getconnection(datasourcewrapper.java:42) at jeus_jspwork._500_ds2_5fjsp._jspservice(_500_ds2_5fjsp.java:56) at jeus.servlet.jsp.httpjspbase.service(httpjspbase.java:54) at javax.servlet.http.httpservlet.service(httpservlet.java:856) 5
at jeus.servlet.jsp.jspservletwrapper.executeservlet(jspservletwrapper.java:147) at jeus.servlet.servlets.jspservlet.execute(jspservlet.java:365) at jeus.servlet.engine.httprequestprocessor.run(httprequestprocessor.java:284) <<!Exception >> [2013.02.22 17:44:50][0][0_576] [container1-15] [JDBC-0003] Failed to create the new data source instance; export-name=[tbds] [2013.02.22 17:44:50][1][0_576] [container1-15] [JDBC-0007] Failed to create a new PooledConnection from [tbds] << Exception >> jeus.jdbc.connectionpool.jeussqlexception: Failed to create the new data source instance; exportname=[ tbds] at jeus.jdbc.connectionpool.connectionpool.getdatasource(connectionpool.java:331) at jeus.jdbc.connectionpool.connectionpool.getpooledconnection(connectionpool.java:452) at jeus.jdbc.connectionpool.connectionstore.createpooledconnection(connectionstore.java:424) at jeus.jdbc.connectionpool.connectionstore.sizeup(connectionstore.java:418) at jeus.jdbc.connectionpool.connectionstore.getjeuspooledconnectionusermatched(connectionstore.java:178) at jeus.jdbc.connectionpool.connectionpool.prepareconnection(connectionpool.java:854) at jeus.jdbc.connectionpool.connectionpool.innergetconnection(connectionpool.java:736) at jeus.jdbc.connectionpool.connectionpool.getconnection(connectionpool.java:662) at jeus.jdbc.datasource.datasourcewrapper.getconnection(datasourcewrapper.java:42) at jeus_jspwork._500_ds2_5fjsp._jspservice(_500_ds2_5fjsp.java:56) at jeus.servlet.jsp.httpjspbase.service(httpjspbase.java:54) at javax.servlet.http.httpservlet.service(httpservlet.java:856) at jeus.servlet.jsp.jspservletwrapper.executeservlet(jspservletwrapper.java:147) at jeus.servlet.servlets.jspservlet.execute(jspservlet.java:365) at jeus.servlet.engine.httprequestprocessor.run(httprequestprocessor.java:284) Caused by: jeus.jdbc.datasource.dbdatasourceexception: Could not find the property [ConnectionProperties] in the driver's class [com.tmax.tibero.jdbc.ext.tbconnectionpooldatasource] for the data source [tbds]. Please check if it is valid property for this data source. at jeus.jdbc.datasource.datasourcegenerator.getdatasource(datasourcegenerator.java:104) at jeus.jdbc.connectionpool.connectionpool.getdatasource(connectionpool.java:328)... 14 more Caused by: java.lang.nosuchmethodexception: com.tmax.tibero.jdbc.ext.tbconnectionpooldatasource.setconnectionproperties( java.util.properties) at java.lang.class.getmethod(class.java:1581) 6
at jeus.jdbc.datasource.dbdsbinder.invokemethod(dbdsbinder.java:98) at jeus.jdbc.datasource.datasourcegenerator.getdatasource(datasourcegenerator.java:64)... 15 more <<!Exception >> 3. 추가설정방법 ConnectionProperties 를통해서 user, password 까지한번에설정이가능하다. 하지만이는주의해서사용해야한다. Oracle 의경우는 ConnectionProperties 에설정된 user, password 를무시하고 <user>, <password> 또는 로 user, password 를설정해야만 DB 로세션을생성할수있다. 이두가지설정중하나라도없이 ConnectionProperties 에 user, password 를설정할경우에러가발생한다. Tibero 의경우는 ConnectionProperties 에설정된값을무시하지는않지만혼용하여사용할경우최종적으로 setting 된값이적용되기에순서를주의해서사용해야한다. ConnectionProperties 의 <value>user=sys,password=tibero,program_name=was1_tbds</value> 와 <user>tibero</user> 를동시에쓸경우나중에쓴값이적용되고이는 JDBC Spec 에어떤방식을사용하라고명시된것이없다. CASE #1 <user>tibero</user> <password>tmax</password> <name>connectionproperties</name> <type>java.util.properties</type> <value>user=sys,password=tibero,program_name=tbds</value> CASE #2 <name>connectionproperties</name> <type>java.util.properties</type> <value>user=sys,password=tibero,program_name=tbds</value> <user>tibero</user> <password>tmax</password> CASE #3 <user>tibero</user> <name>connectionproperties</name> 7
<type>java.util.properties</type> <value>user=sys,password=tibero,program_name=tbds</value> <password>tmax</password> CASE #1 의경우는 sys 유저로 Tibero 에접속된다. CASE #2 의경우는 tibero 유저로 Tibero 에접속된다. CASE #3 의경우는 sys 유저의 password 를 tmax 로입력한것과같아서에러가발생한다. 가능한 ConnectionProperties 에 user, password 를명시하지않을것을권고한다. Stand-Alone JAVA Application 에서설정방법 Oracle public class JdbcTest{ public static void main(string[] args) throws Exception{ Class.forName("oracle.jdbc.driver.OracleDriver"); Properties prop = new Properties(); prop.setproperty("user","scott"); prop.setproperty("password","tiger"); prop.setproperty("v$session.program","java"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora11g", prop); DB 쪽에서다음과같은 SQL 문을통해서확인이가능하다. SQL> select username, program, status, osuser, machine from v$session where username='scott' order by program; USERNAME PROGRAM STATUS OSUSER MACHINE ---------- -------------------- --------------- ---------- --------------- SCOTT JAVA INACTIVE ozke901 kkam SQL> 2 Tibero public class JdbcTest{ public static void main(string[] args) throws Exception{ Class.forName("com.tmax.tibero.jdbc.TbDriver"); Properties prop = new Properties(); prop.setproperty("user","tibero"); 8
prop.setproperty("password","tmax"); prop.setproperty("program_name","java"); Connection conn = DriverManager.getConnection("jdbc:tibero:thin:@localhost:8629:tibero", prop); DB 쪽에서다음과같은 SQL 문을통해서확인이가능하다. SQL> select username, prog_name, status, osuser, machine from v$session where username='tibero' order by prog_name; USERNAME PROG_NAME STATUS OSUSER MACHINE ---------- ------------------------------ ---------- ---------- --------------- TIBERO JAVA ACTIVE tiberokkam TIBERO tbadmin ACTIVE ozke901 kkam 결론 이기능은동일노드에서다수의 DB Connection 이연결될경우이를구분하기위한목적으 로사용이가능하며, DB 관점에서모니터링, Session Kill 시유용하게사용할수있음. 9
Copyright 2013 TmaxSoft Co., Ltd. All Rights Reserved. TmaxSoft Co., Ltd. Trademarks Tmax, WebtoB, WebT, JEUS, ProFrame, SysMaster and OpenFrame are registered trademarks of TmaxSoft Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies. Contact Information TmaxSoft can be contacted at the following addresses to arrange for a consulting team to visit your company and discuss your options for legacy modernization. Korea - TmaxSoft Co., Ltd. Corporate Headquarters 272-6 Seohyeon-dong, Bundang-gu, Seongnam-si, South Korea, 463-824 Tel : (+82) 31-8018-1708 Fax : (+82) 31-8018- 1710 Website : http://tmaxsoft.com U.S.A. - TmaxSoft Inc. 560 Sylvan Avenue Englewood Cliffs, NJ 07632, USA Tel : (+1) 201-567-8266 Fax : (+1) 201-567- 7339 Website : http://us.tmaxsoft.com Japan TmaxSoft Japan Co., Ltd. 5F Sanko Bldg, 3-12-16 Mita, Minato-Ku, Tokyo, 108-0073 Japan Tel : (+81) 3-5765-2550 Fax: (+81) 3-5765- 2567 Website : http://jp.tmaxsoft.com China TmaxSoft China Co., Ltd. Room 1101, Building B, Recreo International Center, East Road Wang Jing, Chaoyang District, Beijing, 100102, P.R.C Tel : (+86) 10-5783-9188 Fax: (+86) 10-5783- 9188(#800) Website : http://cn.tmaxsoft.com China(JV) Upright(Beijing) Software Technology Co., Ltd Room 1102, Building B, Recreo International Center, East Road Wang Jing, Chaoyang District, Beijing, 100102, P.R.C Tel : (+86) 10-5783-9188 Fax: (+86) 10-5783- 9188(#800) Website : www.uprightsoft.com TN-JSIT-C1101002 10