DBTool

DBTool Overview

DBTool provides an easy and reliable way to create and manipulate pools. This process is fully automatic and is performed using an XML file.

A pool represents a set of stored connections to a database. A pooled connection is a logical connection, which actually represents a wrapper over a physical one. Therefore, when a user terminates a connection to the database, the connection is returned to the pool and can be reused by another user. In this way, greater efficiency is achieved by avoiding the process of recreating connections to the pool.

The database is identified using a JDBC URL. The JDBC URL consists of three parts: a protocol identifier (always JDBC), a driver identifier (for example, ODBC, IDB, Oracle, and so on), and a database identifier (the format is driver specific). As an example, jdbc:odbc:demo is the JDBC URL for a database named demo accessed using the JDBC-ODBC bridge driver. The pool also has a name used by the clients, a user name (optional), a password, and a maximum connections limit. The pool can handle connection timeouts and can limit the number of connections to a predefined maximum value.

To create the pool, run dbtool script file, located in <SAPj2eeEngine_install_dir>/tools directory. The dbtool.xml file is passed as a parameter to the dbtool script file.

DBTool Example

An example of an XML file used by DBTool is as follows:

<db-tool>

   
   
   <login-info>
          <host> localhost </host>
          <port> 3011 </port>
          <user-name> Administrator </user-name>
          <user-password> </user-password>
   </login-info>

   
   <jdbc-driver>
          <classname> oracle.jdbc.driver.OracleDriver </classname>
          <jar-file> d:/develop/ebs4.03/lib/OracleDriver.zip </jar-file>
   </jdbc-driver>

   
   
   <database-pool>
          <poolname> CustomerExamplePool </poolname>
          <jdbc-driver-classname> oracle.jdbc.driver.OracleDriver </jdbc-driver-classname>
          <jdbc-url> jdbc:oracle:thin:@<host>:1521:GManeff </jdbc-url>
          <db-user> cts1 </db-user>
          <db-password> cts1 </db-password>
   </database-pool>

   
   
   <database-initialization>
          <poolname> CustomerExamplePool </poolname>
          <sql-statement ignore-error="true">
                DROP TABLE CustomerExample
          </sql-statement>
          <sql-statement>
                CREATE TABLE CustomerExample (
                       unique_id VARCHAR (20) NOT NULL,
                       complete_name VARCHAR (50),
                       street VARCHAR (80),
                       zip_code VARCHAR (20),
                       town VARCHAR (50),
                       PRIMARY KEY (unique_id)
                )
          </sql-statement>
          <sql-statement>
                INSERT INTO CustomerExample VALUES ('1', 'John Smith', 'Main Avenue 54', '98222', 'Washington')
          </sql-statement>
          <sql-statement>
                INSERT INTO CustomerExample VALUES ('2', 'Jane Doe', 'First Lane 60', '55444', 'San Francisco')
          </sql-statement>
   </database-initialization>

</db-tool>

Initially, correct login info about the user must be specified. It includes the following tags:

<host> localhost </host> – specifies the host. By default, it is set to localhost . It can specify a machine name or a valid IP address.

<port> 3011 </port> – specifies the port. This tag is optional and if not entered, a port number is specified by default.

<user-name> Administrator </user-name> – describes the user name.

<user-password> </user-password> – specifies the user password.

After specifying the correct login information about the user, the JDBC driver element data must be entered. It includes the following tags:

<classname> oracle.jdbc.driver.OracleDriver </classname> – specifies the classname of the driver, from which the pool can get connections.

<jar-file> d:/develop/ebs4.03/lib/OracleDriver.zip </jar-file> – specifies the location of the driver. The path must be specified according to the operating system.

The database pool element is next. It includes the following tags:

<poolname> CustomerExamplePool </poolname> – specifies the pool’s name.

<jdbc-driver-classname> oracle.jdbc.driver.OracleDriver </jdbc-driver-classname> – describes the JDBC driver classname.

<jdbc-url> jdbc:oracle:thin:@<host>:1521:GManeff </jdbc-url> – specifies as follows: jdbc – the protocol identifier: oracle – the name of the database: @<host> – name of the machine where the connection with the database is established: 1521 – port listening for connections: Gmaneff – name of the service.

<db-user> cts1 </db-user> – specifies user name in the database.

<db-password> cts1 </db-password> – specifies user password in the database.

The following tags can also be included in the database-pool element:

<init-connections> - specifies the number of the connections obtained from the driver, when the pool is created.

<default-connections> - specifies the number of default connections. If, for example, default-connections value is set to 10 and the number of free and used connections exceeds 10 then all free connections are closed till the number reaches 10.

<max-connections> - specifies the maximal number of connections, which can be obtained from the driver.

<isolation-level> - specifies the isolation level as follows:

none - sets a default isolation. This is the isolation level of the connection returned by the driver.

ruc - sets TRANSACTION_READ_UNCOMMITED isolation level.

rc - sets TRANSACTION_READ_COMMITED isolation level.

rr - sets TRANSACTION_REPEATABLE_READ isolation level.

s - sets TRANSACTION_SERIALIZABLE isolation level.

A short example of database initialization is described below:

<database-initialization>
          <poolname> CustomerExamplePool </poolname>
          <sql-statement ignore-error="true">
                DROP TABLE CustomerExample
          </sql-statement>
          <sql-statement>
                CREATE TABLE CustomerExample (
                       unique_id VARCHAR (20) NOT NULL,
                       complete_name VARCHAR (50),
                       street VARCHAR (80),
                       zip_code VARCHAR (20),
                       town VARCHAR (50),
                       PRIMARY KEY (unique_id)
                )
          </sql-statement>
          <sql-statement>
                INSERT INTO CustomerExample VALUES ('1', 'John Smith', 'Main Avenue 54', '98222', 'Washington')
          </sql-statement>
          <sql-statement>
                INSERT INTO CustomerExample VALUES ('2', 'Jane Doe', 'First Lane 60', '55444', 'San Francisco')
          </sql-statement>
   </database-initialization>

This particular example inserts into the database information about an arbitrary customer. Information about every customer includes unique ID, complete name, zip code, street address, and town. The database initialization is accomplished using <sql-statement> tags.