[MySQL Database Setup Procedures]


JobServer depends on a RDBMS to store and manage its critical data. This document 
will cover the basic steps needed to configure your database such that it is 
operational for Jobserver to use. However, this document is not intended to be a 
thorough discussion of MySQL database configuration, administration, or 
tuning. For example, you will need to insure that your MySQL database is 
configured properly to allow for remote jdbc clients, ...etc. Tuning your 
database will depend, in part, on how large you expect the database tables to 
grow and how many concurrent users and concurrently running jobs you expect
JobServer to manage.


Database Requirements:
----------------------

MySQL versions 4.x and higher are supported. JobServer will operate with either
the standard MySQL storage engine or with the InnoDB storage engine. Since
the InnoDB supports commit/rollback it offers better data integrity but at
a performance cost.


Instructions for setting up MySQL JobServer database:
-----------------------------------------------------


 1) First, you will need to download and install the MySQL database driver MYSQL
    Connector/J 3.x or higher. The download can be found at http://www.mysql.com.
    Place the corresponding jdbc jar into the following directory 
    "jobserver/database/drivers". 

    Next, from the "database" directory run the utility "driverconfig" with 
    the name of the mysql JAR file that you just placed in the directory
    above. For example, if the jdbc jar you placed is named "mysql-connector.jar"
    then you will run the command:

       > driverconfig.sh  mysql-connector.jar  (unix/linux)
       > driverconfig.bat mysql-connector.jar  (windows)

    This will install the MySQL jdbc driver into the JobServer installation.

    Or you can also manually place the MYSQL driver JAR into the following directories:

       jobserver/sys/lib/jsthird
       jobserver/sys/app/webapps/jobserver/WEB-INF/lib


 2) Now you are ready to configure your MySQL database server. Have your
    database administrator create a new database in which to place the JobServer
    tables. You may use an existing database so long as there is no conflict with the
    JobServer's table names. It is, however, recommended that you create a new database
    to avoid potential problems, but this is not required. Make note of the hostname, 
    port number, and database instance name. 

    If you will be running multiple JobServer installations, each must have its own 
    database. 

    Next, have your administrator create a new database user "jsuser" and note the 
    password. You may choose a different name other than "jsuser" if you like.

    To create the JobServer tables you can either do it manually by applying the sql
    file "mysql-jobserver.sql", or you may use the "dbsetup" tool which walks you 
    interactively through the process. In both cases you will need to have access to
    a username/password with administrative rights to create tables, indexes, and
    grant rights.

        i) dbsetup tool:
               If you choose to use the "dbsetup" tool, it will walk you
               through the remaining steps to create the JobServer tables.
               Run the command "dbsetup" found in the "jobserver/database"
               directory and follow its instructions as shown here:
                   
                 > dbsetup.sh  (unix)
                 > dbsetup.bat (windows)

       ii) Manually run script:
               Have your database administrator run the database script 
               "jobserver/database/mysql-jobserver.sql" to create the tables 
               for JobServer. Ideally you should run the script such that the 
               tables are owned by the the user "jsuser". If you use a 
               different username other than "jsuser", you will need to edit 
               the "mysql-jobserver.sql" file and replace all occurrences of 
               "jsuser" with the database username you chose instead.
   

 3) Once the database setup has been completed, the JobServer installation will need to be able to 
    reference the database. The following database properties will need to be defined in the 
    Jobserver "jobserver/conf/system-config.properties" file: 

        dbDriverType=MYSQL         /* Identify that the database used by JobServer as MYSQL     */
        dbHost=localhost           /* Hostname where database is installed                      */
        dbName=somename            /* Name of the database used                                 */
        dbPort=3306                /* Port number to use to connect to database via JDBC        */ 
        dbUsername=jsuser          /* MYSQL user used by JobServer apps to access the database. */
        dbPassword=changeme        /* Password corresponding to jsuser username.                */


 4) Configure the database properties discussed above in the 
    "system-config.properties". Now with the database up and running, check 
    if the database is properly configured and can be accessed by JobServer.
    Run the following command:
    
        > jobserver/bin/jsmonitor.sh  (unix)
        > jobserver/bin/jsmonitor.bat (windows)

    This will tell you if JobServer can detect and connect to the database. 
    If the database status is not shown as "Active" recheck the installation 
    steps to make sure you have configured the database and JobServer properly.


                       [Database Maintenance]


Routine Maintenance
-------------------
It is recommended that you follow and apply the maintenance procedures discussed
in the MySql administration docs. These include:

 - Updating planner statistics
 - Log File Maintenance
 - Backups

Table sizes and fragmentation should be monitored and kept optimal. The tables 
that grow most rapidly will need the most attention. These include the tables 
"js_wbstatus", "js_opstatus", and "js_taudit".


Table Trimming
--------------
The JobServer database tables should normally be trimmed on a periodic basis, to avoid
accumulating excessive amounts of outdated historical data. To do this,  
create a job using the pre-built TaskBean called "JobServerCleanup" that comes with 
the installation. Create, configure, and schedule this Job. Run this job on a regular
basis (for example weekly, monthly, or as needed). It will keep the database tables trimmed
to your specifications by removing outdated historical records from the various database
tables including outdated log files from the filesystem.


Backup
------
Backup database as necessary. Incremental backup is recommended with periodic 
full backup.