Getting Started
Install Databases
Install anyone of the following databases as per your requirement:
• Oracle: Download the supported version of Oracle database from the following site and unzip it to the required location:
http://www.oracle.com/technetwork/indexes/downloads/index.html?ssSo urceSiteId=ocomen#menu-downloads
Install the Oracle server software as directed in the Oracle installation document.
• SQL Server: Download the latest version Microsoft SQL Server from the following site and unzip it to the required location:
http://msdn.microsoft.com/en-us/sqlserver/bb671149.aspx
Install the SQL Server software as directed in the appropriate SQL installation document.
• PostgreSQL: Download the latest version of PostgreSQL 9.1.x from the following site and unzip it to the required location:
http://www.postgresql.org/download/windows/
Using Database Setup Wizard
|
93Using Database Setup Wizard
To make the database setup process easier and more user-friendly, Database Setup Wizard allows you to install and configure the TIBCO MDM database. This is currently available for Oracle and SQL Server. The Database Setup Wizard is run through the Configurator.
• For details on using Database Setup Wizard for Oracle, see , Setting Up Oracle Database, on page 94.
• For details on using Database Setup Wizard for SQL Server, see Setting Up SQL Server Database, page 102.
Prerequisites
Before running the TIBCO MDM Database Wizard, ensure the following:
• Database has been installed.
• Database client installed on the local computer.
• TIBCO MDM user schema is either not created or if created, the credentials to create the schema is available.
• TIBCO MDM is installed and the environment variables created.
• Database SQL scripts are available
— For Oracle: in $MQ_HOME\db\Oracle.
— For SQL Server: in $MQ_HOME\db\sqlserver.
• For Oracle, tablespaces are not created.
94
|
Chapter 4 Setting Up a DatabaseAccessing Database Setup Wizard
To access Database Setup Wizard, start the Configurator and click the Tools menu. The following options are displayed:
• Setup Database (Oracle): Click to setup Oracle database.
• Setup Database (SQL Server): Click to setup SQL Server database.
Depending on the database selected from the Select Deployment Target drop-down the respective database is enabled in the Tools drop-down. For example, in the above screen Oracle database is selected and it is enabled in the Tools drop-down.
Setting Up Oracle Database
The Setup Database (Oracle) option includes the following dialogs that guides you to set up the Oracle database.
• Step 1 - Select Database User Options, page 95
• Step 2 - Specify Database Details, page 95
• Step 3 - Create User for Oracle Database, page 96
• Step 4 - Select Storage Profile Details, page 97
• Step 5 - Setup Custom Profile, page 98
• Step 6 - Confirm Storage Parameters, page 99
• Step 7 - Verify TIBCO MDM Seed Data Summary, page 100
Using Database Setup Wizard
|
95Step 1 - Select Database User Options
This is the first dialog in the Database Setup Wizard. Select if you want to use an existing TIBCO MDM database user or create a new user. If you are not a DBA users, you can create tablespace and users using the scripts. The scripts for creating tablespace and creating users are available in $MQ_HOME/DB/<database name>/configure/.
Click Next.
Step 2 - Specify Database Details
You will see this screen if you opted to use an existing TIBCO MDM database user in Step 1. Provide the following information:
• Oracle Client Path: The installed database location (ORACLE_HOME by default).
• Database Name: The schema or database name (TNS name) to be used.
96
|
Chapter 4 Setting Up a DatabaseStep 3 - Create User for Oracle Database
You will see this screen if you opted to create a new TIBCO MDM database user.
Provide the following information:
• Oracle Client Path: The installed database location (ORACLE_HOME by default)
• Database Name: The schema or database name to be used.
• DBA User Name: The system DBA user name (used to connect to the database)
• DBA User Password: The system DBA password (used to connect to the database)
• New TIBCO MDM Database User Name: The TIBCO MDM User Name to create.
• New TIBCO MDM Database User Password: The TIBCO MDM password to create. Ensure that you remember the user name and password.
• Confirm TIBCO MDM Database User Password: Confirm the TIBCO MDM Database User Password.
Click Next.
Using Database Setup Wizard
|
97Step 4 - Select Storage Profile Details
Select the type of storage profile to create, whether Typical or Custom.
A Typical Profile installs tablespaces (TIBCO MDM uses 5 tablespaces) with default tablespace name. However, you can specific the client database location.
Select this option if you want to use the default tablespace values.
In a Custom Profile, you can specify the values and size, and location for the default tablespaces. Select this option if you want to specify customized values for the default tablespace.
Click Next.
98
|
Chapter 4 Setting Up a DatabaseStep 5 - Setup Custom Profile
You will see this dialog if you opted to create a Custom Profile in the previous step.
Here, the table space names are the default Tables spaces; everything else is customizable - you can modify the table space description, specify the sizes and locations.
Click Next.
Using Database Setup Wizard
|
99Step 6 - Confirm Storage Parameters
This dialog is displayed if you opted to create a custom profile - it displays the values (tablespace descriptions, sizes, and locations) you provided for
confirmation.
Click Install to install the seed data.
100
|
Chapter 4 Setting Up a DatabaseStep 7 - Verify TIBCO MDM Seed Data Summary
This dialog displays the results of the Seed data and tablespace creation and indicates if it was successful.
Errors if any are displayed, and the location of the log file is also displayed - you can click the Open button to view the Log.
By default, the log file is stored in
MQ_HOME\db\oracle\Install\logs\DbSetup_Outputs.log.
Using Database Setup Wizard
|
101102
|
Chapter 4 Setting Up a DatabaseSetting Up SQL Server Database
The Setup Database (SQL Server) option includes the following dialogs that guides you to set up the SQL Server database.
• Step 1 - Select Database User Options, page 102
• Step 2 - Specify Existing User Login Details, page 103
• Step 3 - Create User for SQL Server Database, page 104
• Step 4 - Specify Storage Profile Details, page 105
• Step 5 - Set up Custom Profile, page 106
• Step 6 - Confirm Storage Parameters, page 107
• Step 7 - Verify Installation Summary, page 108
Step 1 - Select Database User Options
This is the first dialog in the Database Setup Wizard. Select if you want to use an existing TIBCO MDM database user or create a new user.
Click Next.
Using Database Setup Wizard
|
103Step 2 - Specify Existing User Login Details
If you have opted to use an existing TIBCO MDM database user in Step 1, provide the following information:
• Server Name: Specify an IP address of a computer where SQL Server is installed.
• User Name: The existing database user name.
• Password: The existing database password.
• Test Connection (login credentials): Click to test the login credentials.
• DBA User Name: The system DBA user name (used to connect to the database)
• DBA Password: The system DBA password (used to connect to the database)
• Test Connection (DBA credentials): Click to test the DBA credentials.
104
|
Chapter 4 Setting Up a DatabaseStep 3 - Create User for SQL Server Database
If you have opted to create a new TIBCO MDM database user, provide the following information:
• Server Name: Specify an IP address of a computer where SQL Server is installed.
• DBA User Name: The system DBA user name (used to connect to the database)
• DBA User Password: The system DBA password (used to connect to the database)
• New MDM Login User Name: The TIBCO MDM user name to create.
• New MDM Login User Password: The TIBCO MDM password to create.
• Confirm MDM Login User Password: Confirm the TIBCO MDM password.
• SQL Server Home: The installed database location.
Click Next.
For a new user, if the provided details are correct; the Login User Created Successfully message is displayed.
Using Database Setup Wizard
|
105Step 4 - Specify Storage Profile Details
Select the type of storage profile to create, whether Typical or Custom.
— Typical Profile: Installs default database name, log storage and primary data files.
— Custom Profile: Allows you to change the default values.
Click Next.
106
|
Chapter 4 Setting Up a DatabaseStep 5 - Set up Custom Profile
If you have opted to create a Custom Profile in the previous step, the Custom Profile Details dialog box is displayed.
You can change the database name. Also, you can modify the default value, size, and location of the log storage and primary data files.
Click Next.
Using Database Setup Wizard
|
107Step 6 - Confirm Storage Parameters
On the Confirm Storage Parameters dialog, verify and confirm the values of database name, and size and location of primary and log storage files.
Click Finish.
108
|
Chapter 4 Setting Up a DatabaseStep 7 - Verify Installation Summary
This dialog displays the results of the seed data and database creation and indicates if it was successful.
In case of any errors, click Open to view the Log. By default, the log file is stored in MQ_HOME\db\sqlserver\install\logs\doAll.log.
Click Close to complete the SQL Server database setup process.
Configuring Oracle Database
|
109Configuring Oracle Database
Setting up the TIBCO MDM database consists several steps, most of which are done manually such as creating users, schemas, and tablespaces; manual database setup, changes to SQL scripts to change location or default tablespace sizes, and so on.
Before starting the TIBCO MDM installation or upgrade, a database machine (or cluster) must exist, a database server must be created, and you must have a user account with full privileges for the database.
This section describes specific configurations necessary for Oracle database to run in compliance with TIBCO MDM.
• Configuration Requirements and Recommendations, page 109
• Configuring TIBCO MDM with Oracle RAC 11g, page 112
• Configuring Oracle Client, page 112
• Importing TIBCO MDM Seed Data, page 115
• Performance Tuning, page 118
• Handling Multibyte Characters, page 118
• Troubleshooting, page 120
Configuration Requirements and Recommendations
To configure the Oracle database, use the Oracle Configuration Assistant. Consult your Database Administrator on standard practices followed by your IT
department to change the recommended structure according to your needs.
Database Sizing Requirements
The following table lists the minimal sizing requirements for init<dbname>.ora
110
|
Chapter 4 Setting Up a Database* These values depend on various factors including concurrent users, message and workflow volumes, number of records, and so on. Your DBA should adjust these values based on the actual load and required performance characteristics.
Parallel max servers 2 4 5*
Log_buffers 25% of the
system memory
Timed_statistics True True True
Max_dump_file_size 5 MB 10 MB 20 MB
Rollback_segments 8 seg 16 seg 32 seg*
Open cursors 300 450 3000
Table 11 Database Sizing Requirements
Database Parameter Setting Low-end Mid-range High-end
Configuring Oracle Database
|
111Specify Database Details
The following database details are required while installing TIBCO MDM:
• TNS name (A valid and tested connect string should be present in the
tnsnames.ora)
• Database name
• Database port
• Database server host name
• Database username and password
Configuration
Set Environment Variables
— ORACLE_HOME
for example, on UNIX:
$export ORACLE_HOME=/u01/app/oracle/product/<ver>
— LD_LIBRARY_PATH
for example, $ORACLE_HOME/lib
— NLS_LANG
for example, AMERICAN_AMERICA.UTF8
Recommended configuration for TIBCO MDM
— Two sets of Redo logs with two members each.
— Analyze regularly (for instance, weekly) or after significant change in data (for example, after importing records in the database).
— Database to be run in ARCHIVE LOG MODE.
— Mirrored control files.
112
|
Chapter 4 Setting Up a DatabaseConfiguring TIBCO MDM with Oracle RAC 11g
Prerequisites
• Ensure that Oracle client is installed.
• Use the latest driver provided by Oracle.
Example
Connection URL:
jdbc:oracle:oci:@ORACLERAC
Where ORACLERAC is the TNS entry in the client’s TNSNAMES.ora file.
OCI drivers are used to support TAF.
TNSNAMES.ora file (client)
ORACLERAC = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname1.domainname.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname2.domainname.com)(PORT = 1521))
Oracle Client Software Developer Edition or Enterprise Edition must be installed on the machine hosting the application server (). Ensure that the sqlldr utility is available.
This TNS entry supports both failover and load balancing.
Configuring Oracle Database
|
113Tablespaces
Tablespaces are required to hold data and indexes for all tables required for TIBCO MDM and for all data sources uploaded.
TIBCO MDM uses the following different kinds of tables and indexes:
• Fixed tables
• Indexes for fixed tables
• Data source tables
• Master catalog tables
• Indexes for master catalog tables Based on this, you can choose:
— One tablespace for data and indexes.
— Two tablespaces, one for data and one for indexes
— Separate tablespaces for each set of tables.
— A combination of the above.
Creating Tablespaces and Users for New Installations
The following is the recommended procedure to create tablespaces for a new TIBCO MDM database installation. In this approach, the Database Administrator is responsible for the creation of tablespaces. TIBCO MDM does not change or create tablespaces.
1. Modify createtablespace.sql
Go to $MQ_HOME/DB/oracle/configure/createtablepsace.sql to change the datafile locations based on your Oracle installation. For example, if you have installed Oracle at E:\app\oradata\orcl\ location, change
C:/oracle/<ver>/oradata/orcl path to E:\app\oradata\orcl\ for all tablespaces.
114
|
Chapter 4 Setting Up a DatabaseUse the createtablepsace.sql script to create the following tablespaces: (It is recommended you create all five.)
a. VELODBDATA1 (tablespace for fixed TIBCO MDM tables) b. VELODBINDX1 (tablespace for fixed TIBCO MDM table indexes) c. VELODBDF (tablespace for TIBCO MDM data source tables) d. VELODBDATA (tablespace for TIBCO MDM master catalog tables) e. VELODBINDX (tablespace for TIBCO MDM master catalog table indexes)
2. Complete Installation (tablespaces and seed data) For complete installation (tablespaces and seed data), run
$MQ_HOME/DB/Oracle/Configure/doall.bat or sh
— Creating only tablespaces (without seed data) To create tablespaces without seed data, run
$MQ_HOME/DB/Oracle/Configure/createusertablespace.sh or bat
— Creating only seed data
To create seed data, see Import Seed Data - Five Tablespaces Option on page 116.
Creating Tablespaces - Alternative Approach
1. Create two different tablespaces, VELODBDATA and VELODBINDX.
2. Create mdmadm user identified by password default tablespace VELODBDATA
temporary tablespace temp.
3. Grant connect, resource to mdmadm.
4. Alter user mdmadm quota 1000m on VELODBINDX.
The VELODBDATA1 tablespace has a minimum size of 100 MB and maximum size of 500 MB. The remaining tablespaces are 100 MB. You can change the size of the tablespaces based on you requirement. You can also set the size as unlimited for the tablespaces.
The Create Tablespace permission is needed only if you want TIBCO MDM to create tablespaces dynamically. Refer to the following section.
In this approach, the TIBCO MDM creates and manages tablespaces. A separate tablespace is created for each enterprise. This tablespace is used for data source uploads. This approach is not recommended.
Configuring Oracle Database
|
1155. Grant create tablespace privilege to mdmadm. (Alternatively, grant
UNLIMITED TABLESPACE privilege to mdmadm.) 6. Set the following properties using the Configurator
(Advanced view, Database, Oracle).
— Set Database > Tablespace Create Privilege to True.
— Set Database > Table Space Name to VELODBDF. The default value is USERS.
— Set Database > Master Catalog/Repository Data Table Space to
VELODBDATA. The default value is USERS.
— Set Database > Master Catalog/Repository Index Table Space to
VELODBINDX. The default value is USERS.
7. Set the following properties using the Configurator if TIBCO MDM is responsible for creating a tablespace for each enterprise on the first data load.
— Database > Table Space File Size
— Database > Table Space Next Size
— Database > Table Space Initial Storage Size
— Database > Table Space Increase %
— Database > Dynamic Table Space Location
— Repository > Table Space Name Prefix
After this is complete, you can create the seed data. See Import Seed Data - One, Two, or Three Tablespaces Option, page 116.
If True is chosen, it will not work with CSV data upload, as CSV data upload requires this option to be set as False.
With this approach, you need to create tablespace privileges for the TIBCO MDM database user.
116
|
Chapter 4 Setting Up a DatabaseYou can also opt to create seed data manually, especially if you want to create the seed data independent of the database installation.
For a new database installation, you must create a database user ID and use the database installation scripts to create all database objects.
Import Seed Data - Five Tablespaces Option
If you opted to create five tablespaces with the TIBCO recommended names, follow these steps to import the seed data.
(If you opted to create five tablespaces with your own names, you need to modify the scripts as mentioned in Import Seed Data - One, Two, or Three Tablespaces Option on page 116).
1. Modify $MQ_HOME/db/oracle/install/install.bat/.sh to change the
USER, PASSWORD,mdmInstanceName,mdmInstanceDesc,and INSTANCE variables. The USER and PASSWORD variables should be set to the user that you created.
2. From the install directory, run the script install.bat/.sh. This script will create the sequences, tables, indexes, triggers, and views in the appropriate tablespaces and will also insert seed data into the tables.
Import Seed Data - One, Two, or Three Tablespaces Option
If you opted to create one, two, or three tablespaces, you need to modify the sqlscripts to create data structures in the appropriate tablespaces. The following scripts in the $MQ_HOME/db/oracle/install/scripts/ddl directory need to be modified based on the tablespaces created.
1. create_tabs.sql
All fixed tables used in TIBCO MDM are created by this script. These tables are created by default in the VELODBDATA1 tablespace. The name of the tablespace needs to be modified appropriately in this script. If the default Before importing seed data, ensure that the environment variable NLS_LANG is set to AMERICAN_AMERICA.UTF8
On UNIX:
export NLS_LANG=AMERICAN_AMERICA.UTF8
On Windows:
set NLS_LANG=AMERICAN_AMERICA.UTF8
For non-Windows environment, ensure that the PATH variable includes the Bourne shell or a compatible shell before running scripts to create seed data.
Configuring Oracle Database
|
117storage parameters are not good enough for some tables, contact your Database Administrator to modify this script to create tables with the appropriate storage parameters.
2. create_PK.sql
The primary keys for all the fixed tables are created by this script. These primary keys are created by default in the VELODBINDX1 tablespace. The tablespace name needs to be modified appropriately in this script.
3. create_indexes.sql
The indexes for all the fixed tables are created by this script. These indexes are created by default in the VELODBINDX1 tablespace. The tablespace name needs to be modified appropriately in this script.
4. create_ds_tables.sql
All data source tables used in TIBCO MDM are created by this script. These tables are created by default in the VELODBDF tablespace. The name of the tablespace needs to be modified appropriately in this script.
5. After modifying the SQL scripts, you need to perform the two steps required for the five tablespace option. The output of these scripts goes into the log file
$MQ_HOME/db/oracle/install/logs/output.log. Ensure that there are no errors during the execution of these scripts.
Deleting User and Tablespaces
Follow these steps to remove the user and tablespaces that were created with the installation.
1. Modify the $MQ_HOME/db/oracle/configure/dropuser.sql script to change the username to the user you created. By default, this script drops user
1. Modify the $MQ_HOME/db/oracle/configure/dropuser.sql script to change the username to the user you created. By default, this script drops user