Ihr Spezialist für komplexe IT-Systeme
  

CT Database Contest - Lazy Monkey Implementation

Frank Berger

07. June 2006

Table of Contents

1. Introduction
2. How to get started
2.1. Install Mono and Java JDK 1.5.0_05
2.2. Install Oracle Database (10.2) Express Edition
2.3. Install OC4J 10g (10.1.3)
2.4. Install the Lazy Monkey Implementation
2.5. Use the Webload-Driver to produce load
2.6. Changing the JDBC Connection Settings
2.7. Using the OC4J Admin Webfrontend
3. Implementation Details
3.1. General
3.2. dsnewcustomer
3.3. dslogin
3.4. dsbrowse
3.5. dspurchase
4. How to Contribute
5. Contributors
6. More Whitepapers about the DVD Store

The German IT magazine "c't" has called for an database contest based on the Dell DVD-Store (some kind of an Demo-Shop). This page gets you started for my implementation of the Dell DVD-Store. Target of development was to just tune the original Oracle DVD-Store implementation from Dell. You can find out more about that contest at the following URLs:

I named my implementation lazy monkey, because I wanted to do as less as possible. Starting from the reference implementation just tune it to become fast, following the KISS principle (KISS principle).

Caution

I did not have the time to create an implementation which could be pass as "ready for production" or "stable". The current release has lab-only / development state. For instance there are several issues:
  • Security issues with the database in terms of configuration (e.g. Oracle Listener) or Database user management - it is a really bad idea to use an user with "grant resource" within an web-application.
  • /usr/lib/oracle is a bad location for hugh datafiles on an production server.
  • There is no linux startup script for OC4J included.
  • OC4J contains a lot of examples and monitoring stuff that you may not want to show to everyone on the internet.
  • Some of the scripts supplied with my implementation contain hardcoded path information assuming an default installation.

  1. The Load-Driver needs .NET, so to run it under Linux get the latest Mono Packages and install them. For OC4J we will need the Java JDK 1.5.0_05.

    www.mono-project.com . Download Java 2 Platform Standard Edition 5.0 .

  1. Download Oracle Database 10g Release 2 (10.2) Express Edition for Linux x86 [198 MB] from: Oracle Database 10g Downloads .

  2. Check the following prerequisites:

    • Your hostname must be resolvable to an valid ip-address
    • libaio version 0.3.96 or above is installed
    • glibc version 2.3.2 or above is installed
    • /etc/init.d/functions exits

    Step-by-Step: 

    dhcppc3:~ # ping `hostname`
PING dhcppc3 (192.168.1.53) 56(84) bytes of data.
64 bytes from dhcppc3 (192.168.1.53): icmp_seq=1 ttl=64 time=0.095 ms
64 bytes from dhcppc3 (192.168.1.53): icmp_seq=2 ttl=64 time=0.109 ms
dhcppc3:~ # rpm -q glibc libaio
glibc-2.3.4-23.4
libaio-0.3.102-3
dhcppc3:~ # cat /etc/init.d/functions
cat: /etc/init.d/functions: No such file or directory
dhcppc3:~ # ln -s /etc/rc.status /etc/init.d/functions

  3. Install the Oracle RPM-Package.

    Step-by-Step: 

    [root@w0004 tmp]# rpm -i oracle-xe-10.2.0.1-0.1.i386.rpm
Executing Post-install steps..........

You must run '/etc/init.d/oracle-xe configure' as root user to
configure the database.

  4. Configure and create an Oracle Database Instance. Accept the default values and choose an password for the DBA accounts.

    Step-by-Step: 

    [root@w0004 tmp]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
...

Configuring Database...
Starting Oracle Net Listener.

If you run into problems, please see the Installation Guide for full documentation and requirements.

  1. Download OC4J 10g (10.1.3) [72 MB] from: Oracle Application Server - Oracle Containers for J2EE 10g (OC4J) - (10.1.3 build#060119) .

  2. Unzip the downloaded package into a new directory.

    Step-by-Step: 

    dhcppc3:~ # unzip oc4j_extended_101300.zip -d /opt/oc4j
  inflating: /opt/oc4j/sqlj/lib/translator.jar
  inflating: /opt/oc4j/toplink/jlib/antlr.jar
  inflating: /opt/oc4j/toplink/jlib/toplink.jar
...

  3. Set the environment variables JAVA_HOME, ORACLE_HOME, OC4J_JVM_ARGS to reflect the path layout of your system and startup OC4J. (Note: ORACLE_HOME points to the OC4J installation path, and not to the path of the Oracle Database files!) Please remember the oc4jadmin password you are asked for during the first startup of OC4J. We need it afterwards for the deployment of the application.

    Step-by-Step: 

    dhcppc3:~ # cd /opt/oc4j
dhcppc3:/opt/oc4j # export JAVA_HOME=/usr/java/jdk1.5.0_05
dhcppc3:/opt/oc4j # export ORACLE_HOME=/opt/oc4j
dhcppc3:/opt/oc4j # export OC4J_JVM_ARGS=-server
dhcppc3:/opt/oc4j # ./bin/oc4j -start
Starting OC4J from /opt/oc4j/j2ee/home ...
05/11/11 23:46:52 Auto-unpacking j2ee/home/applications/admin_web.war... done.
05/11/11 23:46:52 Auto-unpacking j2ee/home/applications/dms0.war... done.
...
05/11/11 23:46:55 Set OC4J administrator's password 
                  (password text will not be displayed as it is entered)
Enter password: foobar
Confirm password: foobar
Confirm password: The password for OC4J administrator "oc4jadmin" has been set.
05/11/11 23:46:58 The OC4J administrator "oc4jadmin" account is activated.
05/11/11 23:47:18 Oracle Containers for J2EE 10g (10.1.3.0.0) - 
                  Developer Preview 4 initialized

  1. Switch to the user oraclexe, download the lazy monkey implementation from this website and the testdata-set / webload-drivers from the Dell website.

    ds2_lazymonkey-1.0.1.tar.gz (1.0.1 Final/Reviewed)

    Testdata and Load-Driver (ds2.tar.gz)

    Step-by-Step: 

    [root@w0004 tmp]# su oraclexe
bash-3.00$ cd
bash-3.00$ wget http://www.fm-berger.de/download/ds2_lazymonkey-1.0.1.tar.gz
bash-3.00$ wget http://linux.dell.com/dvdstore/ds2.tar.gz
bash-3.00$ tar -xzf ds2.tar.gz
bash-3.00$ tar -xzf ds2_lazymonkey-1.0.1.tar.gz

  2. Create the DS2 schema

    • Go to the ds2_lazymonkey directory
    • Set the environment variables using the supplied oracle_env.sh script. (Please make sure that the script contains the right path information for your system, you may have to adjust that!)
    • Create a new testdata-set (e.g. for database size SMALL)
    • Create the DS2 database schema and import the testdata-set.

    Caution

    Some of the scripts used here have hardcoded directory information, if you do not use the default location (/usr/lib/oracle/..) and ORACLE_SID=XE, you have to adjust:
    • ds2_lazymonkey/oracle_env.sh
    • ds2_lazymonkey/build/oracleds2_create_tablespaces.sql

    Step-by-Step: 

    oraclexe@dhcppc3:~> cd ds2/ds2_lazymonkey/
oraclexe@dhcppc3:~/ds2/ds2_lazymonkey> . ./oracle_env.sh
oraclexe@dhcppc3:~/ds2/ds2_lazymonkey> bash gen_data_small.sh
creating customers US
creating customers other
creating orders for Jan
...
creating orders for Dec
creating inventory
creating products
files contain:
10000 cust/row_cust.csv
10000 cust/us_cust.csv
10000 prod/prod.csv
10000 prod/inv.csv
oraclexe@dhcppc3:~/ds2/ds2_lazymonkey> bash create_all_small.sh

  3. Deploy ds2_lazymonkey.ear

    • go to ds2_lazymonkey/web/jsp
    • Set OC4J_HOME and JAVA_HOME
    • deploy ds2_lazymonkey.ear using the deploy.sh script in the same directory. Make sure that OC4J is running and that you supply the correct oc4jadmin password to the script.

    Step-by-Step: 

    oraclexe@dhcppc3:~/ds2/ds2_lazymonkey> cd web/jsp/
oraclexe@dhcppc3:~/ds2/ds2_lazymonkey/web/jsp> export JAVA_HOME=/usr/java/jdk1.5.0_05
oraclexe@dhcppc3:~/ds2/ds2_lazymonkey/web/jsp> export OC4J_HOME=/opt/oc4j
oraclexe@dhcppc3:~/ds2/ds2_lazymonkey/web/jsp> bash deploy.sh foobar
[ 2005-11-12 00:13:13.207 CET ] Application Deployer for ds2_lazymonkey STARTS.
[ 2005-11-12 00:13:13.235 CET ] Unpacking ds2_lazymonkey.ear
[ 2005-11-12 00:13:13.245 CET ] Done unpacking ds2_lazymonkey.ear
[ 2005-11-12 00:13:13.250 CET ] Unpacking ds2_lazymonkey.war
[ 2005-11-12 00:13:13.296 CET ] Done unpacking ds2_lazymonkey.war
[ 2005-11-12 00:13:13.315 CET ] Starting application : ds2_lazymonkey
[ 2005-11-12 00:13:13.316 CET ] Initializing ClassLoader(s)
[ 2005-11-12 00:13:13.317 CET ] Initializing EJB container
[ 2005-11-12 00:13:13.321 CET ] Loading connector(s)
[ 2005-11-12 00:13:13.362 CET ] Starting up resource adapters
[ 2005-11-12 00:13:13.390 CET ] Initializing EJB sessions
[ 2005-11-12 00:13:13.392 CET ] Committing ClassLoader(s)
[ 2005-11-12 00:13:13.394 CET ] Initialize ds2_lazymonkey begins...
[ 2005-11-12 00:13:13.407 CET ] Initialize ds2_lazymonkey ends...
[ 2005-11-12 00:13:13.432 CET ] Started application : ds2_lazymonkey
[ 2005-11-12 00:13:13.455 CET ] Application Deployer for ds2_lazymonkey COMPLETES. 
                                Operation time: 247 msecs

    You are now ready to go, try to access the DVD Store with your Web-Browser at http://your-ip:8888/ds2/

To put load onto the web-application use the provided load driver: 

-bash-3.00$ cd ds2/drivers/
-bash-3.00$ mono ds2webdriver_mono.exe --target=127.0.0.1:8888 \
--virt_dir=/ds2/ --page_type=jsp --run_time=1 --think_time=0 --n_threads=1
target= 192.168.1.80:8888  n_threads= 1  ramp_rate= 10  run_time= 1  db_size_str= S
warmup_time= 1  think_time= 0 pct_newcustomers= 20  n_searches= 3  search_batch_size= 5
n_line_items= 5  virt_dir= /ds2/  page_type= jsp  target_hostname=

Using .NET DateTime for measuring response time

Not generating Windows Performance Monitor Counters
Thread 0: created for User 0
Thread 0: connected to 192.168.1.80:8888
Controller (10/17/2005 12:27:29 AM): all threads running
Controller (10/17/2005 12:27:29 AM): all threads connected - issuing Start

The supplied EAR-file contains default values for the database connection (username: ds2; password: ds2; host: 127.0.0.1; ORACLE_SID: XE). You can change that by editing $OC4J_HOME/j2ee/home/applications/ds2_lazymonkey/META-INF/data-sources.xml. 

...
  <connection-pool name="jdev-connection-pool-ds2" 
                      disable-server-connection-pooling="false"
                      num-cached-statements="100">
    <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource" 
      password="ds2" user="ds2" url="jdbc:oracle:thin:@192.168.1.80:1521:XE"/>
  </connection-pool>
...

OC4J has some nice Web-GUIs you can use to make configuration changes or to get performance data. See the following URLs:

  • http://your-ip:8888/em the Application Server Control GUI (use oc4jadmin as username to login)
  • http://your-ip:8888/dms0/Spy for DMS Metrics like average exection time of JSPs

This implementation is based on the Dell DVD Store Oracle implementation some of the files included are still 1:1 copies from the Dell implementation!

The source code of my implementation is under ds2/ds2_lazymonkey/src with:

  • Oracle JDeveloper workspace file: HeiseDBContest.jws
  • JSPs in hdbc/public_html
  • Source Code for JavaBeans in hdbc/src/mypackage

  • JavaBeans to reduce Java Code inside of the JSPs.
  • JSP code cleanup to remove redundant code blocks and unnecessary for-loops.
  • Made a view basic steps toward user input validation.
  • JDBC Connection/Statement-Pooling along with JDBC PreparedStatement/CallableStatement.
  • Usage of PL/SQL to reduce the amount of calls to the database.

  • user input validation, error messages show up when required fields are missing, or input has wrong type (e.g. String where an numeric value was expected)
  • Dells PL/SQL procedure NEW_CUSTOMER is now used to insert an new user into the database.

  • PL/SQL procedure LOGIN3 is used to check username/password and to retrieve the previous purchases
  • 3 tables are JOINED in one select statement to get that previous purchases for a given customerid
  • a database cursor is used to return that JOIN from PL/SQL to Java
  • Class OracleCachedRowSet is used to decouple the JavaBean ResultSet from the JSP (that makes it easier to ensure that the connection is closed properly after usage)

  • tried to improve handling of arrays (item and selected_item) by changing to int[] instead of String[], also improved the merging of both arrays a little bit
  • new SQL statement to get product titles for a given array of product_ids (using THE and CAST with an JOIN)

  • PL/SQL PURCHASE5 is used to submit an purchase to the database
  • BULK Update and Insert Statements are used for DML operations

The final submission date for that contest WAS the 13. November 2005. But I hope that this contest is repeated next year.

So I am still open for contribution. Send your patches, comments or questions to: [lazy.monkey at fm-berger.de]. But be patient, I do not have that much time to spend on the project!

  • Markus Lidel - helped me to perform an peer-review of my code, found some bugs and did proof-read the "get started" section of that documentation.
  • cd - for some PL/SQL tips (see the heise forum for that contest).
  • Frank Berger - thats me :-)

There are a lot of whitepapers from Dell that use the DVD Store as an example: