Connecting to a remote Db2 server

By default, the Db2 server (Db2 Advanced Enterprise Server Edition version 10.5 fix pack 6) is installed locally with the Cloud APM server. If you are already running a supported fixpack of Db2 Advanced Enterprise Server Edition version 10.5, Db2 Advanced Workgroup Server Edition V11.1, or Db2 Advanced Enterprise Server Edition version 11.1 on an external system, you can choose to use this existing remote Db2 server for the databases that are required by the Cloud APM server instead of installing the default local Db2 server.

Complete these steps to configure an external Db2 server to use for the Cloud APM server databases:

1. On the system where you are installing the Cloud APM server, copy the database scripts from the Cloud APM server installation image to the external system:
   1. Download the Cloud APM server installation image.
      For more information, see the Download instructions topic.
   2. Extract the server installation files as described in step 3 of Downloading and installing the server.
   3. Copy the following scripts to the external system. Ensure that the external_db2_instance user has access to the remote Db2 server directory on the external system where the SQL files are copied to. external_db2_instance is the remote Db2 server instance user name. Specify a user name or accept the default user name, which is db2apm.
      

      
      /installation_media/files/sql/create/create_tdw_db.sql
      /installation_media/files/sql/create/create_scr_db.sql
      /installation_media/files/sql/create/create_datamart_db.sql
      /installation_media/files/sql/update/update_tdw_performance.sql
      /installation_media/packages/SCR/setup-dbconfig-linux_64.bin

      

      
      /installation_media/files/sql/create/create_tdw_db.sql
      /installation_media/files/sql/create/create_scr_db.sql
      /installation_media/files/sql/create/create_datamart_db.sql
      /installation_media/files/sql/update/update_tdw_performance.sql
      /installation_media/packages/SCR/setup-dbconfig-aix_64.bin
      

      where installation_media is the directory where the Cloud APM server installation files are located.
   The scripts are used later in this procedure to create the DATAMART, SCR32, and WAREHOUS databases on the external system.
2. On the external system, create the databases and set up the remote Db2 server:
   1. Find the Db2 server installation path and verify the Db2 version that is installed:

      db2ls

   2. Verify that the Db2 license is installed:

      Db2_install_dir/Db2_version/adm/db2licm -l

      where Db2_install_dir is the Db2 installation path that you retrieved in step 2.a.
   3. Create the external_db2_instance user, add the user to the db2iadm1 group, and create the database instance:
      

      
      useradd -g db2iadm1 -m external_db2_instance

      /Db2_install_dir/Db2_version/instance/db2icrt -u db2fenc1 external_db2_instance

      

      
      mkuser id=value pgrp=db2iadm1 groups=db2iadm1 
      home=/home/external_db2_instance external_db2_instance

      
      chown -R external_db2_instance:db2iadm1 /home/external_db2_instance
      /Db2_install_dir/Db2_version/instance/db2icrt -u db2fenc1 external_db2_instance

      where value is the user ID. 1000 and 1001 are usually used. You can check whether these IDs are available in SMIT: smitty > Security & Users > Users > List All Users. Db2_version is the Db2 version that is installed. Db2_version might not be included in the path when you install the Db2 server yourself. For more information, see the Default users and passwords topic.
   4. Create the itmuser user. Add the itmuser user to the dasadm1 group (applicable only if you are running Db2 version 10.5 fix pack 6 or fix pack 9). Run one set of the following commands depending on the Db2 version that you are running:
      • For Db2 version 10.5 fix pack 6 or fix pack 9, issue the following commands:
        

        useradd -g dasadm1 -m itmuser

        

        mkuser id=value pgrp=dasadm1 groups=dasadm1 home=/home/itmuser itmuser
        

        

        chown -R itmuser:dasadm1 /home/itmuser

        where value is the user ID.
      • For Db2 version 11.1.2 fix pack 2b or 11.1.3 fix pack 3, issue the following commands:
        

        useradd -m itmuser

        

        mkuser id=value home=/home/itmuser itmuser
        

        

        chown -R /home/itmuser

        where value is the user ID.
   5. As the root user, set the passwords for the external_db2_instance user and itmuser accounts:

      
      passwd external_db2_instance

      
      passwd itmuser

      Record the passwords somewhere because you need these values when you start the Cloud APM server installation.
   6. In a text editor, open the /etc/services file and add a service name and the port number for the external_db2_instance user instance.
      For example:

      db2c_external_db2_instance port_number/tcp

      where port_number is the port number that you want the external_db2_instance instance to listen on. Record the port number values somewhere because you need these values when you start the Cloud APM server installation.
   7. Log in to the external_db2_instance user account.
      For example:

      su - db2apm

   8. Update the Db2 configuration with the service name by entering the following commands:

      
      db2 update database manager configuration using svcename db2c_external_db2_instance

      db2stop

      db2start

   9. If you plan to use transparent LDAP authentication on the system where you are installing the Cloud APM server, set the DB2AUTH miscellaneous registry variable to OSAUTHDB on the Db2 instance:

      db2set DB2AUTH=OSAUTHDB

      
      db2stop

      
      db2start

      Transparent LDAP authentication enables the Db2 instance to authenticate users and acquire their groups through the operating system. The operating system, in turn, performs the authentication through an LDAP server.
   10. Turn on TCP/IP communications:

       
       db2set DB2COMM=tcpip

       db2stop

       db2start

   11. Verify that the WAREHOUS, SCR32, and DATAMART databases do not exist:

       db2 list db directory

       If any of the databases exist, remove each with the following command:

       db2 drop db database_name

       where database_name is the name of the database.
   12. Complete the following optional steps:
       1. If you want the Cloud APM databases to use a non-default path for the database, or you want to change the default database names, edit the create_tdw_db.sql, create_scr_db.sql, and create_datamart_db.sql files to specify the changes before starting the SQL scripts to create the databases (next step 2.m). For example, to use the /data/apmserver1 database path for the three databases for a Cloud APM server called apmserver1, edit the SQL files to include the new path by adding ON /data/apmserver1:

          CREATE DATABASE DATAMART on /data/apmserver1 USING CODESET UTF8 TERRITORY US 
          PAGESIZE 8 K 
          CREATE DATABASE SCR32 AUTOMATIC STORAGE YES on /data/apmserver1 USING CODESET 
          UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192 
          CREATE DATABASE WAREHOUS AUTOMATIC STORAGE YES on /data/apmserver1 
          USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192

          If you updated the directory paths, make sure the instance ID is the owner of the path and has write access to the directory.
       2. If you want to change the names of the database, use and editor, such as vi, to change the default database names in the create_tdw_db.sql, create_scr_db.sql, and create_datamart_db.sql files. If you change the name of the WAREHOUS database, the /installation_media/files/sql/update/update_tdw_performance.sql file is also impacted.
   13. Create the DATAMART, SCR32, and WAREHOUS databases by starting the following scripts:

       
       db2 -vf /path/create_tdw_db.sql
       db2 -vf /path/create_scr_db.sql
       db2 -vf /path/create_datamart_db.sql
       db2 -vf /path/update_tdw_performance.sql

       where path is the path and directory where you stored the script after you copied it from the Cloud APM server.
       These scripts create the databases in the home directory of the external_db2_instance.
   14. If this is the first Cloud APM SCR database that you are creating for the Db2 instance, run the setup-dbconfig-operating_system_64.bin script to complete the steps that are required for the SCR32 database. If you already ran the setup-dbconfig-operating_system_64.bin.script for this Db2 instance, skip this step and perform step 2.o instead.

       /path/setup-dbconfig-operating_system_64.bin -i console

       where operating_system is either aix or linux. Follow the console prompts:

       1. When you are prompted to choose a locale, enter 2 to select English.
       2. Enter 1 to accept the license agreement.
       3. Enter the absolute path to the installation directory. The setup-dbconfig executable program installs a program that is used for the creation of the SCR database into this directory. It is useful to specify a directory under your home directory. For example, if the Db2 instance is db2apm and the db2apm user is running the setup-dbconfig program, then specify this path: /home/db2apm/tbsmdb. The program requires 170 MB of disk space, approximately.

          Press Enter if you want to accept the default path, /opt/IBM/tivoli/tbsmdb. If you accept the default path, before continuing with the installation, ensure that the Db2 instance user has permission to create a directory here: /opt/IBM or that this /opt/IBM/tivoli/tbsmdb directory exists already and that the DB2 instance user has write permission to the directory.

          If you intend to have multiple SCR32 databases on this remote Db2 server, use a path that is unique for each Db2 instance. For example, if your Db2 instance name is db2apmg then use the /home/db2apmg/tbsmdb path. The path is not used for the SCR database.

          The Cloud APM SCR database configuration tools installed by the setup_dbconfig-platform_64.bin script should not be installed into the same directory as the Tivoli Business Service Manager database configuration tools if the databases for both products reside on the same remote Db2 server.

       4. Enter 1 to select IBM Cloud Application Performance Management(APM) as the product that uses the database.
       5. Enter 1 to select Simple as the type of installation to run.
       6. Enter 1 to instruct the installer to create the database schema, including the tables, table spaces, and views.
       7. Enter the following database configuration details when you are prompted:

          Database Name (maximum 8 characters) (DEFAULT: SCR32): scr-database-name 
          Database Hostname or IP Address(DEFAULT:): host_name_or_IP_address
          Port (DEFAULT: 50000): port
          Database User ID: database_user_ID 
          Database password: database_password
          Confirm password: database_password
          Database Path (Default: <default>):  default_path

          where:

          scr_database_name

          The name of the SCR database in the create_scr_db.sql file that you used in step 2.m. The default is SCR32.

          port

          The port number that you specified in step 2e.

          database_user_id

          The user ID is usually itmuser, although the external_db2_instance user ID can be used.

          default_path

          The file system where the database is written. The default is the user's home directory. If you modified the scripts in step 2k to use a different path, you must specify the same file system in the default_path.

          Note: If the default name is not used in step 2.l, update the database name.
       8. At the prompt Does the specified user have SYSCTRL or SYSADM authority?, enter 2 "No" if itmuser was specified or 1 "Yes" if the external_db2_instance user was specified.
       9. Enter 2 to specify not to encrypt the database.
       10. If the database user you specified did not have SYSCTRL or SYSADM authority, then enter a database user that has this authority. Specify the external_db2_instance user name.

       Note: Continue to step 2.p.
   15. Run the setup-dbconfig-operating_system_64.bin script only once for each Db2 instance. If you already ran the setup-dbconfig-operating_system_64.bin script for this Db2 instance, when creating a new SCR database for another Cloud APM server, perform the following steps:
       1. Change directory to the path that you entered in step 3.n.iii for the Db2 instance user, for example:

          cd /home/db2apmg/tbsmdb

       2. Run the following grep commands:

          grep "DL_DBManager.ObjectURL=" 
          tools/XMLtoolkit/bin/xmltoolkitsvc.properties
          grep "TBSM.UDF.DatabaseName="  sql/tbsmudf_db.properties
          grep "TBSM.DatabaseName="      sql/tbsm_db.properties
          grep "TBSM.OnPath="            sql/tbsm_db.properties
          grep "TBSM.Port="              sql/tbsm_db.properties

          The following output is displayed:

          [db2apmg@db21056 ~]$ cd /home/db2apmg/tbsmdb
          [db2apmg@db21056 tbsmdb]$ grep "DL_DBManager.ObjectURL=" 
          tools/XMLtoolkit/bin/xmltoolkitsvc.properties                     
          DL_DBManager.ObjectURL=jdbc:db2://db21056.rtp.raleigh.ibm.com:50005/SCgmmg
          [db2apmg@db21056 tbsmdb]$ grep "TBSM.UDF.DatabaseName="  sql/tbsmudf_db.properties
          TBSM.UDF.DatabaseName=SCgmmg
          [db2apmg@db21056 tbsmdb]$ grep "TBSM.DatabaseName="      sql/tbsm_db.properties
          TBSM.DatabaseName=SCgmmg
          [db2apmg@db21056 tbsmdb]$ grep "TBSM.OnPath="            sql/tbsm_db.properties
          TBSM.OnPath= ON /db2/SCgmmg_data
          [db2apmg@db21056 tbsmdb]$ grep "TBSM.Port="              sql/tbsm_db.properties
          TBSM.Port=50005

       3. Update the files to match your port and SCR database name.
       4. Use the tbsm_db.sh command to create tables and views associated with the database:

          ./bin/tbsm_db.sh -s sc -U dbUserId -f c

          where: dbUserid is the Db2 instance user. The bufferpools and tablespaces are also created.
   16. Enter the following command to enable the DB2 scheduler that Datamart uses for aggregation and pruning.

       db2set DB2_ATS_ENABLE=YES

       For detailed information, see the db2set - DB2 profile registry command topic in the DB2 IBM Knowledge Center.
   17. Enter the following command as the external_db2_instance user for each database to adjust the buffer pool and transaction log after you determine the size of your environment.

       db2 connect to WAREHOUS
       db2 update database configuration for WAREHOUS using LOGSECOND WAREHOUS_LOGSECOND
       db2 alter bufferpool IBMDEFAULTBP IMMEDIATE size WAREHOUS_PAGE_COUNT
       db2 disconnect WAREHOUS
       db2 connect to DATAMART
       db2 alter bufferpool IBMDEFAULTBP IMMEDIATE size DATAMART_PAGE_COUNT
       db2 disconnect DATAMART
       db2 connect to SCR32
       db2 update database configuration for SCR32 using LOGSECOND SCR_LOGSECOND
       db2 alter bufferpool IBMDEFAULTBP IMMEDIATE size 5000
       db2 alter bufferpool TBSMCFG16KBP IMMEDIATE size SCR_CFG_PAGE_COUNT
       db2 alter bufferpool TBSM4KBP IMMEDIATE size 1000
       db2 alter bufferpool TBSM32KBP IMMEDIATE size 1000
       db2 alter bufferpool TBSMSCR16KBP IMMEDIATE size SCR_PAGE_COUNT
       db2 disconnect SCR32

       where:

       WAREHOUS_LOGSECOND

       The number of secondary transaction logs: 25 for a small environment; 50 for a medium environment; or 100 for a large environment.

       WAREHOUS_PAGE_COUNT

       The buffer pool page count (8-KB page sizes): 50000 for a small environment; 100000 for a medium environment; or 200000 for a large environment.

       DATAMART_PAGE_COUNT

       The buffer pool page count (8-KB page sizes): 100000 for a small environment; 200000 for a medium environment; or 300000 for a large environment.

       SCR_LOGSECOND

       The number of secondary transaction logs: 25 for a small environment; 50 for a medium environment; or 100 for a large environment.

       SCR_CFG_PAGE_COUNT

       The buffer pool page count (16-KB page sizes): 1000 for a small environment; 5000 for a medium environment; or 10000 for a large environment.

       SCR_PAGE_COUNT

       The buffer pool page count (16-KB page sizes): 30000 for a small environment, 50000 for a medium environment, and 100000 for a large environment.

       Estimate the size of your environment (small, medium, or large) from the Cloud APM server requirements table in Cloud APM server hardware requirements.

       Note: If you are not using the default database names, then replace the WAREHOUS, DATAMART, and SCR32 names with the database names you used in step 2.l.
   18. If you want to run with Db2 authentication set to server_encrypt, complete steps 1 and 2 in Changing Db2 authentication from server to server_encrypt.
3. As the root user, install the IBM Data Server Client on the system where you plan to install the Cloud APM server (the version of the Data Server Client should match the version of the Db2 server that is mentioned in the Before you begin section).

   The IBM Data Server Client requires the local Db2 Cloud APM server user to run Db2 commands. This local Db2 user name is always set to db2apm. Do not confuse this db2apm user with the remote external_db2_instance user that was created in the previous steps.

   1. Create the db2apm user and add the db2apm user to the db2iadm1 group:

      groupadd db2iadm1

      useradd -g db2iadm1 -m db2apm

   2. Create the db2fenc1 user and add the db2fenc1 user to the db2fadm1 group:

      groupadd db2fadm1

      useradd -g db2fadm1 -m db2fenc1

   3. Install the IBM Data Server Client component from the Db2 installation package, which is located in the Cloud APM server installation image : packages/DB2/v10.5fp6_linuxx64_server_t.tar.gz. Extract the package using tar -xzvf v10.5fp6_linuxx64_server_t.tar.gz and change to the server_t directory. If you are using the local version of Db2 server installed with the Cloud APM server, ensure the version of the Data Server Client matches that Db2 server version. If you are using a remote Db2 server, ensure the version of the Data Server Client matches that remote Db2 server version. Review the Db2 server versions for local and remote in the Before you begin section.
      Note: Ensure the version of the Data Server Client matches the remote Db2 server version.
   4. Start the db2setup script:

      ./db2setup

      To run the db2setup script, you must set the DISPLAY environment variable to establish a connection to an X11 server.
   5. After the Db2 Launchpad opens, select Install a Product, then select the client that you want to install and follow the prompts in the Db2 Setup wizard.
   6. Accept the license agreement.
   7. Click Typical as the installation type.
   8. In the Setup a Db2 instance window, select Create a DB2 instance.
   9. Click Existing User and enter db2apm for the user name. Alternatively, enter the following command from the command line after the installation finishes:

      /opt/ibm/db2/Db2_version/instance/db2icrt -u db2fenc1 db2apm

      where Db2_version is the Db2 version that is installed.
   10. Click Next and Finish.