Configuring the database for Maximo® Health

Before you deploy Maximo® Health, a database must be installed, deployed, and configured.

You cannot reuse the IBM® Db2® Warehouse database on IBM® Cloud Pak for Data that is used for Maximo Monitor. You can configure a new Db2 Warehouse database on IBM® Cloud Pak for Data that is only for Maximo® Health, another type of IBM® Db2® database, Microsoft® SQL Server, or Oracle Database.

Configuring Oracle Database

For information about installing and deploying Oracle Database, review the Oracle Database product documentation.

To configure the database for Maximo® Health, you must create tablespaces, create a database user, and configure database settings. The following text is an example of the commands that you must run. For example, maximo is your database username. You can run similar commands by using a SQL query tool.

ALTER SYSTEM SET OPEN_CURSORS = 1000;
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS='CHAR' scope=both;
ALTER SYSTEM SET cursor_sharing='FORCE';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
CREATE OR REPLACE DIRECTORY dump AS '/home/oracle';
Create tablespace maxdata datafile '/opt/oradata/orcl/maxdata.dbf' size 100M autoextend on;
Create tablespace maxindx datafile '/opt/oradata/orcl/maxindx.dbf' size 100M autoextend on;
create temporary tablespace maxtemp tempfile '/opt/oradata/orcl/maxtemp.dbf' size 100M autoextend on 
maxsize unlimited;
create user maximo identified by maximo default tablespace maxdata temporary tablespace maxtemp;
grant connect to maximo;
grant create job to maximo;
grant create trigger to maximo;
grant create session to maximo;
grant create sequence to maximo;
grant create synonym to maximo;
grant create table to maximo;
grant create view to maximo;
grant create procedure to maximo;
grant alter session to maximo;
grant select any dictionary to maximo;
grant execute on ctxsys.ctx_ddl to maximo;
alter user maximo quota unlimited on maxdata;
alter user maximo quota unlimited on maxindx;
commit;
exit;

Configuring SQL Server

For information about configuring SQL Server, review the SQL Server product documentation.

To configure the database for Maximo® Health, you must create tablespaces, create a database user, and configure database settings. The following text is an example of the commands that you must run. For example, maximo is your database username. You can run similar commands by using a SQL query tool.

CREATE DATABASE maxdb76 ON (NAME = 'maxdata', FILENAME = '/var/opt/mssql/data/maxdata.mdf', SIZE = 
500, FILEGROWTH = 10%) LOG ON (NAME = 'MAXIMO_Log', FILENAME = '/var/opt/mssql/data/maxdb76_log.ldf' 
, SIZE = 1, FILEGROWTH = 10%) COLLATE $(COLLATE)
GO
USE maxdb76;
GO
sp_addlogin maximo,Maximo76;
GO
sp_changedbowner maximo;
GO
sp_addsrvrolemember maximo, securityadmin;
GO

Configuring Db2 Warehouse on Cloud Pak for Data 3.5.0

You cannot reuse the Db2 Warehouse database on Cloud Pak for Data that is used for Maximo Monitor, but you can configure a new Db2 Warehouse database on Cloud Pak for Data 3.5.0 that is only for Maximo® Health.

The following information is an example of the steps that you must complete and applies only to Cloud Pak for Data 3.5.0. If you complete different steps, ensure that a database schema and tablespaces are configured and that the ddl_constraint_def parameter is set to yes.

Prerequisites

Before you begin configuring the database for Maximo® Health and before the database is deployed, ensure that you complete the following steps:

  1. Change the default table organization from COLUMN to ROW.
  2. Do not enable text search for the database. If you enable text search, Maximo® Health can be used only in the English (US) locale.

Procedure

The following steps and commands use BLUDB as the name of the database. BLUDB is a default name for a Db2 Warehouse database on Cloud Pak for Data. If BLUDB is not the database name, before you run the commands, ensure that you replace BLUDB with the name of your database.

  1. Confirm that the database is created.

    1. As an administrator, log in to Red Hat® OpenShift® and from the OpenShift cluster, in the navigation menu, click Workloads > Pods.
    2. Locate and open the db2wh-string-db2u-0 pod. string is a randomly generated set of numbers. By default, the pod is located in the zen namespace.
    3. On the Terminal tab, run the following commands:

      su – db2inst1
      
      db2 connect to BLUDB
      

      If the database is configured, the database connection information is returned. The following text is an example of this information. You need these values later in the configuration.

      sh-4.2$ su - db2inst1
      Last login: Tue May 26 14:29:55 UTC 2020
      [db2inst1@db2wh-1589293563350-db2u-0- Db2U db2inst1]$ db2 connect to BLUDB
      
      Database Connection Information
      
      Database server  = DB2/LINUXX8664 11.5.2.0
      SQL authorization ID  = DB2INST1
      Local database alias  = BLUDB
      [db2inst1@db2wh-1589293563350-db2u-0 - Db2U db2inst1]$
      
  2. Create an administrative Cloud Pak for Data user.

    1. As an administrator, log in to Cloud Pak for Data and from the navigation menu, click Administration > User management.
    2. Click New user.
    3. Specify the following information:
      • For the user, specify maximo.
      • For the username, specify maximo.
      • Specify an email address and password.
      • Select the Administrator role.
    4. Click Create.
    5. From the navigation menu, click Data > Databases.
    6. Open the three-dot menu for your Maximo® Health database and then click Details.
    7. From the drop-down menu, click Manage access.
    8. In the maximo user row, click the edit icon.
    9. In the Role field, select Admin and then click Save.
  3. Prepare the database for maxinst. Run the following commands from a database browser terminal or command line.

    1. Run the following command to connect to the Db2® pod. Replace the variable with the string value for your db2wh-string-db2u-0 pod, which was accessed during step 1.
      oc rsh db2wh-*<string>*-db2u-0 /bin/bash
      
    2. Run the following command to connect to the database as the db2inst1 user:
      su - db2inst1
      
    3. Run the following command to connect to the BLUDB database:
      db2 connect to bludb
      
  4. Run the following commands.
    1. Run the following commands to configure the database:
      db2 update db cfg for bludb using SELF_TUNING_MEM ON
      
      db2 update db cfg for bludb using APPGROUP_MEM_SZ 16384 DEFERRED
      
      db2 update db cfg for bludb using APPLHEAPSZ 2048 AUTOMATIC DEFERRED
      
      db2 update db cfg for bludb using AUTO_MAINT ON DEFERRED
      
      db2 update db cfg for bludb using AUTO_TBL_MAINT ON DEFERRED
      
      db2 update db cfg for bludb using AUTO_RUNSTATS ON DEFERRED
      
      db2 update db cfg for bludb using AUTO_REORG ON DEFERRED
      
      db2 update db cfg for bludb using AUTO_DB_BACKUP ON DEFERRED
      
      db2 update db cfg for bludb using CATALOGCACHE_SZ 800 DEFERRED
      
      db2 update db cfg for bludb using CHNGPGS_THRESH 40 DEFERRED
      
      db2 update db cfg for bludb using DBHEAP AUTOMATIC
      
      db2 update db cfg for bludb using LOCKLIST AUTOMATIC DEFERRED
      
      db2 update db cfg for bludb using LOGBUFSZ 1024 DEFERRED
      
      db2 update db cfg for bludb using LOCKTIMEOUT 300 DEFERRED
      
      db2 update db cfg for bludb using LOGPRIMARY 20 DEFERRED
      
      db2 update db cfg for bludb using LOGSECOND 100 DEFERRED
      
      db2 update db cfg for bludb using LOGFILSIZ 8192 DEFERRED
      
      db2 update db cfg for bludb using SOFTMAX 1000 DEFERRED
      
      db2 update db cfg for bludb using MAXFILOP 61440 DEFERRED
      
      db2 update db cfg for bludb using PCKCACHESZ AUTOMATIC DEFERRED
      
      db2 update db cfg for bludb using STAT_HEAP_SZ AUTOMATIC DEFERRED
      
      db2 update db cfg for bludb using STMTHEAP AUTOMATIC DEFERRED
      
      db2 update db cfg for bludb using UTIL_HEAP_SZ 10000 DEFERRED
      
      db2 update db cfg for bludb using DATABASE_MEMORY AUTOMATIC DEFERRED
      
      db2 update db cfg for bludb using AUTO_STMT_STATS OFF DEFERRED
      
      db2 update db cfg for bludb using STMT_CONC LITERALS DEFERRED
      
      db2 update alert cfg for database on bludb using db.db_backup_req SET THRESHOLDSCHECKED YES
      
      db2 update alert cfg for database on bludb using db.tb_reorg_req SET THRESHOLDSCHECKED YES
      
      db2 update alert cfg for database on bludb using db.tb_runstats_req SET THRESHOLDSCHECKED YES
      
      db2 update dbm cfg using PRIV_MEM_THRESH 32767 DEFERRED
      
      db2 update dbm cfg using KEEPFENCED NO DEFERRED
      
      db2 update dbm cfg using NUMDB 2 DEFERRED
      
      db2 update dbm cfg using RQRIOBLK 65535 DEFERRED
      
      db2 update dbm cfg using HEALTH_MON OFF DEFERRED
      
      db2 update dbm cfg using AGENT_STACK_SZ 1000 DEFERRED
      
      db2 update dbm cfg using MON_HEAP_SZ AUTOMATIC DEFERRED
      
      db2 update db cfg using DDL_CONSTRAINT_DEF Yes
      
      db2set DB2_SKIPINSERTED=ON
      
      db2set DB2_INLIST_TO_NLJN=YES
      
      db2set DB2_MINIMIZE_LISTPREFETCH=Y
      
      db2set DB2_EVALUNCOMMITTED=YES
      
      db2set DB2_FMP_COMM_HEAPSZ=65536
      
      db2set DB2_SKIPDELETED=ON
      
      db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON
      
    2. Run the following command to create the buffer pool:
      db2 CREATE BUFFERPOOL MAXBUFPOOL IMMEDIATE SIZE 4096 AUTOMATIC PAGESIZE 32 K
      
    3. Run the following commands to create the tablespaces:
      db2 CREATE REGULAR TABLESPACE MAXDATA PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE INITIALSIZE 5000 M BUFFERPOOL MAXBUFPOOL
      
      db2 CREATE TEMPORARY TABLESPACE MAXTEMP PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL MAXBUFPOOL
      
      db2 CREATE REGULAR TABLESPACE MAXINDEX PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE INITIALSIZE 5000 M BUFFERPOOL MAXBUFPOOL
      
      db2 GRANT USE OF TABLESPACE MAXDATA TO USER MAXIMO
      
    4. Run the following command to create the schema:
      db2 create schema maximo authorization maximo
      
    5. Run the following commands to grant authority to the maximo user:
      db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,
      LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER MAXIMO
      
      db2 GRANT USE OF TABLESPACE MAXDATA TO USER MAXIMO
      
      db2 GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA MAXIMO TO USER MAXIMO
      
    6. Run the following command to break the database connection:
      db2 connect reset
      

Configuring other Db2® databases

The following commands can be used to configure a database that is not the instance of Db2 Warehouse on Cloud Pak for Data, for example, {{site.data.keyword.prodname_db2adv_long}} or an instance of Db2 Warehouse that is not on Cloud Pak for Data. However, the database must be version 11.5 or later.

Before you configure the database, install and deploy it. For installation instructions, review the Db2® product documentation for your database.

The following commands are an example of the commands that you must run. For example, in the following commands, maxdb76 is the name of the database. If maxdb76 is not your database name, ensure that you replace all instances with the correct database name.

Open a command line at the Db2® installation directory and run the following commands:

  1. Run the following command to set up the command line environment:
    db2cmd
    
  2. Run the following commands to create the database instance. Replace the variable with the Db2® administrator password.
    db2icrt -s ese -u db2admin,<administrator_password> -r 50005,50005 ctginst1
    
    set db2instance=ctginst1
    
    db2start
    
    db2 update dbm config using SVCENAME 50005 DEFERRED
    
    db2stop
    
    db2set DB2COMM=tcpip
    
    db2start
    
  3. Run the following commands to create the database:
    db2 create db 'maxdb76' ALIAS 'maxdb76' using codeset UTF-8 territory US pagesize 32 K
    
    db2 connect to 'maxdb76'
    
    db2 GRANT DBADM ON DATABASE TO USER db2admin
    
    db2 GRANT SECADM ON DATABASE TO USER db2admin
    
    db2 connect reset
    
  4. Run the following commands to configure the database:
    db2 update db cfg for maxdb76 using SELF_TUNING_MEM ON
    
    db2 update db cfg for maxdb76 using APPGROUP_MEM_SZ 16384 DEFERRED
    
    db2 update db cfg for maxdb76 using APPLHEAPSZ 2048 AUTOMATIC DEFERRED
    
    db2 update db cfg for maxdb76 using AUTO_MAINT ON DEFERRED
    
    db2 update db cfg for maxdb76 using AUTO_TBL_MAINT ON DEFERRED
    
    db2 update db cfg for maxdb76 using AUTO_RUNSTATS ON DEFERRED
    
    db2 update db cfg for maxdb76 using AUTO_REORG ON DEFERRED
    
    db2 update db cfg for maxdb76 using AUTO_DB_BACKUP ON DEFERRED
    
    db2 update db cfg for maxdb76 using CATALOGCACHE_SZ 800 DEFERRED
    
    db2 update db cfg for maxdb76 using CHNGPGS_THRESH 40 DEFERRED
    
    db2 update db cfg for maxdb76 using DBHEAP AUTOMATIC
    
    db2 update db cfg for maxdb76 using LOCKLIST AUTOMATIC DEFERRED
    
    db2 update db cfg for maxdb76 using LOGBUFSZ 1024 DEFERRED
    
    db2 update db cfg for maxdb76 using LOCKTIMEOUT 300 DEFERRED
    
    db2 update db cfg for maxdb76 using LOGPRIMARY 20 DEFERRED
    
    db2 update db cfg for maxdb76 using LOGSECOND 100 DEFERRED
    
    db2 update db cfg for maxdb76 using LOGFILSIZ 8192 DEFERRED
    
    db2 update db cfg for maxdb76 using SOFTMAX 1000 DEFERRED
    
  5. Run the following command according to your operating system and bit size.

    For a 32-bit Microsoft® Windows® operating system, run the following command:

    db2 update db cfg for maxdb76 using MAXFILOP 32768 DEFERRED #32-bit Windows
    

    For a 64-bit Windows® operating system, run the following command:

    db2 update db cfg for maxdb76 using MAXFILOP 65335 DEFERRED #64-bit Windows
    

    For a 32-bit UNIX® operating system, run the following command:

    db2 update db cfg for maxdb76 using MAXFILOP 30720 DEFERRED #32-bit UNIX
    

    For a 64-bit UNIX® operating system, run the following command:

    db2 update db cfg for maxdb76 using MAXFILOP 61440 DEFERRED #64-bit UNIX
    
  6. Run the following commands to continue to configure the database:
    db2 update db cfg for maxdb76 using PCKCACHESZ AUTOMATIC DEFERRED
    
    db2 update db cfg for maxdb76 using STAT_HEAP_SZ AUTOMATIC DEFERRED
    
    db2 update db cfg for maxdb76 using STMTHEAP AUTOMATIC DEFERRED
    
    db2 update db cfg for maxdb76 using UTIL_HEAP_SZ 10000 DEFERRED
    
    db2 update db cfg for maxdb76 using DATABASE_MEMORY AUTOMATIC DEFERRED
    
    db2 update db cfg for maxdb76 using AUTO_STMT_STATS OFF DEFERRED
    
    db2 update db cfg for maxdb76 using STMT_CONC LITERALS DEFERRED
    
    db2 update alert cfg for database on maxdb76 using db.db_backup_req SET THRESHOLDSCHECKED YES
    
    db2 update alert cfg for database on maxdb76 using db.tb_reorg_req SET THRESHOLDSCHECKED YES
    
    db2 update alert cfg for database on maxdb76 using db.tb_runstats_req SET THRESHOLDSCHECKED YES
    
    db2 update dbm cfg using PRIV_MEM_THRESH 32767 DEFERRED
    
    db2 update dbm cfg using KEEPFENCED NO DEFERRED
    
    db2 update dbm cfg using NUMDB 2 DEFERRED
    
    db2 update dbm cfg using RQRIOBLK 65535 DEFERRED
    
    db2 update dbm cfg using HEALTH_MON OFF DEFERRED
    
    db2 update dbm cfg using AGENT_STACK_SZ 1000 DEFERRED
    
    db2 update dbm cfg using MON_HEAP_SZ AUTOMATIC DEFERRED
    
    db2set DB2_SKIPINSERTED=ON
    
    db2set DB2_INLIST_TO_NLJN=YES
    
    db2set DB2_MINIMIZE_LISTPREFETCH=Y
    
    db2set DB2_EVALUNCOMMITTED=YES
    
    db2set DB2_FMP_COMM_HEAPSZ=65536
    
    db2set DB2_SKIPDELETED=ON
    
    db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON
    
  7. Run the following command to stop the database:
     db2stop force
    
  8. Run the following command to start the database:
     db2start
    
  9. Run the following command to reconnect to the database:
     db2 connect to 'maxdb76'
    
  10. Run the following commands to create a buffer pool:
    db2 CREATE BUFFERPOOL MAXBUFPOOL IMMEDIATE SIZE 4096 AUTOMATIC PAGESIZE 32 K
    
    db2 CREATE REGULAR TABLESPACE MAXDATA PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE INITIALSIZE 5000 M BUFFERPOOL MAXBUFPOOL
    
    db2 CREATE TEMPORARY TABLESPACE MAXTEMP PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL MAXBUFPOOL
    
    db2 CREATE REGULAR TABLESPACE MAXINDEX PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE INITIALSIZE 5000 M BUFFERPOOL MAXBUFPOOL
    
    db2 GRANT USE OF TABLESPACE MAXDATA TO USER MAXIMO
    
  11. Run the following command to create the schema:
    db2 create schema maximo authorization maximo
    
  12. Run the following commands to grant authority to the maximo user:
    db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER MAXIMO
    
    db2 GRANT USE OF TABLESPACE MAXDATA TO USER MAXIMO
    
    db2 GRANT CREATEIN,DROPIN,ALTERIN ON SCHEMA MAXIMO TO USER MAXIMO
    
  13. Run the following command to break the database connection:
    db2 connect reset