BMC AMI Common Enterprise Services Database Configuration

This worksheet is intended to help database administrators prepare a new database for BMC AMI Common Enterprise Services. BMC AMI Common Enterprise Services supports the following databases:

• Db2 z/OS
• Db2 Linux, UNIX, and Windows
  • BMC requires a Unicode code page
• Microsoft SQL Server
  • When installing SQL Server, it is recommended to install with case-sensitive collation.
  • Customers using an edition of SQL Server for Strobe that supports compression may wish to add compression to several SMF data related tables in the database to reduce storage requirements.
    • A script is included in the directory CES/DDL Scripts/sqlserver called compress.sql. This script can be edited to set the server, database name and schema, and then executed using sqlcmd to modify the table definitions.
    • Alternatively, the script can be used as a guide to add compression to the target tables using SQL Server Management Studio.
• Oracle
  • BMC AMI Common Enterprise Services uses national character data types. Using a database with a Unicode character set is recommended, but not required.
  • BMC AMI Common Enterprise Services uses the JDBC Thin (type 4) driver with Thin-style server name syntax for its database URLs. You must know your database's service name for this syntax.
  • Your Oracle DBA must change the default value of the open_cursors parameter to at least 400 to avoid the "Maximum open cursors exceeded" error message.
  • Customers using Oracle for Strobe that has the Oracle Advanced Compression option may wish to consider adding compression to several SMF related tables to reduce storage requirements. We recommend adding data compression to the following tables:
    • SMF_DB2_DB_STATS_QALL
    • SMF_DB2_DB_STATS_QBST
    • SMF_DB2_SYSTEM_STATISTICS
    • SMF_HARD_CAP_REP_STATS
    • SMF_JOB_STEP_INTERVAL
    • SMF_JOB_STEP_TERMINATION
    • SMF_JOB_TERMINATION
• MongoDB
  • The MongoDB is used exclusively with Total Test.

Database server

The DNS name or IP address of your database server.

Db2 z/OS

Look for “DOMAIN”, by browsing the ssidMSTR job and doing a find on DSNL004I.

The ssid see the Db2 subsystem id. ssidMSTR is a required Db2 address space that must be running for Db2 to be operational.  DSNL004I is a message generated by Db2 that can be found JES log output of ssidMSTR.

Port

Port on which the database is listening.

Db2 z/OS

Look for “TCPPORT”, by browsing the ssidMSTR job and doing a find on ‘DSNL004I’.

Instance name (SQL Server)

The named instance of SQL Server can be used instead of a port.

Database location (Db2 z/OS)

Location name of your Db2 host. Look for “LOCATION”, by browsing the ssidMSTR job and doing a find on ‘DSNL004I’.

Database name

The name of an existing database in your database server.

Db2 z/OS and Db2 LUW

The database must use a 32k bufferpool.

SQL

• Limit this name to alphabetic, numeric, and underscore characters.
• Strobe does not support delimited identifiers.
• Microsoft recommends that you back up the master database before creating a new database.
• Because BMC AMI Common Enterprise Services connects through JDBC, Microsoft SQL Server authentication must be used.
  • Windows installation may also use Windows Authentication.

Schema

The schema you would like to associate the data objects with. This will get created if it does not already exist.

Db2 z/OS

This is the Creator/Schema Name to use for the BMC AMI Common Enterprise Services Db2 tables, indexes and views. If this name is different from the "Database user ID" then the "Schema" should be set up as a secondary authorization ID to the "Database user ID". We recommend keeping the Schema and the Database user ID the same.

Oracle

This will be the same as the name used for the CREATE USER command, this must be created prior to configuring the BMC AMI Common Enterprise Services database.

JDBC Driver Path (Db2 z/OS, Db2 LUW)

The IBM Data Server Db2 Driver for JDBC and SQLJ must be installed and the packages bound for that Db2 subsystem. Be sure that the driver files can be read by the BMC AMI Common Enterprise Services installation program running on z/OS UNIX.

For additional information, see “Db2 for z/OS Application Programming space and Reference for Java” in the IBM Knowledge Center for your release of Db2.

If you encounter the following error after installing or upgrading CES, verify the version of your JDBC driver and ensure that it is up to date:

Invalid parameter: Unknown column name seqschema. ERRORCODE=-4460

Security mode

Specifies the log on mode to use when accessing the BMC AMI Common Enterprise Services database.

• Request: SSL is requested. If the server does not support it, then a plain text connection is used.
• Require: SSL is requested. If the server does not support it, then an exception is thrown.
• Authenticate: SSL is requested. If the server does not support it, then an exception is thrown and the server’s certificate must be signed by a trusted CA.

Database user ID

This ID is used to create the BMC AMI Common Enterprise Services tables, indexes, views, triggers, stored procedures, and functions.

Db2 z/OS

This ID is required and is used by BMC AMI Common Enterprise Services to run the applications.

This ID will be used when the Database administrator ID below is not specified. To create the objects, Db2 will require that the ID has the following minimum Db2 authorizations:

• CREATETAB authority for the database; USE privilege for its table spaces.

• DBADM authority for the database.

  GRANT USE OF BUFFERPOOL “default-bufferpool-for-user-data”
  TO “Database user ID”;
  GRANT USE OF STOGROUP “user-specified-storage-group(see database create)”
  TO “Database user ID”;
  GRANT DBADM ON DATABASE "user-specified-database-name" TO "database user ID";
  GRANT BINDADD TO “Database user ID”;
  GRANT CREATEIN ON SCHEMA “Schema-Name” TO “Database user ID”;
  GRANT CREATE IN COLLECTION “Schema-Name” TO “Database user ID”;
  GRANT SELECT ON TABLE “SYSIBM”.”SYSSEQUENCES” TO “Database user ID”;
  GRANT SELECT ON TABLE “SYSIBM”.”SYSDATABASE” TO “Database user ID”;
  GRANT SELECT ON TABLE “SYSIBM”.”SYSTABLESPACE” TO “Database user ID”;

  Important

  If the Schema Name is something other than the Database user ID, then the “Schema” should be set up as a secondary authorization ID to the “Database user ID”.

Database password

The user’s password associated with the database.

Database administrator ID

The Administrator ID is optional. When specified, it is used to create the BMC AMI Common Enterprise Services tables, indexes, views, triggers, stored procedures, and functions. This ID and password may be removed after the installation of BMC AMI Common Enterprise Services is complete, by accessing the installation panel and blanking out the data.

Db2 z/OS

Required if the User ID does not have one of the following roles:

• CREATETAB authority for the database; USE privilege for its table spaces.

• DBADM authority for the database.

  GRANT USE OF BUFFERPOOL “default-bufferpool-for-user-data”
  TO “Database administrator ID”;
  GRANT USE OF STOGROUP “user-specified-storage-group(see database create)”
  TO “Database administrator ID”;
  GRANT DBADM ON DATABASE "user-specified-database-name"
  TO "database administrator ID";
  GRANT BINDADD TO “Database administrator ID”;
  GRANT CREATEIN ON SCHEMA “Schema-Name”
  TO “Database administrator ID”;
  GRANT CREATE IN COLLECTION “Schema-Name”
  TO “Database administrator ID”;
  GRANT SELECT ON TABLE “SYSIBM”.”SYSSEQUENCES”
  TO “Database administrator ID”;
  GRANT SELECT ON TABLE “SYSIBM”.”SYSDATABASE”
  TO “Database administrator ID”;
  GRANT SELECT ON TABLE “SYSIBM”.”SYSTABLESPACE”
  TO “Database administrator ID”;

Db2 Linux, UNIX, Windows

Required if the User ID does not have one of the following roles:

• CREATETAB authority for the database; USE privilege for its table spaces.
• DBADM authority for the database.

Microsoft SQL

Required if the Database User ID does not have the sysadmin role.

Oracle

Required if the Database User ID does not have the following roles:

• CREATE SESSION system privilege.
• CREATE TABLE or CREATE ANY TABLE system privilege.
• CREATE VIEW or CREATE ANY VIEW system privilege.
• CREATE ANY PROCEDURE system privilege.
• Space quota on the database table spaces or the UNLIMITED TABLESPACE system privilege.

Database administrator password

The password associated with the Database Administrator ID.

Sample create database DDL (Db2 z/OS)

Table/view grant (Db2 z/OS)

The DDL for the GRANTs to the Tables and Views is found in the following location: data/DDL Scripts/grants.sql

You may be required to use this to give authorizations to specific authorization users/groups. Edit the file and change 3 items:

• SQLID ('XX')
• Schema ($DB_SCHEMA$)
• Authorization IDs ($SQLID$)

The modified SQL can then be executed in SPUFI or in Batch.