Set up DB2 database


The following information provides configuration guidance to use DB2 databases with B2Bi, including:

About the DB2 database

The B2Bi installer supplies the driver that is required to work with this database. You do not have to supply a database driver. After the B2Bi installation, you can find database drivers at <B2Bi_install_directory>\Interchange\corelib.

Important: You must create the database before B2Bi is started for the first time.

DB2 tuning guidelines

Use these guidelines if you plan to run B2Bi in a cluster of computers.

Connections

Configure the number of permitted DB2 connections based on the values in <B2Bi_install_directory>\Interchange\conf\datastoreconfig.xml and the number of nodes in the cluster.

By default datastoreconfig.xml specifies a minimum of 50 connections per node (partition) and a maximum of 100. The relevant entry in that file looks like this:

<Property key="openjpa.ConnectionProperties" value="DisableConnectionTrack ing=true,ReleaseHelperThreads=0,MinConnectionsPerPartition=50,MaxConnectionsPerP artition=100,ConnectionTimeoutInMs=30000,IdleMaxAgeInMinutes=10,ConnectionTestSt atement="SELECT SYSDATE FROM DUAL"/>

Each machine in the cluster has one control node. If the default datastoreconfig.xml is used, it will take at least n * 100 connections in DB2 to account for the control nodes alone (where n is the number of machines in the cluster). An additional 100 must be added for each trading engine node.

For example, with a cluster of three machines, that have:

  • Two trading engine nodes on machine A
  • Two trading engine nodes on machine B
  • Three trading engine nodes on machine C

The following number of connections are required:

3 * 100 = 300 control node connections

+

(2 + 2 + 3) * 100 = 700 trading engine node connections

This makes a total of 300 + 700 = 1000 connections required in DB2 for this example.

Warning Any database must allow a minimum value of 800 for the “maximum connections” attribute.

Other settings

You may need to adjust other DB2 settings to account for the number of connections. See your DB2 documentation for more information.

Operating system settings

If you specify a large number of connections in DB2, you may need to adjust certain operating system settings. For example, under UNIX you might need to adjust message queue, semaphore, and IPC settings.

See your DB2 and operating system documentation for more information.

DB2 page size requirement

The DB2 parameter MAXAPPLS must be greater than or equal to the value calculated in DB2 tuning guidelines for maximum connections.

If you use DB2, the database must have a minimum page size of 32 kiB.

In addition, set the schema name defined in DB2 in the trading engine datastoreconfig.xml file.

Go to <B2Bi_install_directory>\Interchange\conf and open the file in an editor. Locate the DB2 section (<ConnectionInfo id="DB2">). Add the schema name in the following property:

<Property key="openjpa.jdbc.Schemas" value=""/>

For example, if the schema is named myschema, change the property to:

<Property key="openjpa.jdbc.Schemas" value="myschema"/>

DB2 database setup procedure

To setup the DB2 database:

  1. Use the db2set tool to make local configuration changes on the DB2 server:
  2. db2set DB2_RR_TO_RS=YES
  3. Create the database on the DB2 server.
  4. Make the following DB2 configuration changes:
    Create tablespace cyc_big pagesize 32k managed by system using ('c:\db2data\cyc_big') bufferpool bp32;
    Create temporary tablespace cyc_temp pagesize 32k managed by system using ('c:\db2data\cyc_temp')bufferpool bp32;
    Grant use of tablespace cyc_big to user db2inst1;
  5. Check the database connection settings before you start the servers.
  6. See Check database connections.
  7. Start the servers. The servers connect to the database and generate the required tables.
  8. You can use your database application to confirm that the tables have been added.

DB2 HADR settings

The following settings are required for HADR installations.

Do not define "alternate server"

Do not define an “alternate server” on the DB2 hosts for the B2Bi database. The following DB2 setting is known to cause in-process message loss and should not be used:

db2 update alternate server for database <B2Bi DB> using hostname <hostname> port <port>

The “alternate server” settings should only be defined in the datastoreconfig.xml file. For information about modifying settings in this file, see Check database connections.

Configure connection URL setting

If you are deploying B2Bi with High Availability Disaster Recovery (HADR), you must configure the connection URL setting in the datastoreconfig.xml file, located in the <B2Bi_install_directory>\Interchange\conf directory.

  1. Open datastoreconfig.xml in a text editor.
  2. Locate the line:
  3. Modify the line to:
  4. Save the file.

The following example shows DB2 HADR-related settings in the datastoreconfig.xml file, with modified ConnectionURL attribute:

DB2 custom tablespaces option

About custom tablespaces

Users of DB2 have an option to use custom tablespaces to permit advanced schema management. Under this option, data are organized in different tablespaces rather than the default of a single tablespace. This reduces fragmentation of tablespaces and results in improved performance.

This option is intended for large enterprise systems.

You must configure custom tablespaces using a new database, before you start the trading engine server the first time. DB2 users who want to change to custom tablespaces must manually move tables and indexes.

When custom tablespaces are in place, users can upgrade later to newer versions of B2Bi without additional configuration, provided the same database is used. This is possible because the settings in the B2Bidatastoreconfig.xml file carry forward to the new datastoreconfig.xml file in the upgraded application.

Implementation procedure

  1. Install the trading engine and set up a DB2 database.
  2. Do not start B2Bi.
  3. Edit a sql script to create the custom tablespaces in DB2.
  4. A script, DB2_create_tableSpaces.sql, is provided in <B2Bi_install_directory>\Interchange\tools.
  5. To use this script, first configure it for your environment. You must set correct values for the following two properties:
    • define ORASID=orasid
    • define DATA_FILE_PATH=/oradata/&ORASID/data
  6. In the section of the file for tablespace names, you can use the names as provided. If you change the names, you also must edit the datastoreconfig.xml file to reflect the changed names. You can create more or fewer tablespaces in your own script, but you must change datastoreconfig.xml accordingly. Check each tablespace data file number and size to make sure your database has sufficient capacity.
  7. Save and close the file.
  8. Open for editing the datastoreconfig.xml file in <B2Bi_install_directory>\Interchange\conf.
  9. Change the value of the following element to true:
  10. <TableSpaces enabled="False">
  11. Change the following only if you used your own sql script and not the sample script, or if you changed the tablespace names in the sample script.
    • <TableSpace type="Large_Table" name="CYCLONE_LARGE_TABLES"/>
    • <TableSpace type="Medium_Table" name="CYCLONE_MEDIUM_TABLES"/>
    • <TableSpace type="Small_Table" name="CYCLONE_SMALL_TABLES"/>
    • <TableSpace type="Large_Index" name="CYCLONE_LARGE_INDEXES"/>
    • <TableSpace type="Medium_Index" name="CYCLONE_MEDIUM_INDEXES"/>
    • <TableSpace type="Small_Index" name="CYCLONE_SMALL_INDEXES"/>
  12. Each TableSpace entry must correspond to the tablespace name in the sql script. To group two together (for instance, Medium_Index and Small_Index) into the same tablespace, set the name of the two entries to the same value.
  1. Save and close the file.
  2. Run the SQL script in DB2.
  3. Start the server. This creates the application’s database schema according to the settings in the datastoreconfig.xml file.

Related Links