	Information provided as a “Tip” might be helpful for various reasons: time savings, risk reduction, etc.
	You should carefully read information marked with “Important”. Ignoring such information is typically not a good idea.
	Warnings should not be ignored (risk of data loss, etc.)

2 Prerequisites

As a first step you must download the following software packages:

Download openCRX for DB2 from http://www.opencrx.org/downloads.htm (e.g. opencrx-1.10.0-core.db2-8.zip). The distribution contains DB2 scripts required to install the openCRX database.
Download DB2 Database Server Express-C from http://www-306.ibm.com/software/data/db2/udb/db2express/getstarted.html
Download DB2 Driver for JDBC and SQLJ from from http://www-306.ibm.com/software/data/db2/udb/db2express/getstarted.html

Please ensure that you install the correct JDBC driver (i.e. matching JDK, DB2 version, etc.) and one JDBC driver only! Ignoring this wisdom leads to problems as the connection to the database will fail.

As a next step you must install IBM DB2 (please refer to the appropriate documentation from IBM for installation details).

This document assumes that you use the DB2 Control Center for database administration.

3 Upgrading from previous versions

If you already have DB2 for openCRX installed, upgrade the database as explained below. You can then skip the rest of this document.

3.1 The SQL Script upgrade-from-...

In a first step you must upgrade your database. openCRX distributions provide an SQL script of the form

upgrade-from-<version from>-to-<version to>.sql

If you have installed openCRX 1.9.1, for example, and you want to upgrade to version 1.10.0 you have to run the script upgrade-from-1.9.1-to-1.10.0.sql on your database instance.

3.2 The SQL Script migrate-from-...

In a second step you must migrate your database. openCRX distributions often times provide an SQL script of the form

migrate-from-<version from>-to-<version to>.sql

If you have installed openCRX 1.9.1, for example, and you want to upgrade to version 1.10.0 you have to run the script upgrade-from-1.9.1-to-1.10.0.sql on your database instance.

3.3 The SQL Script drop-from-...

Next you can drop unused tables from your database. openCRX distributions often times provide an SQL script of the form

drop-from-<version from>-to-<version to>.sql

If you have installed openCRX 1.9.1, for example, and you want to drop tables not used by openCRX 1.10.0 you can run the script drop-from-1.9.1-to-1.10.0.sql on your database instance. Alternatively, you can also rename such tables, e.g. from transition_type to _unused_transition_type. Also, it goes without saying that you should never drop a table before you made a backup!

3.4 The SQL Script dbcreate-views.sql

Most new openCRX versions make use of new/changed views, i.e. if an openCRX distribution includes an SQL script of the form

dbcreate-views.sql

then you must run that script. If you have installed openCRX 1.9.1, for example, and you want to upgrade to openCRX 1.10.0 you should run the script dbcreate-views.sql on your database instance. Make sure that old views are indeed dropped and new views properly created.

3.5 The SQL Script dbcreate-indexes.sql

Most new openCRX versions make use of new/changed indexes, i.e. if an openCRX distribution includes an SQL script of the form

dbcreate-indexes.sql

then you should run that script. If you have installed openCRX 1.9.1, for example, and you want to upgrade to openCRX 1.10.0 you should run the script dbcreate-indexes.sql on your database instance.

3.6 Populate Preferences

The last step involves deleting old preferences and populating the table with new ones. Run the SQL script populate-preferences.sql to do this.

4 Create the database

As a first step you must create the database. This can be done with the DB2 Control Center. Start the Control Center and navigate to the appropriate instance and select Databases. Right-click and select Create Database from the pop-up menu as shown below:

Figure 1: Create a new database

Enter CRX_CRX as database name and select 32K as page size (experienced DB2 DBAs might want to create multiple table spaces for optimization reasons). Click Next:

Figure 2: Create schema CRX_CRX

Specify storage options (if any) and click Next:

Figure 3: Specify Storage options

Specify Region options (we strongly recommend the UTF-8 code set):

If you want/need UTF-8 support you should select code set UTF-8

Figure 4: Specify Region options

Verify the settings and then click Finish:

Figure 5: Verify settings

Next we need to create a new user. Navigate to the newly created database and look for the entry User and Group Objects. Right-click the subentry DB Users and then select Add from the pop-up menu as shown below:

Figure 6: Create New User – step 1

Enter SYSTEM and select the appropriate authorities (the user must be able to connect to the database!):

Figure 7: Create New User – step 2

Please note that passwords are managed by the operation system.

In case you did not select page size 32K when you initially created the database, you may want to create a Buffer Pool BP32K (Page Size of 32K) and 2 Table Spaces CRXTS32 and CRXTMPSPC32 a as follows (default Table Spaces rely on 4K page sizes, insufficient for openCRX):

Listing 1: Creating Buffer Pools and Table Spaces with 32K Page Size

CREATE BUFFERPOOL BP32K IMMEDIATE SIZE 250 AUTOMATIC PAGESIZE 32 K ;

CREATE LARGE TABLESPACE CRXTS32 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE AUTORESIZE YES BUFFERPOOL BP32K;

CREATE SYSTEM TEMPORARY TABLESPACE CRXTMPSPC32 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP32K;

You have completed creating the database CRX_CRX.

5 Install the openCRX Database Schema Objects

After creating the schema you are now ready to install the openCRX database schema objects. The following scripts must be executed:

dbcreate-tables.sql
dbcreate-views.sql
dbcreate-indexes.sql
populate-preferences.sql

Do not execute any other scripts included in the distribution.

Navigate to the newly created database. Right-click on it and then select Query from the pop-up menu to open a command editor as shown below:

Figure 8: Open Command Editor

You may want to create Buffer Pool(s) and Table Space(s) of a larger size (e.g. 32K) than the default size of 8K as some openCRX tables have large column sizes (see Listing 1: Creating Buffer Pools and Table Spaces with 32K Page Size for information on how to do that). Otherwise you will receive error messages running the script dbcreate-tables.sql similar to the one below:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0286N A default table space could not be found with a page size of at least "xxxxx" that authorization ID "xxxxx" is authorized to use. SQLSTATE=42727

Copy/paste the database script dbcreate-tables.sql and execute by clicking on the button Execute:

Figure 9: Execute script dbcreate-tables.sql

Similarly, execute the remaining scripts in the following order:

dbcreate-views.sql

You may want to create Buffer Pool(s) and Table Space(s) of a larger size (e.g. 32K) than the default size of 8K as some openCRX views have large column sizes (see Listing 1: Creating Buffer Pools and Table Spaces with 32K Page Size for information on how to do that). Otherwise you will receive error messages running the script dbcreate-views.sql similar to the one below::

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL1585N A system temporary table space with sufficient page size does not exist. SQLSTATE=54048

dbcreate-indexes.sql (hint: you can ignore error messages of the following sort: Duplicate key name 'xxx' Error 1061).
populate-preferences.sql

The scripts should run without errors and after execution you should be able to inspect all the newly created tables, views, and indexes:

Figure 10: Verify creation of tables, views, and indexes

6 Next Steps

If you have completed successfully the database installation you are ready to use the openCRX database CRX_CRX. The application server installation guides explain how to connect the application server to the openCRX database instance.