MySQL Repository Set Up

In this section:

MySQL is a free open source database server that you can use for the WebFOCUS Repository. Official information on MySQL is available at:

http://www.mysql.com/

This section is provided to help those less familiar with MySQL with the installation and configuration of MySQL for use with WebFOCUS.


Top of page

x
Installing MySQL

You can install MySQL as follows:

  1. Download the MySQL installation program from:

    http://www.mysql.com/

    The following page contains links to download MySQL:

    http://dev.mysql.com/downloads/mysql/5.0.html

    The standard download is sufficient.

    The following page includes documentation on installing, configuring, administering, and using MySQL:

    http://dev.mysql.com/doc/mysql/en/index.html

  2. For most platforms, you can download a .tar.gz file and then decompress and extract it to a directory on your system. Then, you need to navigate to the MySQL directory you extracted and execute:
    scripts/mysql_install_db --user=mysql

    Be sure to set a password for the root account.

    Refer to the MySQL documentation for assistance.


Top of page

x
Increasing the max_allowed_packet Parameter Value

When you configure or migrate a MySQL repository for WebFOCUS 8.0, you must increase the size of the max_allowed_packet parameter. MySQL recommends that the value of the max_allowed_packet parameter for MySQL Client and MySQL Server should be increased for applications that use of binary large objects (BLOBs) and character large objects (CLOBs), such as WebFOCUS.

For more information on this topic and how to change the value of the max_allowed_packet parameter for MySQL Client and MySQL Server, see the following website:

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html


Top of page

x
Running MySQL

You can start the MySQL daemon as follows:

  1. Navigate to the directory where you installed MySQL.
  2. Type the following:
    ./bin/mysqld_safe &

    You can stop MySQL by running the following:

    ./bin/mysqladmin -p -u root shutdown

Top of page

x
Administering MySQL

You administer MySQL using a tool that you can launch from the shell:

  1. Navigate to the MySQL bin directory.
  2. Type the following:
    mysql -h localhost -u root -p
  3. Provide the password when prompted.

The following prompt should appear:

mysql>

From this prompt, you can execute SQL commands and administer the database server.

MySQL documentation is available online at the following website:

http://dev.mysql.com/doc/mysql/en/index.html


Top of page

x
Creating the WebFOCUS Reporting Database and User

How to:

You can use the MySQL command line tool to create a database and user for ReportCaster.



x
Procedure: How to Create a MySQL Database and User
  1. Open and log on to the MySQL Command Line Tool.
  2. At the mysql> prompt, type the following to create a new empty database for WebFOCUS:
    CREATE DATABASE webfocus8;

    where:

    webfocus8

    Is the name of the database you will use for WebFOCUS repository. This is case-sensitive.

    You should receive a response like the following:

    Query OK, 1 row affected (0.03 sec)
  3. Optionally, confirm that the database was created by typing the following command at the mysql> prompt:
    show databases;

    You should receive a response that includes your new database. For example:

    +----------+
    | Database |
    +----------+
    | mysql    |
    | webfocus8|
    | test     |
    +----------+
    3 rows in set (0.00 sec)
  4. At the mysql> prompt, type the following to create a new MySQL user ID and grant it access to the WebFOCUS database:
    GRANT ALL PRIVILEGES ON 
      wf.* TO 'wfuser'@'%'
        IDENTIFIED BY 'wfpass';

    where:

    webfocus8

    Is the name of the database you will use for WebFOCUS. This is case-sensitive in some environments.

    %

    Indicates that the database is accessible from any host. To limit which hosts can access the database, provide the host name or IP address of the machine running the WebFOCUS Client and the ReportCaster Distribution Server in place of %. If the application server is on a different machine, you will need to type the command twice to grant access from both hosts.

    webfocus8

    Is the user ID you are creating. This is case-sensitive in some environments. The user ID and password are part of MySQL and not the operating system.

    rcpass

    Is the password for the user ID. This is case-sensitive.

    If you need to change your password, you can retype the GRANT command to provide the new password. The new values will overwrite any existing password.

  5. Optionally, confirm that the user ID was added to the MySQL user table by typing the following command at the mysql> prompt:
    use mysql

    This selects the default mysql database within the MySQL Database Server.

    Ensure that the user ID you created exists and is associated with your database by typing the following command at the mysql> prompt:

    select user,host,db from db;

    This query returns all user IDs and associated host names with the databases they can access. For example:

    +--------+------+---------+
    | user   | host | db      |
    +--------+------+---------+
    | wfuser |%     | wf
    +--------+------+---------+

    After making user ID changes, you can ensure they are refreshed by typing the following command at the mysql> prompt:

    FLUSH PRIVILEGES;
  6. Optionally, specify the database you created for the repository by typing the following command at the mysql> prompt:
    use wf

    where:

    wf

    Is the name of the database you will use for WebFOCUS. This is case-sensitive in some environments.

  7. Optionally, confirm there are no tables in the database by typing the following command at the mysql> prompt:
    show tables;

    If you have not yet created tables, you should receive the following:

    Empty set (0.00 sec)

    After creating the repository tables, you can use this to confirm that the tables exist.


Top of page

x
Installing the MySQL JDBC Driver

The MySQL JDBC driver is known as MySQL Connector/J 3.1.

  1. Download the latest MySQL Connector/J 3.1 from:

    http://www.mysql.com/

    The following page contains links to download MySQL Connector/J 3.1:

    http://dev.mysql.com/downloads/connector/j/3.1.html

    Download the latest ZIP or .tar.gz file containing the source code and Java binary. For example:

    mysql-connector-java-3.1.14.zip

    MySQL has an aggressive release cycle, so the number in this file name may vary.

  2. Place the .tar.gz or ZIP file on your UNIX system. If you use FTP, use binary mode.
  3. Extract the MySQL JDBC driver JAR file. This file is located in the archive as:
    mysql-connector-java-3.1.14/mysql-connector-java-3.1.14-bin.jar

    The number in the directory and file name, will match the number in the name of the ZIP file you download. MySQL has an aggressive release cycle, so the number in this file name may vary.

    If you downloaded a ZIP file, you can use the jar command to extract the JAR file. For example:

    jar xvf mysql-connector-java-3.1.14.zip
      mysql-connector-java-3.1.14/mysql-connector-java-3.1.14-bin.jar
  4. Specify the path to and including this JAR file when prompted during the WebFOCUS Client and ReportCaster Distribution Server installation. The path to and including this JAR file must be in the CLASSPATH variable used by your application server the WebFOCUS Client is deployed on and ReportCaster Distribution Server. Specifying the directory containing the JAR file is not sufficient.

WebFOCUS