JDBC Resource Registration on WebSphere Console

1. JDBC Resource Registration on WebSphere Console NO !!! javax.naming.InitialContext ctx = new javax.naming.InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/DB2UDB"); java.sql.Connection conn = ds.getConnection(); Servlet/JSP 코드에서 명시적으로 conn.close()하였더라도 Servlet/JSP가 끝날 때까지 connection은 pool로 release되지 않음  불필요한 과도한 JDBC 연결자원사용됨/경우에 따라 성능장애 Default: TRANSACTION_NONE Sharable Connection Information logging every called

2. J2EE: DataSource Resource Reference when EAR packaging request start to end with a thread lifetime register User Application Transaction manager end Global JNDI name Mapping during deploy EAR jdbc/MyDB Resource Reference jdbc/DB2UDB Resource Manager Local JNDI name WebSphere Application Server javax.naming.InitialContext ctx = new javax.naming.InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup(“java:comp/env/jdbc/MyDB"); java.sql.Connection conn = ds.getConnection(); Database B

3. Using AAT, Datasource Resource Reference AAT  EAR 웹모듈 자원참조 유형: DataSource 공유범위: Shareable/Unshareable javax.naming.InitialContext ctx = new javax.naming.InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/UnsharedDS"); java.sql.Connection conn = ds.getConnection();

4. Using AAT, Transaction Isolation Level Transaction Isolation Level (1) TRANSACTION_NONE (default) (2) TRANSACTION_READ_UNCOMMITTED (3) TRANSACTION_READ_COMMITTED (4) TRANSACTION_REPEATABLE_READ (5) TRANSACTION_SERIALIZABLE 동시성 제어(Concurrency Control) 이슈 902 웹스피어 5.0 데이타베이스 연결 설정법 http://www.javaservice.net/~java/bbs/read.cgi?m=appserver&b=was&c=r_p&n=1061739578

5. public void method() throws Exception { String name = getEmployeeName("7904"); String dept = getDeptName("1234"); } public String getEmployeeName(String id) throws Exception { Connection conn = null; Statement stmt = null; String name = null; try{ javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/A"); conn = ds.getConnection(); stmt = conn.createStatement(); Result rs = stmt.executeQuery("select ename from emp where empno = " + id); name = rs.getString("ename"); rs.close(); } finally{ if(stmt != null) try{stmt.close();}catch(Exception e){} if(conn != null) try{conn.close();}catch(Exception e){} } return name; } public String getDeptName(String id) throws Exception { Connection conn = null; Statement stmt = null; String name = null; try{ javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/A "); conn = ds.getConnection(); stmt = conn.createStatement(); Result rs = stmt.executeQuery("select deptname from dept where deptno = " + id); name = rs.getString("deptname"); rs.close(); } finally{ if(stmt != null) try{stmt.close();}catch(Exception e){} if(conn != null) try{conn.close();}catch(Exception e){} } return name; } Shareable Connection Resource Manager JDBC connection pool allocate release Database B request end within a thread boundary

6. public void method() throws Exception { String name = getEmployeeName("7904"); String dept = getDeptName("1234"); } public String getEmployeeName(String id) throws Exception { Connection conn = null; Statement stmt = null; String name = null; try{ javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/A"); conn = ds.getConnection(); stmt = conn.createStatement(); Result rs = stmt.executeQuery("select ename from emp where empno = " + id); name = rs.getString("ename"); rs.close(); } finally{ if(stmt != null) try{stmt.close();}catch(Exception e){} if(conn != null) try{conn.close();}catch(Exception e){} } return name; } public String getDeptName(String id) throws Exception { Connection conn = null; Statement stmt = null; String name = null; try{ javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/A "); conn = ds.getConnection(); stmt = conn.createStatement(); Result rs = stmt.executeQuery("select deptname from dept where deptno = " + id); name = rs.getString("deptname"); rs.close(); } finally{ if(stmt != null) try{stmt.close();}catch(Exception e){} if(conn != null) try{conn.close();}catch(Exception e){} } return name; } Unshareable Connection Resource Manager JDBC connection pool allocate release allocate release Database B

7. XA and non-XA JDBC Driver Provider Non-XA JDBC Driver Provider : 1-phase commit resource XA JDBC Driver Provider : 2-phase commit resource Non-XA JDBC Driver Provider javax.naming.InitialContext ctx = new javax.naming.InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup(“java:comp/env/jdbc/MyDB"); java.sql.Connection conn = ds.getConnection(); boolean mode = conn.getAutoCommit();  true XA JDBC Driver Provider javax.naming.InitialContext ctx = new javax.naming.InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup(" java:comp/env/jdbc/MyDB"); java.sql.Connection conn = ds.getConnection(); boolean mode = conn.getAutoCommit();  false

8. Question : If - Unshareable Connection - Non-XA JDBC Provider call() Servlet/JSP EJB ctx.lookup("java:comp/env/jdbc/A"); ctx.lookup("java:comp/env/jdbc/A"); Non-XA JDBC Resource WebSphere Application Server Global Transaction error with 1pc resource !! Shareable needed Database A

9. XA JDBC Provider : 2 phase-commit tx.begin() User Application Transaction manager tranlog tx.commit() 1 phase: prepare 2 phase: commit/rollback XA Resource manager XA Resource manager WebSphere Application Server 1 phase: in-doubt 2 phase: commit/rollback Database B Database A

10. XA JDBC Provider : 2 phase-commit Transaction propagation EJB1 EJB2 XA Resource manager XA Resource manager Database B Database A

11. 2 phase commit sample (XA JDBC Datasource) java.sql.Connection conn1 = null; java.sql.Statement stmt1 = null; java.sql.Connection conn2 = null; java.sql.Statement stmt2 = null; javax.transaction.UserTransaction tx = null; try { javax.naming.InitialContext ctx = new javax.naming.InitialContext(); tx = (javax.transaction.UserTransaction) ctx.lookup("java:comp/UserTransaction"); tx.begin(); // ------------------------------------------------------------------------- javax.sql.DataSource ds1 = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/A"); conn1 = ds1.getConnection(); stmt1 = conn1.createStatement(); stmt1.executeUpdate("update emp set ename = 'LWY" + count + "' where empno = 7934"); // ------------------------------------------------------------------------- javax.sql.DataSource ds2 = (javax.sql.DataSource)ctx.lookup("java:comp/env/jdbc/B"); conn2 = ds2.getConnection(); stmt2 = conn2.createStatement(); stmt2.executeUpdate("update emp set ename = 'LWY" + count + "' where empno = 7934"); // ------------------------------------------------------------------------- tx.commit(); } catch(Exception e){ if ( tx != null ) try{tx.rollback();}catch(Exception ee){} } finally { if ( stmt1 != null ) try { stmt1.close();}catch(Exception e){} if ( conn1 != null ) try { conn1.close();}catch(Exception e){} if ( stmt2 != null ) try { stmt2.close();}catch(Exception e){} if ( conn2 != null ) try { conn2.close();}catch(Exception e){} } Database A Database B Question : How about in EJB? CMT/BMT