5.3 IBM DB2 Database

Starting with IBM TRIRIGA Application Platform 3.5.1, the use of a DB2 back-end database is supported using DB2 10.5. The DB2 configuration is done automatically if you use the TRIRIGA-provided scripts to configure your instance and database. For more information, see the Installation and Implementation Guide. Specifically, the following settings are recommended.

Use the following DB2 registry settings:

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
db2set DB2_ATS_ENABLE=YES
db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON

Use the following DB2 database manager settings:

db2 update dbm cfg using RQRIOBLK 65535

The database must be created with PAGESIZE=32K.

The following DB2 database configuration settings are required:

db2 update db cfg for <dbname> using STMT_CONC OFF
db2 update db cfg for <dbname> using LOCKTIMEOUT 30

The following DB2 database configuration settings are recommended:

db2 update db cfg for <dbname> using LOGPRIMARY 23
db2 update db cfg for <dbname> using LOGFILSIZ 32768
db2 update db cfg for <dbname> using LOGSECOND 12
db2 update db cfg for <dbname> using LOGBUFSZ 8192
db2 update db cfg for <dbname> using CATALOGCACHE_SZ 2048

The database must be configured with the UTF-8 code set and CODEUNITS32 string unit if you need to support multibyte characters.

db2 update db cfg for <dbname> using string_units CODEUNITS32

In general, TRIRIGA does not recommend turning on automatic maintenance settings, such as AUTO_MAINT, AUTO_TBL_MAINT, AUTO_RUNSTATS, and AUTO_STMT_STATS. Instead, schedule database maintenance activities during regular maintenance windows to avoid adversely affecting end-user performance.

To help the query optimizer select an efficient access plan, you must specify the REOPT(ONCE) bind option when you run queries. When the REOPT(ONCE) bind option is used, the query optimizer selects the access plan the first time that the query is run. Each subsequent time that the query is run, the access plan is reused. To specify the REOPT(ONCE) bind option, run the following command:

db2 bind '<db2home>/bnd/db2clipk.bnd' collection NULLIDR1

The user ID created for TRIRIGA to access the database must have DBADM, SECADM, ACCESSCTRL, and DATAACCESS privileges for the database. For assistance in performing DB2 administration tasks, see the DB2 product documentation.

a. DB2 Automatic Buffer Pool Size and Auto Extends

To avoid a performance issue on DB2, ensure that the database is set to use an AUTOMATIC buffer pool instead of a static size.

To manually change the database settings, perform the following steps:

• Step 1. In IBM Data Studio, log in as the TRIRIGA user to the database or instance used by TRIRIGA.
• Step 2. Run the following SQL:

  > select AUTOMATIC FROM TABLE(MON_GET_BUFFERPOOL('',-2)) where upper(bp_name) = 'TRIRIGABUFFERPOOL'

  • a. If the return value is 0 (zero), then continue to Step 3.
  • b. If the return value is 1 (one), then the buffer pool is already set to AUTOMATIC, and you are done.
• Step 3. Stop the application servers pointing to the DB2 database.
• Step 4. Run the following SQL in IBM Data Studio:

  > alter bufferpool TRIRIGABUFFERPOOL immediate size AUTOMATIC

• Step 5. Run the original SQL select to make sure the return value is now 1 (one).
• Step 6. Start and stop the DB2 instance used by TRIRIGA:
  • a. For Windows, from the DB2 Command Window - Administrator, logged in as the DB2 administrator user (typically, this is db2admin) where XXXXX is the name of the instance used by TRIRIGA:

    > set db2instance=XXXXX
    > db2stop force
    > db2start

  • b. For Linux, from a shell window, logged in as the TRIRIGA instance user:

    > db2stop force
    > db2start

• Step 7. Restart the application servers pointing to the DB2 database.

b. DB2 Diagnostic Log

Monitor the DB2 diagnostic log for errors and watch its size to ensure that it doesn’t grow large enough to cause issues with storage space. For details on configuring DB2 to use rotating DB2 diagnostic log files, see DB2 diagnostic (db2diag) log files.

The following performance tuning indexes can be added to DB2 databases that are running TRIRIGA Platform 3.5.x. These recommended DB2 performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the DB2 database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. DB2 database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

• Platform Indexes

Any implementation of Reserve should be tuned to include appropriate indexes for performance improvement. The following indexes were identified to help increase performance dramatically for reserve queries by the TRIRIGA performance team on DB2, but you should review and tune for your specific implementation.

The following performance tuning indexes can be added to DB2 databases that are running TRIRIGA Platform 3.5.x. These recommended DB2 performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the DB2 database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. DB2 database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

• Reserve Indexes

Any implementation of Lease Management and Lease Accounting should be tuned to include appropriate indexes for performance improvement. The following indexes were identified to help increase performance dramatically for lease queries by the TRIRIGA performance team on TRIRIGA, but you should review and tune for your specific implementation.

The following performance tuning indexes can be added to DB2 databases that are running TRIRIGA Platform 3.6.x. These recommended DB2 performance tuning indexes are the result of iterative performance tuning cycles and collaboration with the TRIRIGA development team. The indexes listed here are not included in the TRIRIGA base product unless otherwise stated.

These indexes provide significant performance improvements when measured against a broad performance test workload. While TRIRIGA recommends adding these indexes to the DB2 database platform, performance gains might vary depending on an array of factors including application usage, load patterns, hardware sizing, application, database server configuration, and so on. DB2 database administrators should monitor databases for efficient index usage to determine the overall impact produced by applying the recommended indexes, and to determine additional indexes that will improve performance based on situational and data composition needs.

• Lease Indexes

a. Performance Benchmark Index Objectives

Lease Management and Lease Accounting performance testing occurred using a database that was loaded with approximately 17,000 lease abstracts and 24,000 leases with other large amounts of portfolio data to represent a large customer deployment of the lease application. The performance test indexes were applied to the data-load and benchmark-test environments to improve overall throughput and response times as a result of this project.

b. Performance Environments

The performance environments consist of 3 basic hardware components based on the 3.7 Advanced System Configuration example. The performance benchmark test was conducted by using a specific configuration of this environment.

• Application servers: The application servers are where the majority of the business logic is performed. Application server processes are CPU-intensive and require sufficient RAM (memory). The application tier consists of Java Server Pages (JSP) and Java classes. The J2EE application servers provide a JSP container, a database connection pool, and transaction management services. Application servers are the physical manifestation of the application (middleware) tier.
• Process servers: The process servers are configured almost exactly like application servers, but users do not log onto these servers. They handle the asynchronous workflow requests that are queued from users or by the IBM® TRIRIGA software. Process servers are the physical manifestation of the application (middleware) tier.
• Database server: The database server runs the database processes. The database is where data is stored. Today’s major databases use SQL (Structured Query Language) to store and retrieve data. The application tier communicates with the database tier via JDBC connection pools. It is the physical manifestation of the database tier.

b.1 Data-Load Hardware Environment

The following values describe the data-load hardware environment. With this configuration, the resource utilization bottlenecks were eliminated to load lease batches of at least 1000 leases at a time.

• Database Server:
  • # of vCPUs: 64 virtual CPUs
  • CPU: Intel® Xeon® CPU E5-2686 v4 @ 2.3 GHz
  • Storage: SAN attached
  • Memory: 488 GB
  • NIC: 1 GBit/sec
  • OS: CentOS Linux 7.4, 64-bit
  • Database: DB2 Enterprise Database Server 11.1.2.2, 64-bit
• Application Server (VM 1 of 1):
  • # of vCPUs: 4 virtual CPUs
  • CPU: Intel® Xeon® CPU E5-2686 v4 @ 2.3 GHz
  • Memory: 32 GB
  • OS: CentOS Linux 7.4, 64-bit
  • Application Server: WebSphere Liberty Profile (TRIRIGA embedded)
  • Java Version: Oracle Java 1.8.0_162
  • JVM Heap Size: 4096 MB
  • # of JVMs: 1
  • Usage: Front-end access only
• Process Server (VM 1 of 3):
  • # of vCPUs: 36 virtual CPUs
  • CPU: Intel® Xeon® Platinum 8124M CPU @ 3.0 GHz
  • Memory: 72 GB
  • OS: CentOS Linux 7.4, 64-bit
  • Application Server: WebSphere Liberty Profile (TRIRIGA embedded)
  • Java Version: Oracle Java 1.8.0_162
  • JVM Heap Size: 6144 MB
  • # of JVMs: 1
  • Usage: Non-PM data loads (restricted to single user)
• Process Server (VM 2 of 3):
  • # of vCPUs: 72 virtual CPUs
  • CPU: Intel® Xeon® Platinum 8124M CPU @ 3.0 GHz
  • Memory: 144 GB
  • OS: CentOS Linux 7.4, 64-bit
  • Application Server: WebSphere Liberty Profile (TRIRIGA embedded)
  • Java Version: Oracle Java 1.8.0_162
  • JVM Heap Size: 6144 MB
  • # of JVMs: 1
  • Usage: Lease data loader (restricted to data-load user and SCHEVENT user)
• Process Server (VM 3 of 3):
  • # of vCPUs: 72 virtual CPUs
  • CPU: Intel® Xeon® Platinum 8124M CPU @ 3.0 GHz
  • Memory: 144 GB
  • OS: CentOS Linux 7.4, 64-bit
  • Application Server: WebSphere Liberty Profile (TRIRIGA embedded)
  • Java Version: Oracle Java 1.8.0_162
  • JVM Heap Size: 6144 MB
  • # of JVMs: 1
  • Usage: System workflow processing (restricted to system user)

b.2 Multi-User Benchmark Test Environment

The following values describe the multi-user benchmark-test hardware environment.

• Database Server:
  • # of vCPUs: 64 virtual CPUs
  • CPU: Intel® Xeon® CPU E5-2686 v4 @ 2.3 GHz
  • Storage: SAN attached
  • Memory: 488 GB
  • NIC: 1 GBit/sec
  • OS: CentOS Linux 7.4, 64-bit
  • Database: DB2 Enterprise Database Server 11.1.2.2, 64-bit
• Application Servers (VMs):
  • # of vCPUs: 16 virtual CPUs
  • CPU: Intel® Xeon® Platinum 8124M CPU @ 3.0 GHz
  • Memory: 32 GB
  • OS: CentOS Linux 7.4, 64-bit
  • Application Server: WebSphere Application Server 8.5.5.12
  • Java Version: IBM Java 1.8.0 SR4 FP5, 64-bit
  • JVM Heap Size: 4096 MB
  • # of VMs: 2
  • JVMs per VM: 1
• Process and BIRT/Integration Servers (VMs):
  • # of vCPUs: 36 virtual CPUs
  • CPU: Intel® Xeon® CPU E5-2686 v4 @ 2.3 GHz
  • Memory: 72 GB
  • OS: CentOS Linux 7.4, 64-bit
  • Application Server: WebSphere Application Server 8.5.5.12
  • Java Version: IBM Java 1.8.0 SR4 FP5, 64-bit
  • JVM Heap Size: 6144 MB
  • # of VMs: 2
  • JVMs per VM: 1

b.3 Software Environment

The following values show the software versions used for both the data-load and multi-user benchmark-test environments.

• Software Environment:
  • IBM TRIRIGA 10.5.3.2
  • IBM TRIRIGA Application Platform 3.5.3.4

b.4 Key Configurations

IBM TRIRIGA uses out-of-the-box settings as well as settings from our performance best practices.

The following settings and values identify the key tuning and configuration for the performance test environments.

The settings with bold asterisks (*) were modified from best practices to achieve a balance between response times and resource utilization.

b.4.1 IBM DB2 Database Server

For more information, see the above section 5.3.1 IBM DB2 Database Server Tuning.

• Registry Settings:
  • DB2_COMPATIBILITY_VECTOR: ORA
  • DB2_DEFERRED_PREPARE_SEMANTICS: YES
  • DB2_ATS_ENABLE: YES
  • DB2_USE_ALTERNATE_PAGE_CLEANING: ON
• DBM Configuration:
  • RQRIOBLK: 65535
  • AGENT_STACK_SZ: 1024
• DB Configuration:
  • STMT_CONC: OFF
  • LOCKTIMEOUT: 30
  • LOGPRIMARY: 64*
  • LOGFILSIZ: 65535*
  • LOGSECOND: 48*
  • LOGBUFSZ: 8192
  • CATALOGCACHE_SZ: 2048
  • STRING_UNITS: CODEUNITS32
• Other Settings:
  • db2 bind '<db2home>/bnd/db2clipk.bnd' collection NULLIDR1: REOPT(ONCE)
  • Bufferpool Size: AUTOMATIC

• DB Connection Pool:

  • Minimum Connections: 10
  • Maximum Connections: 100*

• Default Thread Pool (For Single-User Manual Test Environment Only):

  • Minimum Size: 20
  • Maximum Size: 50
  • Thread Inactivity Timeout: 30000

• WebContainer Thread Pool (For Single-User Manual Test Environment Only):

  • Minimum Size: 120
  • Maximum Size: 120
  • Thread Inactivity Timeout: 60000

• Data Source Custom Properties:

  • webSphereDefaultIsolationLevel: 2
  • jdbcCollection: NULLIDR1

b.4.2 Operating System

For more information, see the section 4 Operating System Configuration.

The following changes were applied to the application and process servers involved in the test:

• net.ipv4.ip_local_port_range = 32768 61000

The following ulimit changes were applied to all servers involved in the test:

• Max user processes 8192
• Open files 131072

b.4.3 IBM TRIRIGA Platform

For more information on tuning and configuring the TRIRIGAWEB.properties file, see the section 7 IBM TRIRIGA Tuning.

For Data-Load Environment:

• Application Servers:
  • WF_INSTANCE_SAVE: ERRORS_ONLY
  • BIRT_MEMORY_USAGE_LIMIT: 35
• Lease Load Process Servers:
  • WFAgentMaxThreads: 64*
  • WF_AGENT_MAX_ACTIVE_PER_USER: 64*
  • WF_INSTANCE_SAVE: ERRORS_ONLY
  • BIRT_MEMORY_USAGE_LIMIT: 35
• Other Data Process Server:
  • WFAgentMaxThreads: 32*
  • WF_AGENT_MAX_ACTIVE_PER_USER: 32*
  • WF_INSTANCE_SAVE: ERRORS_ONLY
  • BIRT_MEMORY_USAGE_LIMIT: 35

For Multi-User Benchmark Test Environment:

• Application Servers:
  • WF_INSTANCE_SAVE: ERRORS_ONLY
  • BIRT_MEMORY_USAGE_LIMIT: 35
• Workflow Process Servers:
  • WFAgentMaxThreads: 64*
  • WF_AGENT_MAX_ACTIVE_PER_USER: 16*
  • WF_INSTANCE_SAVE: ERRORS_ONLY
  • BIRT_MEMORY_USAGE_LIMIT: 35
• BIRT & Additional-Agents Process Server:
  • WFAgentMaxThreads: 64*
  • WF_AGENT_MAX_ACTIVE_PER_USER: 16*
  • WF_INSTANCE_SAVE: ERRORS_ONLY
  • BIRT_MEMORY_USAGE_LIMIT: 35

c. Performance Test Indexes

The following sections summarize the test-index methodology and the results of the tests.

c.1 Data-Load Indexes

During the initial phases of the Lease data loading, DB2 snapshots were taken to identify long-running SQL statements. These were then analyzed with the DB2 SQL Advisor, and appropriate indexes were created as outlined below.

• Data-Load Indexes

c.2 Single-User Manual Test Indexes

Steps were performed manually with the Performance Analyzer which was activated for SQL logging. After long-running SQLs (i.e. running more than 1 second) were identified, those were analyzed using the DB2 SQL Advisor and recommended indexes were applied. If the Analyzer recommended indexes, but the improvement was <10%, those indexes were not applied and were not included below.

In addition, any recommended indexes against the IBS_SPEC and IBS_SPEC_ASSIGNMENTS tables were not applied as past experience shows that indexes against these tables can actually be detrimental to overall system performance, especially under load.

c.2.1 Lease Abstract

• Indexes: Lease Abstract

c.2.2 Generate Schedules

• Indexes: Generate Schedules

c.2.3 Activate Lease

No SQLs over 1 second.

c.2.4 Amend & Extend Lease

No SQLs over 1 second.

c.2.5 Revise Lease Contract Data

• Indexes: Revise Lease Contract Data

c.2.6 Revise Lease Accounting Data

• Indexes: Revise Lease Accounting Data

c.2.7 Expire Lease

No SQLs over 1 second.

c.2.8 Get Payment

• Indexes: Get Payment

c.3 Multi-User Benchmark Test Indexes

DB2 snapshots were taken to identify long-running (i.e. running more than 1 second) and/or expensive (i.e. CPU utilization) SQLs. Those were analyzed using the DB2 SQL Advisor and recommended indexes were applied. If the Analyzer recommended indexes, but the improvement was <10%, those indexes were not applied and were not included below.

In addition, any recommended indexes against the IBS_SPEC and IBS_SPEC_ASSIGNMENTS tables were not applied as past experience shows that indexes against these tables can actually be detrimental to overall system performance, especially under load.

c.3.1 Lease Abstract Draft

• Indexes: Lease Abstract Draft

c.3.2 Generate Rent Schedule

• Indexes: Generate Rent Schedule

c.3.3 Activate Lease

• Indexes: Activate Lease

c.3.4 Complete Lease Abstract

No long-running or expensive SQLs.

c.3.5 Amend Lease

• Indexes: Amend Lease

c.3.6 Lease Accounting Review

No long-running or expensive SQLs.

c.3.7 Revise Lease Contract Data

• Indexes: Revise Lease Contract Data

c.3.8 Revise Lease Accounting Data

• Indexes: Revise Lease Accounting Data

c.3.9 Expire Lease

• Indexes: Expire Lease

c.3.10 Period Close (including Journal Entries)

• Indexes: Period Close (including Journal Entries)

c.3.11 Finance Lease Cash-Flow Disclosure Report

• Indexes: Finance Lease Cash-Flow Disclosure Report