How To Configure Weblogic XA JDBC Connections To IBM i DB2

Applies To

• Oracle Weblogic 10.3
• Oracle SOA 11.1.1.6.0
• IBM Toolkit for Java (JTOpen/jt400.jar)

Goal

ให้สามารถทำงาน Two phase commit กับ iSeries/DB2 และ AS400/DB2 ด้วย JDBC driver ได้ ทั้งนี้ยังสามารถนำไปใช้กับ Software อื่นๆที่สนับสนุนงานที่เป็น Global transaction เช่น Oracle Service Bus 11g เป็นต้น

Solution

Weblogic Server

1. Download Driver
   JDBC driver สำหรับ DB2 บน IBM i มีอยู่ 2 แบบ คือ
   1) Native JDBC ซึ่งได้มาจาก IBM Developer Kit for Java
   2) Toolbox JDBC ได้มาจาก IBM Toolbox for Java หรือ JTOpen JDBC driver ซึ่งเป็น open source version ของ IBM Toolbox for Java สามารถ Download ได้ที่ http://jt400.sourceforge.net (ปัจจุบัน version 7.10)
   *** ในตัวอย่างนี้จะใช้ของ JTOpen
2. Unzip and copy
   cp lib/jt400.jar $WLS_HOME/user_projects/domains/base_domain/lib/
   cp lib/jt400.jar $WLS_HOME/wlserver_10.3/server/lib/
3. Verify permissions of the driver files:
   chown oracle:oinstall $WLS_HOME/user_projects/domains/base_domain/lib/jt400.jar
   chown oracle:oinstall $WLS_HOME/wlserver_10.3/server/lib/jt400.jar
   chmod 644 $WLS_HOME/user_projects/domains/base_domain/lib/jt400.jar
   chmod 644 $WLS_HOME/wlserver_10.3/server/lib/jt400.jar
4. Add the driver library to the weblogic class path:
   vi $WLS_HOME/user_projects/domains/base_domain/bin/setDomainEnv.sh
   Add the following line:
           export CLASSPATH="$WLS_HOME/user_projects/domains/base_domain/lib/jt400.jar:${CLASSPATH}"
   จากนั้น Restart WLS
   
   
5. Configure the Connection Pool
   WLS Console:
   a) In the Domain Structure Pane (Left side), click on Deployments
   b) Click on the DbAdapter link in the Deployments table
   c) Click on the “Configuration” Tab
   d) Click on the “Outbound Connection Pools” sub-Tab
   e) Expand the “javax.resource.cci.ConnectionFactory” by clicking on the + button.
   f)  Click the New button to create a new connection pool.
   g) Click the radio button beside “javax.resource.cci.ConnectionFactory” to select it. Click Next.
   h) Enter a JNDI Name in the format “eis/DB/{Connection Name}” (without the quotes)
       For example: eis/DB/mtl400
   i) Click Finish
   j) Back in the “Groups and Instances” list of the “Outbound Connection Pools” expand the
      “javax.resource.cci.ConnectionFactory” node. Again, do not click on the text link, instead, expand      the list by clicking the + box in front of the text.
   k) Find the “eis/DB/{Connection Name} Entry that you created and click on it to configure it.
   
   You are presented with a Properties tab that has a list “Outbound Connection Properties”
   Note: To change property values in this page, you need to click on the property value text, change the value and then hit enter. If you do not hit enter and navigate away, or click on another field, the values will not persist.
   
   l) Change the platformClassName
      from:
                 org.eclipse.persistence.platform.database.Oracle10Platform
      to:
                 oracle.tip.adapter.db.toplinkext.DB2AS400Platform
   m) set the xADataSourceName to the format: jdbc/{Connection Name}DataSource
        For example: jdbc/mtl400v2
   
   
   
   n) Click Save button
   o) Click the Transaction Tab
   p) Change the Transaction Support to “XA Transaction”
   q) Click Save
6. Create the DataSource
   a) Click on Home to return to the main Weblogic Server Administration Console Window.
   b) In the “Service” pane under “Domain Configurations” click on the “Data Sources” link.
   c) Click the New button and select “Generic Data Source” from the list.
   d) Set the Name value to {Connection Name}
       For example: MTL400
   e) set the JNDI Name to the format: jdbc/{Connection Name}DataSource
       For example: jdbc/mtl400v1
       Note: this value has to match the value entered into the xADataSourceName exactly.
   f) In the “Database Type:” list, select “DB2 for i5/OS”
   g) Click Next button
   h) In the Database Driver list, select “JTOpen's DB2 for i5/OS Driver (Type 4 XA) IBM Toolkit for Java; Versions 8.X and later”
   i) Click Next Button
   j) Click Next Button (for the Transaction Options page)
      You are presented with the “Create a New JDBC Data Source” page.
   k) Enter your database name... this would be the default library your connecting to...
   For example: TESTDB
   l) Enter your host name or IP Address of iSeries or AS400 server. If entering a name, make sure it resolves (can be pinged) on that server using ping {hostname}.
   m) For the port enter your port number – this is usually 446 for iSeries DB2 (*DRDA)
   n) Enter valid database credentials... username and password fields...
   o) Click Next Button
       You are prompted with the Test Database Connection pane...
   p) In the properties window, add the following entries – one per line, no delimiters:
       prompt=false
       translate binary=true
       serverName={Your iSeries/AS400 hostname or IP}
   q) Change the “Test Table Name:” value
       from:
                      SQL SELECT COUNT(*) FROM SYSIBM.SYSTABLES
       to:
                      SQL SELECT count(*) FROM qsys2.systables
   r) Click the “Test Configuration” Button
   s) Make a note of the Messages you receive back at the top of the window.
   t) Click the Next Button and check the Servers where you want this deployed.
      For example, “AdminServer” and then click Finish.
   
   *** Notes on Two Phase Commit operations involving iSeries/DB2 tables
   Any tables involved in XA operations (two phase commit) will require journaling to be enabled on those tables on the iSeries/AS400. This can be accomplished using the STRJRNPF command from green screen/5250 terminal. This is the general format:
                 STRJRNPF FILE(YOURLIB/YOURTBL) JRN(JRNLIB/YOUR_JRNL)
   Make sure that your iSeries admin provide the correct values for the journal settings if the tables you are working with are not journaled.

================

Good Luck.

================