Opencrx Installation Guide For Ibm Db2 V9.5 (Express C) : Version 2.0 / 2.1

openCRX Installation Guide for IBM DB2 v9.
5
(EXPRESS C)
Version 2.0 / 2.1
www.opencrx.org
License
The contents of this file are subject to a BSD license (the "License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at http://www.opencrx.org/license.htm
Copyright 2008 © CRIXP Corp. All rights reserved.

openCRX Installation Guide for IBM DB2 v9.5 (EXPRESS C) - Version 2.0
Table of Contents
1 About this Book..............................................................................3
1.1 Who this book is for............................................................. 3
1.2 What do you need to understand this book............................. 3
1.3 Tips, Warnings, etc.............................................................. 3
2 Prerequisites..................................................................................4
3 Upgrading from previous versions..................................................5
3.1 The SQL Script upgrade-from-.............................................. 5
3.2 The SQL Script migrate-from-............................................... 5
3.3 The SQL Script drop-from-................................................... 5
3.4 The SQL Script dbcreate-views.sql......................................... 6
3.5 The SQL Script dbcreate-indexes.sql...................................... 6
3.6 Populate Preferences........................................................... 6
4 Create the database....................................................................... 7
5 Install the openCRX Database Schema Objects............................ 12
6 Next Steps....................................................................................15
List of Figures
Figure 1: Create a new database.............................................................. 7
Figure 2: Create schema CRX_CRX........................................................... 8
Figure 3: Specify Storage options............................................................. 8
Figure 4: Specify Region options............................................................... 9
Figure 5: Verify settings........................................................................ 10
Figure 6: Create New User – step 1......................................................... 10
Figure 7: Create New User – step 2......................................................... 11
Figure 8: Open Command Editor............................................................. 12
Figure 9: Execute script dbcreate-tables.sql.............................................. 13
Figure 10: Verify creation of tables, views, and indexes.............................. 14
List of Listings
Listing 1: Creating Buffer Pools and Table Spaces with 32K Page Size..........11
—2—
About this Book
1 About this Book

This book describes how to setup an openCRX database instance for IBM DB2.
1.1 Who this book is for

The intended audience are openCRX database administrators.
1.2 What do you need to understand this book

This book describes the installation of openCRX for IBM DB2. The book
assumes that you are familiar with DB2 installation and configuration.
1.3 Tips, Warnings, etc.

We make use the following pictograms:
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.)
—3—
Prerequisites
2 Prerequisites
As a first step you must download the following software packages:
● Download and install openCRX SDK Installer. It is available from
http://www.opencrx.org/sdk.htm
The SDK contains DB2 scripts required to install the openCRX database
in the directory <SDK_Install_Dir>\opencrx-x.x.x\core\src\sql
● Download DB2 Express-C data server from http://www-
306.ibm.com/software/data/db2/express/download.html
● Download DB2 Data Server Driver for JDBC and SQLJ from
http://www-306.ibm.com/software/data/db2/express/download.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.
—4—
Upgrading from previous versions
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.
Backup your database BEFORE you run any of the following scripts!
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.11.0, for example, and you want to upgrade
to version 2.0.0 you have to run the script upgrade-from-1.11.0-to-2.0.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.11.0, for example, and you want to upgrade
to version 2.0.0 you have to run the script upgrade-from-1.11.0-to-2.0.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.11.0, for example, and you want to drop
tables not used by openCRX 2.0.0 you can run the script drop-from-1.11.0-to-
2.0.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!
—5—
Upgrading from previous versions
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.11.0, for
example, and you want to upgrade to openCRX 2.0.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.11.0, for
example, and you want to upgrade to openCRX 2.00.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.
—6—
Create the database
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
—7—
Create the 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
—8—
Create the database
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
—9—
Create the database
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
— 10 —
Create the database
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.
— 11 —
Install the openCRX Database Schema Objects
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 in the order given
below:
● 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
— 12 —
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
— 13 —
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
— 14 —
Next Steps
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.
— 15 —

Opencrx Installation Guide For Ibm Db2 V9.5 (Express C) : Version 2.0 / 2.1

Uploaded by

Copyright:

Available Formats

Opencrx Installation Guide For Ibm Db2 V9.5 (Express C) : Version 2.0 / 2.1

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Opencrx Installation Guide For Ibm Db2 V9.5 (Express C) : Version 2.0 / 2.1

Uploaded by

Copyright:

Available Formats

openCRX Installation Guide for IBM DB2 v9.

Version 2.0 / 2.1

Copyright 2008 © CRIXP Corp. All rights reserved.

1 About this Book

1.1 Who this book is for

1.2 What do you need to understand this book

1.3 Tips, Warnings, etc.

Information provided as a “Tip” might be helpful for various reasons:

You should carefully read information marked with “Important”.

Warnings should not be ignored (risk of data loss, etc.)

3 Upgrading from previous versions

3.1 The SQL Script upgrade-from-...

3.2 The SQL Script migrate-from-...

3.3 The SQL Script drop-from-...

3.4 The SQL Script dbcreate-views.sql

3.5 The SQL Script dbcreate-indexes.sql

3.6 Populate Preferences

4 Create the database

Figure 1: Create a new database

Figure 2: Create schema CRX_CRX

Specify storage options (if any) and click Next:

Figure 3: Specify Storage options

Figure 4: Specify Region options

Verify the settings and then click Finish:

Figure 5: Verify settings

Figure 6: Create New User – step 1

Figure 7: Create New User – step 2

You have completed creating the database CRX_CRX.

5 Install the openCRX Database Schema Objects

Do not execute any other scripts included in the distribution.

Figure 8: Open Command Editor

You may want to create Buffer Pool(s) and Table Space(s) of a

Copy/paste the database script dbcreate-tables.sql and execute by clicking

Figure 9: Execute script dbcreate-tables.sql

Similarly, execute the remaining scripts in the following order:

You may want to create Buffer Pool(s) and Table Space(s) of

● dbcreate-indexes.sql (hint: you can ignore error messages of the

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

You might also like