+

Search Tips   |   Advanced Search

AIX stand-alone: Configure Type 2 DB2 drivers


This topic contains the instructions for a Type 2 configuration when the instructions differ from the Type 4 instructions. For general prerequisites and notes for each section, see the Type 4 version of topics using the same titles as the section headers. All topics in the DB2 section that are not in the Type 2 driver support section, are for a Type 4 configuration.

Install DB2

  1. Install DB2 or DB2 client.

  2. If DB2 is installed on another system than WebSphere Portal, ensure the appropriate DB2 client is installed on the same system as WebSphere Portal and have the same name as the server profile name.

  3. On the DB2 system, verify the DB2 instance port was added to the services file during the DB2 installation.

    • Edit /etc/services

    • Ensure that...

        DB2_db2inst1 50000/tcp

      ...where db2inst1 is the DB2 instance name, is in the services file. If you do not see DB2_db2inst1 50000/tcp in the services file, add this entry to the services file.

      Ensure that the port number used is not in use. If the port number is already in use, select a different port number.

  4. Configure your DB2 client with the following commands. If we are using a remote database, complete this step separately from the WebSphere Portal installation.

      db2 update dbm cfg using tp_mon_name WAS

      db2 update dbm cfg using spm_name hostname, where hostname is the host name of WebSphere Portal.

    Because the default for spm_name is the hostname itself, specifying the hostname parameter is optional. If the hostname is more than eight characters, use empty double quotes (" ").

    For example, db2 update dbm cfg using spm_name " ".

Modify database properties

  1. Make backup copies of the following files:

    • WP_PROFILE/ConfigEngine/properties/wkplc.properties

    • WP_PROFILE/ConfigEngine/properties/wkplc_dbdomain.properties

    • WP_PROFILE/ConfigEngine/properties/wkplc_dbtype.properties

    • If we are transferring from a database other than Derby:

      WP_PROFILE/ConfigEngine/properties/wkplc_sourceDb.properties

    Default values are listed in these files. Unless otherwise noted, all values are of type alphanumeric text string. Set the appropriate values for each instance of each property. In wkplc_dbdomain.properties, most properties are repeated for each domain.

  2. Set properties in wkplc_dbdomain.properties

    1. dbdomain.DbType=db2

    2. dbdomain.DbName=domain_db_name DB2 database names cannot exceed eight (8) characters.

      • This value is also the database element in dbdomain.DbUrl.
      • TCP-IP alias for the database.

    3. dbdomain.DbSchema=domain_schema_name

      Some database management systems have schema name restrictions.

    4. dbdomain.DataSourceName=data_source_name

      Do not use the following reserved words:

      • releaseDS
      • communityDS
      • customizationDS
      • jcrDS
      • lmdbDS
      • feedback

    5. dbdomain.DbUrl=JDBC_DB_URL

      The value must conform to the JDBC URL syntax specified by the database.

      The database element of this value should match the value of DbName.

    6. For dbdomain.DbUser, set the user ID for the database configuration user.

    7. For dbdomain.DbPassword, set the password for the database configuration user.

    8. For dbdomain.DbConfigRoleName, set the name of the group for database configuration users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbUser must be assigned to this group.

    9. Optional: For dbdomain.DbRuntimeUser, set the user ID of the database user that should be used by WebSphere Portal to connect to the database at runtime. If no value is specified for this setting, the database configuration user will be used to connect to the databases at runtime.

    10. If dbdomain.DbRuntimeUser is specified, set dbdomain.DbRuntimePassword to be the password of the runtime database user.

    11. For dbdomain.DbRuntimeRoleName, set the name of the group for database runtime users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbRuntimeUser must be assigned to this group.

    12. Optional: For dbdomain.DBA.DbUser, type the database administrator user ID for privileged access operations during database creation and setup. Required.if you run the create-database and setup-database ConfigEngine tasks . If you do not need this parameter, we can either accept the default value or leave blank.

    13. Optional: For dbdomain.DBA.DbPassword, set the database administrator password for privileged access operations during database creation. If you do not need this parameter, we can either accept the default value or leave blank.

    14. For dbdomain.XDbName, type the database loop back alias that needs to be set if you plan to use the create-database task.

    15. For dbdomain.DbNode, type the value for the database node name. Set this value to call create-database.

      Required only for local databases.

  3. Save and close the file.

  4. Update the following properties in wkplc_dbtype.properties.

    1. For db2.DbDriver, set the name of the JDBC driver class.

    2. For db2.DbLibrary, type the directory and name of the .zip or .jar file containing the JDBC driver class.

    3. For db2.JdbcProviderName, set the name of the JDBC provided that WebSphere Portal uses to communicate with its databases.

  5. Save and close the file.

  6. Update the WasPassword value in wkplc.properties. This value is the password for the WAS security authentication used in the environment.

  7. Save and close the file.

Create a remote or local DB2 database manually

  1. Log in as a DB2 instance system authority.

    For example, we can log in as db2inst1 as the DB2 instance owner.

  2. Initialize a DB2 command environment.

    For example, execute...

      . /home/db2inst1/sqllib/db2profile

    db2inst1 is the DB2 instance owner of the DB2 instance.

  3. To configure the DB2 database instance: 

    db2set DB2COMM=TCPIP
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_INLIST_TO_NLJN=YES
db2 "UPDATE DBM CFG USING query_heap_sz 32768"
db2 "UPDATE DBM CFG USING sheapthres 0"

  4. Create databases:

    • Replace dbname with the actual name of the database. Run the commands and each time replace dbname with the actual values for release, community, customization, Java Content Repository, Feedback, and Likeminds. You will need to run the commands once for each database for a total of six times.

      DB2 database names cannot exceed eight characters. Therefore, consider using these database names: release, commun, custom, jcrdb, fdbkdb, and lmdb. 

    db2 "CREATE DB dbname using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR dbname USING applheapsz 4096"
db2 "UPDATE DB CFG FOR dbname USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR dbname USING stmtheap 32768"
db2 "UPDATE DB CFG FOR dbname USING dbheap 2400"
db2 "UPDATE DB CFG FOR dbname USING locklist 1000"
db2 "UPDATE DB CFG FOR dbname USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR dbname USING logprimary 12"
db2 "UPDATE DB CFG FOR dbname USING logsecond 20"
db2 "UPDATE DB CFG FOR dbname USING logbufsz 32"
db2 "UPDATE DB CFG FOR dbname USING avg_appls 5"
db2 "UPDATE DB CFG FOR dbname USING locktimeout 30"
db2 "UPDATE DB CFG FOR dbname using AUTO_MAINT off"

The following steps are only required for the IBM Java Content Repository database.

  1. On the DB2 server systems to create bufferpools and table spaces for the IBM Java Content Repository database (jcrdb).

    • jcrdb is the name of the database used to store user data and objects
    • jcr is the jcr user for jcrdb

      This value can be replaced with any Ithat has administrative authority.

    • dbpassword is the password for the jcr user for the jcrdb

    The commands in this step are different for DB2 and DB2 pureScale. Run the appropriate commands for the environment.

      DB2

      db2 "CONNECT TO jcrdb USER jcr USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('icmlssystspace32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlssystspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K MANAGED BY SYSTEM USING ('icmlsusrtspace4') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

      DB2 pureScale

      db2 "CONNECT TO jcrdb USER jcr USING dbpassword"
db2 "CREATE BUFFERPOOL ICMLSFREQBP4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSVOLATILEBP4 SIZE 16000 PAGESIZE 4 K"
db2 "CREATE BUFFERPOOL ICMLSMAINBP32 SIZE 16000 PAGESIZE 32 K"
db2 "CREATE BUFFERPOOL CMBMAIN4 SIZE 1000 PAGESIZE 4 K"
db2 "CREATE REGULAR TABLESPACE ICMLFQ32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLFQ32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMLNF32 PAGESIZE 32 K MANAGED BY SYSTEM USING ('ICMLNF32') BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE REGULAR TABLESPACE ICMVFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMVFQ04') BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE REGULAR TABLESPACE ICMSFQ04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('ICMSFQ04') BUFFERPOOL ICMLSFREQBP4"
db2 "CREATE REGULAR TABLESPACE CMBINV04 PAGESIZE 4 K MANAGED BY SYSTEM USING ('CMBINV04') BUFFERPOOL CMBMAIN4"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE32 PAGESIZE 32 K BUFFERPOOL ICMLSMAINBP32"
db2 "CREATE SYSTEM TEMPORARY TABLESPACE ICMLSSYSTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
db2 "CREATE USER TEMPORARY TABLESPACE ICMLSUSRTSPACE4 PAGESIZE 4 K BUFFERPOOL ICMLSVOLATILEBP4"
db2 "UPDATE DB CFG FOR jcrdb USING DFT_QUERYOPT 2"
db2 "UPDATE DB CFG FOR jcrdb USING PCKCACHESZ 16384"
db2 "DISCONNECT jcrdb"
db2 "TERMINATE"

  2. On the DB2 client, edit /etc/services file. If it does not specify the DB2 connection service port, add the following text to specify the port for the remote DB2 instance:

      DB2_db2inst1 port1/tcp # DB2 connection service port

    ...where db2inst1 is the name of the DB2 instance on the system, and port1 with the actual port number assigned to the DB2 connection service in the DB2 server installation . The connection service port on the DB2 Client system, WebSphere Portal server, must match the connection service port on the DB2 server. The ports should match by number but not necessarily by name.

  3. Set up the correct service name by entering the following command on the DB2 server system: db2 "UPDATE DBM CFG USING svcename svce_name" where svce_name is the connection service port name specified previously.

  4. On the DB2 client, set DB2COMM to TCP/IP using the db2set command db2set DB2COMM=tcpip.

  5. Catalog the TCP/IP node with the IP address of the remote database server on DB2 Connect: db2 "catalog tcpip node remote_db_node_alias remote database_server_node server connection_service_port" where:

    • remote_db_node_alias is the alias name of the database server that we are defining for the WAS node name. The alias name can contain one to eight characters.
    • database_server_node is the fully qualified host name of the database server system.

    • connection_service_port is the name of the DB2 connection service port configured in the /etc/services file on the database server system.

  6. Catalog the WebSphere Portal databases on DB2 Connect, where:

    • remote_db_name_domain, is the cataloged name of the databases on the server system for each domain.

    • domain_alias_name, is the database alias names that we are defining.

    • remote_db_node_alias is the name that was used previously when you cataloged the TCP/IP node in the previous step.

    The alias for each database must be different from the actual database name and can only contain up to eight characters. 

    db2 "catalog db remote_db_name_release as release_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_community as comm_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_customization as cust_alias_name at node remote_db_node_alias"

db2 "catalog db remote_db_name_fdbkdb as fdbkdb_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_lmdb as lmdb_alias_name at node remote_db_node_alias"
db2 "catalog db remote_db_name_jcrdb as jcrdb_alias_name at node remote_db_node_alias"

  7. Log out of DB2 Connect by entering db2 "terminate".

  8. On DB2 Connect, test the remote connection by issuing the following command in the DB2 command window: db2 "connect to alias_name user username using password", where alias_name is the alias name definedd previously, username is the database user, and password is the password assigned to the database user.

Configure a portal to use the database

  1. Edit the db2cli.ini file that resides on the local system, where WebSphere Portal is installed, before you transfer data.

    Important: The database transfer becomes unresponsive at task action-process-constraints if you do not complete these steps.

    1. Locate the file /home/db2inst1/sqllib/cfg/db2cli.ini.

    2. Add the following lines to the end of the file:

      Edit db2cli.ini:

      • If a section named [COMMON] already exists in the file, extent that section by adding the following lines. Otherwise, add a [COMMON] section to the file.

      • Leave an empty line after ReturnAliases=0. 

      [COMMON]
DYNAMIC=1
ReturnAliases=0

  2. If we are installing multiple instances of WebSphere Portal increase the default configured number of databases.

    For example:

      set client MAX_NETBIOS_CONNECTIONS 254

    A message indicates success if the number was increased.

  3. cd WP_PROFILE/ConfigEngine

  4. Validate configuration properties...

      ./ConfigEngine.sh validate-database -DWasPassword=foo

    Add -DTransferDomainList to the validating task to specify the domains to validate; for example...

      -DTransferDomainList=jcr

    To validate all domains, do not specify this parameter.

  5. cd WP_PROFILE/bin

  6. Stop the WebSphere_Portal server:

      ./stopServer.sh WebSphere_Portal -username wpadmin -password foo

  7. Transfer the database:

    Important: Do not execute the database-transfer task as a background process. This might cause the task to stall.

    1. cd WP_PROFILE/ConfigEngine

      ./ConfigEngine.sh database-transfer -DWasPassword=foo

      • Set -DTransferDomainList to include only the domains to transfer.

        For example, to transfer only the JCR domain...

          ./ConfigEngine.sh database-transfer -DTransferDomainList=jcr -DWasPassword=foo

      • If you have been storing data in Apache Derby for a long time, database transfer could fail with OutOfMemory exceptions. To fix:

          ConfigEngine.bat database-transfer -DDbtJavaMaxMemory=1536M -DWasPassword=foo

    2. Log output is written to:

        WP_PROFILE/ConfigEngine/log/ConfigTrace.log

      If the configuration fails, verify the values in the wkplc.properties, wkplc_dbdomain.properties, and wkplc_dbtype.properties files and then repeat this step.

  8. If dbdomain.DbRuntimeUser is set that user must have sufficient database user privileges:

    1. To manually grant database user privileges:

      1. Copy the appropriate template files to a work directory:

        • createRuntimeRoleForDifferentSchema.sql

          If the name of the database user and the schema name are not the same.

        • createRuntimeRoleForSameSchema.sql

          If the name of the database user and the schema name are the same.

        Files are in the following directories, where dbms is the database management system, and domain represents the database domains we are configuring (release, customization, community, jcr, feedback, and likeminds):

        • PORTAL_HOME/base/wp.db.impl/config/templates/setupdb/dbms/domain

        • PORTAL_HOME/pzn/prereq.pzn/config/templates/setupdb/dbms/domain

        JCR database domain: For the JCR database domain, copy grantPermissionsToRuntimeRoleStatic.sql from the following directory:

        PORTAL_HOME/jcr/wp.content.repository.install/config/templates/setupdb/dbms/jcr/grantPermissionsToRuntimeRoleStatic.sql .

      2. Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.

      3. Execute the SQL statements.

    2. Grant database user privileges:

      1. Ensure the database administrator user ID is specified for domain.DBA.DbUser in...

          WP_PROFILE/ConfigEngine/properties/wkplc_dbdomain.properties

        For example, domain.DBA.DbUser=dbadmin.

        2. ./ConfigEngine.sh grant-runtime-db-user-privileges -DTransferDomainList=release,customization,community,etc...

        You only need to add -DTransferDomainList when granting privileges across specific domains.

  9. Perform a reorg check to improve performance. Perform this step for each database in the property file.

    1. Connect to a database:

        db2 connect to dbName user db2wpadmin using password

      Additional options might be required if additional security has been installed. Refer to DB2 Universal Database™ commands by example .

    2. Run the following commands from the DB2 prompt:

      db2 reorgchk update statistics on table all > xyz.out

    3. Look in the reorg column for entries marked with a star or asterisk * in the file xyz.out.

      1. For each line with *, note the tablename and run the following command for each tablename:

          db2 reorg table tablename

      2. After you have run the reorg command for each tablenames:

          db2 terminate

          db2rbind database_name -l db2rbind.out -u db2_admin -p password

    4. The file db2rbind.out is created when there is an error.

  10. cd WP_PROFILE/bin

  11. Start the WebSphere Portal server.


Parent: AIX stand-alone: Type 2 driver support