Move the Business Analytics repository to MySQL

1. If you are using your LDAP Directory as the Business Analytics User Repository, make sure that at least one user in your LDAP Directory has administrator privileges for Business Analytics before you move the Business Analytics Repository. See Grant User Access to Business Analytics with Built-in Groups for instructions.

When the Business Analytics User Repository is your LDAP Directory, the default administrator account (Administrator user) is disabled.

2. If you are hosting the Business Analytics Repository in a new database, create the database following MySQL documentation. Keep the following points in mind:

Make sure this database is supported by Business Analytics. See for details.

If you want Business Analytics to support international characters in meta-data for artifacts, set the character encoding and collation to UTF-8 when you create the database. See documentation for your database for specific instructions.

For medium or larger MySQL databases that will host the Business Analytics Repository, you should increase the maximum allowed packet size, which defaults to 1MB, for the database.

3. Start the database that will become host to the Business Analytics Repository, if it is not already up.

4. Using the SQL tool for the database that will be host, add Business Analytics Repository tables with the scripts shown below from the corresponding folder in MashZoneNG-install/prestorepository/mysql:

a. Create a Database to hold the Business Analytics Repository tables. See the file createDB.txt for an example.

b. Create a User with rights to the database created in step a. See the file createUser.txt for an example.

c. Connect to the MySQL database created in step a using a SQL tool (for example MySQL command line client) using the user created in step b. See the comments in the file createDBTables.txt for examples.

d. Execute the statements in the file createDBTables.txt to create the tables using the SQL tool (for example, use the MySQL source command: "source /path/to/createDBTables.txt").

e. Execute the statements in the file createSchedulerTables.sql (for example: "source /path/to/createSchedulerTables.sql").

f. Execute the statements in the file createSnapsTables.sql (for example: "source /path/to/createSnapsTables.sql").

This folder also contains scripts to drop the corresponding Business Analytics Repository tables, if needed.

a. Remove the MashZoneNG-install/apache-tomee-jaxrs/presto/WEB-INF/lib/jackbe-presto-rds-postgresql-derby.jar JAR file for each Business Analytics Server that uses this Business Analytics Repository. You can delete this JAR or simply move it to a folder that is not in the classpath for the application server that hosts Business Analytics.

b. Copy this JAR file:

MashZoneNG-install/prestorepository/jackbe-presto-rds-oracle-mysql-mssql.jar

6. Copy the MySQL JDBC driver jar file to MashZoneNG-install/apache-tomee-jaxrs/lib.

7. Open the MashZoneNG-install/apache-tomee-jaxrs/conf/tomee.xml configuration file in the text editor of your choice.

8. For the Business Analytics Repository, edit the <Resource> element with an ID of MashzoneNextGenRepository and:

a. Update the JDBC driver, URL and credential properties:

<Resource id="MashzoneNextGenRepository" type="DataSource">
JdbcDriver com.mysql.jdbc.Driver
JdbcUrl jdbc:mysql://host-name/databasename
UserName = username
Password = password
JtaManaged = false
</Resource>

For MySQL databases, it is recommended that you include the database name in data source URLs. If this information is omitted, testing the data source fails and may also cause errors with access to stored procedures.

b. If needed, update optional properties. See TomEE Datasource Properties for a complete list of optional properties and information on defaults.

9. For the MashZone Repository, edit the <Resource> element with an ID of amzDatabase and:

a. Update the following properties:

<Resource id="amzDatabase" type="DataSource">
JdbcDriver com.mysql.jdbc.Driver
JdbcUrl jdbc:mysql://host-name:port/databasename
UserName = username
Password = password
defaultAutoCommit = true
maxActive = 200
maxIdle = 20
maxOpenPreparedStatements = 0
maxWaitTime = 5000
</Resource>

b. If needed, update optional properties. See TomEE Datasource Properties for a complete list of optional properties and information on defaults.

10. Open the rdsJDBC.properties configuration file, from the web-apps-home/presto/WEB-INF/classes folder, in the text editor of your choice and:

a. Comment out the property definitions for the Derby database.

b. Uncomment the properties for MySQL and update these properties to match the JNDI configuration your previously updated for TomEE.

c. Save your changes to this file.

You must keep JDBC and JNDI configuration synchronized for the Business Analytics Repository. The application server is using JNDI to connect to the Business Analytics Repository, but some components still use JDBC information.

11. Start the Business Analytics Server to apply these changes. This also starts the MashZone Server.

If the Business Analytics Server does not start up successfully, see Troubleshooting Connections to the Business Analytics Repository for suggestions.

a. Open Business Analytics Hub and login.

b. Add a JDBC driver for the new database that should host the Snapshots Repository. See Add or Manage JDBC Drivers for instructions on adding JDBC drivers.

c. Expand the JDBC Configuration menu, if needed, and select Datasources.

e. Update configuration to point to the new database. See Add a Data Source for information on specific configuration properties.

14. Load macros required for the Snapshot feature in Business Analytics:

a. Open a command or terminal window and move to the MashZoneNG-install/presto-cli/bin folder.

b. Enter the appropriate command, shown below, for your operating system:

For Windows	For Linux, OS/X or UNIX
publish-global-macros.bat -u Administrator -p manage -url http://app-server:port/presto/edge/api	./publish-global-macros -u Administrator -p manage -url http://app-server:port/presto/edge/api