Database setup

Database scripts

The database setup involves scripts that set up the schema and initial contents of your database. In the installation media, the scripts are in the install_media\resources\database directory.

You can run these scripts automatically during the installation. However, if corporate policies or other guidelines require you to create the schema as part of the database setup before the installation, run each of these scripts in the following order by using the administrative tools of your database:

1. If necessary, database drop scripts that remove old tables from the database that you are using for B2B Advanced Communications. If you are working with a new database or if you want to keep the old tables, you do not have to run the drop scripts.
2. Data Definition Language (DDL) scripts that are associated with the JNDI data source configurations for the databases that are used by each part of B2B Advanced Communications. The DDL scripts create the tables for these databases. Each of the DDL scripts is associated with a single data source.

   The following data source configurations are used:

   • IdentityServerRepositoryDataSource
   • MEGCommsDataSource
   • MEGInfrastructureDataSource
   • MEGUI

   You must allocate one or more database instances for the data sources. Then, you configure the connection details for each data source when you install B2B Advanced Communications.

   Multiple DDL scripts might be associated with a single data source. The DDL scripts are specific for a database type. A DDL script exists for each type of database that is supported for B2B Advanced Communications.

   All of the member types are configured with the same set of data sources, regardless of what data sources are used by a particular member type.

   Important: If a DDL script fails during the installation of B2B Advanced Communications, you might need to increase the page size of the database. For the recommended page sizes, see Database page sizes.
3. SQL insertion scripts that insert initial permissions data into DB2®, Oracle, and, Microsoft SQL Server database tables.

Database page sizes

IBM requires the following minimum page sizes for each type of database:

Database connection pools

The number of database connection pools is specified in the data source properties file (SystemConfigurationDSLoader.properties) in the install_dir\Members\resources directory. The number of connection pools is assigned for each of the four data sources that accesses the database (IdentityServerRepositoryDataSource, MegCommsDataSource, MEGInfrastructureDataSource, MEGUI).

The maximum number of pools for the MEGUI data source must always be at least 10 higher than the number of visibility consumers per node. You can view the number of visibility consumers per node in the WebSphere® MQ queue manager. The maximum number of pools for the MEGUI data source is set in the ds.3.maxPoolSize property of the SystemConfigurationDSLoader.properties file.

For example, if there are 15 visibility consumers, then the ds.3.maxPoolSize property must be set to at least 25 (15+10=25).

Oracle database block size

The block size (db_block_size) for an Oracle database depends on the value of the NLS_SEMANTICS_LENGTH parameter that you use to define table columns.

The value of NLS_SEMANTICS_LENGTH depends on the data that you are expecting. For example, if NLS_SEMANTICS_LENGTH='BYTE' and you are expecting multibyte character data where 2 bytes equals 1 character, you can store only 50 characters in a 100-byte space.

• If NLS_SEMANTICS_LENGTH is set to the 'BYTE', the block size must be 8 KB (db_block_size=8192).
• If NLS_SEMANTICS_LENGTH is set to the 'CHAR', the block size must be 16 KB (db_block_size=16384).

Oracle service IDs and service names

When you specify the connection information for an Oracle database during an installation of B2B Advanced Communications, you can specify one of the following database identifiers on the Data source configuration page of the Installation Manager:

• The database name or the database service ID (SID) in the Database name field.
• The database service name in the Service name field.

An SID is tied to a specific database instance. A service name can be shared between Oracle instances. For example, in an Oracle Real Application Clusters (RAC) installation, each Oracle cluster node has a unique SID, but shares one or more service names that might reach any or all of the RAC nodes.

The use of the service name simplifies the installation and operations of the database. The service name is then used as the identifier for all of the Oracle processes, including database and schema validation tests during the installation.

A service name also has these benefits:

• You can use the service name to update all instances of a database in a database cluster.
• You can use service names to identify different uses of the same database.

For more information, see the documentation on database SIDs and service names for your version of the Oracle database.

Row Versioning-Based Isolation Level (READ_COMMITTED_SNAPSHOT) for Microsoft SQL Server

• Resolve concurrency issues such as excessive blocking
• Reduce deadlocks

• ALTER DATABASE <DB NAME> SET ALLOW_SNAPSHOT_ISOLATION ON;

• ALTER DATABASE <DB NAME> SET READ_COMMITTED_SNAPSHOT ON;

This snapshot option increases the number of inputs and outputs as well as the size of the tempdb. It is important to have tempdb on fast disks, as well as to have it sized according to your workload.

For more information about using and understanding snapshot isolation and row versioning, see the Microsoft Developer Network web site.