MySQL data source configuration

Use this information to create a MySQL data source.

Table 1. General settings in the MySQL data source window
Window element	Description
Data Source Name	Enter a unique name to identify the data source. You can use only letters, numbers, and the underscore character in the data source name. If you use UTF-8 characters, make sure that the locale on the Impact Server where the data source is saved is set to the UTF-8 character encoding.
JDBC Driver Class	Select the MySQL JDBC driver class. Refer to your database server documentation for the appropriate class name.
Username	Type a valid user name with which you can access the database.
Password	Type a valid password with which you can access to the database. As you type, the characters are replaced with asterisks (*).
Maximum SQL Connection	For maximum performance set the size of the connection pool as greater than or equal to the maximum number of threads that are running in the event processor. Important: Changing the maximum connections setting in an SQL data source requires a restart of the Impact Server. For information about viewing existing thread and connection pool information, see the information in the Netcool/Impact Administration in the section Command-Line tools, Event Processor commands. See the Select PoolConfig from Service where Name='EventProcessor'; Important: In a clustered environment, the event processor configuration is not replicated between servers. You must run the Select PoolConfig from Service where Name='EventProcessor'; command on the primary and the secondary servers. Limiting the number of concurrent connections manages performance. Type or select the maximum number of connections that are allowed to the database at one time. For best performance, this number must be greater than or equal to the maximum number of event processor threads. See Configuring the Event processor service.
Database Failure Policy	Select the failover option. Available options are Fail over, Fail back, and Disable Backup. For more information about failover options, see SQL database DSA failover modes.

Table 2. Primary source settings in the MySQL data source window
Window element	Description
Host Name	Type the host name or IP address of the system where the data source is located. The default value is `localhost`.
Port	Select the port number that is used by the data source. The default number is `3306`.
Database	Type the name of the database to connect to.
Test Connection	Click to test the connection to the host to ensure that you entered the correct information. Success or failure is reported in a message box. If the host is not available at the time you create the data source, you can test it later. To test the connection at any time, from the data source list, right-click the data source and select Test Connections from the list of options. Important: If you see an error message stating that the data source cannot establish a connection to a database because a JDBC driver was not found, it means that a required JDBC driver is missing in the shared library directory. To fix this, place a licensed JDBC driver in the shared library directory and restart the server. For more information see, the SQL database DSAs chapter in the Netcool/Impact DSA Reference Guide.

Table 3. Backup source settings in the MySQL data source window
Window element	Description
Host Name	Type the host name or IP address of the system where the backup data source is located. Optional. The default value is `localhost`.
Port	Select a port number that is used by the backup data source. Optional. The default value is `3306`.
Database	Type the name of the database to connect to.
Test Connection	Click to test the connection to the host to ensure that you entered the correct information. Success or failure is reported in a message box. If the host is not available at the time you create the data source, you can test it later. To test the connection at any time, from the data source list, right-click the data source and select Test Connections from the list of options.

Note: From Fix Pack 23 onwards, a datasource of type MySQL with the MySQL 8 JDBC Driver in the $IMPACT_HOME/dsalib directory can be created. To do this, use the following steps:

Create a SQL datasource of type MySQL.
Go to $IMPACT_HOME/etc and manually edit the datasource file for the MySQL datasource that you created.
Example in the file NCI_XXX.ds

Change the JDBCDRIVER property from:
```
XXX.MySQL.JDBCDRIVER=org.gjt.mm.mysql.Driver
```
to:
```
XXX.MySQLJDBCDRIVER=com.mysql.jdbc.Driver
```

Restart the Impact server.

$IMPACT_HOME/bin/stopImpactServer.sh
$IMPACT_HOME/bin/startImpactServer.sh

Click on Test connect and confirm a connection can be made.

Example before and after of the MySQL data source file:

#This file was written by server.
#Tue Aug 31 03:57:15 PDT 2021
mySQLDataSource.MySQL.PRIMARYPORT=3306
mySQLDataSource.MySQL.JDBCDRIVER=org.gjt.mm.mysql.Driver
mySQLDataSource.MySQL.BACKUPPORT=3306
mySQLDataSource.MySQL.PRIMARYDATABASE=test
mySQLDataSource.MySQL.FAILOVERPOLICY=FAILOVER
mySQLDataSource.MySQL.DBUSERNAME=tester
mySQLDataSource.MySQL.DISABLEFAILOVER=false
mySQLDataSource.MySQL.DBPASSWORD=XXX
mySQLDataSource.MySQL.PRIMARYHOST=XXX1.xxx.ibm.com
mySQLDataSource.MySQL.BACKUPDATABASE=test
mySQLDataSource.MySQL.MAXSQLCONNECTION=5
mySQLDataSource.MySQL.BACKUPHOST=XXX1.xxx.ibm.com

#This file was written by server.
#Tue Aug 31 03:57:15 PDT 2021
mySQLDataSource.MySQL.PRIMARYPORT=3306
mySQLDataSource.MySQL.JDBCDRIVER=com.mysql.jdbc.Driver
mySQLDataSource.MySQL.BACKUPPORT=3306
mySQLDataSource.MySQL.PRIMARYDATABASE=test
mySQLDataSource.MySQL.FAILOVERPOLICY=FAILOVER
mySQLDataSource.MySQL.DBUSERNAME=tester
mySQLDataSource.MySQL.DISABLEFAILOVER=false
mySQLDataSource.MySQL.DBPASSWORD=XXX
mySQLDataSource.MySQL.PRIMARYHOST=XXX1.xxx.ibm.com
mySQLDataSource.MySQL.BACKUPDATABASE=test
mySQLDataSource.MySQL.MAXSQLCONNECTION=5
mySQLDataSource.MySQL.BACKUPHOST=XXX1.xxx.ibm.com

Note:

The required JDBC driver that Impact uses to connect to MySQL server is known as the Connector/J. This is the jar file that needs to be loaded into $IMPACT_HOME/dsalib.

If the MySQL server is configured to use SSL, secure connections from Impact can be made. Secure connections can be achieved by setting additional connection properties. These properties can be set in the Database field in the MySQL Data Source Editor.

For versions 8.0.12 and earlier of Connector/J: Add properties: ?allowPublicKeyRetrieval=true&requireSSL=true

For example:

Database: nameOfDatabase?allowPublicKeyRetrieval=true&requireSSL=true

For versions 8.0.13 to 8.0.18 of Connector/J: Add properties: ?allowPublicKeyRetrieval=true&sslMode=REQUIRED

For example:

Database: nameOfDatabase?allowPublicKeyRetrieval=true&sslMode=REQUIRED

For later versions of Connector/J, please refer to the mySQL documentation regarding required connection properties.