Configuring the database

The CES database stores information related to the CES server.

This topic describes how to create a new database, switch to a new database, or migrate data into a CES database. In performing these tasks, you configure the database settings to generate the data needed to run CES. You can also specify an email address to receive notifications when the database terminates or restarts.

By default, CES installs and uses an Apache Derby database until you switch or migrate to a different database. You might want to switch to a different database if your database configuration has changed, such as after a driver upgrade.

Before you begin

If you are switching or migrating to a database, make sure that it already exists. Otherwise, CES prompts you to create a new database.

To create, switch, or merge to a database:

1. Navigate to Administration > Database.
2. On the Database Settings page, select one of the following Database options:
   • Product Database: Set up a full operating database.
   • SMF Archive Database: Set up a database for archiving iStrobe SMF data from the iStrobe database. Make sure that this database is set up before you archive iStrobe SMF data. The iStrobe SMF Database is only for enterprise-level databases supported by iStrobe.
3. For the DBMS Type, select one of the following options:
   • IBM Db2 for Linux, Unix and Windows
   • IBM Db2 for z/OS
   • Microsoft SQL Server
   • Oracle

   • Apache Derby

     Important

     • Apache Derby is the default setting. This option does not appear when you are configuring an SMF Archive Database.
     • If you are using an IBM Db2 for LUW database, make sure that the number of local database connections that can be concurrently active exceeds the number of active databases. Review the NUMDB parameter in the DBM configuration by using the configuration assistant utility.

4. Based on the selected DBMS Type, the UI dynamically displays relevant fields as described in the following table. Enter the required details.
   

   Field	Description	DBMS Type applicable to
   Database server	Enter the DNS name or the IP address of the server.	All
   Port	Specify the port on which the database should listen.	All
   Database name	Enter the name of the database that you want to use. This must be created in advance. CES will not create the database programmatically.	All
   Schema	Enter the name of the schema with which you would like to associate the data objects. If a schema doesn't already exist, CES creates it.	All
   Instance name	Enter the name of the SQL Server instance. If your Microsoft SQL Server database uses a port, then you do not need to enter the instance name.	Microsoft SQL Server
   Database location	Enter the location of your Db2 host.	IBM Db2 for z/OS
   JDBC driver path	Enter the JDBC driver path. The IBM Data Server Db2 Driver for JDBC and SQLJ must be installed for that Db2 subsystem. Make sure that the CES installation program running on your system can read the driver files.	IBM Db2 for Linux, Unix and Windows IBM Db2 for z/OS
   Security mode	Enter the log-on mode to use when accessing the CES database. Depending on which DBMS type you've selected, select one of the following options: For IBM Db2 for z/OS: Standard: Log on to Db2 by using a user ID and password in plain text. ID Only: Log on to Db2 by using a user ID that does not require a password. AES Encrypted Password: Log on to Db2 by using an unencrypted user ID and an AES-encrypted password. AES Encrypted ID and Password: Log on to Db2 by using AES encryption for both the user ID and password. PassTicket: Log on to Db2 by using a PassTicket generated by a PassTicket generator using IRRRACF.jar. The user ID must not have the PROTECTED attribute enabled. A PassTicket is generated based on an application ID (APPLID) and a User ID. Set the APPLID by using the required environment variable ces.passticket.db2.applid. Set the user ID by using the optional environment variable ces.passticket.db2.userid. The default is the user ID under which the CES job is running. If you provide a custom PassTicket generator, then you must also specify the provider ID by using the environment variable ces.passticket.db2.provider. The default provider name for the PassTicket generator provided by BMC (for USS only) is CES-USS-DB2. For SMF data collection or parallel (asynchronous) profile processing to work with PassTickets, CES must also have the class name and bundle name for the PassTicket generator. Enter these values in the PassTicket Class and PassTicket Bundle Name fields, which are displayed only when the security mode is set to PassTicket. When using the PassTicket generator provided by BMC (for USS only), the PassTicket Class must be set to com.compuware.ces.passticket.provider.PassTicketProvider, and the PassTicket Bundle Name must be set to com.compuware.ces.passticket.provider. Important A passticket is only applicable to CES on z/OS UNIX and is used to authenticate Db2 for z/OS. To configure a passticket, you must modify the cesemn.parmlib file. For more information, see the "Modifying BMC AMI Common Enterprise Services to accommodate PassTicket" section in Milestone 2: Installing or upgrading BMC AMI Products for Web on z/OS UNIX. For Microsoft SQL Server: (Default) None: SSL is not requested or used. Default: SSL is requested. If the server does not support it, a plain connection is used. Require: SSL is requested. If the server does not support it, an exception is thrown. Authenticate: SSL is requested. If the server does not support it, an exception is thrown and the server's certificate must be signed by a trusted CA.	IBM Db2 for z/OS Microsoft SQL Server
   Use Windows Authentication	Enable the toggle switch to use Windows authorization when logging on to the Microsoft SQL Server Database.	Microsoft SQL Server
   Database user ID	Enter the Database user ID. If the database administrator ID is not specified, then the database user ID will be used to create the tables, indexes, and views used by CES. Otherwise, the ID is for use by the CES application. The ID must have one of the following privileges: CREATETAB authority for the database; USE privilege for its table space DBADM authority for the database SYSADM authority Important Changing either the Database user ID changes the credentials only. If you have changed the user ID or the user password associated with the iStrobe database, you must also update the Database user ID and Database user password accordingly.	IBM Db2 for Linux, Unix and Windows IBM Db2 for z/OS Microsoft SQL Server Oracle
   Database user password	Enter the password for the user associated with the database. Changing either the database user password changes the credentials only. Important You can use Windows Authority for SQL server databases with CES on Windows under the following conditions: Your SQL Server is configured for Windows Authority. CES may run as either the local system (default) or a user account. If using LDAP, then CES must run as the user account.	IBM Db2 for Linux, Unix and Windows IBM Db2 for z/OS Microsoft SQL Server Oracle
   Secondary authorization ID	Specify the secondary authorization ID to be used in a SET CURRENT SQLID statement before any run of DDL. This field appears only when the database user ID or database administrator ID does not have the privileges to create the CES tables, indexes, and views. The ID must have one of the privileges listed. Important The database administrator ID is not required if the user ID and user password have sufficient authority to set up the database, including the authority to create tables, views, indexes, stored procedures, functions, and triggers.	IBM Db2 for z/OS
   Use secondary authorization ID for updating data	Select this check box to update data or insert data into the database. Otherwise, the secondary authorization ID is used only during the creation of the database.	IBM Db2 for z/OS
   Database administrator ID	Enter the administrator ID associated with this database to be used to create the tables, indexes, and views used by CES. This is required only if the database user ID does not have any of the following roles: CREATETAB authority for the database; USE privilege for its table space DBADM authority for the database SYSADM authority	IBM Db2 for Linux, Unix and Windows IBM Db2 for z/OS Microsoft SQL Server Oracle
   Database administrator password	Enter the administrator password associated with the database administrator ID.	IBM Db2 for Linux, Unix and Windows IBM Db2 for z/OS Microsoft SQL Server Oracle
   Use SSL connection to database	Select this checkbox to use a secure socket connection to the database.	IBM Db2 for z/OS
   SSL connection keystore	Enter the SSL connection keystore to identify where the key is stored.	IBM Db2 for z/OS
   Keystore password	Enter the Keystore password.	IBM Db2 for z/OS
   Notification email	Enter an email address to receive an email notification when the database terminates. If the database restarts, CES resumes functioning automatically and sends an email notification. Important To send an email to multiple recipients, enter an email address associated with a distribution list.	All

5. Click Save.

6. (When creating an IBM Db2 for z/OS database) You are prompted for the storage group name and buffer pools. The storage group name is required. The default buffer pool names are BP0, BP8K0, BP16K0, and BP32K. CES assigns tables to all four buffer pool sizes, so at least one buffer pool must exist for each size.

   Best practice
   Your storage group name and buffer pool selections are included in the DDL generated by the Generate DDL function, so we recommend that you review and, if necessary, modify the assignments before running the DDL. For more information, see Generating DDL.

7. (If the database does not exist) Select one of the following options and click Ok.
   • Create new database: Creates a new database.
   • Create and migrate existing data: Creates a new database and migrates existing data from the Derby database into a new CES database.

8. (If a database already exists) You can Switch from any CES database to a new or previously used database of a different type. If the database to which you are switching is from an older version of CES, you are prompted to upgrade.

   Best practice
   The upgrade process is not reversible, so before upgrading a database, we recommend that you make a backup. If the upgrade fails, address the cause of the failure and rerun the upgrade. The upgrade continues from the point of failure. Continue this process until the upgrade completes successfully.
   

   Important

   When upgrading a Db2 database and using both a user and administrator ID, grants run giving the user ID the following authorities:

   • For CES database tables, the authority to select, update, delete, and insert.
   • For CES database views, the authority to select.

   If grants fail, the database is still upgraded and the updated database configuration is saved in CES. You can then download the grants DDL script to manually run the grants. The grants DDL script grants authorities on the following views:

   • CICS_SERVICE_TIME_VIEW
   • DDF_SQL_ACTIVITY_EXEC_VIEW
   • DDF_SQL_ACTIVITY_TARGET_VIEW
   • FOLDERACL_VIEW
   • PRF_DBRM_SEARCH_VIEW
   • PRF_DDNAME_DSNAME_SEARCH_VIEW
   • PRF_INDEX_VIEW
   • PRF_MODULE_SEARCH_VIEW
   • PUP_SUBSYS_VIEW
   • VIP_USAGE_VIEW

Generating DDL

You can use a generated DDL to create a new database for use by CES or to upgrade an existing IBM Db2 for z/OS database used by an earlier version of CES.

To create a new database or upgrade an existing database by using a generated DDL, perform the steps in the To create, switch, or merge to a database, but also follow the guidance in the following steps:

1. Perform one of the following steps, depending on whether you are creating a new database of upgrading an existing one:

   • If you are creating a new database, click Generate DDL to save the generated DDL as a file named ddlCreationScripts.zip by default.

     Important

     If the file name is ddlUpgradeScripts.zip, CES connected to an existing database that required an upgrade, and CES generated upgrade scripts instead. If that is not intended, review your configuration before proceeding.

   • If you are upgrading an existing database, you must make sure that CES is able to connect to the database to generate upgrade DDL. Otherwise, CES generates the DDL to create a new database.
     Perform the following steps:
     1. Confirm your existing database connection settings, and enter the database user password if applicable.
        

     2. Click Generate DDL to save the generated DDL as a file named ddlCreationScripts.zip by default.
        

        Important

        If the file name is ddlCreationScripts.zip, CES could not connect to the specified database or a database upgrade was not required, and CES generated creation scripts instead. If that is not intended, review your configuration before proceeding.

2. If your DBMS Type is IBM Db2 for z/OS, the Database settings dialog box is displayed, prompting you to specify storage group and buffer pool settings. Enter the required values, and click Ok to continue.
3. After the database administrator has extracted and run the generated DDL to create or upgrade the database, access the Database Settings page in CES again to enter your database settings, and click Save.