Configuring Db2

You configure Db2 for use by Maximo® Manage before you deploy Maximo Manage in Maximo Application Suite.

Before you begin

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

About this task

The commands in this task can be used to configure a database that is not the instance of Db2 Warehouse on Cloud Pak for Data. However, the database must be version 11.5 or later.

The commands in this task are examples of the commands that you must run. For example, maxdb80 is the name of the database. If maxdb80 is not your database name, ensure that you replace all instances with the correct database name.

Procedure

  1. Log in to the system as a user that has administrative permissions on the system.
  2. Create system users if they do not exist on the system.
    • Windows
      • db2admin
      • maximo
    • Linux® or UNIX
      • maximo (Maximo db user)
      • ctgfenc1 (Db2 fenced user)
      • ctginst1 (Db2 instance owner)

        The administrative user being used most is assigned the primary group of the instance owner to perform some of the following steps.

  3. At the Db2 installation directory, run the following command to set up the command line environment:
    • Windows
      db2cmd
      
    • Linux or UNIX

      Ensure that the /opt/ibm/db2/V11.5/bin, /opt/ibm/db2/v11.5/instance, and /opt/ibm/db2/V11.5/adm directories are added to your PATH.

  4. Run the following commands to create the database instance. Replace the variable with the Db2 administrator password:
    • Windows
      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
      
    • Linux or UNIX
      db2icrt -s ese -u ctgfenc1 -p 50005 ctginst1
      . /home/ctginst1/sqllib/db2profile
      db2start
      db2 update dbm config using SVCENAME 50005 DEFERRED
      db2stop
      db2set DB2COMM=tcpip
      db2start
      
  5. Run the following commands to create the database:
    db2 create db 'maxdb80' ALIAS 'maxdb80' using codeset UTF-8 territory US pagesize 32 K
    db2 connect to 'maxdb80'
    db2 GRANT DBADM ON DATABASE TO USER db2admin (windows only)
    db2 GRANT SECADM ON DATABASE TO USER db2admin (windows only)
    db2 connect reset
    
  6. Run the following commands to configure the database:
    db2 update db cfg for maxdb80 using SELF_TUNING_MEM ON
    db2 update db cfg for maxdb80 using APPGROUP_MEM_SZ 16384 DEFERRED
    db2 update db cfg for maxdb80 using APPLHEAPSZ 2048 AUTOMATIC DEFERRED
    db2 update db cfg for maxdb80 using AUTO_MAINT ON DEFERRED
    db2 update db cfg for maxdb80 using AUTO_TBL_MAINT ON DEFERRED
    db2 update db cfg for maxdb80 using AUTO_RUNSTATS ON DEFERRED
    db2 update db cfg for maxdb80 using AUTO_REORG ON DEFERRED
    db2 update db cfg for maxdb80 using AUTO_DB_BACKUP ON DEFERRED
    db2 update db cfg for maxdb80 using CATALOGCACHE_SZ 800 DEFERRED
    db2 update db cfg for maxdb80 using CHNGPGS_THRESH 40 DEFERRED
    db2 update db cfg for maxdb80 using DBHEAP AUTOMATIC
    db2 update db cfg for maxdb80 using LOCKLIST AUTOMATIC DEFERRED
    db2 update db cfg for maxdb80 using LOGBUFSZ 1024 DEFERRED
    db2 update db cfg for maxdb80 using LOCKTIMEOUT 300 DEFERRED
    db2 update db cfg for maxdb80 using LOGPRIMARY 20 DEFERRED
    db2 update db cfg for maxdb80 using LOGSECOND 100 DEFERRED
    db2 update db cfg for maxdb80 using LOGFILSIZ 8192 DEFERRED
    db2 update db cfg for maxdb80 using SOFTMAX 1000 DEFERRED
    
  7. Run the following command according to your operating system and bit size:
    Operating system Command
    32-bit Microsoft Windows
    db2 update db cfg for maxdb80 using MAXFILOP 32768 DEFERRED #32-bit Windows
    64-bit Windows
    db2 update db cfg for maxdb80 using MAXFILOP 65335 DEFERRED #64-bit Windows
    32-bit UNIX
    db2 update db cfg for maxdb80 using MAXFILOP 30720 DEFERRED #32-bit UNIX
    64-bit UNIX
    db2 update db cfg for maxdb80 using MAXFILOP 61440 DEFERRED #64-bit UNIX
  8. Run the following commands to continue to configure the database:
    db2 update db cfg for maxdb80 using PCKCACHESZ AUTOMATIC DEFERRED
    db2 update db cfg for maxdb80 using STAT_HEAP_SZ AUTOMATIC DEFERRED
    db2 update db cfg for maxdb80 using STMTHEAP AUTOMATIC DEFERRED
    db2 update db cfg for maxdb80 using UTIL_HEAP_SZ 10000 DEFERRED
    db2 update db cfg for maxdb80 using DATABASE_MEMORY AUTOMATIC DEFERRED
    db2 update db cfg for maxdb80 using AUTO_STMT_STATS OFF DEFERRED
    db2 update db cfg for maxdb80 using STMT_CONC LITERALS DEFERRED
    db2 update alert cfg for database on maxdb80 using db.db_backup_req SET THRESHOLDSCHECKED YES
    db2 update alert cfg for database on maxdb80 using db.tb_reorg_req SET THRESHOLDSCHECKED YES
    db2 update alert cfg for database on maxdb80 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
    
  9. For Linux or UNIX, log in to the system, for example as the ctginst1 user, and then restart the Db2 command line environment:
    su - ctginst1
    db2
  10. Run the following command to stop the database:
    db2stop force
  11. Run the following command to start the database:
    db2start
  12. Run the following command to reconnect to the database:
    db2 connect to 'maxdb80'
  13. 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
    
  14. Run the following command to create the schema:
    db2 create schema maximo authorization maximo
  15. 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
    
  16. Run the following command to break the database connection:
    db2 connect reset